from __future__ import annotations
import sqlite3
from pathlib import Path

SCHEMA_SQL = """PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS studio_profile (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  supplier_requisites_text TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY,
  display_name TEXT NOT NULL,
  company_name TEXT,
  responsible_person_name TEXT,
  phone TEXT,
  email TEXT,
  requisites_text TEXT NOT NULL DEFAULT '',
  legal_block_text TEXT NOT NULL DEFAULT '',
  authorized_signer_name TEXT NOT NULL DEFAULT '',
  authorized_signer_position TEXT NOT NULL DEFAULT '',
  contract_number TEXT,
  contract_date_text TEXT,
  notes TEXT,
  project_folder_path TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_clients_display_name ON clients(display_name);

CREATE TABLE IF NOT EXISTS voice_actors (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  display_name TEXT NOT NULL,
  phone TEXT,
  email TEXT,
  payment_details_text TEXT,
  photo_path TEXT,
  notes TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  deleted_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_voice_actors_display_name ON voice_actors(display_name);
CREATE INDEX IF NOT EXISTS idx_voice_actors_active ON voice_actors(is_active);

CREATE TABLE IF NOT EXISTS projects (
  id INTEGER PRIMARY KEY,
  project_number INTEGER NOT NULL UNIQUE,
  client_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  created_date TEXT NOT NULL,
  work_status TEXT NOT NULL CHECK (work_status IN ('IN_PROGRESS','COMPLETED')),
  client_paid INTEGER NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'UAH' CHECK (currency IN ('UAH','USD','EUR')),
  total_amount REAL NOT NULL DEFAULT 0,
  notes TEXT,
  project_folder_path TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_projects_client_id ON projects(client_id);
CREATE INDEX IF NOT EXISTS idx_projects_project_number ON projects(project_number);
CREATE INDEX IF NOT EXISTS idx_projects_created_date ON projects(created_date);
CREATE INDEX IF NOT EXISTS idx_projects_work_status ON projects(work_status);
CREATE INDEX IF NOT EXISTS idx_projects_client_paid ON projects(client_paid);


CREATE TABLE IF NOT EXISTS client_payments (
  id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL,
  payment_date TEXT NOT NULL,
  amount REAL NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'UAH',
  comment TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_client_payments_client_id ON client_payments(client_id);
CREATE INDEX IF NOT EXISTS idx_client_payments_payment_date ON client_payments(payment_date);


CREATE TABLE IF NOT EXISTS voice_actor_payments (
  id INTEGER PRIMARY KEY,
  voice_actor_id INTEGER NOT NULL,
  payment_date TEXT NOT NULL,
  amount REAL NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'UAH',
  comment TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (voice_actor_id) REFERENCES voice_actors(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_voice_actor_payments_actor_id ON voice_actor_payments(voice_actor_id);
CREATE INDEX IF NOT EXISTS idx_voice_actor_payments_payment_date ON voice_actor_payments(payment_date);

CREATE TABLE IF NOT EXISTS service_catalog (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  is_active INTEGER NOT NULL DEFAULT 1,
  deleted_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS project_line_items (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  service_id INTEGER,
  service_name_snapshot TEXT NOT NULL,
  amount REAL NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (service_id) REFERENCES service_catalog(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_line_items_project_id ON project_line_items(project_id);

CREATE TABLE IF NOT EXISTS project_voice_actors (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  voice_actor_id INTEGER,
  voice_actor_name_snapshot TEXT NOT NULL,
  fee REAL NOT NULL DEFAULT 0,
  fee_currency TEXT NOT NULL DEFAULT 'UAH',
  paid_to_actor INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (voice_actor_id) REFERENCES voice_actors(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_pva_project_id ON project_voice_actors(project_id);
CREATE INDEX IF NOT EXISTS idx_pva_voice_actor_id ON project_voice_actors(voice_actor_id);
CREATE INDEX IF NOT EXISTS idx_pva_paid_to_actor ON project_voice_actors(paid_to_actor);

CREATE TABLE IF NOT EXISTS documents (
  id INTEGER PRIMARY KEY,
  doc_type TEXT NOT NULL CHECK (doc_type IN ('INVOICE','ACT','CLIENT_REPORT','ACTOR_REPORT','CONTRACT_FILE')),
  client_id INTEGER,
  voice_actor_id INTEGER,
  doc_number TEXT NOT NULL,
  related_invoice_id INTEGER,
  currency TEXT,
  total_amount REAL NOT NULL DEFAULT 0,
  created_date TEXT NOT NULL,
  with_contract INTEGER NOT NULL DEFAULT 0,
  contract_number_snapshot TEXT,
  contract_date_snapshot TEXT,
  file_path TEXT NOT NULL,
  notes TEXT,
  project_folder_path TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (voice_actor_id) REFERENCES voice_actors(id) ON DELETE SET NULL,
  FOREIGN KEY (related_invoice_id) REFERENCES documents(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_documents_client_id ON documents(client_id);
CREATE INDEX IF NOT EXISTS idx_documents_voice_actor_id ON documents(voice_actor_id);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(doc_type);
CREATE INDEX IF NOT EXISTS idx_documents_doc_number ON documents(doc_number);

CREATE TABLE IF NOT EXISTS document_projects (
  id INTEGER PRIMARY KEY,
  document_id INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  project_number_snapshot INTEGER NOT NULL,
  project_title_snapshot TEXT NOT NULL,
  project_date_snapshot TEXT NOT NULL,
  amount_snapshot REAL NOT NULL,
  currency_snapshot TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_document_projects_document_id ON document_projects(document_id);
CREATE INDEX IF NOT EXISTS idx_document_projects_project_id ON document_projects(project_id);

CREATE TABLE IF NOT EXISTS email_send_history (
  id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL,
  document_id INTEGER NOT NULL,
  sent_to_email TEXT NOT NULL,
  sent_at TEXT NOT NULL,
  subject_snapshot TEXT NOT NULL,
  body_snapshot TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_email_history_client_id ON email_send_history(client_id);
CREATE INDEX IF NOT EXISTS idx_email_history_document_id ON email_send_history(document_id);
CREATE INDEX IF NOT EXISTS idx_email_history_sent_at ON email_send_history(sent_at);

CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
"""


def connect(db_path: Path) -> sqlite3.Connection:
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    return conn



def _ensure_column(conn: sqlite3.Connection, table: str, column: str, ddl: str) -> None:
    """Best-effort добавлення колонки, якщо її немає (для міграцій схеми)."""
    try:
        cols = {row[1] for row in conn.execute(f"PRAGMA table_info({table});").fetchall()}
        if column in cols:
            return
        conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {ddl};")
        conn.commit()
    except Exception:
        # Якщо БД read-only або ALTER TABLE недоступний — просто ігноруємо.
        pass

def init_db(conn: sqlite3.Connection) -> None:
    conn.executescript(SCHEMA_SQL)
    try:
        conn.execute("DROP INDEX IF EXISTS uidx_pva_project_id;")
    except Exception:
        pass
    _ensure_column(conn, 'voice_actors', 'deleted_at', 'TEXT')
    _ensure_column(conn, 'client_payments', 'comment', "TEXT NOT NULL DEFAULT ''")
    _ensure_column(conn, 'client_payments', 'currency', "TEXT NOT NULL DEFAULT 'UAH'")
    _ensure_column(conn, 'client_payments', 'updated_at', "TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP")
    _ensure_column(conn, 'projects', 'project_folder_path', 'TEXT')
    _ensure_column(conn, 'voice_actor_payments', 'comment', "TEXT NOT NULL DEFAULT ''")
    _ensure_column(conn, 'voice_actor_payments', 'currency', "TEXT NOT NULL DEFAULT 'UAH'")
    _ensure_column(conn, 'voice_actor_payments', 'updated_at', "TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP")
    _ensure_column(conn, 'project_voice_actors', 'fee_currency', "TEXT NOT NULL DEFAULT 'UAH'")
    try:
        conn.execute("DELETE FROM app_settings WHERE key='email_signature';")
    except Exception:
        pass
    cur = conn.execute("SELECT id FROM studio_profile WHERE id=1;")
    row = cur.fetchone()
    if row is None:
        conn.execute("INSERT INTO studio_profile (id, updated_at) VALUES (1, datetime('now'));")
    conn.commit()

def get_next_project_number(conn: sqlite3.Connection) -> int:
    cur = conn.execute("SELECT COALESCE(MAX(project_number), 0) + 1 AS n FROM projects;")
    return int(cur.fetchone()["n"])

def get_setting(conn: sqlite3.Connection, key: str, default: str = "") -> str:
    cur = conn.execute("SELECT value FROM app_settings WHERE key = ?;", (key,))
    row = cur.fetchone()
    return row["value"] if row else default

def set_setting(conn: sqlite3.Connection, key: str, value: str) -> None:
    conn.execute(
        "INSERT INTO app_settings(key, value, updated_at) VALUES(?,?, datetime('now')) "
        "ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=datetime('now');",
        (key, value),
    )
    conn.commit()
