import type { getDb } from '@/server/db'

type Db = Awaited<ReturnType<typeof getDb>>

const UNIX_NOW = `CAST(strftime('%s','now') AS INTEGER)`

/** 家族操作日誌 */
export async function migrateGroupLogsTableSchema(db: Db) {
    await db.exec(`
        CREATE TABLE IF NOT EXISTS group_logs (
            log_id INTEGER PRIMARY KEY AUTOINCREMENT,
            group_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            target_id TEXT,
            action_content TEXT NOT NULL,
            operation_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
            FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE SET NULL
        );
        CREATE INDEX IF NOT EXISTS idx_group_logs_group_operation
            ON group_logs(group_id, operation_at DESC);
    `)
}