import 'server-only'

/**
 * 已改名／定案之表名與常用 SQL。應用程式請由此引用，勿再寫 login_sessions、login_history、event_banners、gifts 等舊表名。
 */
export const USER_AUTH_TABLES = {
    userLoginSessions: 'user_login_sessions',
    userLoginHistory: 'user_login_history',
    userAllowedDevices: 'user_allowed_devices',
    indexEventBanners: 'index_event_banners',
    configRoomGifts: 'config_room_gifts',
} as const

const S = USER_AUTH_TABLES.userLoginSessions

export const USER_AUTH_SQL = {
    /** Cookie 中的 session_id → 目前 user_id */
    selectSessionUserId: `SELECT user_id AS userId FROM ${S} WHERE session_id = ?`,
    deleteSessionById: `DELETE FROM ${S} WHERE session_id = ?`,
    insertSession: `INSERT INTO ${S} (
                session_id, user_id, user_agent, device_type, device_name, last_login_at, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?)`,
    listSessionsByUser: `SELECT session_id AS id, device_type AS deviceType, device_name AS deviceName, user_agent AS userAgent, last_login_at AS lastLoginAt
             FROM ${S}
             WHERE user_id = ?
             ORDER BY last_login_at DESC`,
    deleteOtherSessions: `DELETE FROM ${S}
                 WHERE user_id = ? AND session_id != ?`,
    deleteSessionByIdAndUser: `DELETE FROM ${S}
                 WHERE session_id = ? AND user_id = ?`,
    selectSessionForMyDevices: `SELECT session_id AS id, device_type AS deviceType, device_name AS deviceName, user_agent AS userAgent
             FROM ${S}
             WHERE session_id = ? AND user_id = ?`,
} as const

const H = USER_AUTH_TABLES.userLoginHistory

export const USER_LOGIN_HISTORY_SQL = {
    insert: `INSERT INTO ${H} (
                history_id, user_id, ip_address, country_code, user_agent, device_type, device_name, connect_at, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    countInPeriod: `SELECT COUNT(*) as count
             FROM ${H}
             WHERE user_id = ? AND created_at >= ?`,
    maxIpInPeriod: `SELECT ip_address, COUNT(*) as count
             FROM ${H}
             WHERE user_id = ? AND created_at >= ?
             GROUP BY ip_address
             ORDER BY count DESC
             LIMIT 1`,
    pageInPeriod: `SELECT history_id, connect_at, ip_address, country_code
             FROM ${H}
             WHERE user_id = ? AND created_at >= ?
             ORDER BY created_at DESC
             LIMIT ? OFFSET ?`,
} as const

const EB = USER_AUTH_TABLES.indexEventBanners

export const INDEX_EVENT_BANNERS_SQL = {
    selectActive: `SELECT event_id, title, subtitle, banner_url, redirect_url, created_at
             FROM ${EB}
             WHERE is_active = 1
             ORDER BY updated_at DESC, created_at DESC, event_id ASC
             LIMIT 50`,
} as const