import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path


SCHEMA = """
CREATE TABLE IF NOT EXISTS devices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    device_secret_hash TEXT NOT NULL,
    name TEXT,
    owner_email TEXT,
    source_encrypted TEXT,
    status TEXT NOT NULL DEFAULT 'pending',
    catalog_json TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_devices_owner ON devices(owner_email);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL,
    parent_email TEXT,
    xtream_host TEXT,
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_users_parent ON users(parent_email);
"""


def utc_now():
    return datetime.now(timezone.utc).isoformat()


class Database:
    def __init__(self, path: Path):
        path.parent.mkdir(parents=True, exist_ok=True)
        self.path = path
        with self.connect() as connection:
            connection.executescript(SCHEMA)
            self._migrate(connection)

    def _migrate(self, connection):
        columns = [row["name"] for row in connection.execute("PRAGMA table_info(devices)").fetchall()]
        if "playlist_username" not in columns:
            connection.execute("ALTER TABLE devices ADD COLUMN playlist_username TEXT")
        if "playlist_host" not in columns:
            connection.execute("ALTER TABLE devices ADD COLUMN playlist_host TEXT")

    def connect(self):
        connection = sqlite3.connect(str(self.path))
        connection.row_factory = sqlite3.Row
        return connection

    def ensure_admin(self, email, password_hash):
        now = utc_now()
        with self.connect() as connection:
            row = connection.execute("SELECT * FROM users WHERE email=?", (email,)).fetchone()
            if not row:
                connection.execute(
                    """
                    INSERT INTO users(email, password_hash, role, parent_email, active, created_at, updated_at)
                    VALUES (?, ?, 'administrator', NULL, 1, ?, ?)
                    """,
                    (email, password_hash, now, now),
                )
        return self.get_user(email)

    def get_user(self, email):
        with self.connect() as connection:
            row = connection.execute("SELECT * FROM users WHERE email=?", (email,)).fetchone()
        return dict(row) if row else None

    def list_users(self, requester):
        role = requester["role"]
        email = requester["email"]
        with self.connect() as connection:
            if role == "administrator":
                rows = connection.execute(
                    """
                    SELECT id, email, role, parent_email, xtream_host, active, created_at, updated_at
                    FROM users ORDER BY created_at DESC
                    """
                ).fetchall()
            elif role == "supervisor":
                rows = connection.execute(
                    """
                    SELECT id, email, role, parent_email, xtream_host, active, created_at, updated_at
                    FROM users WHERE parent_email=? ORDER BY created_at DESC
                    """,
                    (email,),
                ).fetchall()
            else:
                rows = []
        return [dict(row) for row in rows]

    def create_user(self, email, password_hash, role, parent_email, xtream_host):
        now = utc_now()
        with self.connect() as connection:
            connection.execute(
                """
                INSERT INTO users(email, password_hash, role, parent_email, xtream_host, active, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, 1, ?, ?)
                """,
                (email, password_hash, role, parent_email, xtream_host, now, now),
            )
        return self.get_user(email)

    def update_user(self, email, new_email=None, password_hash=None, xtream_host=None, active=None):
        fields = []
        values = []
        if new_email and new_email != email:
            fields.append("email=?")
            values.append(new_email)
        if password_hash:
            fields.append("password_hash=?")
            values.append(password_hash)
        if xtream_host is not None:
            fields.append("xtream_host=?")
            values.append(xtream_host)
        if active is not None:
            fields.append("active=?")
            values.append(1 if active else 0)
        if not fields:
            return self.get_user(email)
        fields.append("updated_at=?")
        values.append(utc_now())
        values.append(email)
        with self.connect() as connection:
            connection.execute("UPDATE users SET {} WHERE email=?".format(", ".join(fields)), values)
            if new_email and new_email != email:
                connection.execute("UPDATE users SET parent_email=? WHERE parent_email=?", (new_email, email))
                connection.execute("UPDATE devices SET owner_email=? WHERE owner_email=?", (new_email, email))
        return self.get_user(new_email or email)

    def delete_user_tree(self, email):
        target = self.get_user(email)
        if not target:
            return {"deleted": False, "users": 0, "devices": 0}
        with self.connect() as connection:
            emails = [email]
            index = 0
            while index < len(emails):
                children = connection.execute(
                    "SELECT email FROM users WHERE parent_email=?",
                    (emails[index],),
                ).fetchall()
                for child in children:
                    child_email = child["email"]
                    if child_email not in emails:
                        emails.append(child_email)
                index += 1

            placeholders = ",".join(["?"] * len(emails))
            devices = connection.execute(
                "DELETE FROM devices WHERE owner_email IN ({})".format(placeholders),
                emails,
            ).rowcount
            users = connection.execute(
                "DELETE FROM users WHERE email IN ({})".format(placeholders),
                emails,
            ).rowcount
        return {"deleted": users > 0, "users": users, "devices": devices}

    def register_device(self, code, secret_hash):
        now = utc_now()
        with self.connect() as connection:
            row = connection.execute("SELECT status FROM devices WHERE code=?", (code,)).fetchone()
            if row:
                if row["status"] == "pending":
                    connection.execute(
                        """
                        UPDATE devices
                        SET device_secret_hash=?, updated_at=?
                        WHERE code=? AND status='pending'
                        """,
                        (secret_hash, now, code),
                    )
            else:
                connection.execute(
                    """
                    INSERT INTO devices(code, device_secret_hash, created_at, updated_at)
                    VALUES (?, ?, ?, ?)
                    """,
                    (code, secret_hash, now, now),
                )
        return self.get_device_by_code(code)

    def get_device_by_code(self, code):
        with self.connect() as connection:
            row = connection.execute("SELECT * FROM devices WHERE code=?", (code,)).fetchone()
        return dict(row) if row else None

    def list_devices(self, requester, search=""):
        if isinstance(requester, str):
            requester = {"email": requester, "role": "reseller"}
        role = requester["role"]
        email = requester["email"]
        params = []
        where = []
        if role == "administrator":
            pass
        elif role == "supervisor":
            where.append("(owner_email=? OR owner_email IN (SELECT email FROM users WHERE parent_email=?))")
            params.extend([email, email])
        else:
            where.append("owner_email=?")
            params.append(email)
        if search:
            where.append("(code LIKE ? OR name LIKE ? OR playlist_username LIKE ? OR owner_email LIKE ?)")
            like = "%{}%".format(search)
            params.extend([like, like, like, like])
        sql = """
            SELECT id, code, name, owner_email, playlist_username, playlist_host, status, created_at, updated_at
            FROM devices
        """
        if where:
            sql += " WHERE " + " AND ".join(where)
        sql += " ORDER BY updated_at DESC"
        with self.connect() as connection:
            rows = connection.execute(sql, params).fetchall()
        return [dict(row) for row in rows]

    def activate(self, code, owner_email, name, source_encrypted, catalog, playlist_username=None, playlist_host=None):
        with self.connect() as connection:
            result = connection.execute(
                """
                UPDATE devices
                SET owner_email=?, name=?, source_encrypted=?, status='active',
                    catalog_json=?, playlist_username=?, playlist_host=?, updated_at=?
                WHERE code=?
                """,
                (
                    owner_email,
                    name,
                    source_encrypted,
                    json.dumps(catalog),
                    playlist_username,
                    playlist_host,
                    utc_now(),
                    code,
                ),
            )
        return self.get_device_by_code(code) if result.rowcount else None

    def delete_device(self, code, requester):
        if isinstance(requester, str):
            requester = {"email": requester, "role": "reseller"}
        device = self.get_device_by_code(code)
        if not device or not self.can_manage_owner(requester, device.get("owner_email")):
            return False
        with self.connect() as connection:
            result = connection.execute("DELETE FROM devices WHERE code=?", (code,))
        return result.rowcount > 0

    def can_manage_owner(self, requester, owner_email):
        if requester["role"] == "administrator":
            return True
        if requester["email"] == owner_email:
            return True
        if requester["role"] == "supervisor":
            owner = self.get_user(owner_email)
            return bool(owner and owner.get("parent_email") == requester["email"])
        return False