import asyncpg
import os
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")
_pool = None


async def get_pool():
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(DATABASE_URL)
    return _pool


async def init_db():
    pool = await get_pool()
    with open("db/schema.sql") as f:
        sql = f.read()
    async with pool.acquire() as conn:
        await conn.execute(sql)


# ── Users ──────────────────────────────────────────────
async def upsert_user(user_id: int, username: str, full_name: str, language: str = "uz"):
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """
            INSERT INTO users (id, username, full_name, language)
            VALUES ($1, $2, $3, $4)
            ON CONFLICT (id) DO UPDATE SET username=$2, full_name=$3, language=$4
            """,
            user_id, username, full_name, language,
        )


async def set_user_language(user_id: int, language: str):
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            "UPDATE users SET language=$2 WHERE id=$1",
            user_id, language,
        )


async def get_user_language(user_id: int) -> str:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow("SELECT language FROM users WHERE id=$1", user_id)
        return row["language"] if row else "uz"


# ── Chats ──────────────────────────────────────────────
async def create_chat(user_id: int) -> int:
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            "INSERT INTO chats (user_id, status) VALUES ($1, 'new') RETURNING id",
            user_id,
        )
        return row["id"]


async def get_active_chat(user_id: int):
    pool = await get_pool()
    async with pool.acquire() as conn:
        return await conn.fetchrow(
            "SELECT * FROM chats WHERE user_id=$1 AND status != 'closed' ORDER BY id DESC LIMIT 1",
            user_id,
        )


async def update_chat_status(chat_id: int, status: str):
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            "UPDATE chats SET status=$2, updated_at=NOW() WHERE id=$1",
            chat_id, status,
        )


async def get_chats_by_status(status: str = None):
    pool = await get_pool()
    async with pool.acquire() as conn:
        if status and status != "all":
            rows = await conn.fetch(
                """
                SELECT c.*, u.full_name, u.username, u.language,
                       (SELECT text FROM messages WHERE chat_id=c.id ORDER BY id DESC LIMIT 1) AS last_message,
                       (SELECT created_at FROM messages WHERE chat_id=c.id ORDER BY id DESC LIMIT 1) AS last_msg_time
                FROM chats c JOIN users u ON c.user_id=u.id
                WHERE c.status=$1 ORDER BY c.updated_at DESC
                """,
                status,
            )
        else:
            rows = await conn.fetch(
                """
                SELECT c.*, u.full_name, u.username, u.language,
                       (SELECT text FROM messages WHERE chat_id=c.id ORDER BY id DESC LIMIT 1) AS last_message,
                       (SELECT created_at FROM messages WHERE chat_id=c.id ORDER BY id DESC LIMIT 1) AS last_msg_time
                FROM chats c JOIN users u ON c.user_id=u.id
                ORDER BY c.updated_at DESC
                """
            )
        return [dict(r) for r in rows]


async def get_chat_with_messages(chat_id: int):
    pool = await get_pool()
    async with pool.acquire() as conn:
        chat = await conn.fetchrow(
            "SELECT c.*, u.full_name, u.username, u.id as uid FROM chats c JOIN users u ON c.user_id=u.id WHERE c.id=$1",
            chat_id,
        )
        messages = await conn.fetch(
            "SELECT * FROM messages WHERE chat_id=$1 ORDER BY id ASC",
            chat_id,
        )
        return dict(chat) if chat else None, [dict(m) for m in messages]


# ── Messages ───────────────────────────────────────────
async def save_message(chat_id: int, sender: str, text: str):
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            "INSERT INTO messages (chat_id, sender, text) VALUES ($1, $2, $3)",
            chat_id, sender, text,
        )
        await conn.execute(
            "UPDATE chats SET updated_at=NOW() WHERE id=$1",
            chat_id,
        )
