"""Database module using aiosqlite"""
import aiosqlite
import os
from typing import Optional, List

DB_PATH = os.getenv("DB_PATH", "trading_bot.db")


async def init_db():
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                role TEXT DEFAULT 'user',
                is_active INTEGER DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS signals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                asset TEXT NOT NULL,
                platform TEXT NOT NULL,
                direction TEXT NOT NULL,
                timeframe TEXT NOT NULL,
                strength REAL,
                indicators TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS trades (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                asset TEXT,
                platform TEXT,
                direction TEXT,
                amount REAL,
                duration INTEGER,
                result TEXT,
                profit REAL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES users(id)
            )
        """)
        # Seed default admin
        from app.auth import hash_password
        try:
            await db.execute(
                "INSERT INTO users (username, password, role) VALUES (?, ?, ?)",
                ("admin", hash_password("Admin@123"), "admin"),
            )
        except Exception:
            pass  # Already exists
        await db.commit()
    print("✅ Database initialized")


async def get_user_by_username(username: str) -> Optional[dict]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(
            "SELECT * FROM users WHERE username = ? AND is_active = 1", (username,)
        ) as cursor:
            row = await cursor.fetchone()
            return dict(row) if row else None


async def get_all_users() -> List[dict]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(
            "SELECT id, username, role, is_active, created_at FROM users"
        ) as cursor:
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]


async def create_user_db(username: str, password: str, role: str = "user") -> dict:
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "INSERT INTO users (username, password, role) VALUES (?, ?, ?)",
            (username, password, role),
        )
        await db.commit()
        return {"id": cursor.lastrowid, "username": username, "role": role}


async def delete_user_db(user_id: int):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("UPDATE users SET is_active = 0 WHERE id = ?", (user_id,))
        await db.commit()


async def save_signal(signal: dict):
    async with aiosqlite.connect(DB_PATH) as db:
        import json
        await db.execute(
            """INSERT INTO signals (asset, platform, direction, timeframe, strength, indicators)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (
                signal["asset"],
                signal["platform"],
                signal["direction"],
                signal["timeframe"],
                signal.get("strength", 0),
                json.dumps(signal.get("indicators", {})),
            ),
        )
        await db.commit()


async def get_signals_history(limit: int = 50, asset: str = None) -> List[dict]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        if asset:
            query = "SELECT * FROM signals WHERE asset = ? ORDER BY created_at DESC LIMIT ?"
            params = (asset, limit)
        else:
            query = "SELECT * FROM signals ORDER BY created_at DESC LIMIT ?"
            params = (limit,)
        async with db.execute(query, params) as cursor:
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]