from __future__ import annotations
import sqlite3
import re
import json
from datetime import datetime, timedelta
from dataclasses import dataclass
from typing import Optional, Iterable, Any, Dict, List

from mi100.timeutil import now_iso
from mi100.db import get_next_project_number

# NOTE: This repository is intentionally simple and local-only.
# All writes are immediate (auto-save behavior).

@dataclass
class Client:
    id: int
    display_name: str
    company_name: str | None
    responsible_person_name: str | None
    phone: str | None
    email: str | None
    requisites_text: str
    legal_block_text: str
    authorized_signer_name: str
    authorized_signer_position: str
    contract_number: str | None
    contract_date_text: str | None
    notes: str | None

@dataclass
class VoiceActor:
    id: int
    first_name: str
    last_name: str
    display_name: str
    phone: str | None
    email: str | None
    payment_details_text: str | None
    photo_path: str | None
    notes: str | None
    is_active: int

@dataclass
class Project:
    id: int
    project_number: int
    client_id: int
    title: str
    created_date: str
    work_status: str  # IN_PROGRESS / COMPLETED
    client_paid: int  # 0/1
    currency: str     # UAH/USD/EUR
    total_amount: float
    notes: str | None


CURRENCY_CATALOG = [
    {"code": "UAH", "symbol": "₴", "name": "Українська гривня", "default_status": "active"},
    {"code": "USD", "symbol": "$", "name": "Долар США", "default_status": "active"},
    {"code": "EUR", "symbol": "€", "name": "Євро", "default_status": "active"},
    {"code": "CAD", "symbol": "C$", "name": "Канадський долар", "default_status": "disabled"},
    {"code": "GBP", "symbol": "£", "name": "Фунт стерлінгів", "default_status": "disabled"},
    {"code": "PLN", "symbol": "zł", "name": "Польський злотий", "default_status": "disabled"},
    {"code": "CZK", "symbol": "Kč", "name": "Чеська крона", "default_status": "disabled"},
    {"code": "RON", "symbol": "lei", "name": "Румунський лей", "default_status": "disabled"},
    {"code": "BGN", "symbol": "лв", "name": "Болгарський лев", "default_status": "disabled"},
    {"code": "MDL", "symbol": "L", "name": "Молдовський лей", "default_status": "disabled"},
    {"code": "HUF", "symbol": "Ft", "name": "Угорський форинт", "default_status": "disabled"},
    {"code": "RSD", "symbol": "дин", "name": "Сербський динар", "default_status": "disabled"},
    {"code": "CHF", "symbol": "CHF", "name": "Швейцарський франк", "default_status": "disabled"},
]


class Repo:
    def __init__(self, conn: sqlite3.Connection):
        self.conn = conn

    # ---------- Studio profile ----------
    def get_studio_profile(self) -> sqlite3.Row:
        cur = self.conn.execute("SELECT * FROM studio_profile WHERE id=1;")
        return cur.fetchone()

    def update_studio_profile(self, supplier_requisites_text: str, **_ignored) -> None:
        self.conn.execute(
            """UPDATE studio_profile
               SET supplier_requisites_text=?,
                   updated_at=datetime('now')
               WHERE id=1;""",
            (supplier_requisites_text,),
        )
        self.conn.commit()

    def get_client_document_template(self, client_id: int, doc_type: str) -> str:
        key = str(doc_type or '').upper().strip()
        setting_key = f"client_{int(client_id)}_{key.lower()}_template"
        default_invoice = """Рахунок № [[DOC_NUMBER]]
Дата створення: [[CREATED_DATE]]

Платник:
[[PAYER_REQUISITES]]

Постачальник:
[[SUPPLIER_REQUISITES]]

Проєкти:
[[PROJECTS_TABLE]]

Разом: [[TOTAL_AMOUNT]]"""
        default_act = """Акт № [[DOC_NUMBER]]
Дата створення: [[CREATED_DATE]]

Замовник:
[[PAYER_REQUISITES]]

Виконавець:
[[SUPPLIER_REQUISITES]]

Проєкти:
[[PROJECTS_TABLE]]

Разом: [[TOTAL_AMOUNT]]"""
        default = default_invoice if key == 'INVOICE' else default_act
        return self.get_app_setting(setting_key, default)

    def set_client_document_template(self, client_id: int, doc_type: str, template_text: str) -> None:
        key = str(doc_type or '').upper().strip()
        setting_key = f"client_{int(client_id)}_{key.lower()}_template"
        self.set_app_setting(setting_key, template_text.rstrip())

    def get_studio_branding(self) -> dict[str, str]:
        return {
            "studio_title": self.get_app_setting("studio_title", "Mi100 Studio Manager"),
            "studio_logo_path": self.get_app_setting("studio_logo_path", ""),
        }

    def save_studio_branding(self, *, studio_title: str, studio_logo_path: str) -> None:
        self.set_app_setting("studio_title", studio_title.strip() or "Mi100 Studio Manager")
        self.set_app_setting("studio_logo_path", studio_logo_path.strip())

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

    def set_app_setting(self, key: str, value: str) -> None:
        self.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, str(value)),
        )
        self.conn.commit()

    def get_email_settings(self) -> dict[str, str]:
        keys = {
            'smtp_host': '',
            'smtp_port': '587',
            'smtp_username': '',
            'smtp_password': '',
            'smtp_sender_name': '',
            'smtp_sender_email': '',
        }
        return {k: self.get_app_setting(k, v) for k, v in keys.items()}

    def save_email_settings(self, **settings: str) -> None:
        for key, value in settings.items():
            self.set_app_setting(key, value)

    # ---------- Currencies ----------
    def get_currencies(self) -> list[dict[str, str]]:
        raw = self.get_app_setting("currencies_config", "")
        catalog = {row["code"]: dict(row) for row in CURRENCY_CATALOG}
        if raw:
            try:
                saved = json.loads(raw)
                if isinstance(saved, list):
                    for row in saved:
                        code = str((row or {}).get("code") or "").upper().strip()
                        if code in catalog:
                            catalog[code]["status"] = str((row or {}).get("status") or catalog[code].get("default_status") or "disabled")
            except Exception:
                pass
        result = []
        for row in CURRENCY_CATALOG:
            cur = dict(catalog[row["code"]])
            cur.setdefault("status", cur.get("default_status") or "disabled")
            result.append(cur)
        return result

    def get_used_currency_codes(self) -> set[str]:
        queries = [
            "SELECT DISTINCT COALESCE(currency, 'UAH') AS currency FROM projects",
            "SELECT DISTINCT COALESCE(currency, 'UAH') AS currency FROM client_payments",
            "SELECT DISTINCT COALESCE(currency, 'UAH') AS currency FROM voice_actor_payments",
            "SELECT DISTINCT COALESCE(fee_currency, 'UAH') AS currency FROM project_voice_actors",
            "SELECT DISTINCT COALESCE(currency, 'UAH') AS currency FROM documents",
        ]
        used: set[str] = set()
        for sql in queries:
            try:
                for row in self.conn.execute(sql).fetchall():
                    code = str(row['currency'] or 'UAH').upper().strip()
                    if code:
                        used.add(code)
            except Exception:
                continue
        return used

    def save_currencies(self, rows: list[dict[str, str]]) -> None:
        allowed = {row["code"] for row in CURRENCY_CATALOG}
        used_codes = self.get_used_currency_codes()
        payload = []
        for row in rows:
            code = str((row or {}).get("code") or "").upper().strip()
            if code not in allowed:
                continue
            status = str((row or {}).get("status") or "disabled").strip().lower()
            if status not in {"active", "archived", "disabled"}:
                status = "disabled"
            if status == 'disabled' and code in used_codes:
                status = 'archived'
            payload.append({"code": code, "status": status})
        self.set_app_setting("currencies_config", json.dumps(payload, ensure_ascii=False))

    def get_active_currencies(self) -> list[dict[str, str]]:
        rows = [row for row in self.get_currencies() if row.get("status") == "active"]
        return rows or [dict(CURRENCY_CATALOG[0], status='active')]

    def get_selectable_currencies(self, current_codes: Iterable[str] | None = None) -> list[dict[str, str]]:
        current = {str(code or '').upper().strip() for code in (current_codes or []) if str(code or '').strip()}
        rows = []
        seen: set[str] = set()
        for row in self.get_currencies():
            code = str(row.get('code') or '').upper().strip()
            status = str(row.get('status') or '').strip().lower()
            if status == 'active' or code in current:
                rows.append(dict(row))
                seen.add(code)
        for code in sorted(current):
            if code in seen:
                continue
            rows.append({
                'code': code,
                'symbol': self.get_currency_symbol(code),
                'name': code,
                'status': 'archived',
            })
        return rows or [dict(CURRENCY_CATALOG[0], status='active')]

    def is_currency_active(self, currency_code: str | None) -> bool:
        code = str(currency_code or 'UAH').upper().strip()
        return any(str(row.get('code') or '').upper().strip() == code and str(row.get('status') or '').lower() == 'active' for row in self.get_currencies())

    def get_currency_name(self, currency_code: str | None) -> str:
        code = str(currency_code or "UAH").upper().strip()
        for row in self.get_currencies():
            if row["code"] == code:
                return str(row.get("name") or code)
        return code

    def get_currency_symbol(self, currency_code: str | None) -> str:
        code = str(currency_code or "UAH").upper().strip()
        for row in self.get_currencies():
            if row["code"] == code:
                return str(row.get("symbol") or code)
        return {"UAH": "₴", "USD": "$", "EUR": "€", "CAD":"C$", "PLN":"zł", "CZK":"Kč", "RON":"lei", "BGN":"лв", "MDL":"L", "HUF":"Ft", "RSD":"дин", "GBP":"£", "CHF":"CHF"}.get(code, code)

    def format_money(self, amount: float, currency_code: str | None) -> str:
        return f"{float(amount or 0):.2f} {self.get_currency_symbol(currency_code)}"

    def format_currency_map(self, amounts: dict[str, float], *, include_zero: bool = False) -> str:
        lines = []
        seen = set()
        for row in self.get_currencies():
            code = row["code"]
            val = float(amounts.get(code, 0.0) or 0.0)
            if include_zero or abs(val) > 1e-9:
                lines.append(f"{self.get_currency_symbol(code)} {val:.2f}")
            seen.add(code)
        for code in sorted(amounts.keys()):
            if code in seen:
                continue
            val = float(amounts.get(code, 0.0) or 0.0)
            if include_zero or abs(val) > 1e-9:
                lines.append(f"{self.get_currency_symbol(code)} {val:.2f}")
        return "\n".join(lines) if lines else f"{self.get_currency_symbol('UAH')} 0.00"


    def get_email_signature(self) -> str:
        return self.get_app_setting(
            "email_signature",
            "З повагою\nПродакшн студія \"Мі100\""
        )

    def set_email_signature(self, value: str) -> None:
        self.set_app_setting("email_signature", value)

    def get_email_template(self, key: str) -> str:
        defaults = {
            "client_invoice": "Добрий день!\n\nНадсилаємо вам рахунок {doc_number}.\nФайл додано у вкладенні.",
            "client_act": "Добрий день!\n\nНадсилаємо вам акт {doc_number}.\nФайл додано у вкладенні.",
            "client_report": "Добрий день!\n\nНадсилаємо вам звіт {doc_number}.\nФайл додано у вкладенні.",
            "actor_report": "Добрий день!\n\nНадсилаємо вам звіт {doc_number}.\nФайл додано у вкладенні.",
        }
        return self.get_app_setting(f"email_template_{key}", defaults.get(key, ""))

    def set_email_template(self, key: str, value: str) -> None:
        self.set_app_setting(f"email_template_{key}", value)

    def build_email_body(self, *, template_key: str, doc_number: str = "", recipient_name: str = "") -> str:
        template = self.get_email_template(template_key)
        body = template.format(doc_number=doc_number or "", recipient_name=recipient_name or "")
        signature = self.get_email_signature().strip()
        if signature:
            body = (body.rstrip() + "\n\n" + signature).strip()
        return body

    def add_email_history(self, *, client_id: int, document_id: int, sent_to_email: str, subject_snapshot: str, body_snapshot: str) -> int:
        ts = now_iso()
        cur = self.conn.execute(
            """INSERT INTO email_send_history(
                client_id, document_id, sent_to_email, sent_at, subject_snapshot, body_snapshot, created_at
            ) VALUES(?,?,?,?,?,?,?);""",
            (int(client_id), int(document_id), sent_to_email, ts, subject_snapshot, body_snapshot, ts),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def list_client_email_history(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT h.*, d.doc_type, d.doc_number, d.file_path
            FROM email_send_history h
            LEFT JOIN documents d ON d.id = h.document_id
            WHERE h.client_id=?
            ORDER BY h.sent_at DESC, h.id DESC
            """,
            (int(client_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def delete_email_history(self, history_id: int) -> None:
        self.conn.execute("DELETE FROM email_send_history WHERE id=?;", (int(history_id),))
        self.conn.commit()

    # ---------- Clients ----------
    def create_client(
        self,
        display_name: str,
        company_name: str | None = None,
        responsible_person_name: str | None = None,
        phone: str | None = None,
        email: str | None = None,
        requisites_text: str = "",
        legal_block_text: str = "",
        authorized_signer_name: str = "",
        authorized_signer_position: str = "",
        contract_number: str | None = None,
        contract_date_text: str | None = None,
        notes: str | None = None,
    ) -> int:
        ts = now_iso()
        if not (display_name or '').strip():
            display_name = company_name or ''
        if not (company_name or '').strip():
            company_name = display_name

        cur = self.conn.execute(
            """INSERT INTO clients(
                display_name, company_name, responsible_person_name, phone, email,
                requisites_text, legal_block_text,
                authorized_signer_name, authorized_signer_position,
                contract_number, contract_date_text, notes, created_at, updated_at
            ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?);""",
            (
                display_name, company_name, responsible_person_name, phone, email,
                requisites_text, legal_block_text,
                authorized_signer_name, authorized_signer_position,
                contract_number, contract_date_text, notes, ts, ts
            ),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def update_client(self, client_id: int, **fields: Any) -> None:
        if not fields:
            return
        fields["updated_at"] = now_iso()
        cols = ", ".join([f"{k}=?" for k in fields.keys()])
        vals = list(fields.values()) + [client_id]
        self.conn.execute(f"UPDATE clients SET {cols} WHERE id=?;", vals)
        self.conn.commit()

    def get_client(self, client_id: int) -> sqlite3.Row:
        """Отримати одного замовника за id."""
        row = self.conn.execute("SELECT * FROM clients WHERE id=?;", (int(client_id),)).fetchone()
        if row is None:
            raise KeyError(f"Client not found: {client_id}")
        return row

    def get_client_total_payments_by_currency(self, client_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT currency, COALESCE(SUM(amount),0) AS s FROM client_payments WHERE client_id=? GROUP BY currency;",
            (int(client_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_client_total_payments(self, client_id: int) -> float:
        return float(sum(self.get_client_total_payments_by_currency(client_id).values()))

    def get_client_allocated_payments_by_currency(self, client_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT currency, COALESCE(SUM(total_amount),0) AS s FROM projects WHERE client_id=? AND client_paid=1 GROUP BY currency;",
            (int(client_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_client_allocated_payments(self, client_id: int) -> float:
        return float(sum(self.get_client_allocated_payments_by_currency(client_id).values()))

    def get_client_balance_map(self, client_id: int) -> dict[str, float]:
        paid = self.get_client_total_payments_by_currency(client_id)
        allocated = self.get_client_allocated_payments_by_currency(client_id)
        codes = set(paid) | set(allocated)
        return {code: float(paid.get(code, 0.0) - allocated.get(code, 0.0)) for code in codes}

    def get_client_balance(self, client_id: int) -> float:
        return float(sum(self.get_client_balance_map(client_id).values()))

    def get_client_unpaid_totals_by_currency(self, client_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT currency, COALESCE(SUM(total_amount),0) AS s FROM projects WHERE client_id=? AND client_paid=0 GROUP BY currency;",
            (int(client_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_client_unpaid_total(self, client_id: int) -> float:
        return float(sum(self.get_client_unpaid_totals_by_currency(client_id).values()))

    def get_client_debt_map(self, client_id: int) -> dict[str, float]:
        unpaid = self.get_client_unpaid_totals_by_currency(client_id)
        balance = self.get_client_balance_map(client_id)
        codes = set(unpaid) | set(balance)
        out: dict[str, float] = {}
        for code in codes:
            debt = float(unpaid.get(code, 0.0) - balance.get(code, 0.0))
            out[code] = max(0.0, debt)
        return out

    def get_client_debt(self, client_id: int) -> float:
        return float(sum(self.get_client_debt_map(client_id).values()))

    def get_total_clients_debt_map(self) -> dict[str, float]:
        totals: dict[str, float] = {}
        cur = self.conn.execute("SELECT id FROM clients;")
        for row in cur.fetchall():
            for code, value in self.get_client_debt_map(int(row[0])).items():
                totals[code] = totals.get(code, 0.0) + float(value or 0.0)
        return totals

    def get_total_clients_debt(self) -> float:
        return float(sum(self.get_total_clients_debt_map().values()))

    def delete_client(self, client_id: int) -> None:

        self.conn.execute("DELETE FROM clients WHERE id=?;", (int(client_id),))
        self.conn.commit()

    def list_client_payments(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT id, client_id, payment_date, amount, currency, comment
            FROM client_payments
            WHERE client_id=?
            ORDER BY payment_date DESC, id DESC
            """,
            (int(client_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def add_client_payment(self, client_id: int, payment_date: str, amount: float, currency: str = "UAH", comment: str = "") -> int:
        ts = now_iso()
        cur = self.conn.execute(
            """
            INSERT INTO client_payments(client_id, payment_date, amount, currency, comment, created_at, updated_at)
            VALUES(?,?,?,?,?,?,?);
            """,
            (int(client_id), str(payment_date), float(amount), str(currency or 'UAH').upper(), (comment or "").strip(), ts, ts),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def delete_client_payment(self, payment_id: int) -> None:
        self.conn.execute("DELETE FROM client_payments WHERE id=?;", (int(payment_id),))
        self.conn.commit()

    def can_pay_project(self, project_id: int) -> bool:
        row = self.conn.execute("SELECT client_id, total_amount, client_paid, currency FROM projects WHERE id=?;", (int(project_id),)).fetchone()
        if not row:
            return False
        if int(row["client_paid"] or 0) == 1:
            return True
        currency = str(row["currency"] or 'UAH')
        return float(self.get_client_balance_map(int(row["client_id"])).get(currency, 0.0)) + 1e-9 >= float(row["total_amount"] or 0.0)

    def list_clients(self, search: str = "", debtors_only: bool = False) -> list[sqlite3.Row]:
        search = (search or "").strip()
        where = []
        params: list[Any] = []
        if search:
            where.append("(c.display_name LIKE ? OR c.company_name LIKE ? OR c.responsible_person_name LIKE ? OR c.email LIKE ? OR c.phone LIKE ?)")
            s = f"%{search}%"
            params += [s, s, s, s, s]
        sql = "SELECT c.* FROM clients c"
        if where:
            sql += " WHERE " + " AND ".join(where)
        sql += " ORDER BY COALESCE(c.company_name, c.display_name, '') COLLATE NOCASE"
        rows = list(self.conn.execute(sql, params).fetchall())
        out = []
        for row in rows:
            item = dict(row)
            cid = int(item['id'])
            balance_map = self.get_client_balance_map(cid)
            debt_map = self.get_client_debt_map(cid)
            item['balance'] = float(sum(balance_map.values()))
            item['debt'] = float(sum(debt_map.values()))
            item['balance_map'] = balance_map
            item['debt_map'] = debt_map
            item['balance_display'] = self.format_currency_map(balance_map)
            item['debt_display'] = self.format_currency_map(debt_map)
            if debtors_only and all(abs(v) < 1e-9 for v in debt_map.values()):
                continue
            out.append(item)
        class RowLike(dict):
            def keys(self):
                return super().keys()
        return [RowLike(r) for r in out]


    def list_client_projects(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT
                p.id,
                p.project_number,
                p.created_date,
                p.title,
                p.work_status,
                p.client_paid,
                p.currency,
                p.total_amount,
                COALESCE(p.project_folder_path, '') AS project_folder_path,
                COALESCE((
                    SELECT GROUP_CONCAT(COALESCE(va.display_name, pva.voice_actor_name_snapshot), ', ')
                    FROM project_voice_actors pva
                    LEFT JOIN voice_actors va ON va.id = pva.voice_actor_id
                    WHERE pva.project_id = p.id
                ), '') AS actor_names
            FROM projects p
            WHERE p.client_id=?
            ORDER BY
                CASE
                    WHEN p.work_status='IN_PROGRESS' AND p.client_paid=0 THEN 0
                    WHEN p.work_status='IN_PROGRESS' AND p.client_paid=1 THEN 1
                    WHEN p.work_status='COMPLETED' AND p.client_paid=0 THEN 2
                    ELSE 3
                END,
                p.created_date DESC, p.id DESC
            """,
            (int(client_id),),
        )
        rows = [dict(r) for r in cur.fetchall()]
        balance_map = self.get_client_balance_map(int(client_id))
        for row in rows:
            cur = str(row.get('currency') or 'UAH')
            row['payment_possible'] = 1 if int(row.get('client_paid') or 0) == 1 or float(balance_map.get(cur, 0.0)) + 1e-9 >= float(row.get('total_amount') or 0.0) else 0
        return rows

    # ---------- Voice actors ----------
    def create_voice_actor(
        self,
        display_name: str,
        phone: str = "",
        email: str = "",
        payment_details: str = "",
        notes: str = "",
        is_active: bool = True,
    ) -> int:
        """Створити диктора (voice actor). Повертає id.

        У БД поля first_name та last_name мають NOT NULL.
        У UI ми поки що працюємо з одним полем display_name, тому
        заповнюємо first_name/last_name автоматично з display_name.
        """

        dn = (display_name or "").strip()
        if not dn:
            raise ValueError("display_name is required")

        parts = [p for p in dn.split() if p]
        first_name = parts[0] if parts else dn
        last_name = " ".join(parts[1:]) if len(parts) > 1 else ""

        cur = self.conn.execute(
            """
            INSERT INTO voice_actors (
                first_name, last_name, display_name,
                phone, email, payment_details_text,
                notes, is_active,
                created_at, updated_at
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
            """,
            (
                first_name,
                last_name,
                dn,
                phone.strip(),
                email.strip(),
                payment_details.strip(),
                notes.strip(),
                1 if is_active else 0,
            ),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def update_voice_actor(
        self,
        voice_actor_id: int,
        display_name: str,
        phone: str = "",
        email: str = "",
        payment_details: str = "",
        notes: str = "",
        is_active: bool = True,
    ) -> None:
        """Оновити диктора."""
        self.conn.execute(
            """
            UPDATE voice_actors
               SET display_name=?,
                   phone=?,
                   email=?,
                   payment_details_text=?,
                   notes=?,
                   is_active=?,
                   updated_at=CURRENT_TIMESTAMP
             WHERE id=? AND deleted_at IS NULL;
            """,
            (
                display_name.strip(),
                phone.strip(),
                email.strip(),
                payment_details.strip(),
                notes.strip(),
                1 if is_active else 0,
                int(voice_actor_id),
            ),
        )
        self.conn.commit()

    def delete_voice_actor(self, voice_actor_id: int) -> None:
        """М'яке видалення диктора."""
        self.conn.execute(
            "UPDATE voice_actors SET deleted_at=CURRENT_TIMESTAMP, updated_at=CURRENT_TIMESTAMP WHERE id=?;",
            (int(voice_actor_id),),
        )
        self.conn.commit()

    def get_voice_actor(self, voice_actor_id: int):
        cur = self.conn.execute(
            "SELECT * FROM voice_actors WHERE id=? AND deleted_at IS NULL;",
            (int(voice_actor_id),),
        )
        return cur.fetchone()


    def get_voice_actor_total_payments_by_currency(self, voice_actor_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT currency, COALESCE(SUM(amount),0) AS s FROM voice_actor_payments WHERE voice_actor_id=? GROUP BY currency;",
            (int(voice_actor_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_voice_actor_total_payments(self, voice_actor_id: int) -> float:
        return float(sum(self.get_voice_actor_total_payments_by_currency(voice_actor_id).values()))

    def get_voice_actor_allocated_payments_by_currency(self, voice_actor_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT COALESCE(pva.fee_currency, 'UAH') AS currency, COALESCE(SUM(CASE WHEN COALESCE(pva.paid_to_actor,0)>0 THEN COALESCE(pva.fee,0) ELSE 0 END),0) AS s FROM project_voice_actors pva WHERE pva.voice_actor_id=? GROUP BY COALESCE(pva.fee_currency, 'UAH');",
            (int(voice_actor_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_voice_actor_allocated_payments(self, voice_actor_id: int) -> float:
        return float(sum(self.get_voice_actor_allocated_payments_by_currency(voice_actor_id).values()))

    def get_voice_actor_balance_map(self, voice_actor_id: int) -> dict[str, float]:
        paid = self.get_voice_actor_total_payments_by_currency(voice_actor_id)
        allocated = self.get_voice_actor_allocated_payments_by_currency(voice_actor_id)
        codes = set(paid) | set(allocated)
        return {code: float(paid.get(code, 0.0) - allocated.get(code, 0.0)) for code in codes}

    def get_voice_actor_balance(self, voice_actor_id: int) -> float:
        return float(sum(self.get_voice_actor_balance_map(voice_actor_id).values()))

    def get_voice_actor_unpaid_totals_by_currency(self, voice_actor_id: int) -> dict[str, float]:
        cur = self.conn.execute(
            "SELECT COALESCE(pva.fee_currency, 'UAH') AS currency, COALESCE(SUM(CASE WHEN COALESCE(pva.paid_to_actor,0)=0 THEN COALESCE(pva.fee,0) ELSE 0 END),0) AS s FROM project_voice_actors pva WHERE pva.voice_actor_id=? GROUP BY COALESCE(pva.fee_currency, 'UAH');",
            (int(voice_actor_id),),
        )
        return {str(r[0] or 'UAH'): float(r[1] or 0.0) for r in cur.fetchall()}

    def get_voice_actor_unpaid_total(self, voice_actor_id: int) -> float:
        return float(sum(self.get_voice_actor_unpaid_totals_by_currency(voice_actor_id).values()))

    def get_voice_actor_debt_map(self, voice_actor_id: int) -> dict[str, float]:
        unpaid = self.get_voice_actor_unpaid_totals_by_currency(voice_actor_id)
        balance = self.get_voice_actor_balance_map(voice_actor_id)
        codes = set(unpaid) | set(balance)
        out: dict[str, float] = {}
        for code in codes:
            debt = float(unpaid.get(code, 0.0) - balance.get(code, 0.0))
            out[code] = max(0.0, debt)
        return out

    def get_voice_actor_debt(self, voice_actor_id: int) -> float:
        return float(sum(self.get_voice_actor_debt_map(voice_actor_id).values()))

    def list_voice_actor_payments(self, voice_actor_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            "SELECT id, voice_actor_id, payment_date, amount, currency, comment FROM voice_actor_payments WHERE voice_actor_id=? ORDER BY payment_date DESC, id DESC;",
            (int(voice_actor_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def add_voice_actor_payment(self, voice_actor_id: int, payment_date: str, amount: float, currency: str = "UAH", comment: str = "") -> int:
        ts = now_iso()
        cur = self.conn.execute(
            """INSERT INTO voice_actor_payments(voice_actor_id, payment_date, amount, currency, comment, created_at, updated_at)
               VALUES(?,?,?,?,?,?,?);""",
            (int(voice_actor_id), str(payment_date), float(amount or 0.0), str(currency or 'UAH').upper(), str(comment or ''), ts, ts),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def delete_voice_actor_payment(self, payment_id: int) -> None:
        self.conn.execute("DELETE FROM voice_actor_payments WHERE id=?;", (int(payment_id),))
        self.conn.commit()

    def list_voice_actors(self, include_inactive: bool = True):
        """Список дикторів + борг з урахуванням балансу."""
        where = "WHERE deleted_at IS NULL"
        params: list = []
        if not include_inactive:
            where += " AND is_active=1"
        cur = self.conn.execute(f"SELECT * FROM voice_actors {where} ORDER BY display_name COLLATE NOCASE;", params)
        rows = []
        for row in cur.fetchall():
            item = dict(row)
            item['debt_to_actor'] = float(self.get_voice_actor_debt(int(item['id'])))
            item['debt_map'] = self.get_voice_actor_debt_map(int(item['id']))
            item['balance_map'] = self.get_voice_actor_balance_map(int(item['id']))
            item['debt_display'] = self.format_currency_map(item['debt_map'])
            item['balance_display'] = self.format_currency_map(item['balance_map'])
            rows.append(item)
        return rows

    def voice_actor_debt(self, voice_actor_id: int) -> int:
        return int(self.get_voice_actor_debt(int(voice_actor_id)))

    def list_voice_actor_project_items(self, voice_actor_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT
                p.id AS project_id,
                p.created_date,
                p.title,
                COALESCE(pva.fee_currency, 'UAH') AS currency,
                COALESCE(p.project_folder_path, '') AS project_folder_path,
                COALESCE(pva.fee, 0) AS fee,
                CASE WHEN COALESCE(pva.paid_to_actor, 0) > 0 THEN 1 ELSE 0 END AS paid_to_actor,
                CASE
                    WHEN COALESCE(pva.paid_to_actor, 0) > 0 THEN 1
                    WHEN (SELECT COALESCE(SUM(vap.amount),0) FROM voice_actor_payments vap WHERE vap.voice_actor_id=pva.voice_actor_id AND vap.currency=COALESCE(pva.fee_currency,'UAH'))
                         - (SELECT COALESCE(SUM(CASE WHEN COALESCE(pp.paid_to_actor,0)>0 THEN COALESCE(pp.fee,0) ELSE 0 END),0) FROM project_voice_actors pp WHERE pp.voice_actor_id=pva.voice_actor_id AND COALESCE(pp.fee_currency,'UAH')=COALESCE(pva.fee_currency,'UAH'))
                         >= COALESCE(pva.fee, 0)
                    THEN 1 ELSE 0
                END AS payment_possible
            FROM project_voice_actors pva
            JOIN projects p ON p.id = pva.project_id
            WHERE pva.voice_actor_id=?
            ORDER BY
                CASE WHEN COALESCE(pva.paid_to_actor, 0) > 0 THEN 1 ELSE 0 END ASC,
                p.created_date DESC,
                p.id DESC
            """,
            (int(voice_actor_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def set_voice_actor_project_paid(self, voice_actor_id: int, project_id: int, paid: bool) -> None:
        cur = self.conn.execute(
            "SELECT COALESCE(pva.fee, 0), COALESCE(pva.paid_to_actor,0), COALESCE(pva.fee_currency,'UAH') FROM project_voice_actors pva WHERE pva.voice_actor_id=? AND pva.project_id=?;",
            (int(voice_actor_id), int(project_id)),
        )
        row = cur.fetchone()
        fee = float(row[0] if row and row[0] is not None else 0.0)
        current_paid = float(row[1] if row and row[1] is not None else 0.0)
        fee_currency = str(row[2] or 'UAH') if row and row[2] is not None else 'UAH'
        if paid and fee > 0 and current_paid <= 0:
            balance = float(self.get_voice_actor_balance_map(int(voice_actor_id)).get(fee_currency, 0.0))
            if balance + 1e-9 < fee:
                raise ValueError("INSUFFICIENT_VOICE_ACTOR_BALANCE")
        self.conn.execute(
            "UPDATE project_voice_actors SET paid_to_actor=?, updated_at=CURRENT_TIMESTAMP WHERE voice_actor_id=? AND project_id=?;",
            (fee if paid else 0, int(voice_actor_id), int(project_id)),
        )
        self.conn.commit()

    def set_all_voice_actor_projects_paid(self, voice_actor_id: int) -> None:
        rows = self.list_voice_actor_project_items(int(voice_actor_id))
        for row in rows:
            fee = float(row.get('fee') or 0.0)
            if fee <= 0 or int(row.get('paid_to_actor') or 0) != 0:
                continue
            self.set_voice_actor_project_paid(int(voice_actor_id), int(row['project_id']), True)
    # ---------- Documents ----------
    def list_projects_for_ids(self, project_ids: list[int], client_id: int | None = None) -> list[dict[str, Any]]:
        ids = [int(x) for x in project_ids if x is not None]
        if not ids:
            return []
        placeholders = ",".join(["?"] * len(ids))
        params: list[Any] = ids[:]
        where = [f"p.id IN ({placeholders})"]
        if client_id is not None:
            where.append("p.client_id=?")
            params.append(int(client_id))
        cur = self.conn.execute(
            f"""
            SELECT p.id, p.project_number, p.created_date, p.title, p.work_status, p.client_paid, p.currency, p.total_amount
            FROM projects p
            WHERE {' AND '.join(where)}
            ORDER BY p.created_date ASC, p.project_number ASC
            """,
            params,
        )
        return [dict(r) for r in cur.fetchall()]

    def next_document_number(self, doc_type: str) -> str:
        if doc_type in ("INVOICE", "ACT"):
            prefix = "Р" if doc_type == "INVOICE" else "А"
            dt = datetime.now()
            while True:
                candidate = prefix + dt.strftime("%H%M%S%d%m%Y")
                exists = self.conn.execute(
                    "SELECT 1 FROM documents WHERE doc_number=? LIMIT 1;",
                    (candidate,),
                ).fetchone()
                if not exists:
                    return candidate
                dt += timedelta(seconds=1)

        if doc_type == "CLIENT_REPORT":
            prefix = "CRPT"
        elif doc_type == "ACTOR_REPORT":
            prefix = "ARPT"
        else:
            prefix = "DOC"
        rows = self.conn.execute(
            "SELECT doc_number FROM documents WHERE doc_type=? ORDER BY id ASC;",
            (doc_type,),
        ).fetchall()
        max_num = 0
        for row in rows:
            doc_number = str(row["doc_number"] or "")
            if doc_number.startswith(prefix + "-"):
                try:
                    max_num = max(max_num, int(doc_number.split("-", 1)[1]))
                except Exception:
                    pass
        return f"{prefix}-{max_num + 1:04d}"

    def create_document(
        self,
        *,
        doc_type: str,
        client_id: int | None,
        voice_actor_id: int | None,
        doc_number: str,
        related_invoice_id: int | None = None,
        currency: str = "UAH",
        total_amount: float = 0.0,
        created_date: str = "",
        with_contract: bool = False,
        contract_number_snapshot: str | None = None,
        contract_date_snapshot: str | None = None,
        file_path: str = "",
        notes: str | None = None,
        projects: list[dict[str, Any]] | None = None,
    ) -> int:
        ts = now_iso()
        cur = self.conn.execute(
            """INSERT INTO documents(
                doc_type, client_id, voice_actor_id, doc_number, related_invoice_id,
                currency, total_amount, created_date, with_contract,
                contract_number_snapshot, contract_date_snapshot, file_path, notes, created_at, updated_at
            ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""",
            (
                doc_type, client_id, voice_actor_id, doc_number, related_invoice_id,
                currency, float(total_amount or 0), created_date, 1 if with_contract else 0,
                contract_number_snapshot, contract_date_snapshot, file_path, notes, ts, ts,
            ),
        )
        document_id = int(cur.lastrowid)
        for p in (projects or []):
            self.conn.execute(
                """INSERT INTO document_projects(
                    document_id, project_id, project_number_snapshot, project_title_snapshot,
                    project_date_snapshot, amount_snapshot, currency_snapshot, created_at
                ) VALUES(?,?,?,?,?,?,?,?);""",
                (
                    document_id,
                    int(p["id"]),
                    int(p.get("project_number") or 0),
                    str(p.get("title") or ""),
                    str(p.get("created_date") or ""),
                    float(p.get("total_amount") or 0),
                    str(p.get("currency") or currency or "UAH"),
                    ts,
                ),
            )
        self.conn.commit()
        return document_id

    def list_client_documents(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT d.*,
                   CASE d.doc_type
                       WHEN 'INVOICE' THEN 'Рахунок'
                       WHEN 'ACT' THEN 'Акт'
                       ELSE d.doc_type
                   END AS doc_type_label,
                   COUNT(dp.id) AS project_count
            FROM documents d
            LEFT JOIN document_projects dp ON dp.document_id = d.id
            WHERE d.client_id=? AND d.doc_type IN ('INVOICE','ACT')
            GROUP BY d.id
            ORDER BY d.created_date DESC, d.id DESC
            """,
            (int(client_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    @staticmethod
    def _report_period_note(start_date: str | None, end_date: str | None, unpaid_only: bool, free_only: bool=False) -> str:
        return f"period_start={start_date or ''}|period_end={end_date or ''}|unpaid_only={1 if unpaid_only else 0}|free_only={1 if free_only else 0}"

    @staticmethod
    def _parse_report_period_note(notes: str | None) -> tuple[str, str, bool, bool]:
        data = {}
        for part in str(notes or '').split('|'):
            if '=' in part:
                k, v = part.split('=', 1)
                data[k.strip()] = v.strip()
        return data.get('period_start', ''), data.get('period_end', ''), data.get('unpaid_only', '0') == '1', data.get('free_only', '0') == '1'

    def list_client_reports(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT * FROM documents
            WHERE client_id=? AND doc_type='CLIENT_REPORT'
            ORDER BY created_date DESC, id DESC
            """,
            (int(client_id),),
        )
        rows = [dict(r) for r in cur.fetchall()]
        for row in rows:
            start, end, unpaid, free_only = self._parse_report_period_note(row.get('notes'))
            row['period_start'] = start
            row['period_end'] = end
            row['unpaid_only'] = unpaid
            row['free_only'] = free_only
        return rows

    def list_actor_reports(self, voice_actor_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT * FROM documents
            WHERE voice_actor_id=? AND doc_type='ACTOR_REPORT'
            ORDER BY created_date DESC, id DESC
            """,
            (int(voice_actor_id),),
        )
        rows = [dict(r) for r in cur.fetchall()]
        for row in rows:
            start, end, unpaid, free_only = self._parse_report_period_note(row.get('notes'))
            row['period_start'] = start
            row['period_end'] = end
            row['unpaid_only'] = unpaid
            row['free_only'] = free_only
        return rows

    def list_client_invoices(self, client_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT d.*, COUNT(dp.id) AS project_count
            FROM documents d
            LEFT JOIN document_projects dp ON dp.document_id=d.id
            WHERE d.client_id=? AND d.doc_type='INVOICE'
            GROUP BY d.id
            ORDER BY d.created_date DESC, d.id DESC
            """,
            (int(client_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def get_document(self, document_id: int) -> dict[str, Any] | None:
        row = self.conn.execute(
            "SELECT * FROM documents WHERE id=?;",
            (int(document_id),),
        ).fetchone()
        return dict(row) if row else None

    def get_document_projects(self, document_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT * FROM document_projects
            WHERE document_id=?
            ORDER BY project_date_snapshot ASC, project_number_snapshot ASC, id ASC
            """,
            (int(document_id),),
        )
        return [dict(r) for r in cur.fetchall()]

    def has_act_for_invoice(self, invoice_id: int) -> bool:
        row = self.conn.execute(
            "SELECT 1 FROM documents WHERE doc_type='ACT' AND related_invoice_id=? LIMIT 1;",
            (int(invoice_id),),
        ).fetchone()
        return row is not None

    def _parse_invoice_ids_from_notes(self, notes: str | None) -> list[int]:
        text = str(notes or "")
        m = re.search(r"\[invoice_ids:([0-9, ]+)\]", text)
        if not m:
            return []
        result: list[int] = []
        for part in m.group(1).split(','):
            part = part.strip()
            if part.isdigit():
                result.append(int(part))
        return result

    def get_document_linked_invoice_ids(self, document_id: int) -> list[int]:
        doc = self.get_document(int(document_id))
        if not doc:
            return []
        ids: list[int] = []
        rid = doc.get('related_invoice_id')
        if rid:
            ids.append(int(rid))
        for inv_id in self._parse_invoice_ids_from_notes(doc.get('notes')):
            if inv_id not in ids:
                ids.append(inv_id)
        return ids

    def invoice_has_any_act(self, invoice_id: int) -> bool:
        invoice_id = int(invoice_id)
        rows = self.conn.execute(
            "SELECT id, related_invoice_id, notes FROM documents WHERE doc_type='ACT';"
        ).fetchall()
        for row in rows:
            ids = []
            if row['related_invoice_id']:
                ids.append(int(row['related_invoice_id']))
            ids.extend([x for x in self._parse_invoice_ids_from_notes(row['notes']) if x not in ids])
            if invoice_id in ids:
                return True
        return False

    def get_invoice_projects_state(self, client_id: int) -> list[dict[str, Any]]:
        rows = self.list_client_projects(int(client_id))
        for row in rows:
            inv = self.conn.execute(
                """
                SELECT d.id, d.doc_number
                FROM documents d
                JOIN document_projects dp ON dp.document_id=d.id
                WHERE d.doc_type='INVOICE' AND d.client_id=? AND dp.project_id=?
                ORDER BY d.id DESC LIMIT 1
                """,
                (int(client_id), int(row['id'])),
            ).fetchone()
            row['invoice_id'] = int(inv['id']) if inv else None
            row['invoice_number'] = str(inv['doc_number']) if inv else ''
            row['has_invoice'] = inv is not None
        rows.sort(key=lambda r: (1 if r.get('has_invoice') else 0, str(r.get('created_date') or ''), int(r.get('project_number') or 0)))
        return rows

    def list_client_invoices_available_for_act(self, client_id: int) -> list[dict[str, Any]]:
        rows = self.list_client_invoices(int(client_id))
        for row in rows:
            row['has_act'] = self.invoice_has_any_act(int(row['id']))
        rows.sort(key=lambda r: (1 if r.get('has_act') else 0, str(r.get('created_date') or ''), int(r.get('id') or 0)))
        return rows

    def list_client_documents_grouped(self, client_id: int, search: str = "") -> list[dict[str, Any]]:
        docs = self.list_client_documents(int(client_id))
        search = (search or '').strip().lower()
        acts = [d for d in docs if str(d.get('doc_type')) == 'ACT']
        invoices = [d for d in docs if str(d.get('doc_type')) == 'INVOICE']
        linked_invoice_ids: set[int] = set()
        groups: list[tuple[list[dict[str, Any]], dict[str, Any]]] = []
        for act in acts:
            inv_ids = self.get_document_linked_invoice_ids(int(act['id']))
            linked_invoice_ids.update(inv_ids)
            inv_rows = [inv for inv in invoices if int(inv['id']) in inv_ids]
            if inv_rows:
                inv_rows.sort(key=lambda r: (str(r.get('created_date') or ''), int(r.get('id') or 0)))
                groups.append((inv_rows, act))
        unlinked = [inv for inv in invoices if int(inv['id']) not in linked_invoice_ids]
        unlinked.sort(key=lambda r: (str(r.get('created_date') or ''), int(r.get('id') or 0)), reverse=True)
        groups.sort(key=lambda g: (str(g[1].get('created_date') or ''), int(g[1].get('id') or 0)), reverse=True)

        result: list[dict[str, Any]] = []
        for inv in unlinked:
            item = dict(inv)
            item['_group_sort'] = 0
            item['_group_marker'] = ''
            item['_linked_invoice_ids'] = []
            result.append(item)
        for inv_rows, act in groups:
            for idx, inv in enumerate(inv_rows):
                item = dict(inv)
                item['_group_sort'] = 1
                item['_group_marker'] = '┏' if idx == 0 else '┃'
                item['_linked_invoice_ids'] = [int(x['id']) for x in inv_rows]
                result.append(item)
            act_item = dict(act)
            act_item['_group_sort'] = 1
            act_item['_group_marker'] = '┗'
            act_item['_linked_invoice_ids'] = [int(x['id']) for x in inv_rows]
            result.append(act_item)

        if search:
            filtered = []
            for row in result:
                hay = f"{row.get('doc_number','')} {row.get('created_date','')}".lower()
                if search in hay:
                    filtered.append(row)
            result = filtered
        return result

    def delete_act_and_optionally_invoices(self, act_id: int, delete_invoices: bool = False) -> list[str]:
        removed_files: list[str] = []
        doc = self.get_document(int(act_id))
        if not doc or str(doc.get('doc_type')) != 'ACT':
            return removed_files
        inv_ids = self.get_document_linked_invoice_ids(int(act_id))
        removed_files.append(str(doc.get('file_path') or ''))
        self.conn.execute("DELETE FROM documents WHERE id=?;", (int(act_id),))
        if delete_invoices:
            for inv_id in inv_ids:
                inv = self.get_document(int(inv_id))
                if inv:
                    removed_files.append(str(inv.get('file_path') or ''))
                self.conn.execute("DELETE FROM documents WHERE id=?;", (int(inv_id),))
        self.conn.commit()
        return [p for p in removed_files if p]

    def delete_document(self, document_id: int) -> None:
        self.conn.execute("DELETE FROM documents WHERE id=?;", (int(document_id),))
        self.conn.commit()

    def get_next_project_number(self) -> int:
        return int(get_next_project_number(self.conn))

    def create_project(
        self,
        client_id: int,
        title: str,
        created_date: str,
        work_status: str = "IN_PROGRESS",
        client_paid: int = 0,
        currency: str = "UAH",
        total_amount: float = 0.0,
        notes: str | None = None,
        voice_actor_id: Optional[int] = None,
        voice_actor_name_snapshot: str = "",
        actor_fee: float = 0.0,
        actor_rows: Optional[list[dict[str, Any]]] = None,
    ) -> int:
        ts = now_iso()
        number = get_next_project_number(self.conn)
        cur = self.conn.execute(
            """INSERT INTO projects(
                project_number, client_id, title, created_date, work_status,
                client_paid, currency, total_amount, notes, created_at, updated_at
            ) VALUES(?,?,?,?,?,?,?,?,?,?,?);""",
            (number, client_id, title, created_date, work_status, client_paid, currency, float(total_amount), notes, ts, ts),
        )
        project_id = int(cur.lastrowid)

        rows = actor_rows
        if rows is None:
            rows = []
            if voice_actor_id is not None:
                rows.append({
                    "voice_actor_id": int(voice_actor_id),
                    "voice_actor_name_snapshot": (voice_actor_name_snapshot or "").strip(),
                    "fee": float(actor_fee or 0.0),
                    "fee_currency": "UAH",
                    "paid_to_actor": 0,
                })

        for r in rows:
            vid = r.get("voice_actor_id")
            if vid in (None, ""):
                continue
            self.conn.execute(
                """INSERT INTO project_voice_actors(
                        project_id, voice_actor_id, voice_actor_name_snapshot,
                        fee, fee_currency, paid_to_actor, created_at, updated_at
                    ) VALUES(?,?,?,?,?,?,?,?);""",
                (
                    project_id,
                    int(vid),
                    (r.get("voice_actor_name_snapshot") or "").strip(),
                    float(r.get("fee", 0.0) or 0.0),
                    str(r.get("fee_currency") or "UAH").upper(),
                    int(r.get("paid_to_actor", 0) or 0),
                    ts,
                    ts,
                ),
            )
        self.conn.commit()
        return project_id

    def update_project(self, project_id: int, **fields: Any) -> None:
        if not fields:
            return

        actor_rows = fields.pop("actor_rows", None)
        actor_keys = {"voice_actor_id", "voice_actor_name_snapshot", "actor_fee", "fee_currency", "paid_to_actor"}
        actor_fields = {k: fields.pop(k) for k in list(fields.keys()) if k in actor_keys}

        if fields:
            fields["updated_at"] = now_iso()
            cols = ", ".join([f"{k}=?" for k in fields.keys()])
            vals = list(fields.values()) + [project_id]
            self.conn.execute(f"UPDATE projects SET {cols} WHERE id=?;", vals)

        ts = now_iso()
        if actor_rows is not None:
            self.conn.execute("DELETE FROM project_voice_actors WHERE project_id=?;", (int(project_id),))
            for r in actor_rows:
                vid = r.get("voice_actor_id")
                if vid in (None, ""):
                    continue
                self.conn.execute(
                    """INSERT INTO project_voice_actors(
                            project_id, voice_actor_id, voice_actor_name_snapshot,
                            fee, fee_currency, paid_to_actor, created_at, updated_at
                        ) VALUES(?,?,?,?,?,?,?,?);""",
                    (
                        int(project_id),
                        int(vid),
                        (r.get("voice_actor_name_snapshot") or "").strip(),
                        float(r.get("fee", 0.0) or 0.0),
                        str(r.get("fee_currency") or "UAH").upper(),
                        int(r.get("paid_to_actor", 0) or 0),
                        ts,
                        ts,
                    ),
                )
        elif actor_fields:
            self.conn.execute("DELETE FROM project_voice_actors WHERE project_id=?;", (int(project_id),))
            vid = actor_fields.get("voice_actor_id")
            if vid is not None:
                self.conn.execute(
                    """INSERT INTO project_voice_actors(
                            project_id, voice_actor_id, voice_actor_name_snapshot,
                            fee, fee_currency, paid_to_actor, created_at, updated_at
                        ) VALUES(?,?,?,?,?,?,?,?);""",
                    (
                        int(project_id),
                        int(vid),
                        (actor_fields.get("voice_actor_name_snapshot") or "").strip(),
                        float(actor_fields.get("actor_fee", 0.0) or 0.0),
                        str(actor_fields.get("fee_currency") or "UAH").upper(),
                        int(actor_fields.get("paid_to_actor", 0) or 0),
                        ts,
                        ts,
                    ),
                )

        self.conn.commit()

    def delete_project(self, project_id: int) -> None:
        self.conn.execute("DELETE FROM projects WHERE id=?;", (project_id,))
        self.conn.commit()

    def get_project_actor_rows(self, project_id: int) -> list[dict[str, Any]]:
        cur = self.conn.execute(
            """
            SELECT pva.*, COALESCE(va.display_name, pva.voice_actor_name_snapshot, '') AS voice_actor_name
            FROM project_voice_actors pva
            LEFT JOIN voice_actors va ON va.id = pva.voice_actor_id
            WHERE pva.project_id=?
            ORDER BY pva.id ASC
            """,
            (int(project_id),),
        )
        return [dict(r) for r in cur.fetchall()]


    def list_projects(
        self,
        search: str = "",
        show_all: bool = False,
        show_debtors_only: bool = False,
    ) -> List[Dict[str, Any]]:
        search = (search or "").strip()
        where = []
        params: list[Any] = []
        if search:
            where.append("""(
                CAST(project_number AS TEXT) LIKE ?
                OR title LIKE ?
                OR created_date LIKE ?
                OR client_id IN (SELECT id FROM clients WHERE display_name LIKE ? OR company_name LIKE ?)
            )""")
            s = f"%{search}%"
            params += [s, s, s, s, s]
        if not show_all:
            where.append("(work_status='IN_PROGRESS' OR (work_status='COMPLETED' AND client_paid=0))")
        if show_debtors_only:
            where.append("p.client_paid=0")
        sql = """SELECT
                    p.*,
                    COALESCE(c.company_name, c.display_name, '') AS client_name,
                    COALESCE(pva.actor_names, '') AS voice_actor_name,
                    COALESCE(pva.actor_count, 0) AS actor_count,
                    COALESCE(pva.total_actor_fee, 0) AS actor_fee,
                    COALESCE(pva.any_paid_to_actor, 0) AS paid_to_actor
                 FROM projects p
                 JOIN clients c ON c.id = p.client_id
                 LEFT JOIN (
                    SELECT
                        pv.project_id,
                        GROUP_CONCAT(COALESCE(va.display_name, pv.voice_actor_name_snapshot), ', ') AS actor_names,
                        COUNT(*) AS actor_count,
                        COALESCE(SUM(pv.fee), 0) AS total_actor_fee,
                        MAX(pv.paid_to_actor) AS any_paid_to_actor
                    FROM project_voice_actors pv
                    LEFT JOIN voice_actors va ON va.id = pv.voice_actor_id
                    GROUP BY pv.project_id
                 ) pva ON pva.project_id = p.id"""
        if where:
            sql += " WHERE " + " AND ".join(where)
        sql += """ ORDER BY
                    CASE
                        WHEN p.work_status='IN_PROGRESS' AND p.client_paid=0 THEN 0
                        WHEN p.work_status='IN_PROGRESS' AND p.client_paid=1 THEN 1
                        WHEN p.work_status='COMPLETED' AND p.client_paid=0 THEN 2
                        ELSE 3
                    END,
                    p.created_date DESC, p.project_number DESC;"""
        cur = self.conn.execute(sql, params)
        return [dict(r) for r in cur.fetchall()]


    def get_project(self, project_id: int) -> Optional[Dict[str, Any]]:
        row = self.conn.execute(
            """
            SELECT
                p.*,
                COALESCE(c.company_name, c.display_name, '') AS client_name,
                COALESCE(pva.actor_names, '') AS voice_actor_name,
                COALESCE(pva.actor_count, 0) AS actor_count,
                COALESCE(pva.total_actor_fee, 0) AS actor_fee,
                COALESCE(pva.any_paid_to_actor, 0) AS paid_to_actor
            FROM projects p
            JOIN clients c ON c.id = p.client_id
            LEFT JOIN (
                SELECT
                    pv.project_id,
                    GROUP_CONCAT(COALESCE(va.display_name, pv.voice_actor_name_snapshot), ', ') AS actor_names,
                    COUNT(*) AS actor_count,
                    COALESCE(SUM(pv.fee), 0) AS total_actor_fee,
                    MAX(pv.paid_to_actor) AS any_paid_to_actor
                FROM project_voice_actors pv
                LEFT JOIN voice_actors va ON va.id = pv.voice_actor_id
                GROUP BY pv.project_id
            ) pva ON pva.project_id = p.id
            WHERE p.id = ?
            """,
            (int(project_id),),
        ).fetchone()
        if not row:
            return None
        data = dict(row)
        data["actor_rows"] = self.get_project_actor_rows(int(project_id))
        return data

    def count_debtor_clients(self) -> int:
        cur = self.conn.execute("SELECT id FROM clients ORDER BY id;")
        count = 0
        for r in cur.fetchall():
            try:
                if self.get_client_debt(int(r['id'])) > 0:
                    count += 1
            except Exception:
                pass
        return count

    def get_project_folder_path(self, project_id: int) -> str:
        row = self.conn.execute("SELECT COALESCE(project_folder_path, '') AS p FROM projects WHERE id=?;", (int(project_id),)).fetchone()
        return str(row['p']) if row else ''

    def set_project_folder_path(self, project_id: int, folder_path: str) -> None:
        self.conn.execute(
            "UPDATE projects SET project_folder_path=?, updated_at=? WHERE id=?;",
            (str(folder_path or ''), now_iso(), int(project_id)),
        )
        self.conn.commit()

    def set_project_work_status(self, project_id: int, work_status: str) -> None:
        status = str(work_status or 'IN_PROGRESS').upper().strip()
        if status not in ('IN_PROGRESS', 'COMPLETED'):
            status = 'IN_PROGRESS'
        self.update_project(int(project_id), work_status=status)

    def set_project_client_paid(self, project_id: int, client_paid: int) -> None:
        val = 1 if int(client_paid) == 1 else 0
        row = self.conn.execute(
            "SELECT client_id, total_amount, client_paid, currency FROM projects WHERE id=?",
            (int(project_id),),
        ).fetchone()
        if not row:
            raise KeyError(f"Project not found: {project_id}")
        current_paid = 1 if int(row["client_paid"] or 0) == 1 else 0
        if current_paid == val:
            return
        if val == 1:
            cur = str(row['currency'] or 'UAH')
            balance = float(self.get_client_balance_map(int(row["client_id"])).get(cur, 0.0))
            needed = float(row["total_amount"] or 0.0)
            if balance + 1e-9 < needed:
                raise ValueError("INSUFFICIENT_CLIENT_BALANCE")
        self.conn.execute(
            "UPDATE projects SET client_paid=?, updated_at=? WHERE id=?",
            (val, now_iso(), int(project_id)),
        )
        self.conn.commit()
