-- Parche: Inicialización del esquema de Recursos Humanos (RRHH) y registro en el Menú y Permisos.
-- Ejecutar con la base de datos correspondiente (PostgreSQL/SQLite)

CREATE SCHEMA IF NOT EXISTS hr;

-- 1. Departamentos organizacionales
CREATE TABLE IF NOT EXISTS hr.departments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. Registro de empleados
CREATE TABLE IF NOT EXISTS hr.employees (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    identification_number VARCHAR(50) NOT NULL,
    hiring_date DATE NOT NULL,
    department_id UUID REFERENCES hr.departments(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,
    UNIQUE (company_id, identification_number)
);

-- 3. Contratos de trabajo
CREATE TABLE IF NOT EXISTS hr.contracts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID NOT NULL REFERENCES hr.employees(id) ON DELETE CASCADE,
    contract_type VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    base_salary NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (contract_type IN ('indefinido', 'temporal', 'obra_servicio', 'practicas')),
    CHECK (status IN ('active', 'suspended', 'terminated')),
    CHECK (base_salary >= 0)
);

-- 4. Nóminas (Planillas de sueldos)
CREATE TABLE IF NOT EXISTS hr.payrolls (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_earnings NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    total_deductions NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    total_net NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    journal_entry_id UUID REFERENCES public.journal_entries(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (status IN ('draft', 'approved', 'paid', 'posted')),
    CHECK (start_date <= end_date)
);

-- 5. Detalle de nómina por empleado
CREATE TABLE IF NOT EXISTS hr.payroll_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payroll_id UUID NOT NULL REFERENCES hr.payrolls(id) ON DELETE CASCADE,
    employee_id UUID NOT NULL REFERENCES hr.employees(id) ON DELETE CASCADE,
    base_salary NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    bonuses NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    deductions NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    net_pay NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (base_salary >= 0),
    CHECK (bonuses >= 0),
    CHECK (deductions >= 0),
    CHECK (net_pay >= 0)
);

-- 6. Ausencias y permisos (Leaves)
CREATE TABLE IF NOT EXISTS hr.leaves (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID NOT NULL REFERENCES hr.employees(id) ON DELETE CASCADE,
    leave_type VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    notes TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (leave_type IN ('vacacion', 'enfermedad', 'maternidad_paternidad', 'no_remunerado', 'otros')),
    CHECK (status IN ('pending', 'approved', 'rejected')),
    CHECK (start_date <= end_date)
);

-- Índices para mejorar rendimiento de consultas
CREATE INDEX IF NOT EXISTS idx_hr_departments_company ON hr.departments(company_id);
CREATE INDEX IF NOT EXISTS idx_hr_employees_company ON hr.employees(company_id);
CREATE INDEX IF NOT EXISTS idx_hr_employees_dept ON hr.employees(department_id);
CREATE INDEX IF NOT EXISTS idx_hr_contracts_emp ON hr.contracts(employee_id);
CREATE INDEX IF NOT EXISTS idx_hr_payrolls_company ON hr.payrolls(company_id);
CREATE INDEX IF NOT EXISTS idx_hr_payroll_lines_payroll ON hr.payroll_lines(payroll_id);
CREATE INDEX IF NOT EXISTS idx_hr_leaves_emp ON hr.leaves(employee_id);

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

-- 1. Insertar Módulo de Recursos Humanos
INSERT INTO auth.module (id, name_es, name_en, icon, order_index, is_active)
VALUES ('7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Recursos Humanos', 'Human Resources', 'badge', 60, 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 RRHH
INSERT INTO auth.page (id, module_id, name_es, name_en, path, icon, order_index, is_active)
VALUES
  ('d1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8a', '7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Departamentos', 'Departments', '/hr/departments', 'domain', 10, TRUE),
  ('d1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8b', '7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Empleados', 'Employees', '/hr/employees', 'badge', 20, TRUE),
  ('d1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8c', '7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Nóminas', 'Payrolls', '/hr/payrolls', 'payments', 30, TRUE),
  ('d1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8d', '7f0f8a32-9fc1-4eb9-ea98-05f37f5d6e60', 'Vacaciones y Permisos', 'Leaves & Absences', '/hr/leaves', 'event_busy', 40, 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', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8a', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8b', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8c', TRUE, TRUE, TRUE, TRUE),
  ('8a9b3a32-dfc7-4eb5-aa94-c1f93f1d2e1c', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8d', 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', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8a', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8b', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8c', TRUE, TRUE, TRUE, TRUE),
  ('1b7c4a32-cfc8-4eb6-ba95-d2f04f2d3e2d', 'd1d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e8d', 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;
