-- Parche: Inicialización de Órdenes de Compra/Servicio y Precompromisos
-- Ejecutar en la base de datos (PostgreSQL)

BEGIN;

-- 1. Tabla principal de Órdenes de Compra y Servicio
CREATE TABLE IF NOT EXISTS public.purchase_orders (
    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,
    order_number VARCHAR(30) NOT NULL, -- Número correlativo O/C o O/S
    order_type VARCHAR(20) NOT NULL CHECK (order_type IN ('purchase', 'service')),
    provider_id UUID NOT NULL REFERENCES public.contacts(id) ON DELETE RESTRICT,
    issue_date DATE NOT NULL,
    description TEXT NULL,
    subtotal NUMERIC(18, 4) NOT NULL DEFAULT 0.0,
    tax_amount 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', 'precommitted', 'approved', 'completed', 'void')),
    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, order_number)
);

-- 2. Líneas de detalle vinculadas a Cuentas Contables y Clasificadores Presupuestarios
CREATE TABLE IF NOT EXISTS public.purchase_order_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_order_id UUID NOT NULL REFERENCES public.purchase_orders(id) ON DELETE CASCADE,
    description TEXT NOT 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, -- Imputación Contable
    classifier_id UUID NOT NULL REFERENCES budget.classifiers(id) ON DELETE RESTRICT,       -- Imputación Presupuestaria
    line_order INT NOT NULL DEFAULT 0
);

-- 3. Tabla de Precompromisos para apartar y verificar la disponibilidad de las partidas
CREATE TABLE IF NOT EXISTS budget.pre_commitments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiscal_year_id UUID NOT NULL REFERENCES budget.fiscal_years(id) ON DELETE RESTRICT,
    org_unit_id UUID NOT NULL REFERENCES budget.organizational_units(id) ON DELETE RESTRICT,
    classifier_id UUID NOT NULL REFERENCES budget.classifiers(id) ON DELETE RESTRICT,
    source_document_id UUID NOT NULL REFERENCES public.purchase_orders(id) ON DELETE CASCADE,
    amount NUMERIC(18, 4) NOT NULL CHECK (amount > 0),
    status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'released', 'executed')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Índices de Rendimiento
CREATE INDEX IF NOT EXISTS idx_purchase_orders_company ON public.purchase_orders(company_id);
CREATE INDEX IF NOT EXISTS idx_purchase_order_lines_order ON public.purchase_order_lines(purchase_order_id);
CREATE INDEX IF NOT EXISTS idx_pre_commitments_lookup ON budget.pre_commitments(fiscal_year_id, org_unit_id, classifier_id);

-- 4. Registrar las Nuevas Páginas en el Menú de "Compras" (Módulo ID '6f4c8b5e-df92-4eb3-9d0f-4f737d8e7d5f')
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES 
  ('8b1d9c0e-4316-5f5a-b8e8-c6d0e2a8f55a', '6f4c8b5e-df92-4eb3-9d0f-4f737d8e7d5f', 'Órdenes de Compra', 'Purchase Orders', '/purchases/orders', 'receipt', 10, TRUE),
  ('9c2d0e1f-4316-5f5a-b8e8-c6d0e2a8f55a', '6f4c8b5e-df92-4eb3-9d0f-4f737d8e7d5f', 'Órdenes de Servicio', 'Service Orders', '/purchases/services', 'work_outline', 20, TRUE)
ON CONFLICT (id) DO UPDATE 
SET name_es = EXCLUDED.name_es, name_en = EXCLUDED.name_en, path = EXCLUDED.path, icon = EXCLUDED.icon, order_index = EXCLUDED.order_index;

-- 5. Otorgar Permisos por Defecto al Administrador de Sistema para estas pantallas
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete)
VALUES 
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '8b1d9c0e-4316-5f5a-b8e8-c6d0e2a8f55a', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', '9c2d0e1f-4316-5f5a-b8e8-c6d0e2a8f55a', TRUE, TRUE, TRUE, TRUE)
ON CONFLICT (role_id, page_id) DO UPDATE 
SET can_view = EXCLUDED.can_view, can_create = EXCLUDED.can_create, can_edit = EXCLUDED.can_edit, can_delete = EXCLUDED.can_delete;

COMMIT;
