- Renombrado «DC3» a «API Peñasquito» (URL base del documento) en UI/docs; rutas y claves de config conservadas por compatibilidad. - Persistencia de ticket_id en nueva tabla `penasquito_tickets`: POST /search/ guarda el ticket y los siguientes requests retoman GET /result/<ticket_id>/ sin reencolar. - Polling acotado por request HTTP (~6 s) con `PenasquitoPendingException` y caja «reintentar» en lookup; ticket queda válido 1 h. - `users_cache.company` nuevo (con migración idempotente); persistResult guarda empresa + competencias en transacción y aplica source distinto. - Mensajes HTTP específicos (400/401/403/404/408/429/5xx) y `/` final forzado en rutas (DRF), FOLLOWLOCATION habilitado. - Vista de perfil muestra «Empresa» y aviso cuando no hay competencias. - Auth::login evita doble fetch cuando ya tenemos el `result` del API. - i18n ES/EN actualizados; lookup_pending_*; limpieza de archivos lang. Co-authored-by: Cursor <cursoragent@cursor.com>
145 lines
5.3 KiB
SQL
145 lines
5.3 KiB
SQL
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
CREATE DATABASE IF NOT EXISTS certimine CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE certimine;
|
|
|
|
DROP TABLE IF EXISTS audit_log;
|
|
DROP TABLE IF EXISTS sync_event;
|
|
DROP TABLE IF EXISTS course_requirement;
|
|
DROP TABLE IF EXISTS courses;
|
|
DROP TABLE IF EXISTS certificates;
|
|
DROP TABLE IF EXISTS contractor_authorizations;
|
|
DROP TABLE IF EXISTS frm_nom_map;
|
|
DROP TABLE IF EXISTS public_documents;
|
|
DROP TABLE IF EXISTS penasquito_tickets;
|
|
DROP TABLE IF EXISTS users_cache;
|
|
DROP TABLE IF EXISTS nom_competency;
|
|
DROP TABLE IF EXISTS frm_standard;
|
|
|
|
CREATE TABLE frm_standard (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(64) NOT NULL UNIQUE,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_es VARCHAR(255) NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
doc_url VARCHAR(512) NULL,
|
|
description_en TEXT NULL,
|
|
description_es TEXT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE nom_competency (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(32) NOT NULL UNIQUE,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_es VARCHAR(255) NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 0
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE frm_nom_map (
|
|
frm_id INT UNSIGNED NOT NULL,
|
|
nom_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (frm_id, nom_id),
|
|
CONSTRAINT fk_frm_nom_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_frm_nom_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE public_documents (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
title_en VARCHAR(255) NOT NULL,
|
|
title_es VARCHAR(255) NOT NULL,
|
|
url VARCHAR(512) NOT NULL,
|
|
frm_id INT UNSIGNED NULL,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
CONSTRAINT fk_pubdoc_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE users_cache (
|
|
mobiliseme_id VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
employee_number VARCHAR(64) NULL,
|
|
display_name VARCHAR(255) NOT NULL,
|
|
company VARCHAR(255) NULL,
|
|
person_type ENUM('employee','contractor') NOT NULL DEFAULT 'employee',
|
|
green_license_flag TINYINT(1) NOT NULL DEFAULT 0,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
KEY idx_employee_number (employee_number)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Tickets pendientes del API Peñasquito (POST /search/ → GET /result/<ticket_id>/).
|
|
-- Permite reanudar el polling entre requests HTTP sin tener que reencolar.
|
|
CREATE TABLE penasquito_tickets (
|
|
passport VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
ticket_id VARCHAR(128) NOT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'pending',
|
|
posicion INT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_polled_at DATETIME NULL,
|
|
last_error VARCHAR(255) NULL,
|
|
KEY idx_pt_status (status),
|
|
KEY idx_pt_ticket (ticket_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE certificates (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
mobiliseme_id VARCHAR(64) NOT NULL,
|
|
nom_id INT UNSIGNED NULL,
|
|
frm_id INT UNSIGNED NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
issued_at DATE NULL,
|
|
expires_at DATE NULL,
|
|
status ENUM('valid','expiring','expired','pending') NOT NULL DEFAULT 'pending',
|
|
external_ref VARCHAR(128) NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
KEY idx_person (mobiliseme_id),
|
|
UNIQUE KEY uq_cert_ext (mobiliseme_id, external_ref),
|
|
CONSTRAINT fk_cert_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE SET NULL,
|
|
CONSTRAINT fk_cert_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE contractor_authorizations (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
mobiliseme_id VARCHAR(64) NOT NULL,
|
|
frm_id INT UNSIGNED NOT NULL,
|
|
valid_until DATE NULL,
|
|
source VARCHAR(32) NOT NULL DEFAULT 'sync',
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_contractor_frm (mobiliseme_id, frm_id),
|
|
CONSTRAINT fk_auth_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE courses (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
title_en VARCHAR(255) NOT NULL,
|
|
title_es VARCHAR(255) NOT NULL,
|
|
url VARCHAR(512) NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 0
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE course_requirement (
|
|
course_id INT UNSIGNED NOT NULL,
|
|
nom_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (course_id, nom_id),
|
|
CONSTRAINT fk_cr_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_cr_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE sync_event (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
external_id VARCHAR(128) NOT NULL,
|
|
event_type VARCHAR(64) NOT NULL,
|
|
payload_hash CHAR(64) NOT NULL,
|
|
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
processed_at DATETIME NULL,
|
|
UNIQUE KEY uq_ext (external_id, event_type)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE audit_log (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
action VARCHAR(64) NOT NULL,
|
|
subject_type VARCHAR(32) NOT NULL,
|
|
subject_id VARCHAR(128) NOT NULL,
|
|
ip VARCHAR(45) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|