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


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);
"""


def utc_now() -> str:
    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)

    def connect(self) -> sqlite3.Connection:
        connection = sqlite3.connect(self.path)
        connection.row_factory = sqlite3.Row
        return connection

    def register_device(self, code: str, secret_hash: str) -> dict[str, Any]:
        now = utc_now()
        with self.connect() as connection:
            connection.execute(
                """
                INSERT INTO devices(code, device_secret_hash, created_at, updated_at)
                VALUES (?, ?, ?, ?)
                ON CONFLICT(code) DO UPDATE SET
                    device_secret_hash=excluded.device_secret_hash,
                    updated_at=excluded.updated_at
                WHERE devices.status='pending'
                """,
                (code, secret_hash, now, now),
            )
        return self.get_device_by_code(code)

    def get_device_by_code(self, code: str) -> dict[str, Any] | None:
        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, owner_email: str) -> list[dict[str, Any]]:
        with self.connect() as connection:
            rows = connection.execute(
                """
                SELECT id, code, name, status, created_at, updated_at
                FROM devices WHERE owner_email=? ORDER BY updated_at DESC
                """,
                (owner_email,),
            ).fetchall()
        return [dict(row) for row in rows]

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

    def delete_device(self, code: str, owner_email: str) -> bool:
        with self.connect() as connection:
            result = connection.execute(
                "DELETE FROM devices WHERE code=? AND owner_email=?",
                (code, owner_email),
            )
        return result.rowcount > 0