"""Database PulsaPay: skema, koneksi, data awal, dan perhitungan saldo.

Aplikasi pembukuan sederhana untuk usaha:
- Top Up Pulsa / Data / Token / E-Wallet lewat aplikasi Prastika
- Transfer & Tarik Tunai lewat aplikasi SeaBank

Semua saldo dihitung ulang dari data transaksi (tidak disimpan sebagai angka
yang bisa melenceng), sehingga koreksi/hapus transaksi otomatis rapi.
"""

import datetime
import hashlib
import os
import secrets
import sqlite3

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(BASE_DIR, "data")
DB_PATH = os.path.join(DATA_DIR, "pulsapay.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'kasir',
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    token TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    provider TEXT,
    nominal INTEGER NOT NULL DEFAULT 0,
    cost INTEGER NOT NULL DEFAULT 0,
    price INTEGER NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE NOT NULL,
    type TEXT NOT NULL,
    product_id INTEGER,
    product_name TEXT,
    category TEXT,
    customer_name TEXT,
    destination TEXT,
    destination_name TEXT,
    nominal INTEGER NOT NULL DEFAULT 0,
    cost INTEGER NOT NULL DEFAULT 0,
    price INTEGER NOT NULL DEFAULT 0,
    fee INTEGER NOT NULL DEFAULT 0,
    total INTEGER NOT NULL DEFAULT 0,
    profit INTEGER NOT NULL DEFAULT 0,
    note TEXT,
    created_at TEXT NOT NULL,
    created_by INTEGER
);

CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    category TEXT NOT NULL,
    amount INTEGER NOT NULL,
    note TEXT,
    created_at TEXT NOT NULL,
    created_by INTEGER
);

CREATE TABLE IF NOT EXISTS balance_moves (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account TEXT NOT NULL,
    amount INTEGER NOT NULL,
    note TEXT,
    created_at TEXT NOT NULL,
    created_by INTEGER
);

CREATE TABLE IF NOT EXISTS spareparts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT NOT NULL DEFAULT 'Umum',
    unit TEXT NOT NULL DEFAULT 'pcs',
    stock INTEGER NOT NULL DEFAULT 0,
    min_stock INTEGER NOT NULL DEFAULT 0,
    cost INTEGER NOT NULL DEFAULT 0,
    price INTEGER NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sparepart_moves (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sparepart_id INTEGER NOT NULL,
    qty INTEGER NOT NULL,
    type TEXT NOT NULL,
    cost INTEGER NOT NULL DEFAULT 0,
    note TEXT,
    service_id INTEGER,
    created_at TEXT NOT NULL,
    created_by INTEGER
);

CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE NOT NULL,
    device_type TEXT NOT NULL,
    device_name TEXT NOT NULL,
    serial TEXT,
    customer_name TEXT NOT NULL,
    customer_phone TEXT,
    complaint TEXT,
    diagnosis TEXT,
    technician TEXT,
    status TEXT NOT NULL DEFAULT 'selesai',
    service_fee INTEGER NOT NULL DEFAULT 0,
    parts_cost INTEGER NOT NULL DEFAULT 0,
    parts_price INTEGER NOT NULL DEFAULT 0,
    total INTEGER NOT NULL DEFAULT 0,
    profit INTEGER NOT NULL DEFAULT 0,
    note TEXT,
    created_at TEXT NOT NULL,
    completed_at TEXT,
    created_by INTEGER
);

CREATE TABLE IF NOT EXISTS service_parts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_id INTEGER NOT NULL,
    sparepart_id INTEGER,
    name TEXT NOT NULL,
    qty INTEGER NOT NULL,
    cost INTEGER NOT NULL DEFAULT 0,
    price INTEGER NOT NULL DEFAULT 0,
    subtotal INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_tx_created ON transactions(created_at);
CREATE INDEX IF NOT EXISTS idx_tx_type ON transactions(type);
CREATE INDEX IF NOT EXISTS idx_exp_date ON expenses(date);
CREATE INDEX IF NOT EXISTS idx_sp_moves ON sparepart_moves(sparepart_id);
CREATE INDEX IF NOT EXISTS idx_svc_created ON services(created_at);
CREATE INDEX IF NOT EXISTS idx_svc_status ON services(status);
CREATE INDEX IF NOT EXISTS idx_svc_parts ON service_parts(service_id);
"""

DEFAULT_SETTINGS = {
    "business_name": "PulsaPay",
    "tagline": "Top Up • Transfer • Tarik Tunai",
    "address": "",
    "phone": "",
    "receipt_footer": "Terima kasih, semoga bermanfaat!",
    "accent": "#0ea5a4",
    "logo": "",
    "saldo_prastika_awal": "0",
    "saldo_seabank_awal": "0",
    "kas_awal": "0",
    "fee_transfer": "2500",
    "fee_tarik": "2500",
}

SEED_PRODUCTS = [
    # (nama, kategori, provider, nominal, modal, harga jual)
    ("Telkomsel 5.000", "Pulsa", "Telkomsel", 5000, 5700, 6500),
    ("Telkomsel 10.000", "Pulsa", "Telkomsel", 10000, 10700, 11500),
    ("Telkomsel 25.000", "Pulsa", "Telkomsel", 25000, 24800, 26000),
    ("Telkomsel 50.000", "Pulsa", "Telkomsel", 50000, 49300, 51000),
    ("Indosat 10.000", "Pulsa", "Indosat", 10000, 10500, 11500),
    ("XL 10.000", "Pulsa", "XL", 10000, 10400, 11500),
    ("Tri 10.000", "Pulsa", "Tri", 10000, 10300, 11500),
    ("Smartfren 10.000", "Pulsa", "Smartfren", 10000, 10200, 11500),
    ("Telkomsel Data 3 GB / 30 hari", "Paket Data", "Telkomsel", 0, 32000, 35000),
    ("Indosat Data 5 GB / 30 hari", "Paket Data", "Indosat", 0, 38000, 42000),
    ("XL Data 5 GB / 30 hari", "Paket Data", "XL", 0, 36000, 40000),
    ("Token PLN 20.000", "Token PLN", "PLN", 20000, 20300, 21500),
    ("Token PLN 50.000", "Token PLN", "PLN", 50000, 50300, 51500),
    ("Token PLN 100.000", "Token PLN", "PLN", 100000, 100300, 102000),
    ("GoPay 50.000", "E-Wallet", "GoPay", 50000, 50500, 52000),
    ("OVO 50.000", "E-Wallet", "OVO", 50000, 50500, 52000),
    ("DANA 50.000", "E-Wallet", "DANA", 50000, 50500, 52000),
    ("ShopeePay 50.000", "E-Wallet", "ShopeePay", 50000, 50500, 52000),
    ("Mobile Legends 86 Diamond", "Game", "Moonton", 0, 20000, 23000),
    ("Free Fire 70 Diamond", "Game", "Garena", 0, 9500, 11500),
    ("PUBG Mobile 60 UC", "Game", "Tencent", 0, 14000, 16000),
]


def now_str():
    return datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")


def today_str():
    return datetime.date.today().isoformat()


def connect():
    os.makedirs(DATA_DIR, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


def hash_password(password):
    salt = secrets.token_bytes(16)
    digest = hashlib.pbkdf2_hmac("sha256", password.encode("utf-8"), salt, 120000)
    return "pbkdf2_sha256$120000${}${}".format(salt.hex(), digest.hex())


def verify_password(password, stored):
    try:
        algo, iterations, salt_hex, digest_hex = stored.split("$")
        if algo != "pbkdf2_sha256":
            return False
        digest = hashlib.pbkdf2_hmac(
            "sha256", password.encode("utf-8"), bytes.fromhex(salt_hex), int(iterations)
        )
        return secrets.compare_digest(digest.hex(), digest_hex)
    except (ValueError, TypeError):
        return False


def get_settings(conn):
    rows = conn.execute("SELECT key, value FROM settings").fetchall()
    data = dict(DEFAULT_SETTINGS)
    data.update({row["key"]: row["value"] for row in rows})
    return data


def set_setting(conn, key, value):
    conn.execute(
        "INSERT INTO settings(key, value) VALUES(?, ?) "
        "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
        (key, str(value)),
    )


def init_db():
    os.makedirs(DATA_DIR, exist_ok=True)
    conn = connect()
    try:
        conn.executescript(SCHEMA)
        # Rumus servis: total tagihan = biaya jasa, laba = biaya jasa - modal sparepart.
        conn.execute(
            "UPDATE services SET total = service_fee, profit = service_fee - parts_cost"
        )
        if conn.execute("SELECT COUNT(*) AS n FROM users").fetchone()["n"] == 0:
            conn.execute(
                "INSERT INTO users(username, name, password_hash, role, active, created_at) "
                "VALUES(?, ?, ?, ?, 1, ?)",
                ("admin", "Admin Toko", hash_password("admin123"), "admin", now_str()),
            )
        if conn.execute("SELECT COUNT(*) AS n FROM products").fetchone()["n"] == 0:
            stamp = now_str()
            conn.executemany(
                "INSERT INTO products(name, category, provider, nominal, cost, price, active, created_at) "
                "VALUES(?, ?, ?, ?, ?, ?, 1, ?)",
                [(*product, stamp) for product in SEED_PRODUCTS],
            )
        for key, value in DEFAULT_SETTINGS.items():
            conn.execute(
                "INSERT OR IGNORE INTO settings(key, value) VALUES(?, ?)", (key, value)
            )
        conn.commit()
    finally:
        conn.close()


def balances(conn):
    """Hitung saldo & aset dari saldo awal + mutasi + transaksi + servis.

    Rumus:
      Saldo Prastika = awal + tambah/koreksi - modal top up
      Saldo SeaBank  = awal + tambah/koreksi + (tarik tunai + fee) - transfer
      Kas            = awal + tambah/koreksi + uang masuk - uang keluar - biaya
      Nilai stok     = total (stok sparepart x harga modal)
      Aset           = Kas + Saldo Prastika + Saldo SeaBank + nilai stok
    """
    settings = get_settings(conn)
    initial = {
        "prastika": int(settings.get("saldo_prastika_awal") or 0),
        "seabank": int(settings.get("saldo_seabank_awal") or 0),
        "kas": int(settings.get("kas_awal") or 0),
    }
    moves = {"prastika": 0, "seabank": 0, "kas": 0}
    for row in conn.execute(
        "SELECT account, COALESCE(SUM(amount), 0) AS total FROM balance_moves GROUP BY account"
    ):
        if row["account"] in moves:
            moves[row["account"]] = row["total"] or 0

    topup_cost = conn.execute(
        "SELECT COALESCE(SUM(cost), 0) AS v FROM transactions WHERE type = 'topup'"
    ).fetchone()["v"]
    transfer_out = conn.execute(
        "SELECT COALESCE(SUM(nominal), 0) AS v FROM transactions WHERE type = 'transfer'"
    ).fetchone()["v"]
    tarik_in = conn.execute(
        "SELECT COALESCE(SUM(nominal + fee), 0) AS v FROM transactions WHERE type = 'tarik'"
    ).fetchone()["v"]
    cash_in = conn.execute(
        "SELECT COALESCE(SUM(total), 0) AS v FROM transactions WHERE type IN ('topup', 'transfer')"
    ).fetchone()["v"]
    tarik_cash_out = conn.execute(
        "SELECT COALESCE(SUM(nominal), 0) AS v FROM transactions WHERE type = 'tarik'"
    ).fetchone()["v"]
    expenses = conn.execute("SELECT COALESCE(SUM(amount), 0) AS v FROM expenses").fetchone()["v"]
    service_in = conn.execute(
        "SELECT COALESCE(SUM(total), 0) AS v FROM services "
        "WHERE status IN ('selesai', 'diambil')"
    ).fetchone()["v"]
    stock_value = conn.execute(
        "SELECT COALESCE(SUM(stock * cost), 0) AS v FROM spareparts"
    ).fetchone()["v"]

    prastika = initial["prastika"] + moves["prastika"] - topup_cost
    seabank = initial["seabank"] + moves["seabank"] + tarik_in - transfer_out
    kas = initial["kas"] + moves["kas"] + cash_in - tarik_cash_out - expenses + service_in
    return {
        "prastika": prastika,
        "seabank": seabank,
        "kas": kas,
        "stock_value": stock_value,
        "aset": prastika + seabank + kas + stock_value,
        "initial": initial,
        "moves": moves,
        "detail": {
            "topup_cost": topup_cost,
            "transfer_out": transfer_out,
            "tarik_in": tarik_in,
            "cash_in": cash_in,
            "tarik_cash_out": tarik_cash_out,
            "expenses": expenses,
            "service_in": service_in,
            "stock_value": stock_value,
        },
    }


def make_code(conn, prefix):
    stamp = datetime.datetime.now().strftime("%y%m%d")
    for _ in range(50):
        code = "{}-{}-{}".format(prefix, stamp, secrets.randbelow(9000) + 1000)
        if not conn.execute("SELECT 1 FROM transactions WHERE code = ?", (code,)).fetchone():
            return code
    return "{}-{}-{}".format(prefix, stamp, secrets.token_hex(3).upper())


def make_service_code(conn):
    stamp = datetime.datetime.now().strftime("%y%m%d")
    for _ in range(50):
        code = "SV-{}-{}".format(stamp, secrets.randbelow(9000) + 1000)
        if not conn.execute("SELECT 1 FROM services WHERE code = ?", (code,)).fetchone():
            return code
    return "SV-{}-{}".format(stamp, secrets.token_hex(3).upper())


if __name__ == "__main__":
    init_db()
    print("Database siap:", DB_PATH)