# -*- coding: utf-8 -*-
"""
Слой данных (SQLite) для Кабинета Директора.

Модель сделана сразу под мультипользователя, хотя вход пока один:
  users             — пользователи (пока один — из auth_config.json)
  plan_tabs         — вкладки для сортировки задач «в планах»
  tasks             — задачи
  task_participants — участники ГРУППОВОЙ задачи (кто её видит и правит)
  task_images       — картинки/скрины, прикреплённые к задаче

Ключевое разделение дат:
  • deadline  — дедлайн задачи (необязательный, «чекбокс дедлайна»);
  • plan_date — на какой день календаря пользователь положил задачу при сортировке.
Это разные вещи.
"""
import os
import json
import sqlite3
from datetime import date

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "mfspro.db")

PRIORITIES = ["КРИТИЧНО", "ВАЖНО", "НА ПОТОМ", "ПОДУМАТЬ"]
PLACEMENT_INBOX = "inbox"       # ИНБОКС — только что созданные/присланные
PLACEMENT_TAB = "tab"           # разложены по вкладкам
PLACEMENT_CALENDAR = "calendar" # разложены по календарю (есть plan_date)


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    email         TEXT UNIQUE NOT NULL,
    name          TEXT NOT NULL,
    password_hash TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS plan_tabs (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name     TEXT NOT NULL,
    position INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS tasks (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    owner_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sender_id    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    description  TEXT NOT NULL,
    priority     TEXT NOT NULL DEFAULT 'ВАЖНО',
    has_deadline INTEGER NOT NULL DEFAULT 0,
    deadline     TEXT,
    notes        TEXT NOT NULL DEFAULT '',
    is_personal  INTEGER NOT NULL DEFAULT 1,
    is_group     INTEGER NOT NULL DEFAULT 0,
    placement    TEXT NOT NULL DEFAULT 'inbox',
    plan_date    TEXT,
    plan_tab_id  INTEGER REFERENCES plan_tabs(id) ON DELETE SET NULL,
    completed    INTEGER NOT NULL DEFAULT 0,
    completed_at TEXT,
    created_at   TEXT NOT NULL,
    position     INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS task_participants (
    task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (task_id, user_id)
);

CREATE TABLE IF NOT EXISTS task_images (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id  INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    filename TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS payments (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    payee       TEXT NOT NULL,                 -- «Кому» платим
    amount      REAL NOT NULL DEFAULT 0,
    deadline    TEXT,                          -- дедлайн оплаты (необязательный)
    comment     TEXT NOT NULL DEFAULT '',
    status      TEXT NOT NULL DEFAULT 'queue', -- queue | paid
    created_by  INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- кто зарегистрировал
    paid_by     INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- кто провёл оплату
    created_at  TEXT NOT NULL,
    paid_at     TEXT
);

CREATE TABLE IF NOT EXISTS payment_files (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    payment_id    INTEGER NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
    filename      TEXT NOT NULL,   -- путь относительно uploads/
    original_name TEXT NOT NULL DEFAULT '',
    is_image      INTEGER NOT NULL DEFAULT 0
);
"""


def init_db():
    """Создаёт таблицы (идемпотентно) и сидит первого пользователя из auth_config.json."""
    conn = get_db()
    try:
        conn.executescript(SCHEMA)
        conn.commit()
        _seed_user(conn)
        # Миграция: бывшая дефолтная вкладка «Общее» теперь называется «Инбокс».
        conn.execute("UPDATE plan_tabs SET name = 'Инбокс' WHERE name = 'Общее'")
        # Миграция: старые задачи из отдельного инбокса переносим во вкладку «Инбокс».
        conn.execute(
            """UPDATE tasks SET placement = 'tab',
                 plan_tab_id = (SELECT id FROM plan_tabs p
                                WHERE p.owner_id = tasks.owner_id
                                ORDER BY position, id LIMIT 1)
               WHERE placement = 'inbox'
                 AND EXISTS (SELECT 1 FROM plan_tabs p WHERE p.owner_id = tasks.owner_id)"""
        )
        conn.commit()
    finally:
        conn.close()


def _seed_user(conn):
    cur = conn.execute("SELECT COUNT(*) AS n FROM users")
    if cur.fetchone()["n"] > 0:
        return
    cfg_path = os.path.join(BASE_DIR, "auth_config.json")
    with open(cfg_path, "r", encoding="utf-8") as f:
        cfg = json.load(f)
    email = cfg["email"].strip().lower()
    name = cfg.get("name") or "Директор"
    conn.execute(
        "INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)",
        (email, name, cfg["password_hash"]),
    )
    conn.commit()


# ---------- Пользователи ----------

def get_user_by_email(conn, email):
    return conn.execute(
        "SELECT * FROM users WHERE email = ?", (email.strip().lower(),)
    ).fetchone()


def get_user(conn, user_id):
    return conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()


def list_users(conn):
    return conn.execute("SELECT id, email, name FROM users ORDER BY name").fetchall()


# ---------- Вкладки планов ----------

def list_plan_tabs(conn, owner_id):
    tabs = conn.execute(
        "SELECT * FROM plan_tabs WHERE owner_id = ? ORDER BY position, id",
        (owner_id,),
    ).fetchall()
    if not tabs:
        conn.execute(
            "INSERT INTO plan_tabs (owner_id, name, position) VALUES (?, 'Инбокс', 0)",
            (owner_id,),
        )
        conn.commit()
        tabs = conn.execute(
            "SELECT * FROM plan_tabs WHERE owner_id = ? ORDER BY position, id",
            (owner_id,),
        ).fetchall()
    return tabs


def add_plan_tab(conn, owner_id, name):
    pos = conn.execute(
        "SELECT COALESCE(MAX(position), 0) + 1 AS p FROM plan_tabs WHERE owner_id = ?",
        (owner_id,),
    ).fetchone()["p"]
    cur = conn.execute(
        "INSERT INTO plan_tabs (owner_id, name, position) VALUES (?, ?, ?)",
        (owner_id, name.strip() or "Вкладка", pos),
    )
    conn.commit()
    return cur.lastrowid


def rename_plan_tab(conn, owner_id, tab_id, name):
    tabs = list_plan_tabs(conn, owner_id)
    if not tabs:
        return
    # Папку «Инбокс» (первую) переименовывать нельзя — это точка приёма задач.
    if tab_id == tabs[0]["id"]:
        return
    name = (name or "").strip()
    if not name:
        return
    conn.execute(
        "UPDATE plan_tabs SET name = ? WHERE id = ? AND owner_id = ?",
        (name, tab_id, owner_id),
    )
    conn.commit()


def delete_plan_tab(conn, owner_id, tab_id):
    tabs = list_plan_tabs(conn, owner_id)
    if not tabs:
        return
    inbox_id = tabs[0]["id"]
    # Вкладку «Инбокс» (первую) удалять нельзя — это точка приёма задач.
    if tab_id == inbox_id:
        return
    # Задачи удаляемой вкладки переносим в «Инбокс».
    conn.execute(
        "UPDATE tasks SET plan_tab_id = ? WHERE plan_tab_id = ? AND owner_id = ?",
        (inbox_id, tab_id, owner_id),
    )
    conn.execute(
        "DELETE FROM plan_tabs WHERE id = ? AND owner_id = ?", (tab_id, owner_id)
    )
    conn.commit()


# ---------- Задачи ----------

def _task_visible_filter():
    """Задача видна пользователю, если он владелец ИЛИ участник групповой."""
    return (
        "(t.owner_id = :uid OR "
        " (t.is_group = 1 AND EXISTS (SELECT 1 FROM task_participants tp "
        "   WHERE tp.task_id = t.id AND tp.user_id = :uid)))"
    )


def list_tasks_for_user(conn, uid):
    q = f"SELECT t.* FROM tasks t WHERE {_task_visible_filter()} ORDER BY t.position, t.id"
    return conn.execute(q, {"uid": uid}).fetchall()


def list_delegated_tasks(conn, uid):
    """Задачи, которые пользователь ПОРУЧИЛ другим: он отправитель, но не владелец."""
    return conn.execute(
        "SELECT * FROM tasks WHERE sender_id = ? AND owner_id != ? "
        "ORDER BY completed, position, id",
        (uid, uid),
    ).fetchall()


def get_task(conn, task_id):
    return conn.execute("SELECT * FROM tasks WHERE id = ?", (task_id,)).fetchone()


def task_participant_ids(conn, task_id):
    rows = conn.execute(
        "SELECT user_id FROM task_participants WHERE task_id = ?", (task_id,)
    ).fetchall()
    return [r["user_id"] for r in rows]


def task_images(conn, task_id):
    rows = conn.execute(
        "SELECT id, filename FROM task_images WHERE task_id = ? ORDER BY id", (task_id,)
    ).fetchall()
    return [dict(r) for r in rows]


def create_task(conn, *, owner_id, sender_id, description, priority,
                has_deadline, deadline, notes, is_personal, is_group,
                participant_ids):
    created = date.today().isoformat()
    # Новые/присланные дела прилетают во вкладку «Инбокс» получателя (первая вкладка).
    inbox_tab = list_plan_tabs(conn, owner_id)[0]["id"]
    cur = conn.execute(
        """INSERT INTO tasks
           (owner_id, sender_id, description, priority, has_deadline, deadline,
            notes, is_personal, is_group, placement, plan_date, plan_tab_id,
            completed, completed_at, created_at, position)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'tab', NULL, ?, 0, NULL, ?, 0)""",
        (owner_id, sender_id, description.strip(), priority,
         1 if has_deadline else 0, deadline if has_deadline else None,
         notes or "", 1 if is_personal else 0, 1 if is_group else 0, inbox_tab, created),
    )
    task_id = cur.lastrowid
    if is_group:
        ids = set(participant_ids or [])
        ids.add(owner_id)
        for pid in ids:
            conn.execute(
                "INSERT OR IGNORE INTO task_participants (task_id, user_id) VALUES (?, ?)",
                (task_id, pid),
            )
    conn.commit()
    return task_id


def update_task(conn, task_id, **fields):
    allowed = {
        "description", "priority", "has_deadline", "deadline", "notes",
        "is_personal", "is_group", "placement", "plan_date", "plan_tab_id",
        "completed", "completed_at", "position",
    }
    sets, vals = [], []
    for k, v in fields.items():
        if k in allowed:
            sets.append(f"{k} = ?")
            vals.append(v)
    if not sets:
        return
    vals.append(task_id)
    conn.execute(f"UPDATE tasks SET {', '.join(sets)} WHERE id = ?", vals)
    conn.commit()


def set_participants(conn, task_id, owner_id, participant_ids):
    conn.execute("DELETE FROM task_participants WHERE task_id = ?", (task_id,))
    ids = set(participant_ids or [])
    ids.add(owner_id)
    for pid in ids:
        conn.execute(
            "INSERT OR IGNORE INTO task_participants (task_id, user_id) VALUES (?, ?)",
            (task_id, pid),
        )
    conn.commit()


def move_task(conn, task_id, *, placement, plan_date=None, plan_tab_id=None):
    conn.execute(
        "UPDATE tasks SET placement = ?, plan_date = ?, plan_tab_id = ? WHERE id = ?",
        (placement, plan_date, plan_tab_id, task_id),
    )
    conn.commit()


def toggle_completed(conn, task_id):
    t = get_task(conn, task_id)
    if not t:
        return
    new_val = 0 if t["completed"] else 1
    conn.execute(
        "UPDATE tasks SET completed = ?, completed_at = ? WHERE id = ?",
        (new_val, date.today().isoformat() if new_val else None, task_id),
    )
    conn.commit()


def delete_task(conn, task_id):
    conn.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
    conn.commit()


def add_task_image(conn, task_id, filename):
    conn.execute(
        "INSERT INTO task_images (task_id, filename) VALUES (?, ?)", (task_id, filename)
    )
    conn.commit()


# ---------- Оплаты (общие для всех сотрудников) ----------

def list_payments(conn, status):
    """status: 'queue' или 'paid'. Возвращает список оплат с именами сотрудников."""
    order = "p.deadline IS NULL, p.deadline, p.id" if status == "queue" \
        else "p.paid_at DESC, p.id DESC"
    return conn.execute(
        f"""SELECT p.*, cu.name AS created_by_name, pu.name AS paid_by_name
            FROM payments p
            LEFT JOIN users cu ON cu.id = p.created_by
            LEFT JOIN users pu ON pu.id = p.paid_by
            WHERE p.status = ?
            ORDER BY {order}""",
        (status,),
    ).fetchall()


def payment_stats(conn):
    row = conn.execute(
        """SELECT
             COALESCE(SUM(CASE WHEN status='queue' THEN amount END), 0) AS queue_sum,
             COALESCE(SUM(CASE WHEN status='queue' THEN 1 END), 0)      AS queue_count,
             COALESCE(SUM(CASE WHEN status='paid'  THEN amount END), 0) AS paid_sum,
             COALESCE(SUM(CASE WHEN status='paid'  THEN 1 END), 0)      AS paid_count
           FROM payments"""
    ).fetchone()
    return dict(row)


def get_payment(conn, payment_id):
    return conn.execute("SELECT * FROM payments WHERE id = ?", (payment_id,)).fetchone()


def create_payment(conn, *, payee, amount, deadline, comment, created_by):
    cur = conn.execute(
        """INSERT INTO payments (payee, amount, deadline, comment, status,
                                 created_by, created_at)
           VALUES (?, ?, ?, ?, 'queue', ?, ?)""",
        (payee.strip(), amount, deadline or None, comment or "", created_by,
         date.today().isoformat()),
    )
    conn.commit()
    return cur.lastrowid


def mark_payment_paid(conn, payment_id, paid_by):
    conn.execute(
        "UPDATE payments SET status='paid', paid_by=?, paid_at=? WHERE id=? AND status='queue'",
        (paid_by, date.today().isoformat(), payment_id),
    )
    conn.commit()


def unmark_payment_paid(conn, payment_id):
    """Вернуть оплату обратно в очередь (на случай ошибки)."""
    conn.execute(
        "UPDATE payments SET status='queue', paid_by=NULL, paid_at=NULL WHERE id=?",
        (payment_id,),
    )
    conn.commit()


def delete_payment(conn, payment_id):
    conn.execute("DELETE FROM payments WHERE id = ?", (payment_id,))
    conn.commit()


def add_payment_file(conn, payment_id, filename, original_name, is_image):
    conn.execute(
        "INSERT INTO payment_files (payment_id, filename, original_name, is_image) VALUES (?, ?, ?, ?)",
        (payment_id, filename, original_name or "", 1 if is_image else 0),
    )
    conn.commit()


def payment_files(conn, payment_id):
    rows = conn.execute(
        "SELECT id, filename, original_name, is_image FROM payment_files WHERE payment_id = ? ORDER BY id",
        (payment_id,),
    ).fetchall()
    return [dict(r) for r in rows]


def rollover_calendar_tasks(conn, uid):
    """Незавершённые календарные задачи из прошлого переносим на сегодня (как в старой версии)."""
    today = date.today().isoformat()
    conn.execute(
        """UPDATE tasks SET plan_date = ?
           WHERE placement = 'calendar' AND completed = 0
             AND plan_date IS NOT NULL AND plan_date < ?
             AND owner_id = ?""",
        (today, today, uid),
    )
    conn.commit()