20250429-api-cache.sql

-- UserApiCache 主表
CREATE TABLE `UserApiCache` (
    `Id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `UserApiKeyId` INTEGER NOT NULL,
    `ModelId` INTEGER NOT NULL,
    `RequestHashCode` INTEGER NOT NULL,
    `Expires` DATETIME NOT NULL,
    `ClientInfoId` INTEGER NOT NULL,
    `CreatedAt` DATETIME NOT NULL,
    FOREIGN KEY (`ClientInfoId`) REFERENCES `ClientInfo`(`Id`),
    FOREIGN KEY (`ModelId`) REFERENCES `Model`(`Id`),
    FOREIGN KEY (`UserApiKeyId`) REFERENCES `UserApiKey`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX `IX_UserApiCache_Expires`
    ON `UserApiCache`(`Expires`);

CREATE INDEX `IX_UserApiCache_RequestHashCode`
    ON `UserApiCache`(`RequestHashCode`);

CREATE INDEX `IX_UserApiCache_ClientInfoId`
    ON `UserApiCache`(`ClientInfoId`);

CREATE INDEX `IX_UserApiCache_ModelId`
    ON `UserApiCache`(`ModelId`);

CREATE INDEX `IX_UserApiCache_UserApiKeyId`
    ON `UserApiCache`(`UserApiKeyId`);

-- UserApiCacheBody 表
CREATE TABLE `UserApiCacheBody` (
    `UserApiCacheId` INTEGER PRIMARY KEY,
    `Request` TEXT NOT NULL,
    `Response` TEXT NOT NULL,
    FOREIGN KEY (`UserApiCacheId`) REFERENCES `UserApiCache`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- UserApiCacheUsage 表
CREATE TABLE `UserApiCacheUsage` (
    `Id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `UserApiCacheId` INTEGER NOT NULL,
    `ClientInfoId` INTEGER NOT NULL,
    `UsedAt` DATETIME NOT NULL,
    FOREIGN KEY (`UserApiCacheId`) REFERENCES `UserApiCache`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`ClientInfoId`) REFERENCES `ClientInfo`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 若需为 UserApiCacheUsage 增加索引（根据实际查询需要）可另加：
-- CREATE INDEX `IX_UserApiCacheUsage_UserApiCacheId` ON `UserApiCacheUsage`(`UserApiCacheId`);
-- CREATE INDEX `IX_UserApiCacheUsage_ClientInfoId` ON `UserApiCacheUsage`(`ClientInfoId`);