michangarrito/database/schemas/08-customers.sql

-- =============================================================================
-- MICHANGARRITO - 08 CUSTOMERS
-- =============================================================================
-- Clientes y sistema de fiados
-- =============================================================================

-- Clientes
CREATE TABLE IF NOT EXISTS customers.customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),

    address TEXT,
    address_reference TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),

    fiado_enabled BOOLEAN DEFAULT true,
    fiado_limit DECIMAL(10,2),
    current_fiado_balance DECIMAL(10,2) DEFAULT 0,

    total_purchases DECIMAL(12,2) DEFAULT 0,
    purchase_count INTEGER DEFAULT 0,
    last_purchase_at TIMESTAMPTZ,

    whatsapp_opt_in BOOLEAN DEFAULT false,

    notes TEXT,
    status VARCHAR(20) DEFAULT 'active',

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

CREATE INDEX idx_customers_tenant ON customers.customers(tenant_id);
CREATE INDEX idx_customers_phone ON customers.customers(tenant_id, phone);
CREATE INDEX idx_customers_name ON customers.customers USING gin(to_tsvector('spanish', name));

CREATE TRIGGER update_customers_updated_at
    BEFORE UPDATE ON customers.customers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Actualizar FK en sales
ALTER TABLE sales.sales
ADD CONSTRAINT fk_sales_customer
FOREIGN KEY (customer_id) REFERENCES customers.customers(id);

-- Fiados
CREATE TABLE IF NOT EXISTS customers.fiados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    customer_id UUID NOT NULL REFERENCES customers.customers(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    original_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    remaining_amount DECIMAL(10,2) NOT NULL,

    due_date DATE,
    status VARCHAR(20) DEFAULT 'pending',

    description TEXT,

    last_reminder_at TIMESTAMPTZ,
    reminder_count INTEGER DEFAULT 0,

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

CREATE INDEX idx_fiados_tenant ON customers.fiados(tenant_id);
CREATE INDEX idx_fiados_customer ON customers.fiados(customer_id);
CREATE INDEX idx_fiados_status ON customers.fiados(status);

CREATE TRIGGER update_fiados_updated_at
    BEFORE UPDATE ON customers.fiados
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Trigger para actualizar balance del cliente
CREATE TRIGGER update_fiado_balance
    AFTER INSERT OR UPDATE OR DELETE ON customers.fiados
    FOR EACH ROW EXECUTE FUNCTION customers.update_customer_fiado_balance();

-- Actualizar FK en sales para fiado
ALTER TABLE sales.sales
ADD CONSTRAINT fk_sales_fiado
FOREIGN KEY (fiado_id) REFERENCES customers.fiados(id);

-- Pagos de fiados
CREATE TABLE IF NOT EXISTS customers.fiado_payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiado_id UUID NOT NULL REFERENCES customers.fiados(id) ON DELETE CASCADE,

    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,

    notes TEXT,

    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_fiado_payments_fiado ON customers.fiado_payments(fiado_id);