-- Parche: Inicialización del esquema de Presupuesto (budget) y registro en el Menú y Permisos.
-- Ejecutar en la base de datos de producción (PostgreSQL)

CREATE SCHEMA IF NOT EXISTS budget;

-- 1. Años Fiscales Presupuestarios
CREATE TABLE IF NOT EXISTS budget.fiscal_years (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    year INTEGER NOT NULL UNIQUE,
    status VARCHAR(16) NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_status CHECK (status IN ('draft', 'open', 'closed'))
);

-- 2. Clasificadores Presupuestarios (Ingresos/Gastos)
CREATE TABLE IF NOT EXISTS budget.classifiers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(16) NOT NULL,
    parent_id UUID REFERENCES budget.classifiers(id) ON DELETE SET NULL,
    account_id UUID REFERENCES public.chart_accounts(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_type CHECK (type IN ('income', 'expense'))
);

-- 3. Unidades Organizacionales / Centros de Costo
CREATE TABLE IF NOT EXISTS budget.organizational_units (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    parent_id UUID REFERENCES budget.organizational_units(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 4. Formulación del Presupuesto (PIA/PIM)
CREATE TABLE IF NOT EXISTS budget.formulations (
    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,
    requested_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    approved_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0000, -- PIA
    current_amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,  -- PIM
    status VARCHAR(16) NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_formulation_status CHECK (status IN ('draft', 'submitted', 'approved')),
    CONSTRAINT unique_formulation UNIQUE (fiscal_year_id, org_unit_id, classifier_id),
    CONSTRAINT chk_requested_amount CHECK (requested_amount >= 0),
    CONSTRAINT chk_approved_amount CHECK (approved_amount >= 0),
    CONSTRAINT chk_current_amount CHECK (current_amount >= 0)
);

-- 5. Resoluciones de Modificación Presupuestaria
CREATE TABLE IF NOT EXISTS budget.modifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiscal_year_id UUID NOT NULL REFERENCES budget.fiscal_years(id) ON DELETE RESTRICT,
    type VARCHAR(16) NOT NULL,
    document_number VARCHAR(64) NOT NULL,
    description TEXT,
    approved_at DATE NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'draft',
    created_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_modification_type CHECK (type IN ('transfer', 'increase', 'decrease')),
    CONSTRAINT chk_modification_status CHECK (status IN ('draft', 'approved'))
);

-- 6. Detalles de las Modificaciones Presupuestarias
CREATE TABLE IF NOT EXISTS budget.modification_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    modification_id UUID NOT NULL REFERENCES budget.modifications(id) ON DELETE CASCADE,
    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,
    amount NUMERIC(18, 4) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 7. Historial y Fases de Ejecución Presupuestaria
CREATE TABLE IF NOT EXISTS budget.executions (
    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,
    phase VARCHAR(20) NOT NULL,
    source_document_type VARCHAR(64) NOT NULL,
    source_document_id UUID NOT NULL,
    ref_execution_id UUID REFERENCES budget.executions(id) ON DELETE SET NULL,
    amount NUMERIC(18, 4) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE RESTRICT,
    CONSTRAINT chk_execution_phase CHECK (phase IN ('pre_commitment', 'commitment', 'accrued', 'paid'))
);

-- Índices de Rendimiento
CREATE INDEX IF NOT EXISTS idx_budget_formulations_lookup ON budget.formulations(fiscal_year_id, org_unit_id, classifier_id);
CREATE INDEX IF NOT EXISTS idx_budget_executions_lookup ON budget.executions(fiscal_year_id, org_unit_id, classifier_id);
CREATE INDEX IF NOT EXISTS idx_budget_executions_phase ON budget.executions(phase);
CREATE INDEX IF NOT EXISTS idx_budget_modification_entries_mod ON budget.modification_entries(modification_id);

-- ==========================================
-- REGISTRO DE MENÚ Y PERMISOS DE PRESUPUESTO
-- ==========================================

-- 1. Insertar Módulo de Presupuesto
INSERT INTO auth.module (id, name_es, name_en, icon, order_index, is_active)
VALUES ('5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Presupuesto', 'Budgeting', 'account_balance_wallet', 55, TRUE)
ON CONFLICT (id) DO UPDATE
SET name_es = EXCLUDED.name_es,
    name_en = EXCLUDED.name_en,
    icon = EXCLUDED.icon,
    order_index = EXCLUDED.order_index,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 2. Insertar Páginas de Presupuesto
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES
  ('c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9a', '5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Clasificadores', 'Classifiers', '/budget/classifiers', 'account_tree', 10, TRUE),
  ('c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9b', '5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Formulación', 'Formulation', '/budget/formulation', 'calendar_today', 20, TRUE),
  ('c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9c', '5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Modificaciones', 'Modifications', '/budget/modifications', 'published_with_changes', 30, TRUE),
  ('c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9d', '5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Ejecución Presupuestaria', 'Execution', '/budget/execution', 'analytics', 40, TRUE),
  ('c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9e', '5b8f8a32-9fc1-4eb9-ea98-05f37f5d6e70', 'Reportes de Ejecución', 'Execution Reports', '/budget/reports', 'assessment', 50, TRUE)
ON CONFLICT (id) DO UPDATE
SET module_id = EXCLUDED.module_id,
    name_es = EXCLUDED.name_es,
    name_en = EXCLUDED.name_en,
    path = EXCLUDED.path,
    icon = EXCLUDED.icon,
    order_index = EXCLUDED.order_index,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 3. Asignar Permisos a Administrador (Acceso Total)
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete)
VALUES
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9a', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9b', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9c', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9d', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9e', 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;

-- 4. Asignar Permisos a Gerente (Acceso Total)
INSERT INTO auth.profile (role_id, page_id, can_view, can_create, can_edit, can_delete)
VALUES
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9a', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9b', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9c', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9d', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'c2d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e9e', 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;
