-- Mandatos GE - Gestión Laboral y Novedades de Personal
-- Versión corregida compatible con MySQL/MariaDB en cPanel.
-- La quincena se guarda como campo normal y se calcula desde PHP para evitar errores con GENERATED ALWAYS en MariaDB/cPanel.

CREATE TABLE IF NOT EXISTS companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(180) NOT NULL,
    nit VARCHAR(50) NULL,
    ciudad VARCHAR(120) NULL,
    contacto VARCHAR(150) NULL,
    correo_contacto VARCHAR(150) NULL,
    telefono_contacto VARCHAR(80) NULL,
    estado ENUM('Activa','Inactiva') NOT NULL DEFAULT 'Activa',
    logo_nombre VARCHAR(255) NULL,
    logo_ruta VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_companies_nit (nit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    nombre VARCHAR(150) NOT NULL,
    email VARCHAR(180) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('super_admin','admin_empresa','consulta') NOT NULL DEFAULT 'consulta',
    estado ENUM('Activo','Inactivo') NOT NULL DEFAULT 'Activo',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_users_email (email),
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS collaborators (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(30) DEFAULT 'CC',
    numero_documento VARCHAR(60) NOT NULL,
    primer_nombre VARCHAR(100) NULL,
    segundo_nombre VARCHAR(100) NULL,
    primer_apellido VARCHAR(100) NULL,
    segundo_apellido VARCHAR(100) NULL,
    nombre_completo VARCHAR(250) NOT NULL,
    fecha_nacimiento DATE NULL,
    sexo VARCHAR(30) NULL,
    correo VARCHAR(180) NULL,
    telefono VARCHAR(80) NULL,
    direccion VARCHAR(250) NULL,
    ciudad VARCHAR(120) NULL,
    area VARCHAR(120) NULL,
    cargo VARCHAR(150) NULL,
    tipo_vinculacion VARCHAR(100) NULL,
    fecha_ingreso DATE NULL,
    fecha_retiro DATE NULL,
    estado ENUM('Activo','Retirado','Suspendido','En selección','Inactivo') NOT NULL DEFAULT 'Activo',
    salario_modalidad VARCHAR(40) NULL,
    eps VARCHAR(150) NULL,
    afp VARCHAR(150) NULL,
    arl VARCHAR(150) NULL,
    caja_compensacion VARCHAR(150) NULL,
    fondo_cesantias VARCHAR(150) NULL,
    salario_base DECIMAL(14,2) NULL,
    observaciones TEXT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_collaborator_doc_company (company_id, numero_documento),
    KEY idx_collaborators_company_estado (company_id, estado),
    KEY idx_collaborators_tipo_documento (tipo_documento),
    KEY idx_collaborators_salario_modalidad (salario_modalidad),
    KEY idx_collaborators_nombre (nombre_completo),
    CONSTRAINT fk_collaborators_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_collaborators_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_collaborators_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS novelties (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NULL,
    documento_text VARCHAR(80) NULL,
    colaborador_text VARCHAR(255) NULL,
    fecha_registro DATE NOT NULL,
    fecha_inicio DATE NULL,
    fecha_fin DATE NULL,
    quincena VARCHAR(20) NULL,
    categoria VARCHAR(100) NOT NULL,
    tipo_novedad VARCHAR(140) NOT NULL,
    dias DECIMAL(8,2) NULL,
    horas DECIMAL(8,2) NULL,
    valor DECIMAL(14,2) NULL,
    estado_novedad ENUM('Registrada','En revisión','Cerrada','Anulada') NOT NULL DEFAULT 'Registrada',
    impacto_legal TEXT NULL,
    descripcion TEXT NULL,
    soporte_nombre VARCHAR(255) NULL,
    soporte_ruta VARCHAR(255) NULL,
    origen VARCHAR(100) DEFAULT 'Registro manual',
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_novelties_company_dates (company_id, fecha_inicio, fecha_fin),
    KEY idx_novelties_tipo (tipo_novedad),
    KEY idx_novelties_categoria (categoria),
    KEY idx_novelties_quincena (quincena),
    CONSTRAINT fk_novelties_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_novelties_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE SET NULL,
    CONSTRAINT fk_novelties_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_novelties_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NULL,
    novelty_id INT UNSIGNED NULL,
    tipo_documento VARCHAR(120) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    size_bytes INT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_documents_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_documents_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE SET NULL,
    CONSTRAINT fk_documents_novelty FOREIGN KEY (novelty_id) REFERENCES novelties(id) ON DELETE SET NULL,
    CONSTRAINT fk_documents_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    company_id INT UNSIGNED NULL,
    accion VARCHAR(120) NOT NULL,
    tabla_afectada VARCHAR(120) NULL,
    registro_id VARCHAR(80) NULL,
    detalle TEXT NULL,
    ip VARCHAR(80) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_company_date (company_id, created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO companies (nombre, nit, ciudad, contacto, correo_contacto, estado)
SELECT 'Vergel Plántulas y Vivero S.A.S.', NULL, 'El Rosal, Cundinamarca', NULL, NULL, 'Activa'
WHERE NOT EXISTS (SELECT 1 FROM companies WHERE nombre = 'Vergel Plántulas y Vivero S.A.S.');
