michangarrito/database/schemas/15-invoices.sql

-- =============================================================================
-- MICHANGARRITO - 15 INVOICES (SAT/CFDI)
-- =============================================================================
-- Facturacion electronica mexicana (CFDI)
-- =============================================================================

-- Crear schema si no existe
CREATE SCHEMA IF NOT EXISTS billing;

-- Configuracion fiscal por tenant
CREATE TABLE IF NOT EXISTS billing.tax_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Datos fiscales del emisor
    rfc VARCHAR(13) NOT NULL,
    razon_social VARCHAR(200) NOT NULL,
    regimen_fiscal VARCHAR(3) NOT NULL,
    codigo_postal VARCHAR(5) NOT NULL,

    -- Certificado digital (CSD)
    csd_certificate TEXT,
    csd_private_key_encrypted TEXT,
    csd_password_encrypted TEXT,
    csd_valid_from TIMESTAMPTZ,
    csd_valid_to TIMESTAMPTZ,

    -- PAC Provider
    pac_provider VARCHAR(20) DEFAULT 'facturapi',
    pac_api_key_encrypted TEXT,
    pac_sandbox BOOLEAN DEFAULT true,

    -- Configuracion
    serie VARCHAR(10) DEFAULT 'A',
    folio_actual INTEGER DEFAULT 1,
    auto_send_email BOOLEAN DEFAULT true,
    logo_url TEXT,

    status VARCHAR(20) DEFAULT 'pending',
    -- pending, active, suspended

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

CREATE INDEX idx_tax_configs_tenant ON billing.tax_configs(tenant_id);
CREATE INDEX idx_tax_configs_rfc ON billing.tax_configs(rfc);

CREATE TRIGGER update_tax_configs_updated_at
    BEFORE UPDATE ON billing.tax_configs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Facturas (CFDI)
CREATE TABLE IF NOT EXISTS billing.invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    -- Tipo de comprobante
    tipo_comprobante VARCHAR(1) NOT NULL DEFAULT 'I',
    -- I = Ingreso, E = Egreso, T = Traslado, P = Pago, N = Nomina

    -- Folio fiscal
    uuid VARCHAR(36) UNIQUE,
    serie VARCHAR(10),
    folio INTEGER,

    -- Receptor (cliente)
    receptor_rfc VARCHAR(13) NOT NULL,
    receptor_nombre VARCHAR(200) NOT NULL,
    receptor_regimen_fiscal VARCHAR(3),
    receptor_codigo_postal VARCHAR(5) NOT NULL,
    receptor_uso_cfdi VARCHAR(4) NOT NULL,
    receptor_email VARCHAR(200),

    -- Montos
    subtotal DECIMAL(12,2) NOT NULL,
    descuento DECIMAL(12,2) DEFAULT 0,
    total_impuestos_trasladados DECIMAL(12,2) DEFAULT 0,
    total_impuestos_retenidos DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,

    -- Pago
    forma_pago VARCHAR(2) NOT NULL,
    metodo_pago VARCHAR(3) NOT NULL,
    condiciones_pago VARCHAR(100),

    -- Moneda
    moneda VARCHAR(3) DEFAULT 'MXN',
    tipo_cambio DECIMAL(10,6) DEFAULT 1,

    -- Archivos
    xml_url TEXT,
    pdf_url TEXT,
    qr_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'draft',
    -- draft, pending, stamped, sent, cancelled

    -- Cancelacion
    cancelled_at TIMESTAMPTZ,
    cancel_reason VARCHAR(2),
    cancel_uuid_replacement VARCHAR(36),

    -- Timbrado
    stamped_at TIMESTAMPTZ,
    pac_response JSONB,

    -- Metadata
    notes TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoices_tenant ON billing.invoices(tenant_id);
CREATE INDEX idx_invoices_sale ON billing.invoices(sale_id);
CREATE INDEX idx_invoices_uuid ON billing.invoices(uuid);
CREATE INDEX idx_invoices_receptor ON billing.invoices(receptor_rfc);
CREATE INDEX idx_invoices_status ON billing.invoices(status);
CREATE INDEX idx_invoices_date ON billing.invoices(created_at);

CREATE TRIGGER update_invoices_updated_at
    BEFORE UPDATE ON billing.invoices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Conceptos de factura
CREATE TABLE IF NOT EXISTS billing.invoice_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES billing.invoices(id) ON DELETE CASCADE,
    product_id UUID REFERENCES catalog.products(id),

    -- Clave producto/servicio SAT
    clave_prod_serv VARCHAR(8) NOT NULL,
    no_identificacion VARCHAR(100),

    -- Descripcion
    descripcion VARCHAR(1000) NOT NULL,

    -- Cantidad
    cantidad DECIMAL(12,6) NOT NULL,
    clave_unidad VARCHAR(3) NOT NULL,
    unidad VARCHAR(20),

    -- Precios
    valor_unitario DECIMAL(12,6) NOT NULL,
    descuento DECIMAL(12,2) DEFAULT 0,
    importe DECIMAL(12,2) NOT NULL,

    -- Objeto de impuesto
    objeto_imp VARCHAR(2) DEFAULT '02',

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_items_invoice ON billing.invoice_items(invoice_id);

-- Impuestos de conceptos
CREATE TABLE IF NOT EXISTS billing.invoice_item_taxes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_item_id UUID NOT NULL REFERENCES billing.invoice_items(id) ON DELETE CASCADE,

    tipo VARCHAR(10) NOT NULL,
    -- traslado, retencion

    impuesto VARCHAR(3) NOT NULL,
    -- 001 = ISR, 002 = IVA, 003 = IEPS

    tipo_factor VARCHAR(10) NOT NULL,
    -- Tasa, Cuota, Exento

    tasa_o_cuota DECIMAL(10,6),
    base DECIMAL(12,2) NOT NULL,
    importe DECIMAL(12,2),

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_item_taxes_item ON billing.invoice_item_taxes(invoice_item_id);

-- Historial de facturas (para auditoria)
CREATE TABLE IF NOT EXISTS billing.invoice_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES billing.invoices(id) ON DELETE CASCADE,

    action VARCHAR(20) NOT NULL,
    -- created, stamped, sent, cancelled

    details JSONB,
    performed_by UUID,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_history_invoice ON billing.invoice_history(invoice_id);

-- Funcion para obtener siguiente folio
CREATE OR REPLACE FUNCTION get_next_invoice_folio(p_tenant_id UUID, p_serie VARCHAR DEFAULT 'A')
RETURNS INTEGER AS $$
DECLARE
    next_folio INTEGER;
BEGIN
    UPDATE billing.tax_configs
    SET folio_actual = folio_actual + 1
    WHERE tenant_id = p_tenant_id
    RETURNING folio_actual INTO next_folio;

    IF next_folio IS NULL THEN
        next_folio := 1;
    END IF;

    RETURN next_folio;
END;
$$ LANGUAGE plpgsql;

-- Funcion para obtener resumen de facturacion
CREATE OR REPLACE FUNCTION get_invoice_summary(p_tenant_id UUID, p_month DATE DEFAULT CURRENT_DATE)
RETURNS TABLE (
    total_invoices INTEGER,
    total_amount DECIMAL(12,2),
    total_cancelled INTEGER,
    by_status JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::INTEGER as total_invoices,
        COALESCE(SUM(total), 0) as total_amount,
        COUNT(*) FILTER (WHERE status = 'cancelled')::INTEGER as total_cancelled,
        jsonb_object_agg(status, cnt) as by_status
    FROM (
        SELECT status, COUNT(*) as cnt
        FROM billing.invoices
        WHERE tenant_id = p_tenant_id
        AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', p_month)
        GROUP BY status
    ) s,
    billing.invoices i
    WHERE i.tenant_id = p_tenant_id
    AND DATE_TRUNC('month', i.created_at) = DATE_TRUNC('month', p_month);
END;
$$ LANGUAGE plpgsql;