# -*- coding: ascii -*-
"""
Akemi Retenciones ISLR v1.0 - Base de Datos
(c) 2026 Akemi Tech Labs
"""
import sqlite3
import os
import sys
import shutil
from datetime import datetime

DB_NAME = "akemi_retenciones.db"


def _base_dir():
    if getattr(sys, 'frozen', False):
        return os.path.dirname(sys.executable)
    return os.path.dirname(os.path.abspath(__file__))


def get_db_path():
    return os.path.join(_base_dir(), DB_NAME)


def get_connection():
    conn = sqlite3.connect(get_db_path())
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def backup_db():
    path = get_db_path()
    if os.path.exists(path):
        ts = datetime.now().strftime("%Y%m%d_%H%M%S")
        bk = path.replace(".db", "_bk_%s.db" % ts)
        shutil.copy2(path, bk)
        folder = os.path.dirname(path)
        bks = sorted([f for f in os.listdir(folder)
                       if f.startswith("akemi_retenciones_bk_")])
        while len(bks) > 5:
            os.remove(os.path.join(folder, bks.pop(0)))


def init_db():
    conn = get_connection()
    c = conn.cursor()

    c.execute("""CREATE TABLE IF NOT EXISTS configuracion (
        id INTEGER PRIMARY KEY,
        rif_agente TEXT NOT NULL DEFAULT 'J300899764',
        nombre_agente TEXT NOT NULL DEFAULT 'SOCIEDAD BENEFICA AMIGOS DEL PADRE PIO',
        valor_ut REAL NOT NULL DEFAULT 43.00,
        factor_minimo REAL NOT NULL DEFAULT 83.3333,
        ruta_salida_xml TEXT DEFAULT '',
        prefijo_factura_medicos TEXT DEFAULT '',
        updated_at TEXT)""")

    c.execute("""CREATE TABLE IF NOT EXISTS beneficiarios (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rif TEXT NOT NULL UNIQUE,
        nombre TEXT NOT NULL,
        tipo_persona TEXT NOT NULL DEFAULT 'PNR',
        categoria TEXT DEFAULT 'MEDICO',
        activo INTEGER DEFAULT 1,
        created_at TEXT DEFAULT (datetime('now','localtime')),
        updated_at TEXT)""")

    c.execute("""CREATE TABLE IF NOT EXISTS periodos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        periodo TEXT NOT NULL UNIQUE,
        anio INTEGER NOT NULL,
        mes INTEGER NOT NULL,
        valor_ut REAL NOT NULL DEFAULT 43.00,
        estado TEXT DEFAULT 'ABIERTO',
        fecha_declaracion TEXT,
        created_at TEXT DEFAULT (datetime('now','localtime')))""")

    c.execute("""CREATE TABLE IF NOT EXISTS retenciones (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        periodo_id INTEGER NOT NULL,
        beneficiario_id INTEGER NOT NULL,
        numero_factura TEXT NOT NULL,
        numero_control TEXT NOT NULL,
        fecha_operacion TEXT NOT NULL,
        codigo_concepto TEXT NOT NULL DEFAULT '002',
        monto_operacion REAL NOT NULL DEFAULT 0,
        porcentaje_retencion REAL NOT NULL DEFAULT 3,
        monto_retenido REAL NOT NULL DEFAULT 0,
        sustraendo REAL DEFAULT 0,
        origen TEXT DEFAULT 'MANUAL',
        seleccionado INTEGER DEFAULT 1,
        created_at TEXT DEFAULT (datetime('now','localtime')),
        FOREIGN KEY (periodo_id) REFERENCES periodos(id),
        FOREIGN KEY (beneficiario_id) REFERENCES beneficiarios(id))""")

    c.execute("""CREATE TABLE IF NOT EXISTS xml_generados (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        periodo_id INTEGER NOT NULL,
        fecha_generacion TEXT NOT NULL,
        nombre_archivo TEXT NOT NULL,
        ruta_archivo TEXT NOT NULL,
        total_registros INTEGER DEFAULT 0,
        total_monto_operacion REAL DEFAULT 0,
        total_monto_retenido REAL DEFAULT 0,
        FOREIGN KEY (periodo_id) REFERENCES periodos(id))""")

    c.execute("""CREATE TABLE IF NOT EXISTS codigos_concepto (
        codigo TEXT PRIMARY KEY,
        descripcion TEXT NOT NULL,
        porcentaje_pnr REAL DEFAULT 0,
        porcentaje_pjd REAL DEFAULT 0,
        tiene_sustraendo INTEGER DEFAULT 0)""")

    if not c.execute("SELECT 1 FROM configuracion LIMIT 1").fetchone():
        c.execute("""INSERT INTO configuracion
            (id,rif_agente,nombre_agente,valor_ut,factor_minimo,
             ruta_salida_xml,prefijo_factura_medicos)
            VALUES (1,'J300899764','SOCIEDAD BENEFICA AMIGOS DEL PADRE PIO',
                    43.00,83.3333,'','')""")

    c.execute("""CREATE INDEX IF NOT EXISTS
        idx_ret_periodo ON retenciones(periodo_id)""")
    c.execute("""CREATE INDEX IF NOT EXISTS
        idx_ret_benef ON retenciones(beneficiario_id)""")

    for cd in [
        ("001","Sueldos y Salarios",0,0,0),
        ("002","Honorarios Prof. No Mercantiles (PNR)",3,5,1),
        ("003","Honorarios Prof. No Mercantiles (PNNR)",34,0,0),
        ("004","Honorarios Prof. No Mercantiles (PJD)",5,5,0),
        ("005","Honorarios Prof. No Mercantiles (PJND)",34,0,0),
        ("022","Comisiones Mercantiles (PNR)",3,0,1),
        ("046","Arrendamiento Bienes Inmuebles (PNR)",3,0,1),
        ("053","Servicios contratados (PNR)",1,0,1),
        ("055","Servicios contratados (PJD)",0,2,0),
        ("066","Compra bienes muebles (PJD)",0,1,0),
    ]:
        c.execute("INSERT OR IGNORE INTO codigos_concepto VALUES (?,?,?,?,?)", cd)

    conn.commit()
    conn.close()


def get_config():
    conn = get_connection()
    row = conn.execute("SELECT * FROM configuracion WHERE id=1").fetchone()
    conn.close()
    return dict(row) if row else {}


def update_config(**kw):
    conn = get_connection()
    kw["updated_at"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    s = ", ".join("%s=?" % k for k in kw)
    conn.execute("UPDATE configuracion SET %s WHERE id=1" % s, list(kw.values()))
    conn.commit()
    conn.close()


def get_or_create_periodo(anio, mes, valor_ut=None):
    periodo = "%04d%02d" % (anio, mes)
    conn = get_connection()
    row = conn.execute("SELECT * FROM periodos WHERE periodo=?", (periodo,)).fetchone()
    if not row:
        if valor_ut is None:
            valor_ut = get_config().get("valor_ut", 43.0)
        conn.execute("INSERT INTO periodos (periodo,anio,mes,valor_ut) VALUES (?,?,?,?)",
                     (periodo, anio, mes, valor_ut))
        conn.commit()
        row = conn.execute("SELECT * FROM periodos WHERE periodo=?", (periodo,)).fetchone()
    conn.close()
    return dict(row)


def get_or_create_beneficiario(rif, nombre, tipo="PNR", cat="MEDICO"):
    rif = rif.strip().upper().replace("-","").replace(".","")
    conn = get_connection()
    row = conn.execute("SELECT * FROM beneficiarios WHERE rif=?", (rif,)).fetchone()
    if not row:
        conn.execute("INSERT INTO beneficiarios (rif,nombre,tipo_persona,categoria) VALUES (?,?,?,?)",
                     (rif, nombre.strip().upper(), tipo, cat))
        conn.commit()
        row = conn.execute("SELECT * FROM beneficiarios WHERE rif=?", (rif,)).fetchone()
    conn.close()
    return dict(row)


def find_beneficiario_by_name(nombre):
    conn = get_connection()
    row = conn.execute("SELECT * FROM beneficiarios WHERE nombre LIKE ?",
                       ("%" + nombre.strip().upper() + "%",)).fetchone()
    conn.close()
    return dict(row) if row else None


def codigo_tiene_sustraendo(codigo):
    conn = get_connection()
    row = conn.execute(
        "SELECT tiene_sustraendo FROM codigos_concepto WHERE codigo=?",
        (str(codigo).zfill(3),)).fetchone()
    conn.close()
    if row:
        return bool(row["tiene_sustraendo"])
    return False


def calcular_retencion(monto, porcentaje, valor_ut, tiene_sustraendo):
    if monto <= 0:
        return 0.0, 0.0
    if tiene_sustraendo:
        minimo = 1000.0 * valor_ut / 12.0
        sustraendo = round(minimo * porcentaje / 100.0, 2)
        if monto <= minimo:
            return 0.0, sustraendo
        ret = round(monto * porcentaje / 100.0 - sustraendo, 2)
        return max(ret, 0.0), sustraendo
    else:
        return round(monto * porcentaje / 100.0, 2), 0.0


def insertar_retencion(periodo_id, benef_id, fac, ctrl, fecha, cod,
                       monto, pct, ret, sust, origen="IMPORTADO"):
    conn = get_connection()
    conn.execute("""INSERT INTO retenciones
        (periodo_id,beneficiario_id,numero_factura,numero_control,
         fecha_operacion,codigo_concepto,monto_operacion,
         porcentaje_retencion,monto_retenido,sustraendo,origen)
        VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
        (periodo_id, benef_id, fac, ctrl, fecha, cod, monto, pct, ret, sust, origen))
    conn.commit()
    conn.close()


def insertar_retenciones_batch(registros):
    if not registros:
        return
    conn = get_connection()
    conn.executemany("""INSERT INTO retenciones
        (periodo_id,beneficiario_id,numero_factura,numero_control,
         fecha_operacion,codigo_concepto,monto_operacion,
         porcentaje_retencion,monto_retenido,sustraendo,origen)
        VALUES (?,?,?,?,?,?,?,?,?,?,?)""", registros)
    conn.commit()
    conn.close()


def get_retenciones_periodo(periodo_id, solo_sel=False):
    conn = get_connection()
    sql = """SELECT r.*, b.rif, b.nombre, b.tipo_persona
        FROM retenciones r JOIN beneficiarios b ON r.beneficiario_id=b.id
        WHERE r.periodo_id=?"""
    if solo_sel:
        sql += " AND r.seleccionado=1"
    sql += " ORDER BY r.fecha_operacion, r.id"
    rows = conn.execute(sql, (periodo_id,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def eliminar_retenciones_periodo(pid):
    conn = get_connection()
    conn.execute("DELETE FROM retenciones WHERE periodo_id=?", (pid,))
    conn.commit()
    conn.close()


def toggle_seleccion(rid, val):
    conn = get_connection()
    conn.execute("UPDATE retenciones SET seleccionado=? WHERE id=?", (1 if val else 0, rid))
    conn.commit()
    conn.close()


def seleccionar_todos(pid, val):
    conn = get_connection()
    conn.execute("UPDATE retenciones SET seleccionado=? WHERE periodo_id=?", (1 if val else 0, pid))
    conn.commit()
    conn.close()


def registrar_xml(pid, nombre, ruta, nreg, tmonto, tret):
    conn = get_connection()
    conn.execute("""INSERT INTO xml_generados
        (periodo_id,fecha_generacion,nombre_archivo,ruta_archivo,
         total_registros,total_monto_operacion,total_monto_retenido)
        VALUES (?,datetime('now','localtime'),?,?,?,?,?)""",
        (pid, nombre, ruta, nreg, tmonto, tret))
    conn.commit()
    conn.close()


def get_all_periodos():
    conn = get_connection()
    rows = conn.execute("SELECT * FROM periodos ORDER BY periodo DESC").fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_all_beneficiarios(activos=True):
    conn = get_connection()
    sql = "SELECT * FROM beneficiarios"
    if activos:
        sql += " WHERE activo=1"
    sql += " ORDER BY nombre"
    rows = conn.execute(sql).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_resumen_periodo(pid):
    conn = get_connection()
    row = conn.execute("""SELECT COUNT(*) as total_reg,
        COALESCE(SUM(monto_operacion),0) as total_monto,
        COALESCE(SUM(monto_retenido),0) as total_ret,
        COUNT(DISTINCT beneficiario_id) as total_benef
        FROM retenciones WHERE periodo_id=?""", (pid,)).fetchone()
    conn.close()
    return dict(row) if row else {}


def get_resumen_por_concepto(pid):
    conn = get_connection()
    rows = conn.execute("""SELECT r.codigo_concepto, cc.descripcion,
        COUNT(*) as cantidad, SUM(r.monto_operacion) as total_monto,
        SUM(r.monto_retenido) as total_ret
        FROM retenciones r LEFT JOIN codigos_concepto cc ON r.codigo_concepto=cc.codigo
        WHERE r.periodo_id=? GROUP BY r.codigo_concepto""", (pid,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_resumen_por_beneficiario(pid):
    conn = get_connection()
    rows = conn.execute("""SELECT b.rif, b.nombre,
        COUNT(*) as cantidad, SUM(r.monto_operacion) as total_monto,
        SUM(r.monto_retenido) as total_ret
        FROM retenciones r JOIN beneficiarios b ON r.beneficiario_id=b.id
        WHERE r.periodo_id=? GROUP BY b.id ORDER BY total_monto DESC""", (pid,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_historial_xml(pid=None):
    conn = get_connection()
    sql = """SELECT x.*, p.periodo FROM xml_generados x
        JOIN periodos p ON x.periodo_id=p.id"""
    params = ()
    if pid:
        sql += " WHERE x.periodo_id=?"
        params = (pid,)
    sql += " ORDER BY x.fecha_generacion DESC"
    rows = conn.execute(sql, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]
