-- 创建project_template_bindings表
-- 用于管理项目和模板之间的多对多绑定关系

CREATE TABLE IF NOT EXISTS project_template_bindings (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    template_id TEXT NOT NULL,
    binding_name TEXT,  -- 绑定的自定义名称
    description TEXT,
    priority INTEGER NOT NULL DEFAULT 0,  -- 绑定优先级，数值越小优先级越高
    is_active BOOLEAN NOT NULL DEFAULT 1,
    binding_type TEXT NOT NULL DEFAULT '"Primary"',  -- JSON格式的BindingType
    binding_status TEXT NOT NULL DEFAULT '"Active"',  -- JSON格式的BindingStatus
    metadata TEXT,  -- JSON格式的额外元数据
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE,
    UNIQUE(project_id, template_id)  -- 确保同一项目不能重复绑定同一模板
);

-- 创建索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_project_id ON project_template_bindings (project_id);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_template_id ON project_template_bindings (template_id);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_binding_type ON project_template_bindings (binding_type);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_binding_status ON project_template_bindings (binding_status);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_is_active ON project_template_bindings (is_active);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_priority ON project_template_bindings (priority);
CREATE INDEX IF NOT EXISTS idx_project_template_bindings_created_at ON project_template_bindings (created_at);
