-- Parche: Esquema de Conceptos de Nómina y Sobrescrituras
-- Ejecutar con la base de datos correspondiente (PostgreSQL/SQLite)

CREATE TABLE IF NOT EXISTS hr.payroll_concepts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    calculation_type VARCHAR(20) NOT NULL,
    default_value NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    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_concept_type CHECK (type IN ('earnings', 'deductions')),
    CONSTRAINT chk_calculation_type CHECK (calculation_type IN ('fixed', 'percentage')),
    CONSTRAINT chk_default_value CHECK (default_value >= 0)
);

CREATE INDEX IF NOT EXISTS idx_payroll_concepts_company ON hr.payroll_concepts(company_id);

CREATE TABLE IF NOT EXISTS hr.employee_concepts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID NOT NULL REFERENCES hr.employees(id) ON DELETE CASCADE,
    concept_id UUID NOT NULL REFERENCES hr.payroll_concepts(id) ON DELETE CASCADE,
    value NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    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_employee_concept_value CHECK (value >= 0),
    UNIQUE (employee_id, concept_id)
);

CREATE INDEX IF NOT EXISTS idx_employee_concepts_employee ON hr.employee_concepts(employee_id);

CREATE TABLE IF NOT EXISTS hr.payroll_line_details (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payroll_line_id UUID NOT NULL REFERENCES hr.payroll_lines(id) ON DELETE CASCADE,
    concept_id UUID NOT NULL REFERENCES hr.payroll_concepts(id) ON DELETE RESTRICT,
    concept_name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    amount NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_line_details_amount CHECK (amount >= 0)
);

CREATE INDEX IF NOT EXISTS idx_line_details_line ON hr.payroll_line_details(payroll_line_id);

-- Insertar Página de Conceptos en el menú de RRHH
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES
  ('d1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8e', '7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Conceptos de Nómina', 'Payroll Concepts', '/hr/concepts', 'list', 25, 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;

-- 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', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8e', 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;

-- 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', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8e', 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;
