# ============================================================
# core/db_init.py
# Khởi tạo database SQLite + FTS5 cho từng công ty
# ============================================================

import sqlite3
import json
import os

TEMPLATES_DIR = os.path.join(os.path.dirname(__file__), "..", "templates")


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


def init_company_db(db_path: str, standard: str = "TT200") -> None:
    """
    Khởi tạo toàn bộ schema cho database của 1 công ty.
    standard: 'TT200' hoặc 'TT133'
    """
    # Đảm bảo thư mục tồn tại
    db_dir = os.path.dirname(os.path.abspath(db_path))
    os.makedirs(db_dir, exist_ok=True)
    
    conn = get_conn(db_path)
    c = conn.cursor()

    # ── Bảng meta công ty ──────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS company_meta (
        key   TEXT PRIMARY KEY,
        value TEXT
    )
    """)

    # ── Bảng tỷ lệ thuế Hộ Kinh Doanh (TT40/2021) ──────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS hkd_tax_rates (
        id INTEGER PRIMARY KEY,
        code TEXT UNIQUE NOT NULL,      -- Mã nhóm: nhom_1_5, nhom_3...
        name TEXT NOT NULL,             -- Tên hiển thị: 1.5% (Bán buôn, bán lẻ...)
        gtgt_rate REAL DEFAULT 0,       -- Tỷ lệ thuế GTGT (%)
        tncn_rate REAL DEFAULT 0,       -- Tỷ lệ thuế TNCN (%)
        description TEXT                -- Ghi chú/chi tiết ngành nghề
    )
    """)

    # ── Bảng tài khoản kế toán ─────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS accounts (
        code        TEXT PRIMARY KEY,             -- Mã TK: 1111, 331...
        name        TEXT NOT NULL,                -- Tên tài khoản
        name_en     TEXT,
        type        TEXT NOT NULL,                -- asset/liability/equity/revenue/expense/summary
        level       INTEGER NOT NULL DEFAULT 1,   -- Cấp 1-5
        parent_code TEXT,                         -- Mã TK cha
        is_active   INTEGER DEFAULT 1,
        custom_note TEXT,
        FOREIGN KEY (parent_code) REFERENCES accounts(code)
    )
    """)

    # ── Đối tác (khách hàng / nhà cung cấp) ───────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS partners (
        id          INTEGER PRIMARY KEY,
        code        TEXT UNIQUE,                  -- Mã nội bộ: KH001
        name        TEXT NOT NULL,
        tax_code    TEXT,
        type        TEXT DEFAULT 'both',          -- customer/vendor/both
        address     TEXT,
        phone       TEXT,
        email       TEXT,
        bank_account TEXT,
        bank_name   TEXT,
        is_active   INTEGER DEFAULT 1,
        notes       TEXT,
        created_at  TEXT DEFAULT (datetime('now','localtime'))
    )
    """)

    # ── Kỳ kế toán ─────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS periods (
        id          INTEGER PRIMARY KEY,
        year        INTEGER NOT NULL,
        month       INTEGER NOT NULL,
        status      TEXT DEFAULT 'open',          -- open / closed / locked
        closed_at   TEXT,
        UNIQUE(year, month)
    )
    """)

    # ── Chứng từ kế toán (Vouchers) ────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS vouchers (
        id          INTEGER PRIMARY KEY,
        voucher_no  TEXT UNIQUE,                  -- Số chứng từ: PC001/2026
        voucher_type TEXT NOT NULL,               -- PT/PC/BNKthu/BNKchi/HDXB/HDMV/NK/XK/PB/KC/other
        voucher_date TEXT NOT NULL,               -- Ngày chứng từ
        period_year INTEGER NOT NULL,
        period_month INTEGER NOT NULL,
        description TEXT,                         -- Nội dung diễn giải
        partner_id  INTEGER REFERENCES partners(id),
        reference   TEXT,                         -- Số tham chiếu (số HĐ, số PXK...)
        currency    TEXT DEFAULT 'VND',
        exchange_rate REAL DEFAULT 1.0,
        total_debit REAL DEFAULT 0,
        total_credit REAL DEFAULT 0,
        status      TEXT DEFAULT 'draft',         -- draft / posted / cancelled
        created_by  TEXT,
        created_at  TEXT DEFAULT (datetime('now','localtime')),
        posted_at   TEXT,
        notes       TEXT
    )
    """)

    # ── Dòng bút toán (Journal Entries) ───────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS journal_entries (
        id          INTEGER PRIMARY KEY,
        voucher_id  INTEGER NOT NULL REFERENCES vouchers(id) ON DELETE CASCADE,
        line_no     INTEGER NOT NULL,             -- Số thứ tự dòng trong chứng từ
        account_code TEXT NOT NULL REFERENCES accounts(code),
        debit       REAL DEFAULT 0,               -- Nợ
        credit      REAL DEFAULT 0,               -- Có
        description TEXT,
        partner_id  INTEGER REFERENCES partners(id),
        cost_center TEXT,                         -- Mã bộ phận / trung tâm chi phí
        project     TEXT,
        tax_code    TEXT,                         -- Mã thuế (GTGT 10%, 8%...)
        UNIQUE(voucher_id, line_no)
    )
    """)

    # ── Số dư đầu kỳ ───────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS opening_balances (
        id          INTEGER PRIMARY KEY,
        account_code TEXT NOT NULL REFERENCES accounts(code),
        year        INTEGER NOT NULL,
        month       INTEGER NOT NULL DEFAULT 1,
        debit_balance  REAL DEFAULT 0,            -- Dư Nợ đầu kỳ
        credit_balance REAL DEFAULT 0,            -- Dư Có đầu kỳ
        UNIQUE(account_code, year, month)
    )
    """)

    # ── Hóa đơn (Invoice registry) ─────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS invoices (
        id          INTEGER PRIMARY KEY,
        invoice_no  TEXT,                         -- Số hóa đơn
        invoice_series TEXT,                      -- Ký hiệu mẫu số / ký hiệu HĐ
        invoice_date TEXT,
        voucher_id  INTEGER REFERENCES vouchers(id),
        partner_id  INTEGER REFERENCES partners(id),
        invoice_type TEXT DEFAULT 'output',       -- output / input
        pretax_amount REAL DEFAULT 0,
        vat_rate    REAL DEFAULT 0.1,
        vat_amount  REAL DEFAULT 0,
        total_amount REAL DEFAULT 0,
        status      TEXT DEFAULT 'valid',         -- valid / cancelled / replaced
        ocr_image_path TEXT,                      -- ảnh HĐ gốc (nếu có)
        notes       TEXT
    )
    """)

    # ── Audit log ──────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS audit_log (
        id          INTEGER PRIMARY KEY,
        table_name  TEXT NOT NULL,
        record_id   INTEGER,
        action      TEXT NOT NULL,               -- INSERT/UPDATE/DELETE/CANCEL/POST
        old_data    TEXT,                        -- JSON
        new_data    TEXT,                        -- JSON
        performed_by TEXT DEFAULT 'system',
        performed_at TEXT DEFAULT (datetime('now','localtime'))
    )
    """)

    # ── Bút toán định kỳ ───────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS recurring_templates (
        id          INTEGER PRIMARY KEY,
        name        TEXT NOT NULL,
        voucher_type TEXT NOT NULL,
        entries_json TEXT NOT NULL,               -- JSON list entries
        frequency   TEXT DEFAULT 'monthly',       -- monthly/quarterly/yearly
        description TEXT,
        is_active   INTEGER DEFAULT 1,
        last_run_year  INTEGER,
        last_run_month INTEGER,
        notes       TEXT,
        created_at  TEXT DEFAULT (datetime('now','localtime')),
        updated_at  TEXT
    )
    """)

    # ── Nhóm hàng hóa (Product Categories) ────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS product_categories (
        id          INTEGER PRIMARY KEY,
        name        TEXT NOT NULL UNIQUE,
        description TEXT,
        gtgt_rate   REAL DEFAULT 0.0,
        tncn_rate   REAL DEFAULT 0.0,
        is_active   INTEGER DEFAULT 1,
        created_at  TEXT DEFAULT (datetime('now','localtime'))
    )
    """)

    # ── Sản phẩm / Hàng hóa (Inventory) ───────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id          INTEGER PRIMARY KEY,
        code        TEXT UNIQUE,                  -- Mã hàng: HH001
        name        TEXT NOT NULL,
        unit        TEXT,                         -- Đơn vị tính: Cái, Kg, Bộ...
        category_id INTEGER REFERENCES product_categories(id),
        category    TEXT,                         -- Nhóm hàng (legacy)
        industry_code TEXT,                       -- Mã ngành nghề thuế khoán (nhom_1_5, nhom_3, v.v.)
        purchase_price REAL DEFAULT 0,            -- Giá mua mặc định
        sale_price  REAL DEFAULT 0,               -- Giá bán mặc định
        is_active   INTEGER DEFAULT 1,
        notes       TEXT,
        created_at  TEXT DEFAULT (datetime('now','localtime'))
    )
    """)

    # ── Biến động kho (Inventory Movements) ───────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS inventory_movements (
        id          INTEGER PRIMARY KEY,
        product_id  INTEGER NOT NULL REFERENCES products(id),
        voucher_id  INTEGER REFERENCES vouchers(id) ON DELETE CASCADE,
        move_date   TEXT NOT NULL,
        type        TEXT NOT NULL,               -- in (nhập) / out (xuất)
        quantity    REAL NOT NULL,
        unit_price  REAL,
        total_value REAL,
        notes       TEXT
    )
    """)

    # ── Users (phân quyền truy cập công ty này) ────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id          INTEGER PRIMARY KEY,
        username    TEXT NOT NULL UNIQUE,
        display_name TEXT,
        role        TEXT DEFAULT 'accountant',   -- admin/accountant/viewer
        is_active   INTEGER DEFAULT 1,
        created_at  TEXT DEFAULT (datetime('now','localtime'))
    )
    """)

    # ── Phiên bản schema (migration tracking) ────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS schema_version (
        version    INTEGER PRIMARY KEY,
        applied_at TEXT DEFAULT (datetime('now','localtime')),
        note       TEXT
    )
    """)
    c.execute("INSERT OR IGNORE INTO schema_version (version, note) VALUES (1, 'Initial schema')")

    # ── Index hiệu suất ─────────────────────────────────────
    c.execute("CREATE INDEX IF NOT EXISTS idx_je_account   ON journal_entries(account_code)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_je_voucher   ON journal_entries(voucher_id)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_inv_product  ON inventory_movements(product_id)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_inv_date     ON inventory_movements(move_date)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_voucher_date ON vouchers(voucher_date)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_voucher_type ON vouchers(voucher_type)")

    # ── FTS5: tìm kiếm chứng từ ────────────────────────────
    c.execute("""
    CREATE VIRTUAL TABLE IF NOT EXISTS vouchers_fts USING fts5(
        voucher_no,
        description,
        reference,
        notes,
        content='vouchers',
        content_rowid='id',
        tokenize='unicode61'
    )
    """)

    # ── FTS5: tìm kiếm đối tác ─────────────────────────────
    c.execute("""
    CREATE VIRTUAL TABLE IF NOT EXISTS partners_fts USING fts5(
        code,
        name,
        tax_code,
        address,
        notes,
        content='partners',
        content_rowid='id',
        tokenize='unicode61'
    )
    """)

    # ── Triggers sync FTS5 ─────────────────────────────────
    for table, fts, cols in [
        ("vouchers", "vouchers_fts", "voucher_no, description, reference, notes"),
        ("partners", "partners_fts", "code, name, tax_code, address, notes"),
    ]:
        col_list = cols.replace(" ", "")
        new_vals = ", ".join(f"new.{c.strip()}" for c in cols.split(","))
        old_vals = ", ".join(f"old.{c.strip()}" for c in cols.split(","))

        c.execute(f"""
        CREATE TRIGGER IF NOT EXISTS {table}_ai
        AFTER INSERT ON {table} BEGIN
            INSERT INTO {fts}(rowid, {cols})
            VALUES (new.id, {new_vals});
        END
        """)
        c.execute(f"""
        CREATE TRIGGER IF NOT EXISTS {table}_ad
        AFTER DELETE ON {table} BEGIN
            INSERT INTO {fts}({fts}, rowid, {cols})
            VALUES ('delete', old.id, {old_vals});
        END
        """)
        c.execute(f"""
        CREATE TRIGGER IF NOT EXISTS {table}_au
        AFTER UPDATE ON {table} BEGIN
            INSERT INTO {fts}({fts}, rowid, {cols})
            VALUES ('delete', old.id, {old_vals});
            INSERT INTO {fts}(rowid, {cols})
            VALUES (new.id, {new_vals});
        END
        """)

    # ── Nạp biểu thuế GTGT/TNCN mặc định ───────────────────
    tax_rates = [
        ("nhom_1_5",        "1.5% — Phân phối, cung cấp hàng hóa",  1.0, 0.5,
         "[TT40 PL-I STT1] Bán buôn, bán lẻ HH; khuyến mại, chiết khấu TM."),
        ("nhom_dv_thuan",   "7% — DV thuần túy, lưu trú, XD không bao thầu", 5.0, 2.0,
         "[TT40 PL-I STT2] Lưu trú, massage, tắm hơi, cắt tóc, tư vấn, CNTT, XD không bao thầu NVL."),
        ("nhom_sx_vantai",  "4.5% — SX, vận tải, DV gắn HH, XD có bao thầu", 3.0, 1.5,
         "[TT40 PL-I STT3] Nhà hàng, ăn uống, sản xuất, gia công, vận tải, sửa chữa có linh kiện, XD có bao thầu NVL."),
        ("nhom_cho_thue",   "10% — Cho thuê tài sản", 5.0, 5.0,
         "[TT40 PL-I STT2] Cho thuê nhà, đất, xưởng, kho, PT vận tải/máy móc không kèm người điều khiển."),
        ("nhom_kd_khac",    "3% — KD khác, DV chịu GTGT 5%", 2.0, 1.0,
         "[TT40 Đ.5] SX/gia công HH chịu GTGT 5%; DV chịu 5%; HKD chưa liệt kê ở nhóm 1,3,4."),
        ("khong_xac_dinh",  "Tùy chỉnh — Ngoại lệ / chưa xác định", 0.0, 0.0,
         "HH/DV không chịu GTGT; bồi thường HĐ; DT dưới 100tr/năm; nền tảng số xuyên biên giới.")
    ]
    c.executemany("""
    INSERT OR REPLACE INTO hkd_tax_rates (code, name, gtgt_rate, tncn_rate, description)
    VALUES (?, ?, ?, ?, ?)
    """, tax_rates)

    # ── Nạp / Cập nhật product_categories mặc định TT40/2021 (6 nhóm chuẩn) ──
    # Luôn upsert để đồng bộ tên/mô tả mới nhất theo Project.md
    default_cats = [
        ("Nhóm 1 — Phân phối, cung cấp hàng hóa",
         "Bán buôn, bán lẻ hàng hóa; đại lý bán đúng giá hưởng hoa hồng; "
         "tạp hóa; siêu thị mini; cửa hàng vật tư, linh kiện; "
         "bán hàng thương mại trực tuyến (thuần hàng hóa); thưởng, chiết khấu thương mại. "
         "Ngoại lệ GTGT 0%: hàng hóa không chịu thuế GTGT hoặc thuế suất 0%; "
         "hợp tác kinh doanh với tổ chức đã khai thuế GTGT; bồi thường vi phạm hợp đồng.",
         1.0, 0.5),
        ("Nhóm 2 — Dịch vụ thuần túy, lưu trú, xây dựng không bao thầu nguyên vật liệu",
         "Dịch vụ lưu trú: khách sạn, nhà nghỉ, phòng trọ; "
         "tắm hơi, massage, karaoke, vũ trường, bi-a, internet, game; "
         "cắt tóc, làm đầu, gội đầu; may đo, giặt là; bưu chính, chuyển phát; "
         "môi giới, đấu giá, hoa hồng đại lý; "
         "tư vấn pháp luật, tài chính, kế toán, kiểm toán; "
         "công nghệ thông tin, viễn thông, quảng cáo số; lập trình phần mềm, marketing, dạy học; "
         "xây dựng, lắp đặt không bao thầu nguyên vật liệu (chỉ tính công). "
         "Đại lý xổ số, bảo hiểm, bán hàng đa cấp: GTGT 0%, TNCN 5%.",
         5.0, 2.0),
        ("Nhóm 3 — Sản xuất, vận tải, dịch vụ gắn hàng hóa, xây dựng có bao thầu nguyên vật liệu",
         "Nhà hàng, quán ăn, quán cà phê; "
         "sản xuất, gia công, chế biến sản phẩm hàng hóa; "
         "khai thác, chế biến khoáng sản; "
         "vận tải hàng hóa và vận tải hành khách; "
         "sửa chữa, bảo dưỡng máy móc, thiết bị, ô tô, xe máy (có thay linh kiện); "
         "xây dựng, lắp đặt có bao thầu nguyên vật liệu.",
         3.0, 1.5),
        ("Nhóm 4 — Cho thuê tài sản",
         "Cho thuê nhà, đất, cửa hàng, nhà xưởng, kho bãi (trừ dịch vụ lưu trú ngắn hạn); "
         "cho thuê phương tiện vận tải, máy móc, thiết bị không kèm người điều khiển; "
         "cho thuê các loại tài sản khác không kèm dịch vụ. "
         "Khai thuế theo Mẫu 01/TTS ban hành kèm Thông tư 40/2021.",
         5.0, 5.0),
        ("Nhóm 5 — Kinh doanh khác, dịch vụ chịu thuế GTGT 5%",
         "Sản xuất, gia công hàng hóa chịu thuế GTGT 5%: nông sản, lâm sản, thủy sản qua chế biến; "
         "thiết bị, dụng cụ y tế; nước sạch; sách giáo khoa, sách giáo dục; "
         "cung cấp dịch vụ thuộc đối tượng chịu thuế GTGT 5%; "
         "các hoạt động kinh doanh khác chưa được liệt kê ở Nhóm 1, 3, 4.",
         2.0, 1.0),
        ("Nhóm 6 — Tùy chỉnh / Ngoại lệ",
         "Hàng hóa, dịch vụ không chịu thuế GTGT hoặc thuế suất GTGT 0%; "
         "các khoản bồi thường vi phạm hợp đồng; "
         "doanh thu dưới 100 triệu đồng/năm (miễn nộp thuế); "
         "nền tảng số xuyên biên giới; ngành nghề đặc biệt theo quyết định riêng của cơ quan thuế. "
         "→ Nhập tay tỷ lệ % thuế phù hợp.",
         0.0, 0.0),
    ]
    # Upsert từng nhóm theo thứ tự — INSERT nếu chưa có, UPDATE nếu đã có (ID 1-6)
    for idx, (name, desc, gtgt, tncn) in enumerate(default_cats, start=1):
        exists = c.execute("SELECT id FROM product_categories WHERE id=?", (idx,)).fetchone()
        if exists:
            c.execute("""
                UPDATE product_categories SET name=?, description=?, gtgt_rate=?, tncn_rate=?
                WHERE id=?
            """, (name, desc, gtgt, tncn, idx))
        else:
            c.execute("""
                INSERT INTO product_categories (name, description, gtgt_rate, tncn_rate)
                VALUES (?, ?, ?, ?)
            """, (name, desc, gtgt, tncn))

    conn.commit()

    # ── Nạp danh mục tài khoản ─────────────────────────────
    coa_file = os.path.join(TEMPLATES_DIR, f"coa_{standard.lower()}.json")
    if os.path.exists(coa_file):
        with open(coa_file, encoding="utf-8") as f:
            accounts = json.load(f)
        c.executemany("""
        INSERT OR IGNORE INTO accounts (code, name, type, level, parent_code)
        VALUES (:code, :name, :type, :level, :parent)
        """, accounts)
        conn.commit()
        print(f"  ✅ Nạp {len(accounts)} tài khoản ({standard})")

    conn.close()
    print(f"  ✅ Database khởi tạo thành công: {db_path}")


def check_db_integrity(db_path: str) -> bool:
    """Kiểm tra tính toàn vẹn của database."""
    conn = get_conn(db_path)
    c = conn.cursor()
    result = c.execute("PRAGMA integrity_check").fetchone()
    conn.close()
    return result[0] == "ok"
