20250310-chat-config.sql

-- 创建主表
CREATE TABLE ChatConfig (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    HashCode INTEGER NOT NULL DEFAULT 0,
    ModelId INTEGER NOT NULL,
    SystemPrompt TEXT,
    Temperature REAL,
    WebSearchEnabled INTEGER NOT NULL,
    MaxOutputTokens INTEGER,
    ReasoningEffort INTEGER NOT NULL,
    FOREIGN KEY (ModelId) REFERENCES Model(Id)
);

-- 创建索引（SQLite自动为主键创建索引）
CREATE INDEX IX_ChatConfig_ModelId ON ChatConfig (ModelId);
CREATE INDEX IX_ChatConfig_HashCode ON ChatConfig (HashCode) WHERE HashCode != 0;

-- 创建临时表
CREATE TEMP TABLE TempChatSpan (
    RowNumber INTEGER PRIMARY KEY AUTOINCREMENT,
    ChatId INTEGER,
    SpanId INTEGER,
    HashCode INTEGER,
    ModelId INTEGER,
    Temperature REAL,
    WebSearchEnabled INTEGER,
    SystemPrompt TEXT,
    MaxOutputTokens INTEGER,
    ReasoningEffort INTEGER
);

-- 插入数据到临时表（重写OUTER APPLY为LEFT JOIN）
INSERT INTO TempChatSpan (ChatId, SpanId, HashCode, ModelId, Temperature, WebSearchEnabled, SystemPrompt, MaxOutputTokens, ReasoningEffort)
SELECT
    cs.ChatId,
    cs.SpanId,
    0 AS HashCode,
    cs.ModelId,
    cs.Temperature,
    cs.EnableSearch AS WebSearchEnabled,
    COALESCE(SystemPrompt.Content, DefaultPrompt.Content) AS SystemPrompt,
    NULL AS MaxOutputTokens,
    0 AS ReasoningEffort
FROM ChatSpan cs
INNER JOIN Chat c ON cs.ChatId = c.Id
LEFT JOIN (
    SELECT m.ChatId, mct.Content
    FROM Message m
    INNER JOIN MessageContent mc ON m.Id = mc.MessageId
    INNER JOIN MessageContentText mct ON mc.Id = mct.Id
    WHERE m.ChatRoleId = 1
    GROUP BY m.ChatId
    ORDER BY MIN(m.CreatedAt)
) AS SystemPrompt ON SystemPrompt.ChatId = cs.ChatId
LEFT JOIN (
    SELECT Content
    FROM Prompt
    WHERE IsSystem = 1 AND IsDefault = 1
    LIMIT 1
) AS DefaultPrompt ON 1=1;

-- 插入数据到ChatConfig（SQLite不需要IDENTITY_INSERT）
INSERT INTO ChatConfig (Id, HashCode, ModelId, SystemPrompt, Temperature, WebSearchEnabled, MaxOutputTokens, ReasoningEffort)
SELECT
    RowNumber,
    HashCode,
    ModelId,
    SystemPrompt,
    Temperature,
    WebSearchEnabled,
    MaxOutputTokens,
    ReasoningEffort
FROM TempChatSpan;

-- 重建ChatSpan表（SQLite需要分步处理）
CREATE TABLE Tmp_ChatSpan (
    ChatId INTEGER NOT NULL,
    SpanId INTEGER NOT NULL,
    Enabled INTEGER NOT NULL DEFAULT 1,
    ChatConfigId INTEGER,
    PRIMARY KEY (ChatId, SpanId),
    FOREIGN KEY (ChatConfigId) REFERENCES ChatConfig(Id)
);

INSERT INTO Tmp_ChatSpan (ChatId, SpanId)
SELECT ChatId, SpanId FROM ChatSpan;

DROP TABLE ChatSpan;
ALTER TABLE Tmp_ChatSpan RENAME TO ChatSpan;

-- 创建索引和外键
CREATE INDEX IX_ChatSpan_ChatConfigId ON ChatSpan (ChatConfigId);

-- 更新ChatConfigId（使用CTE）
WITH ChatSpanUpdate AS (
    SELECT 
        cs.rowid,
        t.RowNumber AS NewChatConfigId
    FROM ChatSpan cs
    INNER JOIN TempChatSpan t ON cs.ChatId = t.ChatId AND cs.SpanId = t.SpanId
)
UPDATE ChatSpan
SET ChatConfigId = (
    SELECT NewChatConfigId 
    FROM ChatSpanUpdate 
    WHERE ChatSpan.rowid = ChatSpanUpdate.rowid
);

-- 添加非空约束（需要重建表）
CREATE TABLE Tmp_ChatSpan_v2 (
    ChatId INTEGER NOT NULL,
    SpanId INTEGER NOT NULL,
    Enabled INTEGER NOT NULL DEFAULT 1,
    ChatConfigId INTEGER NOT NULL,
    PRIMARY KEY (ChatId, SpanId),
    FOREIGN KEY (ChatConfigId) REFERENCES ChatConfig(Id)
);

INSERT INTO Tmp_ChatSpan_v2
SELECT * FROM ChatSpan WHERE ChatConfigId IS NOT NULL;

DROP TABLE ChatSpan;
ALTER TABLE Tmp_ChatSpan_v2 RENAME TO ChatSpan;

-- 创建MessageResponse表
CREATE TABLE MessageResponse (
    MessageId INTEGER PRIMARY KEY,
    UsageId INTEGER NOT NULL,
    ReactionId INTEGER,
    ChatConfigId INTEGER NOT NULL,
    FOREIGN KEY (MessageId) REFERENCES Message(Id) ON DELETE CASCADE,
    FOREIGN KEY (UsageId) REFERENCES UserModelUsage(Id),
    FOREIGN KEY (ChatConfigId) REFERENCES ChatConfig(Id)
);

CREATE INDEX IX_MessageResponse_UsageId ON MessageResponse (UsageId);
CREATE INDEX IX_MessageResponse_ChatConfigId ON MessageResponse (ChatConfigId);

-- 插入数据到MessageResponse
INSERT INTO MessageResponse (MessageId, UsageId, ReactionId, ChatConfigId)
SELECT
    m.Id,
    m.UsageId,
    m.ReactionId,
    sub.ChatConfigId
FROM Message m
INNER JOIN (
    SELECT 
        m.Id,
        cc.Id AS ChatConfigId,
        ROW_NUMBER() OVER (
            PARTITION BY m.Id
            ORDER BY CASE WHEN cs.SpanId = m.SpanId THEN 0 ELSE 1 END,
                     cc.Id DESC
        ) AS rn
    FROM Message m
    INNER JOIN ChatSpan cs ON m.ChatId = cs.ChatId
    INNER JOIN ChatConfig cc ON cs.ChatConfigId = cc.Id
    WHERE m.ChatRoleId = 3
) AS sub ON m.Id = sub.Id AND sub.rn = 1;

-- 删除旧列（需要重建表）
CREATE TABLE Tmp_Message (
    Id INTEGER PRIMARY KEY,
    ChatId INTEGER NOT NULL,
    SpanId INTEGER,
    CreatedAt DATETIME NOT NULL,
    ChatRoleId INTEGER NOT NULL,
    FOREIGN KEY (ChatId) REFERENCES Chat(Id),
    FOREIGN KEY (ChatRoleId) REFERENCES ChatRole(Id)
);

INSERT INTO Tmp_Message
SELECT Id, ChatId, SpanId, CreatedAt, ChatRoleId
FROM Message;

DROP TABLE Message;
ALTER TABLE Tmp_Message RENAME TO Message;

-- 清理数据
UPDATE Chat SET LeafMessageId = NULL
WHERE EXISTS (
    SELECT 1 FROM Message 
    WHERE Chat.LeafMessageId = Message.Id 
    AND Message.ChatRoleId = 1
);

DELETE FROM Message WHERE ChatRoleId = 1;
DELETE FROM ChatRole WHERE id = 1;