-- Fase 4: Ventas y Clientes (PostgreSQL / public)
-- Requiere: companies, branches, contacts, chart_accounts, tax_rates

-- 1. Crear Tablas de Ventas
CREATE TABLE IF NOT EXISTS public.sales_invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    branch_id UUID NULL REFERENCES public.branches(id) ON DELETE SET NULL,
    customer_id UUID NOT NULL REFERENCES public.contacts(id) ON DELETE RESTRICT,
    receivable_account_id UUID NOT NULL REFERENCES public.chart_accounts(id) ON DELETE RESTRICT,
    invoice_number VARCHAR(30) NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    reference VARCHAR(100) NULL,
    currency_code VARCHAR(3) NOT NULL DEFAULT 'USD',
    exchange_rate NUMERIC(18, 8) NOT NULL DEFAULT 1.0,
    discount_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    tax_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    subtotal NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    total NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    balance NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    notes TEXT NULL,
    created_by UUID NULL REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (company_id, invoice_number),
    CHECK (status IN ('draft', 'posted', 'paid', 'partially_paid', 'void'))
);

CREATE TABLE IF NOT EXISTS public.sales_invoice_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES public.sales_invoices(id) ON DELETE CASCADE,
    description TEXT NULL,
    quantity NUMERIC(18, 4) NOT NULL DEFAULT 1.0,
    price NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    tax_rate_id UUID NULL REFERENCES public.tax_rates(id) ON DELETE RESTRICT,
    tax_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    total NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    chart_account_id UUID NOT NULL REFERENCES public.chart_accounts(id) ON DELETE RESTRICT, -- Cuenta contable de Ingreso
    line_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS public.payment_receives (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    branch_id UUID NULL REFERENCES public.branches(id) ON DELETE SET NULL,
    customer_id UUID NOT NULL REFERENCES public.contacts(id) ON DELETE RESTRICT,
    payment_number VARCHAR(30) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50) NOT NULL DEFAULT 'cash', -- cash, bank, check, card, other
    amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    currency_code VARCHAR(3) NOT NULL DEFAULT 'USD',
    exchange_rate NUMERIC(18, 8) NOT NULL DEFAULT 1.0,
    chart_account_id UUID NOT NULL REFERENCES public.chart_accounts(id) ON DELETE RESTRICT, -- Cuenta Caja/Banco (Debitar)
    notes TEXT NULL,
    created_by UUID NULL REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (company_id, payment_number)
);

CREATE TABLE IF NOT EXISTS public.payment_receive_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id UUID NOT NULL REFERENCES public.payment_receives(id) ON DELETE CASCADE,
    invoice_id UUID NOT NULL REFERENCES public.sales_invoices(id) ON DELETE CASCADE,
    amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    UNIQUE (payment_id, invoice_id)
);

-- Índices de rendimiento
CREATE INDEX IF NOT EXISTS idx_sales_invoices_company ON public.sales_invoices(company_id);
CREATE INDEX IF NOT EXISTS idx_sales_invoices_customer ON public.sales_invoices(customer_id);
CREATE INDEX IF NOT EXISTS idx_sales_invoices_date ON public.sales_invoices(invoice_date);
CREATE INDEX IF NOT EXISTS idx_sales_invoice_lines_invoice ON public.sales_invoice_lines(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payment_receives_company ON public.payment_receives(company_id);
CREATE INDEX IF NOT EXISTS idx_payment_receives_customer ON public.payment_receives(customer_id);

-- 2. Registrar Páginas de Ventas en auth.page
-- Se utiliza el Module ID '5f3b7b4d-ef81-4eb2-8c9f-3f626c7e6c4e' de Ventas ya registrado en la BD.
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES 
(
    'd2f1e0c9-b5a6-4f7e-8c9d-0e1f2a3b4c5d',
    '5f3b7b4d-ef81-4eb2-8c9f-3f626c7e6c4e', -- Módulo Ventas existente
    'Facturas de Venta',
    'Sales Invoices',
    '/sales/invoices',
    'receipt_long',
    20,
    true
),
(
    'c1f0e9c8-b5a6-4f7e-8c9d-0e1f2a3b4c5d',
    '5f3b7b4d-ef81-4eb2-8c9f-3f626c7e6c4e', -- Módulo Ventas existente
    'Cobros',
    'Payments Received',
    '/sales/payments',
    'monetization_on',
    30,
    true
) ON CONFLICT (id) DO NOTHING;

-- 3. Asignar Permisos a los Roles Administrador y Gerente
INSERT INTO auth.profile (id, role_id, page_id, can_view, can_create, can_edit, can_delete)
VALUES
(
    gen_random_uuid(),
    '8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', -- Administrador
    'd2f1e0c9-b5a6-4f7e-8c9d-0e1f2a3b4c5d', -- Facturas de Venta
    true, true, true, true
),
(
    gen_random_uuid(),
    '8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', -- Administrador
    'c1f0e9c8-b5a6-4f7e-8c9d-0e1f2a3b4c5d', -- Cobros
    true, true, true, true
),
(
    gen_random_uuid(),
    '1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', -- Gerente
    'd2f1e0c9-b5a6-4f7e-8c9d-0e1f2a3b4c5d', -- Facturas de Venta
    true, true, true, false
),
(
    gen_random_uuid(),
    '1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', -- Gerente
    'c1f0e9c8-b5a6-4f7e-8c9d-0e1f2a3b4c5d', -- Cobros
    true, true, true, false
) ON CONFLICT DO NOTHING;
