-- Patch: Notas de Crédito y Notas de Débito (Ventas)
-- Requiere: companies, branches, contacts, sales_invoices, chart_accounts, tax_rates

CREATE TABLE IF NOT EXISTS public.sales_adjustments (
    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,
    adjustment_type VARCHAR(20) NOT NULL CHECK (adjustment_type IN ('credit_note', 'debit_note')),
    document_number VARCHAR(30) NOT NULL,
    document_date DATE NOT NULL,
    reference_invoice_id UUID NULL REFERENCES public.sales_invoices(id) ON DELETE CASCADE,
    receivable_account_id UUID NOT NULL REFERENCES public.chart_accounts(id) ON DELETE RESTRICT,
    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,
    status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'posted', 'void')),
    journal_entry_id UUID NULL REFERENCES public.journal_entries(id) ON DELETE SET NULL,
    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, adjustment_type, document_number)
);

CREATE TABLE IF NOT EXISTS public.sales_adjustment_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    adjustment_id UUID NOT NULL REFERENCES public.sales_adjustments(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 o Devolución
    line_order INT NOT NULL DEFAULT 0
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_sales_adjustments_company ON public.sales_adjustments(company_id);
CREATE INDEX IF NOT EXISTS idx_sales_adjustments_customer ON public.sales_adjustments(customer_id);
CREATE INDEX IF NOT EXISTS idx_sales_adjustments_invoice ON public.sales_adjustments(reference_invoice_id);
CREATE INDEX IF NOT EXISTS idx_sales_adjustment_lines_adj ON public.sales_adjustment_lines(adjustment_id);

-- Registrar Páginas
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES 
(
    'd3f2e1c0-b6a7-4f7e-8c9d-0e1f2a3b4c5d',
    '5f3b7b4d-ef81-4eb2-8c9f-3f626c7e6c4e', -- Módulo Ventas
    'Notas de Crédito',
    'Credit Notes',
    '/sales/credit-notes',
    'assignment_return',
    40,
    true
),
(
    'd4f3e2c1-b7a8-4f7e-8c9d-0e1f2a3b4c5d',
    '5f3b7b4d-ef81-4eb2-8c9f-3f626c7e6c4e', -- Módulo Ventas
    'Notas de Débito',
    'Debit Notes',
    '/sales/debit-notes',
    'note_add',
    50,
    true
) ON CONFLICT (id) DO NOTHING;

-- 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
-- Notas de Crédito
(
    gen_random_uuid(),
    '8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', -- Administrador
    'd3f2e1c0-b6a7-4f7e-8c9d-0e1f2a3b4c5d',
    true, true, true, true
),
(
    gen_random_uuid(),
    '1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', -- Gerente
    'd3f2e1c0-b6a7-4f7e-8c9d-0e1f2a3b4c5d',
    true, true, true, false
),
-- Notas de Débito
(
    gen_random_uuid(),
    '8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', -- Administrador
    'd4f3e2c1-b7a8-4f7e-8c9d-0e1f2a3b4c5d',
    true, true, true, true
),
(
    gen_random_uuid(),
    '1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', -- Gerente
    'd4f3e2c1-b7a8-4f7e-8c9d-0e1f2a3b4c5d',
    true, true, true, false
) ON CONFLICT DO NOTHING;
