<?php
/**
 * @intranet-file common/incidencias_bridge.php
 * @intranet-file-version 2026.03.13.1
 * @intranet-file-date 2026-03-13
 * @intranet-file-owner CSIF Intranet
 */
declare(strict_types=1);

/**
 * Puente para crear incidencias desde otras apps sin depender de correo.
 *
 * Ejemplo de uso:
 *   require_once __DIR__ . '/../../common/incidencias_bridge.php';
 *   $pdo = db();
 *   inc_bridge_ensure_schema($pdo);
 *   $res = inc_bridge_create_incident($pdo, $data, $_FILES['imagen'] ?? null);
 */

require_once __DIR__ . '/db.php';

function inc_bridge_root_dir(): string
{
    return dirname(__DIR__);
}

function inc_bridge_upload_dir(): string
{
    return inc_bridge_root_dir() . '/apps/incidencias/uploads';
}

function inc_bridge_upload_policy(): array
{
    return [
        'pdf' => ['application/pdf'],
        'png' => ['image/png'],
        'jpg' => ['image/jpeg'],
        'jpeg' => ['image/jpeg'],
        'gif' => ['image/gif'],
        'webp' => ['image/webp'],
        'txt' => ['text/plain'],
        'csv' => ['text/plain', 'text/csv', 'application/csv', 'application/vnd.ms-excel'],
        'zip' => ['application/zip', 'application/x-zip-compressed'],
        'doc' => ['application/msword'],
        'docx' => ['application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'application/zip'],
        'xls' => ['application/vnd.ms-excel'],
        'xlsx' => ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/zip'],
        'odt' => ['application/vnd.oasis.opendocument.text', 'application/zip'],
        'ods' => ['application/vnd.oasis.opendocument.spreadsheet', 'application/zip'],
    ];
}

function inc_bridge_validate_upload(string $tmpPath, string $origName, int $size): ?array
{
    $maxSize = 10 * 1024 * 1024;
    if ($tmpPath === '' || $size <= 0 || $size > $maxSize || !is_uploaded_file($tmpPath)) {
        return null;
    }

    $ext = strtolower((string)pathinfo($origName, PATHINFO_EXTENSION));
    $policy = inc_bridge_upload_policy();
    if ($ext === '' || !isset($policy[$ext])) {
        return null;
    }

    $detectedMime = '';
    if (class_exists('finfo')) {
        $finfo = new finfo(FILEINFO_MIME_TYPE);
        $detectedMime = (string)($finfo->file($tmpPath) ?: '');
    } elseif (function_exists('mime_content_type')) {
        $detectedMime = (string)(mime_content_type($tmpPath) ?: '');
    }

    if ($detectedMime === '' || !in_array($detectedMime, $policy[$ext], true)) {
        return null;
    }

    return [
        'ext' => '.' . preg_replace('/[^a-zA-Z0-9]/', '', $ext),
        'mime' => $detectedMime,
    ];
}

/**
 * Crea el esquema mínimo de la app Incidencias si no existe.
 *
 * Nota: si el usuario de BD no tiene permisos CREATE, esto lanzará una excepción.
 */
function inc_bridge_ensure_schema(PDO $pdo): void
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS inc_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        active TINYINT(1) NOT NULL DEFAULT 1,
        sort_order INT NOT NULL DEFAULT 100,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inc_incidents (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(30) NOT NULL,
        department VARCHAR(100) NOT NULL,
        category_id INT NULL,
        title VARCHAR(200) NOT NULL,
        description MEDIUMTEXT NOT NULL,
        status ENUM('abierta','en_progreso','resuelta','cerrada') NOT NULL DEFAULT 'abierta',
        priority ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media',
        created_by VARCHAR(64) NOT NULL,
        created_email VARCHAR(255) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NULL,
        updated_by VARCHAR(64) NULL,
        assigned_to VARCHAR(64) NULL,
        last_activity_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_dept_status (department, status),
        INDEX idx_created_by (created_by),
        INDEX idx_last_activity (last_activity_at),
        UNIQUE KEY uniq_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inc_attachments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        incident_id INT NOT NULL,
        uploader VARCHAR(64) NOT NULL,
        original_name VARCHAR(255) NOT NULL,
        stored_name VARCHAR(255) NOT NULL,
        mime_type VARCHAR(120) NULL,
        size_bytes BIGINT NOT NULL,
        sha256 CHAR(64) NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_incident (incident_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inc_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        incident_id INT NOT NULL,
        actor VARCHAR(64) NOT NULL,
        action VARCHAR(50) NOT NULL,
        from_value VARCHAR(255) NULL,
        to_value VARCHAR(255) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_incident (incident_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // FKs: si existen ya, ignoramos
    try {
        $pdo->exec("ALTER TABLE inc_incidents
            ADD CONSTRAINT fk_inc_category
            FOREIGN KEY (category_id) REFERENCES inc_categories(id) ON DELETE SET NULL");
    } catch (Throwable $e) {
        // noop
    }

    try {
        $pdo->exec("ALTER TABLE inc_attachments
            ADD CONSTRAINT fk_attach_incident
            FOREIGN KEY (incident_id) REFERENCES inc_incidents(id) ON DELETE CASCADE");
    } catch (Throwable $e) {
        // noop
    }

    try {
        $pdo->exec("ALTER TABLE inc_history
            ADD CONSTRAINT fk_hist_incident
            FOREIGN KEY (incident_id) REFERENCES inc_incidents(id) ON DELETE CASCADE");
    } catch (Throwable $e) {
        // noop
    }

    // Seed categorías base
    $pdo->exec("INSERT IGNORE INTO inc_categories (name, active, sort_order) VALUES
        ('Hardware', 1, 10),
        ('Software', 1, 20),
        ('Impresoras', 1, 30),
        ('Red', 1, 40),
        ('Cuenta/Permisos', 1, 50),
        ('Telefonía', 1, 60),
        ('Otro', 1, 999)");
}

function inc_bridge_get_or_create_category(PDO $pdo, string $name, int $sortOrder = 70): int
{
    $name = trim($name);
    if ($name === '') return 0;

    $st = $pdo->prepare("INSERT IGNORE INTO inc_categories (name, active, sort_order) VALUES (:n, 1, :s)");
    $st->execute(['n' => $name, 's' => $sortOrder]);

    $st2 = $pdo->prepare("SELECT id FROM inc_categories WHERE name = :n LIMIT 1");
    $st2->execute(['n' => $name]);
    return (int)($st2->fetchColumn() ?: 0);
}

function inc_bridge_log_history(PDO $pdo, int $incidentId, string $actor, string $action, ?string $from, ?string $to): void
{
    $st = $pdo->prepare("INSERT INTO inc_history (incident_id, actor, action, from_value, to_value) VALUES (:id, :actor, :action, :fromv, :tov)");
    $st->execute([
        'id' => $incidentId,
        'actor' => $actor,
        'action' => $action,
        'fromv' => $from,
        'tov' => $to,
    ]);
}

/**
 * Crea una incidencia y (opcionalmente) adjunta un único fichero.
 *
 * @param array $data
 *   - department (string)
 *   - category_id (int|null)
 *   - title (string)
 *   - description (string)
 *   - priority (baja|media|alta|critica) opcional
 *   - created_by (string)
 * @param array|null $file Estructura tipo $_FILES['campo'] (un solo archivo)
 * @return array{incident_id:int, code:string}
 */
function inc_bridge_create_incident(PDO $pdo, array $data, ?array $file = null): array
{
    $dept = trim((string)($data['department'] ?? ''));
    $title = trim((string)($data['title'] ?? ''));
    $desc = trim((string)($data['description'] ?? ''));
    $prio = trim((string)($data['priority'] ?? 'media'));
    $createdBy = trim((string)($data['created_by'] ?? ''));
    $categoryId = isset($data['category_id']) ? (int)$data['category_id'] : 0;

    if ($dept === '') throw new RuntimeException('Departamento vacío.');
    if ($createdBy === '') throw new RuntimeException('Usuario creador vacío.');
    if ($title === '' || mb_strlen($title) < 5) throw new RuntimeException('Título inválido.');
    if ($desc === '' || mb_strlen($desc) < 10) throw new RuntimeException('Descripción inválida.');
    if (!in_array($prio, ['baja','media','alta','critica'], true)) $prio = 'media';

    $code = 'INC-' . date('Ymd') . '-' . strtoupper(bin2hex(random_bytes(2)));

    $pdo->beginTransaction();
    try {
        $st = $pdo->prepare("INSERT INTO inc_incidents (code, department, category_id, title, description, status, priority, created_by, created_email)
            VALUES (:code, :dept, :cat, :title, :desc, 'abierta', :prio, :me, :email)");
        $st->execute([
            'code' => $code,
            'dept' => $dept,
            'cat' => ($categoryId > 0) ? $categoryId : null,
            'title' => $title,
            'desc' => $desc,
            'prio' => $prio,
            'me' => $createdBy,
            'email' => ($data['created_email'] ?? null),
        ]);

        $incidentId = (int)$pdo->lastInsertId();
        inc_bridge_log_history($pdo, $incidentId, $createdBy, 'create', null, null);

        // Adjuntar un único fichero (opcional)
        if ($file && isset($file['error']) && (int)$file['error'] === UPLOAD_ERR_OK) {
            $tmpPath = (string)($file['tmp_name'] ?? '');
            $origName = (string)($file['name'] ?? 'adjunto');
            $size = (int)($file['size'] ?? 0);

            $uploadMeta = inc_bridge_validate_upload($tmpPath, $origName, $size);
            if ($uploadMeta !== null) {
                $dir = inc_bridge_upload_dir();
                if (!is_dir($dir)) {
                    if (!mkdir($dir, 0770, true) && !is_dir($dir)) {
                        throw new RuntimeException('No se pudo crear el directorio de adjuntos.');
                    }
                }

                $storedName = $code . '-' . bin2hex(random_bytes(8)) . $uploadMeta['ext'];
                $dest = $dir . '/' . $storedName;

                if (!move_uploaded_file($tmpPath, $dest)) {
                    throw new RuntimeException('No se pudo guardar el adjunto.');
                }

                $sha256 = hash_file('sha256', $dest) ?: '';
                if ($sha256 === '') {
                    @unlink($dest);
                    throw new RuntimeException('No se pudo calcular el hash del adjunto.');
                }

                $stA = $pdo->prepare("INSERT INTO inc_attachments (incident_id, uploader, original_name, stored_name, mime_type, size_bytes, sha256)
                    VALUES (:iid, :u, :o, :s, :m, :b, :h)");
                $stA->execute([
                    'iid' => $incidentId,
                    'u' => $createdBy,
                    'o' => $origName,
                    's' => $storedName,
                    'm' => $uploadMeta['mime'],
                    'b' => $size,
                    'h' => $sha256,
                ]);

                inc_bridge_log_history($pdo, $incidentId, $createdBy, 'attachments_add', null, '1');
            } elseif ($tmpPath !== '' || $size > 0) {
                throw new RuntimeException('El adjunto no cumple la política de tipos permitidos.');
            }
        }

        $pdo->commit();
        return ['incident_id' => $incidentId, 'code' => $code];
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}
