erp-mecanicas-diesel-database-v2/init/13-fiados-schema.sql

-- ===========================================
-- MECANICAS DIESEL - Fiados (Credit) Schema
-- ===========================================
-- Sistema de crédito para clientes
-- Tracking de ventas a crédito y pagos

SET search_path TO service_management, public;

-- -------------------------------------------
-- CUSTOMER_CREDIT_ACCOUNTS - Cuentas de crédito
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS service_management.customer_credit_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    customer_id UUID NOT NULL REFERENCES service_management.customers(id),

    -- Límites de crédito
    credit_limit DECIMAL(12,2) DEFAULT 0 CHECK (credit_limit >= 0),
    credit_days INTEGER DEFAULT 30 CHECK (credit_days >= 0),

    -- Balances
    current_balance DECIMAL(12,2) DEFAULT 0,  -- Puede ser positivo (debe) o negativo (a favor)

    -- Estadísticas
    total_credit_given DECIMAL(14,2) DEFAULT 0,
    total_payments_received DECIMAL(14,2) DEFAULT 0,
    overdue_amount DECIMAL(12,2) DEFAULT 0,

    -- Estado
    is_active BOOLEAN DEFAULT true,
    is_frozen BOOLEAN DEFAULT false,  -- Congelar crédito si hay problemas
    frozen_reason TEXT,
    frozen_at TIMESTAMP WITH TIME ZONE,
    frozen_by UUID,

    -- Audit
    created_by UUID,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_customer_credit_account UNIQUE (tenant_id, customer_id)
);

CREATE INDEX idx_credit_accounts_tenant ON service_management.customer_credit_accounts(tenant_id);
CREATE INDEX idx_credit_accounts_customer ON service_management.customer_credit_accounts(customer_id);
CREATE INDEX idx_credit_accounts_balance ON service_management.customer_credit_accounts(tenant_id, current_balance) WHERE current_balance > 0;
CREATE INDEX idx_credit_accounts_overdue ON service_management.customer_credit_accounts(tenant_id, overdue_amount) WHERE overdue_amount > 0;

CREATE TRIGGER trg_credit_accounts_updated_at
    BEFORE UPDATE ON service_management.customer_credit_accounts
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- RLS
SELECT create_tenant_rls_policies('service_management', 'customer_credit_accounts');

-- -------------------------------------------
-- FIADOS - Ventas a crédito
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS service_management.fiados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Relaciones
    customer_id UUID NOT NULL REFERENCES service_management.customers(id),
    credit_account_id UUID NOT NULL REFERENCES service_management.customer_credit_accounts(id),
    order_id UUID REFERENCES service_management.service_orders(id),

    -- Identificación
    fiado_number VARCHAR(20) NOT NULL,

    -- Monto
    original_amount DECIMAL(12,2) NOT NULL CHECK (original_amount > 0),
    remaining_amount DECIMAL(12,2) NOT NULL CHECK (remaining_amount >= 0),

    -- Fechas
    issued_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    due_at TIMESTAMP WITH TIME ZONE NOT NULL,
    paid_at TIMESTAMP WITH TIME ZONE,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'partial', 'paid', 'overdue', 'cancelled', 'written_off')),

    -- Descripción
    description TEXT,
    notes TEXT,

    -- Audit
    created_by UUID,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_fiado_number UNIQUE (tenant_id, fiado_number)
);

CREATE INDEX idx_fiados_tenant ON service_management.fiados(tenant_id);
CREATE INDEX idx_fiados_customer ON service_management.fiados(customer_id);
CREATE INDEX idx_fiados_account ON service_management.fiados(credit_account_id);
CREATE INDEX idx_fiados_order ON service_management.fiados(order_id);
CREATE INDEX idx_fiados_status ON service_management.fiados(tenant_id, status);
CREATE INDEX idx_fiados_due ON service_management.fiados(due_at) WHERE status IN ('pending', 'partial');
CREATE INDEX idx_fiados_overdue ON service_management.fiados(tenant_id, due_at) WHERE status = 'overdue';

CREATE TRIGGER trg_fiados_updated_at
    BEFORE UPDATE ON service_management.fiados
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- RLS
SELECT create_tenant_rls_policies('service_management', 'fiados');

-- -------------------------------------------
-- FIADO_PAYMENTS - Pagos/Abonos a fiados
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS service_management.fiado_payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Relaciones
    customer_id UUID NOT NULL REFERENCES service_management.customers(id),
    credit_account_id UUID NOT NULL REFERENCES service_management.customer_credit_accounts(id),
    fiado_id UUID REFERENCES service_management.fiados(id),  -- NULL si es pago general a cuenta

    -- Identificación
    payment_number VARCHAR(20) NOT NULL,

    -- Monto
    amount DECIMAL(12,2) NOT NULL CHECK (amount > 0),

    -- Método de pago
    payment_method VARCHAR(20) NOT NULL
        CHECK (payment_method IN ('cash', 'card', 'transfer', 'check', 'other')),
    payment_reference VARCHAR(100),  -- Referencia de transferencia, número de cheque, etc.

    -- Descripción
    notes TEXT,

    -- Audit
    received_by UUID,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_payment_number UNIQUE (tenant_id, payment_number)
);

CREATE INDEX idx_fiado_payments_tenant ON service_management.fiado_payments(tenant_id);
CREATE INDEX idx_fiado_payments_customer ON service_management.fiado_payments(customer_id);
CREATE INDEX idx_fiado_payments_account ON service_management.fiado_payments(credit_account_id);
CREATE INDEX idx_fiado_payments_fiado ON service_management.fiado_payments(fiado_id);
CREATE INDEX idx_fiado_payments_date ON service_management.fiado_payments(created_at DESC);

-- RLS
SELECT create_tenant_rls_policies('service_management', 'fiado_payments');

-- -------------------------------------------
-- COMMENTS
-- -------------------------------------------
COMMENT ON TABLE service_management.customer_credit_accounts IS 'Cuentas de crédito de clientes';
COMMENT ON COLUMN service_management.customer_credit_accounts.current_balance IS 'Saldo actual: positivo = cliente debe, negativo = saldo a favor';
COMMENT ON COLUMN service_management.customer_credit_accounts.is_frozen IS 'Crédito congelado por morosidad u otra razón';

COMMENT ON TABLE service_management.fiados IS 'Ventas a crédito (fiados) individuales';
COMMENT ON COLUMN service_management.fiados.fiado_number IS 'Número único de fiado (F-001, F-002, etc.)';
COMMENT ON COLUMN service_management.fiados.remaining_amount IS 'Monto pendiente de pago';
COMMENT ON COLUMN service_management.fiados.due_at IS 'Fecha de vencimiento basada en credit_days del cliente';

COMMENT ON TABLE service_management.fiado_payments IS 'Pagos/abonos recibidos para fiados';
COMMENT ON COLUMN service_management.fiado_payments.fiado_id IS 'NULL si el pago se aplica a la cuenta general, no a un fiado específico';

-- -------------------------------------------
-- VIEW: Customer Credit Summary
-- -------------------------------------------
CREATE OR REPLACE VIEW service_management.v_customer_credit_summary AS
SELECT
    ca.tenant_id,
    ca.customer_id,
    c.name AS customer_name,
    c.phone AS customer_phone,
    ca.credit_limit,
    ca.credit_days,
    ca.current_balance,
    ca.credit_limit - ca.current_balance AS available_credit,
    ca.overdue_amount,
    ca.is_active,
    ca.is_frozen,
    (SELECT COUNT(*) FROM service_management.fiados f WHERE f.credit_account_id = ca.id AND f.status IN ('pending', 'partial', 'overdue')) AS pending_fiados_count,
    (SELECT COALESCE(SUM(f.remaining_amount), 0) FROM service_management.fiados f WHERE f.credit_account_id = ca.id AND f.status = 'overdue') AS overdue_fiados_amount,
    (SELECT MAX(fp.created_at) FROM service_management.fiado_payments fp WHERE fp.credit_account_id = ca.id) AS last_payment_at
FROM service_management.customer_credit_accounts ca
JOIN service_management.customers c ON c.id = ca.customer_id;

COMMENT ON VIEW service_management.v_customer_credit_summary IS 'Resumen de crédito por cliente con estadísticas';