import type { GpLiveDb } from './gpLiveDb'
import { isPostgresDb } from './gpLiveDb'
import { unixNowSql } from './sqlDialect'

/**
 * 建立核心表結構（SQLite / PostgreSQL 共用邏輯）。
 * 後續欄位演進仍由 userSchemaMigration 處理（Postgres 路徑會透過 sqlDialect 轉譯 PRAGMA 等）。
 */
export async function initCoreSchema(db: GpLiveDb): Promise<void> {
    const dialect = db.dialect
    const UNIX_SEC_SQL = unixNowSql(dialect)

    if (dialect === 'sqlite') {
        await db.exec(`PRAGMA journal_mode = WAL;`)
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }

    const groupLogsIdCol =
        dialect === 'postgres'
            ? 'log_id BIGSERIAL PRIMARY KEY'
            : 'log_id INTEGER PRIMARY KEY AUTOINCREMENT'

    await db.exec(`
        CREATE TABLE IF NOT EXISTS users (
            user_id TEXT PRIMARY KEY,
            display_id TEXT NOT NULL UNIQUE,
            display_name TEXT NOT NULL,
            account TEXT NOT NULL UNIQUE,
            password_hash TEXT,
            email TEXT UNIQUE,
            avatar_url TEXT,
            banner_url TEXT,
            has_change_nickname INTEGER NOT NULL DEFAULT 0,
            is_verified INTEGER NOT NULL DEFAULT 0,
            coin_balance INTEGER NOT NULL DEFAULT 0,
            streamer_exp INTEGER NOT NULL DEFAULT 0,
            rich_exp INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );

        CREATE TABLE IF NOT EXISTS user_site_admin (
            user_id TEXT PRIMARY KEY,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_vods (
            vod_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            title TEXT NOT NULL,
            thumbnail_url TEXT,
            duration TEXT,
            category TEXT,
            tags TEXT,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS room_streams (
            room_id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            description TEXT,
            thumbnail_url TEXT,
            live_input_identifier TEXT,
            stream_token TEXT,
            chat_disabled INTEGER NOT NULL DEFAULT 0,
            stream_status TEXT NOT NULL DEFAULT 'disconnected',
            category TEXT,
            attribute TEXT,
            started_at INTEGER,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER,
            FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS room_pinned_message (
            room_id TEXT PRIMARY KEY,
            message_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            contents TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS user_board_posts (
            post_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            content TEXT NOT NULL,
            thumbnail_url TEXT,
            is_pinned INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS room_stream_likes (
            room_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (room_id, user_id),
            FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_actions (
            user_id TEXT NOT NULL,
            target_id TEXT NOT NULL,
            action_type TEXT NOT NULL CHECK(action_type IN ('follow', 'block')),
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (user_id, target_id, action_type),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS room_bans (
            room_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            banned_by TEXT NOT NULL,
            reason TEXT,
            duration_ms INTEGER,
            banned_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            expires_at INTEGER,
            PRIMARY KEY (room_id, user_id),
            FOREIGN KEY(room_id) REFERENCES room_streams(room_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(banned_by) REFERENCES users(user_id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS user_login_history (
            history_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            ip_address TEXT NOT NULL,
            country_code TEXT,
            user_agent TEXT,
            device_type TEXT,
            device_name TEXT,
            connect_at INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_login_sessions (
            session_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            user_agent TEXT,
            device_type TEXT,
            device_name TEXT,
            last_login_at INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_allowed_devices (
            device_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            session_id TEXT,
            device_type TEXT,
            device_name TEXT,
            user_agent TEXT,
            register_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_login_settings (
            user_id TEXT PRIMARY KEY,
            login_notice INTEGER NOT NULL DEFAULT -11,
            foreign_login_block INTEGER NOT NULL DEFAULT 0,
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_notification_settings (
            notification_setting_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            push_enabled INTEGER NOT NULL DEFAULT 0 CHECK (push_enabled IN (0, 1)),
            record_enabled INTEGER NOT NULL DEFAULT 0 CHECK (record_enabled IN (0, 1)),
            PRIMARY KEY (user_id, notification_setting_id),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_notifications (
            notification_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            type TEXT NOT NULL,
            title TEXT NOT NULL,
            content TEXT,
            redirect_url TEXT,
            is_read INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS system_notices (
            notice_id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            content TEXT,
            redirect_url TEXT,
            category TEXT NOT NULL DEFAULT 'general',
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );

        CREATE TABLE IF NOT EXISTS index_event_banners (
            event_id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            subtitle TEXT,
            banner_url TEXT,
            redirect_url TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );

        CREATE TABLE IF NOT EXISTS room_mods (
            room_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            added_by TEXT NOT NULL,
            added_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (room_id, user_id),
            FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(added_by) REFERENCES users(user_id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS catch (
            catch_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            thumbnail_url TEXT,
            video_stream_url TEXT,
            tags TEXT,
            is_show INTEGER NOT NULL DEFAULT 1,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS catch_likes (
            catch_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (catch_id, user_id),
            FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS config_room_gifts (
            gift_id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            is_lottery INTEGER NOT NULL DEFAULT 0,
            lottery_url TEXT,
            amount INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );

        CREATE TABLE IF NOT EXISTS config_avatar_frames (
            item_id TEXT PRIMARY KEY,
            name TEXT NOT NULL DEFAULT '',
            img_url TEXT NOT NULL,
            type TEXT NOT NULL CHECK (type IN ('IMG', 'LOTTERY')),
            tag TEXT NOT NULL DEFAULT '',
            amount INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );

        CREATE TABLE IF NOT EXISTS user_avatar_frames (
            user_id TEXT NOT NULL,
            frame_id TEXT NOT NULL,
            is_used INTEGER NOT NULL DEFAULT 0,
            acquired_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            expiration_at INTEGER,
            PRIMARY KEY (user_id, frame_id),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(frame_id) REFERENCES config_avatar_frames(item_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS rank_rich (
            rank_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            target_id TEXT,
            gift_id TEXT,
            gift_count INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE SET NULL,
            FOREIGN KEY(gift_id) REFERENCES config_room_gifts(gift_id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS rank_stream (
            rank_id TEXT PRIMARY KEY,
            target_id TEXT,
            user_id TEXT NOT NULL,
            gift_id TEXT,
            gift_count INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE SET NULL,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(gift_id) REFERENCES config_room_gifts(gift_id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS groups (
            group_id TEXT PRIMARY KEY,
            display_id TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            avatar_url TEXT,
            banner_url TEXT,
            is_verified INTEGER NOT NULL DEFAULT 0,
            is_can_application INTEGER NOT NULL DEFAULT 1,
            group_level_exp INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL})
        );
        CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_display_id ON groups(display_id);

        CREATE TABLE IF NOT EXISTS user_groups (
            user_id TEXT NOT NULL,
            group_id TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'member',
            contribution INTEGER NOT NULL DEFAULT 0,
            join_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (user_id, group_id),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS group_apply (
            group_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            application_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (user_id, group_id),
            FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS group_logs (
            ${groupLogsIdCol},
            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_SEC_SQL}),
            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);

        CREATE TABLE IF NOT EXISTS catch_comments (
            comment_id TEXT PRIMARY KEY,
            catch_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            contents TEXT NOT NULL,
            parent_id TEXT,
            reply_to TEXT,
            is_pinned INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL,
            edited_at INTEGER,
            FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(parent_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS catch_comment_likes (
            comment_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            PRIMARY KEY (comment_id, user_id),
            FOREIGN KEY(comment_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS user_board_post_comments (
            comment_id TEXT PRIMARY KEY,
            post_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            content TEXT NOT NULL,
            parent_id TEXT,
            created_at INTEGER NOT NULL DEFAULT (${UNIX_SEC_SQL}),
            edited_at INTEGER,
            FOREIGN KEY(post_id) REFERENCES user_board_posts(post_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(parent_id) REFERENCES user_board_post_comments(comment_id) ON DELETE CASCADE
        );
    `)

    await ensureOptionalColumns(db)
}

async function ensureOptionalColumns(db: GpLiveDb): Promise<void> {
    const userVodCols = await db.all<{ name: string }>(`PRAGMA table_info(user_vods);`)
    const userVodColNames = new Set((userVodCols ?? []).map((c) => c.name))
    if (userVodCols && userVodCols.length > 0 && !userVodColNames.has('tags')) {
        await db.exec(`ALTER TABLE user_vods ADD COLUMN tags TEXT;`)
    }

    if (isPostgresDb(db)) {
        return
    }

    try {
        const hasRooms = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'rooms' LIMIT 1`
        )
        if (hasRooms?.name) {
            await db.exec(`PRAGMA foreign_keys = OFF;`)
            await db.exec(`DROP TABLE IF EXISTS rooms;`)
            await db.exec(`PRAGMA foreign_keys = ON;`)
        }
    } catch {
        // ignore
    }

    try {
        const sysNoticeCols = await db.all<{ name: string }>(`PRAGMA table_info(system_notices);`)
        const sysNoticeColNames = new Set((sysNoticeCols ?? []).map((c) => c.name))
        if (sysNoticeCols && sysNoticeCols.length > 0 && !sysNoticeColNames.has('redirect_url')) {
            await db.exec(`ALTER TABLE system_notices ADD COLUMN redirect_url TEXT;`)
        }
    } catch (e: unknown) {
        const msg = e && typeof e === 'object' && 'message' in e ? String((e as { message: string }).message) : ''
        if (!msg.includes('no such table')) {
            console.error('Migration error for system_notices.redirect_url column:', e)
        }
    }
}