-- WireAdmin Phase 4: HR & Remunerations

-- Indicadores Económicos (UF, UTM, Topes)
CREATE TABLE IF NOT EXISTS wa_hr_indicators (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period DATE NOT NULL, -- YYYY-MM-01
    uf_value DECIMAL(10, 2) NOT NULL,
    utm_value DECIMAL(10, 2) NOT NULL,
    tope_imponible_afp DECIMAL(10, 2) NOT NULL, -- En UF
    tope_imponible_ips DECIMAL(10, 2) NOT NULL, -- En UF
    tope_imponible_cesantia DECIMAL(10, 2) NOT NULL, -- En UF
    sueldo_minimo INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_period (period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ficha de Empleados
CREATE TABLE IF NOT EXISTS wa_hr_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    rut VARCHAR(12) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(20),
    address VARCHAR(255),
    birth_date DATE,
    nationality VARCHAR(50) DEFAULT 'Chilena',
    civil_status ENUM('soltero', 'casado', 'divorciado', 'viudo') DEFAULT 'soltero',
    
    -- Datos Bancarios
    bank_name VARCHAR(100),
    account_type ENUM('corriente', 'vista', 'ahorro') DEFAULT 'vista',
    account_number VARCHAR(50),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_employee_rut (company_id, rut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contratos
CREATE TABLE IF NOT EXISTS wa_hr_contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    company_id INT NOT NULL,
    
    start_date DATE NOT NULL,
    end_date DATE, -- NULL si es indefinido
    type ENUM('indefinido', 'plazo_fijo', 'obra_faena') NOT NULL,
    position VARCHAR(100) NOT NULL,
    
    -- Remuneración
    base_salary INT NOT NULL, -- Sueldo Base Mensual
    colacion INT DEFAULT 0,
    movilizacion INT DEFAULT 0,
    has_gratification BOOLEAN DEFAULT 1, -- 25% con tope
    
    -- Previsión
    afp VARCHAR(50) NOT NULL, -- Nombre AFP
    isapre VARCHAR(50) NOT NULL, -- Nombre Isapre o Fonasa
    isapre_amount_type ENUM('porcentaje', 'uf', 'clp') DEFAULT 'porcentaje',
    isapre_value DECIMAL(10, 4) DEFAULT 7.0, -- 7% o X UF
    sis_covered_by_employer BOOLEAN DEFAULT 1,
    
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (employee_id) REFERENCES wa_hr_employees(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Liquidaciones de Sueldo (Payroll)
CREATE TABLE IF NOT EXISTS wa_hr_payrolls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    employee_id INT NOT NULL,
    contract_id INT NOT NULL,
    period DATE NOT NULL, -- YYYY-MM-01
    
    days_worked INT DEFAULT 30,
    
    -- Haberes Imponibles
    base_salary_amount INT NOT NULL,
    gratification_amount INT NOT NULL,
    overtime_amount INT DEFAULT 0,
    bonus_imponible INT DEFAULT 0,
    total_taxable INT NOT NULL, -- Total Imponible
    
    -- Haberes No Imponibles
    colacion_amount INT DEFAULT 0,
    movilizacion_amount INT DEFAULT 0,
    family_allowance INT DEFAULT 0, -- Asignación Familiar
    total_non_taxable INT NOT NULL,
    
    total_remuneration INT NOT NULL, -- Total Haberes
    
    -- Descuentos Legales
    afp_amount INT NOT NULL,
    isapre_amount INT NOT NULL,
    cesantia_amount INT NOT NULL, -- Aporte trabajador
    tax_amount INT NOT NULL, -- Impuesto Único
    
    total_legal_discounts INT NOT NULL,
    
    -- Otros Descuentos
    advance_payment INT DEFAULT 0, -- Anticipos
    other_discounts INT DEFAULT 0,
    
    total_discounts INT NOT NULL,
    
    net_pay INT NOT NULL, -- Sueldo Líquido
    employer_cost INT NOT NULL, -- Costo Empresa (SIS, Cesantía empleador, Mutual, etc)
    
    status ENUM('draft', 'approved', 'paid') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES wa_hr_employees(id),
    FOREIGN KEY (contract_id) REFERENCES wa_hr_contracts(id),
    UNIQUE KEY unique_payroll_period (contract_id, period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
