# -*- coding: utf-8 -*-
"""لایه دیتابیس ساده (SQLite) برای سفارش‌ها و کاربران."""
import sqlite3
import time
from contextlib import contextmanager

import config


@contextmanager
def _conn():
    con = sqlite3.connect(config.DB_PATH)
    con.row_factory = sqlite3.Row
    try:
        yield con
        con.commit()
    finally:
        con.close()


def init_db():
    with _conn() as con:
        con.executescript(
            """
            CREATE TABLE IF NOT EXISTS users (
                user_id     INTEGER PRIMARY KEY,
                username    TEXT,
                first_name  TEXT,
                created_at  INTEGER
            );

            CREATE TABLE IF NOT EXISTS orders (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id     INTEGER,
                product_key TEXT,
                title       TEXT,
                amount_toman INTEGER,
                method      TEXT,          -- 'toman' | 'usdt'
                status      TEXT,          -- pending | awaiting_review | paid | rejected
                txid        TEXT,          -- برای پرداخت تتر
                created_at  INTEGER,
                updated_at  INTEGER
            );
            """
        )


def upsert_user(user_id: int, username: str, first_name: str):
    with _conn() as con:
        con.execute(
            """INSERT INTO users(user_id, username, first_name, created_at)
               VALUES(?,?,?,?)
               ON CONFLICT(user_id) DO UPDATE SET username=excluded.username,
                                                  first_name=excluded.first_name""",
            (user_id, username, first_name, int(time.time())),
        )


def create_order(user_id, product_key, title, amount_toman, method) -> int:
    now = int(time.time())
    with _conn() as con:
        cur = con.execute(
            """INSERT INTO orders(user_id, product_key, title, amount_toman,
                                  method, status, created_at, updated_at)
               VALUES(?,?,?,?,?,?,?,?)""",
            (user_id, product_key, title, amount_toman, method, "pending", now, now),
        )
        return cur.lastrowid


def get_order(order_id: int):
    with _conn() as con:
        row = con.execute("SELECT * FROM orders WHERE id=?", (order_id,)).fetchone()
        return dict(row) if row else None


def set_order_status(order_id: int, status: str, txid: str = None):
    with _conn() as con:
        if txid is not None:
            con.execute(
                "UPDATE orders SET status=?, txid=?, updated_at=? WHERE id=?",
                (status, txid, int(time.time()), order_id),
            )
        else:
            con.execute(
                "UPDATE orders SET status=?, updated_at=? WHERE id=?",
                (status, int(time.time()), order_id),
            )


def user_paid_orders(user_id: int):
    with _conn() as con:
        rows = con.execute(
            "SELECT * FROM orders WHERE user_id=? AND status='paid' ORDER BY id DESC",
            (user_id,),
        ).fetchall()
        return [dict(r) for r in rows]