import secrets
import sqlite3
from datetime import datetime

DB_NAME = "cinema.db"

# وضعیت‌هایی که جزو ظرفیت اشغال‌شده حساب می‌شوند.
# اگر می‌خواهید سفارش‌های در انتظار رسید هم جا را نگه دارند،
# "waiting_receipt" را به این لیست اضافه کنید (در این صورت باید رزروهای
# بدون رسید به‌صورت زمان‌دار آزاد شوند).
COUNTED_STATUSES = ("pending_review", "approved")

# کد بلیت: ۵ کاراکتر از حروف بزرگ انگلیسی و ارقام.
# کاراکترهای شبیه هم (0/O و 1/I/L) حذف شده‌اند تا موقع خواندن و تایپ اشتباه نشود.
CODE_ALPHABET = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ"
CODE_LENGTH = 5


def connect():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def _columns(con, table):
    return {row[1] for row in con.execute(f"PRAGMA table_info({table})").fetchall()}


def _add_missing_columns(con, table, definitions):
    existing = _columns(con, table)
    for name, definition in definitions.items():
        if name not in existing:
            con.execute(f"ALTER TABLE {table} ADD COLUMN {name} {definition}")


def init_db():
    with connect() as con:
        con.execute("""
        CREATE TABLE IF NOT EXISTS shows (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            cinema TEXT NOT NULL,
            show_date TEXT NOT NULL,
            ticket_price INTEGER NOT NULL DEFAULT 100000,
            active INTEGER NOT NULL DEFAULT 1,
            capacity INTEGER NOT NULL DEFAULT 100,
            created_at TEXT NOT NULL
        )
        """)

        con.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            username TEXT,
            buyer_name TEXT NOT NULL,
            phone TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            unit_price INTEGER NOT NULL,
            total_price INTEGER NOT NULL,
            status TEXT NOT NULL DEFAULT 'waiting_receipt',
            receipt_file_id TEXT,
            ticket_codes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            show_id INTEGER,
            show_title TEXT,
            show_cinema TEXT,
            show_date TEXT
        )
        """)

        # ارتقای دیتابیس‌های قدیمی بدون حذف سفارش‌ها یا سانس‌های قبلی
        _add_missing_columns(con, "orders", {
            "username": "TEXT",
            "receipt_file_id": "TEXT",
            "ticket_codes": "TEXT",
            "show_id": "INTEGER",
            "show_title": "TEXT",
            "show_cinema": "TEXT",
            "show_date": "TEXT",
            "updated_at": "TEXT",
        })

        existing_shows = _columns(con, "shows")
        if "ticket_price" not in existing_shows:
            con.execute(
                "ALTER TABLE shows ADD COLUMN ticket_price INTEGER NOT NULL DEFAULT 100000"
            )
        # ظرفیت سالن؛ سانس‌های قدیمی ظرفیت ۱۰۰ می‌گیرند
        if "capacity" not in existing_shows:
            con.execute(
                "ALTER TABLE shows ADD COLUMN capacity INTEGER NOT NULL DEFAULT 100"
            )
        # اگر ستون capacity از قبل (بدون مقدار پیش‌فرض) وجود داشته،
        # سانس‌های قدیمی مقدار NULL دارند؛ آن‌ها را هم ۱۰۰ می‌کنیم
        con.execute(
            "UPDATE shows SET capacity = 100 WHERE capacity IS NULL OR capacity <= 0"
        )

        # جدول بلیت‌ها: هر بلیت یک ردیف دارد تا ورود هر بلیت جداگانه ثبت شود
        con.execute("""
        CREATE TABLE IF NOT EXISTS tickets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            code TEXT NOT NULL UNIQUE,
            used INTEGER NOT NULL DEFAULT 0,
            used_at TEXT,
            used_by INTEGER
        )
        """)

        # بلیت‌های قبلاً صادرشده (قبل از این تغییر) را هم وارد جدول می‌کنیم
        old_rows = con.execute(
            "SELECT id, ticket_codes FROM orders "
            "WHERE status = 'approved' AND ticket_codes IS NOT NULL"
        ).fetchall()
        for r in old_rows:
            for code in r["ticket_codes"].split(","):
                code = code.strip()
                if code:
                    con.execute(
                        "INSERT OR IGNORE INTO tickets (order_id, code) VALUES (?, ?)",
                        (r["id"], code),
                    )


def add_show(title, cinema, show_date, ticket_price, capacity):
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    with connect() as con:
        cur = con.execute(
            """
            INSERT INTO shows
            (title, cinema, show_date, ticket_price, capacity, active, created_at)
            VALUES (?, ?, ?, ?, ?, 1, ?)
            """,
            (title, cinema, show_date, ticket_price, capacity, now),
        )
        return cur.lastrowid


def get_show(show_id):
    with connect() as con:
        return con.execute(
            "SELECT * FROM shows WHERE id = ?", (show_id,)
        ).fetchone()


def list_shows(active_only=False):
    with connect() as con:
        if active_only:
            return con.execute(
                "SELECT * FROM shows WHERE active = 1 ORDER BY id DESC"
            ).fetchall()
        return con.execute(
            "SELECT * FROM shows ORDER BY active DESC, id DESC"
        ).fetchall()


def deactivate_show(show_id):
    with connect() as con:
        cur = con.execute(
            "UPDATE shows SET active = 0 WHERE id = ? AND active = 1",
            (show_id,),
        )
        return cur.rowcount == 1


def delete_show(show_id):
    """حذف کامل سانس و همه سفارش‌های مربوط به آن."""
    with connect() as con:
        con.execute(
            "DELETE FROM tickets WHERE order_id IN "
            "(SELECT id FROM orders WHERE show_id = ?)", (show_id,)
        )
        con.execute("DELETE FROM orders WHERE show_id = ?", (show_id,))
        cur = con.execute("DELETE FROM shows WHERE id = ?", (show_id,))
        return cur.rowcount == 1


def _sold(con, show_id):
    marks = ",".join("?" * len(COUNTED_STATUSES))
    row = con.execute(
        f"SELECT COALESCE(SUM(quantity), 0) FROM orders "
        f"WHERE show_id = ? AND status IN ({marks})",
        (show_id, *COUNTED_STATUSES),
    ).fetchone()
    return row[0]


def count_sold(show_id):
    """تعداد بلیت‌های اشغال‌شده (در انتظار بررسی + تأییدشده)."""
    with connect() as con:
        return _sold(con, show_id)


def remaining_capacity(show_id):
    with connect() as con:
        show = con.execute(
            "SELECT capacity FROM shows WHERE id = ?", (show_id,)
        ).fetchone()
        if not show:
            return 0
        capacity = show["capacity"] if show["capacity"] is not None else 100
        return capacity - _sold(con, show_id)


def count_registrations(show_id):
    """تعداد سفارش‌ها و بلیت‌ها به تفکیک وضعیت برای یک سانس."""
    with connect() as con:
        return con.execute(
            "SELECT status, COUNT(*) AS orders, COALESCE(SUM(quantity), 0) AS tickets "
            "FROM orders WHERE show_id = ? GROUP BY status",
            (show_id,),
        ).fetchall()


def create_order(user_id, username, buyer_name, phone, quantity, unit_price, show):
    """
    ثبت سفارش. چک ظرفیت و درج سفارش در یک تراکنش قفل‌شده انجام می‌شود
    تا دو خریدار هم‌زمان نتوانند از ظرفیت بیشتر بخرند.
    اگر ظرفیت کافی نباشد None برمی‌گرداند.
    """
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    total = quantity * unit_price

    con = connect()
    try:
        con.isolation_level = None  # مدیریت دستی تراکنش
        con.execute("BEGIN IMMEDIATE")

        row = con.execute(
            "SELECT capacity FROM shows WHERE id = ?", (show["id"],)
        ).fetchone()
        capacity = row["capacity"] if row and row["capacity"] is not None else 100
        if not row or quantity > capacity - _sold(con, show["id"]):
            con.execute("ROLLBACK")
            return None

        cur = con.execute("""
            INSERT INTO orders (
                user_id, username, buyer_name, phone, quantity,
                unit_price, total_price, status, created_at, updated_at,
                show_id, show_title, show_cinema, show_date
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, 'waiting_receipt', ?, ?, ?, ?, ?, ?)
        """, (
            user_id, username, buyer_name, phone, quantity,
            unit_price, total, now, now,
            show["id"], show["title"], show["cinema"], show["show_date"]
        ))
        order_id = cur.lastrowid
        con.execute("COMMIT")
        return order_id
    except Exception:
        try:
            con.execute("ROLLBACK")
        except Exception:
            pass
        raise
    finally:
        con.close()


def get_order(order_id):
    with connect() as con:
        return con.execute(
            "SELECT * FROM orders WHERE id = ?", (order_id,)
        ).fetchone()


def set_receipt(order_id, file_id):
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with connect() as con:
        cur = con.execute("""
            UPDATE orders
            SET receipt_file_id = ?, status = 'pending_review', updated_at = ?
            WHERE id = ? AND status = 'waiting_receipt'
        """, (file_id, now, order_id))
        return cur.rowcount == 1


def approve_order(order_id, codes):
    # کدهای هر بلیت هم به‌صورت یک رشته در سفارش و هم جداگانه در جدول tickets
    # ذخیره می‌شوند. تغییر وضعیت فقط وقتی انجام می‌شود که سفارش در انتظار بررسی باشد.
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    codes_text = ", ".join(codes)

    with connect() as con:
        cur = con.execute("""
            UPDATE orders
            SET status = 'approved', ticket_codes = ?, updated_at = ?
            WHERE id = ? AND status = 'pending_review'
        """, (codes_text, now, order_id))
        if cur.rowcount != 1:
            return False
        for code in codes:
            con.execute(
                "INSERT INTO tickets (order_id, code) VALUES (?, ?)",
                (order_id, code),
            )
        return True


def reject_order(order_id):
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with connect() as con:
        cur = con.execute("""
            UPDATE orders
            SET status = 'rejected', updated_at = ?
            WHERE id = ? AND status = 'pending_review'
        """, (now, order_id))
        return cur.rowcount == 1


def list_user_orders(user_id):
    with connect() as con:
        return con.execute("""
            SELECT * FROM orders
            WHERE user_id = ?
            ORDER BY id DESC
            LIMIT 20
        """, (user_id,)).fetchall()


def all_orders():
    with connect() as con:
        return con.execute(
            "SELECT * FROM orders ORDER BY id DESC"
        ).fetchall()


# ---------------------- ورود به سالن (چک بلیت) ----------------------

def get_ticket(code):
    with connect() as con:
        return con.execute("""
            SELECT t.code, t.used, t.used_at, t.used_by, t.order_id,
                   o.buyer_name, o.phone, o.quantity, o.show_id,
                   o.show_title, o.show_cinema, o.show_date
            FROM tickets t
            JOIN orders o ON o.id = t.order_id
            WHERE t.code = ?
        """, (code,)).fetchone()


def list_order_tickets(order_id):
    with connect() as con:
        return con.execute(
            "SELECT * FROM tickets WHERE order_id = ? ORDER BY id", (order_id,)
        ).fetchall()


def check_in(code, checker_id):
    """ثبت ورود یک بلیت. فقط اگر قبلاً استفاده نشده باشد True برمی‌گرداند."""
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with connect() as con:
        cur = con.execute(
            "UPDATE tickets SET used = 1, used_at = ?, used_by = ? "
            "WHERE code = ? AND used = 0",
            (now, checker_id, code),
        )
        return cur.rowcount == 1


def check_in_order(order_id, checker_id):
    """ثبت ورود همه‌ی بلیت‌های استفاده‌نشده‌ی یک سفارش؛ تعداد را برمی‌گرداند."""
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with connect() as con:
        cur = con.execute(
            "UPDATE tickets SET used = 1, used_at = ?, used_by = ? "
            "WHERE order_id = ? AND used = 0",
            (now, checker_id, order_id),
        )
        return cur.rowcount


def undo_check_in(code):
    with connect() as con:
        cur = con.execute(
            "UPDATE tickets SET used = 0, used_at = NULL, used_by = NULL "
            "WHERE code = ? AND used = 1",
            (code,),
        )
        return cur.rowcount == 1


def entry_stats(show_id):
    with connect() as con:
        return con.execute("""
            SELECT COUNT(*) AS total, COALESCE(SUM(t.used), 0) AS entered
            FROM tickets t
            JOIN orders o ON o.id = t.order_id
            WHERE o.show_id = ?
        """, (show_id,)).fetchone()


def generate_codes(count):
    """count کد یکتای ۵ کاراکتری می‌سازد که قبلاً در دیتابیس استفاده نشده باشند."""
    codes = []
    with connect() as con:
        while len(codes) < count:
            code = "".join(secrets.choice(CODE_ALPHABET) for _ in range(CODE_LENGTH))
            if code in codes:
                continue
            exists = con.execute(
                "SELECT 1 FROM tickets WHERE code = ?", (code,)
            ).fetchone()
            if not exists:
                codes.append(code)
    return codes