from __future__ import annotations

import sqlite3
import threading
from contextlib import contextmanager

from src.config import settings

_lock = threading.RLock()

DEFAULT_SETTINGS = {

    "welcome_text": (
        "Halo, <b>{name}</b>! 👋\n\n"
        "👤 Username: {username}\n"
        "🆔 ID User: <code>{user_id}</code>\n"
        "📅 Waktu Start: {start_time}\n"
        "🗓 Terdaftar Sejak: {joined_at}\n\n"
        "Selamat datang di <b>Media Viralinmas</b>.\n"
# <<<<<<< HEAD
#         "Dapatkan video melalui link khusus dari channel utama."
        
# =======
        "Dapatkan video melalui link khusus dari channel utama.\n\n"
        "👥 Total Pengguna Bot: <b>{total_users}</b>"
# >>>>>>> 8677ab1
    ),

    "channel_caption_template": (
        "🎬 <i>Nama Media:</i> <b><i>{title}</i></b>\n\n"
        "💎 <i>Join VIP untuk mendapatkan update video viral lainnya:</i>\n"
        "👉 <a href=\"https://t.me/viralinmasbot\"><b><i>ORDER VIP VIA BOT</i></b></a>\n\n"
        "🔗 <b>Tonton Video:</b>\n"
        "{link}\n\n"
        "<i>Diposting oleh "
        "<a href=\"https://t.me/asupan_viralinmas\">Viralinmas</a></i>"
    ),

    "default_video_caption": (
        "🎥 <b><i>Asupan Viralinmas</i></b>\n\n"
        "💎 <b><i>JOIN VIP untuk update video setiap hari:</i></b>\n"
        "👉 <a href=\"https://t.me/viralinmasbot\">t.me/viralinmasbot</a>\n\n"
        "👁 Ditonton: <b>{total_views}</b> kali\n"
        "👥 Penonton: <b>{total_viewers}</b> orang\n\n"
        "<blockquote>"
        "<b><i>NOTE:</i></b>\n"
        "<i>Video akan hilang dari chat ini dalam 2 jam.\n"
        "Klik kembali link media untuk melihatnya lagi.</i>"
        "</blockquote>"
    ),

    "owner_link": "https://t.me/masvir77",
    "main_channel_link": "https://t.me/mediaviralinmas",
    "donation_enabled": "1",
    "maintenance_mode": "0",
    "maintenance_text": "🔧 Bot sedang dalam pemeliharaan. Silakan coba kembali beberapa saat lagi.",
    "auto_backup_enabled": "1",
    "backup_retention": "7",
}

@contextmanager
def connect():
    with _lock:
        con = sqlite3.connect(settings.database_path, timeout=30)
        con.row_factory = sqlite3.Row
        con.execute("PRAGMA foreign_keys=ON")
        con.execute("PRAGMA journal_mode=WAL")
        try:
            yield con
            con.commit()
        except Exception:
            con.rollback()
            raise
        finally:
            con.close()


def _column_exists(con: sqlite3.Connection, table: str, column: str) -> bool:
    return any(row["name"] == column for row in con.execute(f"PRAGMA table_info({table})"))


def _ensure_column(con: sqlite3.Connection, table: str, definition: str) -> None:
    column = definition.split()[0]
    if not _column_exists(con, table, column):
        con.execute(f"ALTER TABLE {table} ADD COLUMN {definition}")


def init_db():
    schema = """
    CREATE TABLE IF NOT EXISTS users(
        id INTEGER PRIMARY KEY,
        username TEXT,
        full_name TEXT,
        is_banned INTEGER DEFAULT 0,
        joined_at TEXT DEFAULT CURRENT_TIMESTAMP,
        last_seen TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS categories(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE COLLATE NOCASE,
        active INTEGER DEFAULT 1,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS caption_templates(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE COLLATE NOCASE,
        content TEXT NOT NULL,
        is_default INTEGER DEFAULT 0,
        is_system INTEGER DEFAULT 0,
        default_content TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS media(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL,
        telegram_file_id TEXT NOT NULL,
        thumbnail_file_id TEXT,
        title TEXT NOT NULL,
        user_caption TEXT NOT NULL,
        filename TEXT,
        category_id INTEGER NOT NULL,
        channel_message_id INTEGER,
        channel_chat_id INTEGER,
        active INTEGER DEFAULT 1,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(category_id) REFERENCES categories(id)
    );
    CREATE TABLE IF NOT EXISTS media_views(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        media_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        viewed_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(media_id) REFERENCES media(id) ON DELETE CASCADE
    );
    CREATE TABLE IF NOT EXISTS force_sub_channels(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        chat_id TEXT NOT NULL UNIQUE,
        title TEXT NOT NULL,
        invite_link TEXT NOT NULL,
        button_text TEXT NOT NULL,
        active INTEGER DEFAULT 1,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS settings(
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        default_value TEXT,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS welcome_buttons(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        label TEXT NOT NULL,
        url TEXT NOT NULL DEFAULT '',
        setting_key TEXT,
        default_label TEXT,
        default_url TEXT,
        is_system INTEGER DEFAULT 0,
        position INTEGER DEFAULT 0,
        active INTEGER DEFAULT 1
    );
    CREATE TABLE IF NOT EXISTS star_donations(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        amount INTEGER NOT NULL,
        telegram_payment_charge_id TEXT NOT NULL UNIQUE,
        provider_payment_charge_id TEXT,
        invoice_payload TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS delete_jobs(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        chat_id INTEGER NOT NULL,
        message_id INTEGER NOT NULL,
        run_at TEXT NOT NULL,
        UNIQUE(chat_id,message_id)
    );
    CREATE TABLE IF NOT EXISTS broadcast_templates(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        content_type TEXT NOT NULL DEFAULT 'text',
        file_id TEXT,
        caption TEXT NOT NULL DEFAULT '',
        button_text TEXT NOT NULL DEFAULT '',
        button_url TEXT NOT NULL DEFAULT '',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    """

    with connect() as con:
        con.executescript(schema)
        _ensure_column(con, "settings", "default_value TEXT")
        _ensure_column(con, "settings", "updated_at TEXT")
        _ensure_column(con, "caption_templates", "is_system INTEGER DEFAULT 0")
        _ensure_column(con, "caption_templates", "default_content TEXT")
        _ensure_column(con, "welcome_buttons", "setting_key TEXT")
        _ensure_column(con, "welcome_buttons", "default_label TEXT")
        _ensure_column(con, "broadcast_templates", "caption TEXT NOT NULL DEFAULT ''")
        _ensure_column(con, "broadcast_templates", "button_text TEXT NOT NULL DEFAULT ''")
        _ensure_column(con, "broadcast_templates", "button_url TEXT NOT NULL DEFAULT ''")
        _ensure_column(con, "welcome_buttons", "default_url TEXT")
        _ensure_column(con, "welcome_buttons", "is_system INTEGER DEFAULT 0")

        con.execute("INSERT OR IGNORE INTO categories(name) VALUES('masvir')")

        for key, value in DEFAULT_SETTINGS.items():
            con.execute(
                """
                INSERT INTO settings(key, value, default_value)
                VALUES(?, ?, ?)
                ON CONFLICT(key) DO UPDATE SET
                    default_value=COALESCE(settings.default_value, excluded.default_value)
                """,
                (key, value, value),
            )

        # Migrasi welcome_text: perbarui jika belum ada placeholder {total_users}
        new_welcome = DEFAULT_SETTINGS["welcome_text"]
        con.execute(
            "UPDATE settings SET value=?, default_value=? WHERE key='welcome_text' AND value NOT LIKE '%{total_users}%'",
            (new_welcome, new_welcome),
        )

        # Migrasi default_video_caption: perbarui jika belum ada {total_views} atau masih format lama (dengan |)
        new_video_caption = DEFAULT_SETTINGS["default_video_caption"]
        con.execute(
            "UPDATE settings SET value=?, default_value=? WHERE key='default_video_caption' AND (value NOT LIKE '%{total_views}%' OR value LIKE '%kali  |  %')",
            (new_video_caption, new_video_caption),
        )

        con.execute(
            """
            INSERT INTO caption_templates(
                name, content, is_default, is_system, default_content
            ) VALUES('Caption Default', ?, 1, 1, ?)
            ON CONFLICT(name) DO UPDATE SET
                is_system=1,
                default_content=COALESCE(caption_templates.default_content, excluded.default_content)
            """,
            (
                DEFAULT_SETTINGS["default_video_caption"],
                DEFAULT_SETTINGS["default_video_caption"],
            ),
        )

        system_buttons = [
            ("📢 Channel Utama", "main_channel_link", 0),
            ("👤 Owner", "owner_link", 1),
            ("⭐ Donasi Bintang", "donation_enabled", 2),
        ]
        # Migrasi tombol Bantuan lama menjadi Donasi Bintang.
        con.execute(
            """
            UPDATE welcome_buttons
            SET label='⭐ Donasi Bintang',
                setting_key='donation_enabled',
                default_label='⭐ Donasi Bintang',
                default_url='',
                url='',
                is_system=1,
                active=1
            WHERE setting_key='help_link'
            """
        )

        for label, setting_key, position in system_buttons:
            default_url = DEFAULT_SETTINGS[setting_key]
            exists = con.execute(
                "SELECT id FROM welcome_buttons WHERE setting_key=?",
                (setting_key,),
            ).fetchone()
            if exists:
                con.execute(
                    """
                    UPDATE welcome_buttons
                    SET is_system=1,
                        default_label=COALESCE(default_label, ?),
                        default_url=COALESCE(default_url, ?)
                    WHERE setting_key=?
                    """,
                    (label, default_url, setting_key),
                )
            else:
                con.execute(
                    """
                    INSERT INTO welcome_buttons(
                        label, url, setting_key, default_label, default_url,
                        is_system, position, active
                    ) VALUES(?, '', ?, ?, ?, 1, ?, 1)
                    """,
                    (label, setting_key, label, default_url, position),
                )