database.py

import sqlite3
from contextlib import contextmanager
from decimal import Decimal, ROUND_HALF_UP
from typing import Iterable, Optional, Tuple

DB_NAME = "invoices.db"

VALID_DOC_TYPES = ("Income", "Expenses")
VALID_QUARTERS = (1, 2, 3, 4)


def euros_to_cents(value) -> int:
    """Convert a euro amount to integer cents using decimal rounding."""
    amount = Decimal(str(value or 0)).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
    return int(amount * 100)


def cents_to_euros(cents: Optional[int]) -> float:
    """Convert integer cents to a float euro value for display only."""
    return float(Decimal(cents or 0) / Decimal(100))


def calculate_invoice_cents(base_euros, iva_rate, retencion_rate) -> Tuple[int, int, int, int]:
    """
    Calculate all monetary amounts in integer cents.

    The database stores money as integer cents to avoid accumulated floating-point
    errors in tax summaries.
    """
    base_cents = euros_to_cents(base_euros)
    iva = Decimal(base_cents) * Decimal(str(iva_rate or 0)) / Decimal(100)
    ret = Decimal(base_cents) * Decimal(str(retencion_rate or 0)) / Decimal(100)

    iva_cents = int(iva.quantize(Decimal("1"), rounding=ROUND_HALF_UP))
    retencion_cents = int(ret.quantize(Decimal("1"), rounding=ROUND_HALF_UP))
    total_cents = base_cents + iva_cents - retencion_cents
    return base_cents, iva_cents, retencion_cents, total_cents


@contextmanager
def get_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        yield conn
    finally:
        conn.close()


def init_db():
    """
    Create a fresh database schema.
    """
    with get_connection() as conn:
        cursor = conn.cursor()

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS company_profile (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                fiscal_name TEXT,
                nif_cif TEXT,
                fiscal_address TEXT,
                default_iva_rate REAL DEFAULT 21.0,
                default_retencion_rate REAL DEFAULT 0.0,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """)

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS clients (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                cif TEXT UNIQUE NOT NULL CHECK (length(trim(cif)) > 0),
                name TEXT NOT NULL CHECK (length(trim(name)) > 0),
                email TEXT,
                address TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS invoices (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                invoice_number TEXT NOT NULL CHECK (length(trim(invoice_number)) > 0),
                date TEXT NOT NULL CHECK (date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),
                year INTEGER NOT NULL CHECK (year BETWEEN 2000 AND 2100),
                quarter INTEGER NOT NULL CHECK (quarter BETWEEN 1 AND 4),
                type TEXT NOT NULL CHECK (type IN ('Income', 'Expenses')),
                client_id INTEGER NOT NULL,

                base_imponible_cents INTEGER NOT NULL CHECK (base_imponible_cents >= 0),
                iva_rate REAL NOT NULL CHECK (iva_rate >= 0 AND iva_rate <= 100),
                iva_amount_cents INTEGER NOT NULL CHECK (iva_amount_cents >= 0),
                retencion_rate REAL NOT NULL DEFAULT 0.0 CHECK (retencion_rate >= 0 AND retencion_rate <= 100),
                retencion_amount_cents INTEGER NOT NULL DEFAULT 0 CHECK (retencion_amount_cents >= 0),
                total_cents INTEGER NOT NULL,

                original_filename TEXT,
                reference_description TEXT,
                gdrive_file_id TEXT UNIQUE,
                notes TEXT,

                parse_status TEXT NOT NULL DEFAULT 'not_parsed'
                    CHECK (parse_status IN ('not_parsed', 'pending', 'parsed', 'failed')),
                parsed_json TEXT,

                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

                FOREIGN KEY(client_id) REFERENCES clients(id) ON UPDATE CASCADE ON DELETE RESTRICT,
                UNIQUE(client_id, invoice_number, type)
            )
        ''')

        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_invoices_year_quarter_type
            ON invoices(year, quarter, type)
        ''')
        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_invoices_client_date
            ON invoices(client_id, date)
        ''')

        conn.commit()


def add_client(cif, name, email="", address=""):
    """Insert a new client/provider into the registry."""
    cif_clean = (cif or "").upper().strip()
    name_clean = (name or "").strip()

    if not cif_clean:
        return False, "Error: CIF/NIF is required."
    if not name_clean:
        return False, "Error: client/provider name is required."

    try:
        with get_connection() as conn:
            conn.execute(
                "INSERT INTO clients (cif, name, email, address) VALUES (?, ?, ?, ?)",
                (cif_clean, name_clean, (email or "").strip(), (address or "").strip()),
            )
            conn.commit()
        return True, "Client/provider added successfully."
    except sqlite3.IntegrityError:
        return False, f"Error: a client/provider with CIF/NIF {cif_clean} already exists."


def add_invoice(invoice_number, inv_date, year, quarter, doc_type, client_id,
                base_euros, iva_rate, retencion_rate, gdrive_file_id,
                original_filename="", reference_description="", notes=""):
    """
    Add an invoice record using one SQLite transaction.

    Monetary fields are calculated and stored as integer cents.
    """
    inv_number_clean = (invoice_number or "").strip()
    if not inv_number_clean:
        raise ValueError("Invoice number cannot be empty.")
    if doc_type not in VALID_DOC_TYPES:
        raise ValueError("Document type must be Income or Expenses.")
    if quarter not in VALID_QUARTERS:
        raise ValueError("Quarter must be between 1 and 4.")

    base_cents, iva_cents, retencion_cents, total_cents = calculate_invoice_cents(
        base_euros, iva_rate, retencion_rate
    )

    with get_connection() as conn:
        try:
            conn.execute("BEGIN")
            conn.execute('''
                INSERT INTO invoices (
                    invoice_number, date, year, quarter, type, client_id,
                    base_imponible_cents, iva_rate, iva_amount_cents,
                    retencion_rate, retencion_amount_cents, total_cents,
                    original_filename, reference_description, gdrive_file_id, notes
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                inv_number_clean, str(inv_date), int(year), int(quarter), doc_type, int(client_id),
                base_cents, float(iva_rate), iva_cents,
                float(retencion_rate or 0), retencion_cents, total_cents,
                (original_filename or "").strip(),(reference_description or "").strip(),
                gdrive_file_id, (notes or "").strip(),
            ))
            conn.commit()
        except Exception:
            conn.rollback()
            raise


def get_all_clients():
    """Fetch all client rows to populate dropdown menus."""
    with get_connection() as conn:
        cursor = conn.execute("SELECT id, name, cif, address FROM clients ORDER BY name ASC")
        return cursor.fetchall()
    

def get_company_profile():
    with get_connection() as conn:
        cursor = conn.cursor()

        cursor.execute("""
            SELECT fiscal_name, nif_cif, fiscal_address,
                   default_iva_rate, default_retencion_rate
            FROM company_profile
            WHERE id = 1
        """)

        row = cursor.fetchone()

    if row is None:
        return {
            "fiscal_name": "",
            "nif_cif": "",
            "fiscal_address": "",
            "default_iva_rate": 21.0,
            "default_retencion_rate": 0.0,
        }

    return {
        "fiscal_name": row[0] or "",
        "nif_cif": row[1] or "",
        "fiscal_address": row[2] or "",
        "default_iva_rate": row[3],
        "default_retencion_rate": row[4],
    }


def save_company_profile(profile):
    with get_connection() as conn:
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO company_profile (
                id, fiscal_name, nif_cif, fiscal_address,
                default_iva_rate, default_retencion_rate,
                updated_at
            )
            VALUES (1, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
            ON CONFLICT(id) DO UPDATE SET
                fiscal_name = excluded.fiscal_name,
                nif_cif = excluded.nif_cif,
                fiscal_address = excluded.fiscal_address,
                default_iva_rate = excluded.default_iva_rate,
                default_retencion_rate = excluded.default_retencion_rate,
                updated_at = CURRENT_TIMESTAMP
        """, (
            profile["fiscal_name"].strip(),
            profile["nif_cif"].strip().upper(),
            profile["fiscal_address"].strip(),
            profile["default_iva_rate"],
            profile["default_retencion_rate"],
        ))

        conn.commit()

    return True, "Company profile saved."