-- ============================================================
-- quoERP — Portal del Empleado (Autoservicio RRHH)
-- Migracion: 035_employee_portal.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Acceso al portal por empleado
CREATE TABLE IF NOT EXISTS employee_portal_access (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    employee_id     INT UNSIGNED    NOT NULL,
    email           VARCHAR(150)    NOT NULL,
    password_hash   VARCHAR(255)    DEFAULT NULL,
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    must_change_pwd TINYINT(1)      NOT NULL DEFAULT 1
        COMMENT '1 = debe cambiar la contrasena en el primer acceso',
    last_login_at   DATETIME        DEFAULT NULL,
    last_login_ip   VARCHAR(45)     DEFAULT NULL,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_company_email (company_id, email),
    UNIQUE KEY uk_employee (employee_id),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documentos subidos por el empleado desde el portal
CREATE TABLE IF NOT EXISTS employee_portal_documents (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    employee_id     INT UNSIGNED    NOT NULL,
    name            VARCHAR(200)    NOT NULL COMMENT 'Nombre descriptivo del documento',
    doc_type        ENUM(
        'cedula','titulo','certificado','contrato','otro'
    ) NOT NULL DEFAULT 'otro',
    file_path       VARCHAR(255)    NOT NULL,
    file_name       VARCHAR(255)    NOT NULL COMMENT 'Nombre original del archivo',
    file_size       INT UNSIGNED    DEFAULT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending'
        COMMENT 'Revisado por RRHH',
    reviewed_by     INT UNSIGNED    DEFAULT NULL,
    reviewed_at     DATETIME        DEFAULT NULL,
    review_notes    VARCHAR(300)    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_employee (employee_id),
    INDEX idx_company  (company_id),
    INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Solicitudes de actualizacion de datos (RRHH las aprueba)
CREATE TABLE IF NOT EXISTS employee_data_requests (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    employee_id     INT UNSIGNED    NOT NULL,
    field_name      VARCHAR(60)     NOT NULL COMMENT 'Campo a actualizar: phone, address, bank_account...',
    old_value       VARCHAR(500)    DEFAULT NULL,
    new_value       VARCHAR(500)    NOT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by     INT UNSIGNED    DEFAULT NULL,
    reviewed_at     DATETIME        DEFAULT NULL,
    review_notes    VARCHAR(300)    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_employee (employee_id),
    INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;