from __future__ import annotations

import hashlib
import json
import os
import sqlite3
from contextlib import closing
from pathlib import Path
from typing import Any, Optional

from .schemas import CreateRun, utcnow


ACTIVE_STATES = ("pending", "queued", "running")


class IdempotencyConflict(Exception):
    pass


class ActiveRunConflict(Exception):
    pass


class Store:
    def __init__(self, path: Path):
        self.path = path

    def connect(self) -> sqlite3.Connection:
        self.path.parent.mkdir(mode=0o700, parents=True, exist_ok=True)
        os.chmod(self.path.parent, 0o700)
        conn = sqlite3.connect(self.path, timeout=10, isolation_level=None)
        try:
            os.chmod(self.path, 0o600)
        except OSError:
            conn.close()
            raise
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA busy_timeout=10000")
        conn.execute("PRAGMA foreign_keys=ON")
        return conn

    def initialize(self) -> None:
        with closing(self.connect()) as conn:
            conn.execute("PRAGMA journal_mode=WAL")
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS sync_runs (
                    run_id TEXT PRIMARY KEY, request_hash TEXT NOT NULL,
                    connection TEXT NOT NULL, mode TEXT NOT NULL, tables_json TEXT NOT NULL,
                    status TEXT NOT NULL, created_at TEXT NOT NULL, started_at TEXT,
                    finished_at TEXT, error_message TEXT, log_reference TEXT NOT NULL,
                    requires_reconciliation INTEGER NOT NULL DEFAULT 0
                );
                CREATE TABLE IF NOT EXISTS sync_run_tables (
                    run_id TEXT NOT NULL REFERENCES sync_runs(run_id) ON DELETE CASCADE,
                    name TEXT NOT NULL, status TEXT NOT NULL, rows_imported INTEGER,
                    previous_rows INTEGER, started_at TEXT, finished_at TEXT,
                    backup_table TEXT, error_message TEXT, PRIMARY KEY(run_id, name)
                );
                CREATE INDEX IF NOT EXISTS sync_runs_queue_idx ON sync_runs(status, created_at);
                CREATE UNIQUE INDEX IF NOT EXISTS one_active_run_per_connection
                  ON sync_runs(connection) WHERE status IN ('pending','queued','running');
            """)

    @staticmethod
    def request_hash(payload: CreateRun) -> str:
        canonical = json.dumps(payload.model_dump(mode="json"), sort_keys=True, separators=(",", ":"))
        return hashlib.sha256(canonical.encode()).hexdigest()

    def create(self, payload: CreateRun) -> tuple[dict[str, Any], bool]:
        run_id = str(payload.run_id)
        digest = self.request_hash(payload)
        now = utcnow()
        conn = self.connect()
        try:
            conn.execute("BEGIN IMMEDIATE")
            existing = conn.execute("SELECT request_hash FROM sync_runs WHERE run_id=?", (run_id,)).fetchone()
            if existing:
                if existing["request_hash"] != digest:
                    raise IdempotencyConflict()
                conn.commit()
                return self.snapshot(run_id), False
            active = conn.execute("SELECT run_id FROM sync_runs WHERE connection=? AND status IN ('pending','queued','running')", (payload.connection,)).fetchone()
            if active:
                raise ActiveRunConflict()
            conn.execute("""INSERT INTO sync_runs
                (run_id, request_hash, connection, mode, tables_json, status, created_at, log_reference)
                VALUES (?, ?, ?, ?, ?, 'queued', ?, ?)""",
                (run_id, digest, payload.connection, payload.mode, json.dumps(payload.tables), now, f"{run_id}.jsonl"))
            conn.executemany("INSERT INTO sync_run_tables(run_id,name,status) VALUES(?,?,'pending')",
                             [(run_id, table) for table in payload.tables])
            conn.commit()
            return {"run_id": run_id, "status": "queued", "tables": []}, True
        except sqlite3.IntegrityError as exc:
            conn.rollback()
            raise ActiveRunConflict() from exc
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    def next_queued(self) -> Optional[str]:
        conn = self.connect()
        try:
            conn.execute("BEGIN IMMEDIATE")
            row = conn.execute("SELECT run_id FROM sync_runs WHERE status='queued' ORDER BY created_at, run_id LIMIT 1").fetchone()
            if row is None:
                conn.commit()
                return None
            conn.execute("UPDATE sync_runs SET status='running', started_at=? WHERE run_id=? AND status='queued'", (utcnow(), row["run_id"]))
            conn.commit()
            return row["run_id"]
        finally:
            conn.close()

    def snapshot(self, run_id: str) -> Optional[dict[str, Any]]:
        conn = self.connect()
        try:
            run = conn.execute("SELECT * FROM sync_runs WHERE run_id=?", (run_id,)).fetchone()
            if run is None:
                return None
            tables = conn.execute("SELECT name,status,rows_imported,previous_rows,started_at,finished_at,backup_table,error_message FROM sync_run_tables WHERE run_id=? ORDER BY rowid", (run_id,)).fetchall()
            return {"run_id": run["run_id"], "status": run["status"], "started_at": run["started_at"],
                    "finished_at": run["finished_at"], "error_message": run["error_message"],
                    "tables": [dict(table) for table in tables]}
        finally:
            conn.close()

    def queued_payload(self, run_id: str) -> tuple[str, list[str]]:
        conn = self.connect()
        try:
            row = conn.execute("SELECT connection,tables_json FROM sync_runs WHERE run_id=?", (run_id,)).fetchone()
            return row["connection"], json.loads(row["tables_json"])
        finally:
            conn.close()

    def requeue(self, run_id: str) -> None:
        with closing(self.connect()) as conn:
            conn.execute("UPDATE sync_runs SET status='queued',started_at=NULL WHERE run_id=? AND status='running'", (run_id,))

    def mark_existing_runs_uncertain(self) -> None:
        with closing(self.connect()) as conn:
            conn.execute("UPDATE sync_runs SET requires_reconciliation=1,error_message='Worker restarted during execution; reconciliation required.' WHERE status='running'")

    def table_update(self, run_id: str, name: str, **fields: Any) -> None:
        allowed = {"status", "rows_imported", "previous_rows", "started_at", "finished_at", "backup_table", "error_message"}
        fields = {key: value for key, value in fields.items() if key in allowed}
        if not fields:
            return
        sql = ",".join(f"{key}=?" for key in fields)
        with closing(self.connect()) as conn:
            conn.execute(f"UPDATE sync_run_tables SET {sql} WHERE run_id=? AND name=?", (*fields.values(), run_id, name))

    def finish(self, run_id: str, status: str, error: Optional[str] = None) -> None:
        with closing(self.connect()) as conn:
            conn.execute("UPDATE sync_runs SET status=?, finished_at=?, error_message=? WHERE run_id=?", (status, utcnow(), error, run_id))

    def mark_uncertain(self, run_id: str, message: str) -> None:
        with closing(self.connect()) as conn:
            conn.execute("UPDATE sync_runs SET requires_reconciliation=1,error_message=? WHERE run_id=?", (message, run_id))

    def reconcile(self, run_id: str, message: str) -> bool:
        with closing(self.connect()) as conn:
            cursor = conn.execute("UPDATE sync_runs SET status='failed',finished_at=?,error_message=?,requires_reconciliation=0 WHERE run_id=? AND status='running' AND requires_reconciliation=1", (utcnow(), message, run_id))
            return cursor.rowcount == 1
