-- =====================================================================
--  SORC CONTRACTS  |  Esquema de base de datos
--  MySQL 8+  |  InnoDB  |  utf8mb4_unicode_ci
--  Legión IA +51960685684
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
--  ORGANIZACIÓN
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS empresas (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  razon_social  VARCHAR(255) NOT NULL,
  rfc           VARCHAR(13)  NULL,
  domicilio     VARCHAR(500) NULL,
  logo_path     VARCHAR(500) NULL,
  activo        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_rfc (rfc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unidades (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  empresa_id  BIGINT UNSIGNED NOT NULL,
  nombre      VARCHAR(200) NOT NULL,
  direccion   VARCHAR(500) NULL,
  activo      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_emp (empresa_id),
  CONSTRAINT fk_uni_emp FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  SEGURIDAD / ACCESO
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave       VARCHAR(40)  NOT NULL,
  nombre      VARCHAR(100) NOT NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rol_clave (clave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permisos (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave       VARCHAR(80)  NOT NULL,
  descripcion VARCHAR(200) NULL,
  UNIQUE KEY uq_perm_clave (clave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rol_permisos (
  rol_id     BIGINT UNSIGNED NOT NULL,
  permiso_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (rol_id, permiso_id),
  CONSTRAINT fk_rp_rol  FOREIGN KEY (rol_id)     REFERENCES roles(id)    ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permiso_id) REFERENCES permisos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS usuarios (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre        VARCHAR(150) NOT NULL,
  email         VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  rol_id        BIGINT UNSIGNED NOT NULL,
  empresa_id    BIGINT UNSIGNED NULL,
  activo        TINYINT(1)   NOT NULL DEFAULT 1,
  last_login    DATETIME     NULL,
  created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_email (email),
  KEY idx_rol (rol_id),
  CONSTRAINT fk_usr_rol FOREIGN KEY (rol_id)     REFERENCES roles(id),
  CONSTRAINT fk_usr_emp FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  EMPLEADOS (espejo local de SORC, nunca recaptura)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS empleados (
  id                          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sorc_id                     VARCHAR(64)  NOT NULL,
  empresa_id                  BIGINT UNSIGNED NOT NULL,
  unidad_id                   BIGINT UNSIGNED NULL,
  nombre_completo             VARCHAR(255) NOT NULL,
  curp                        CHAR(18)     NULL,
  rfc                         VARCHAR(13)  NULL,
  nss                         VARCHAR(11)  NULL,
  fecha_nacimiento            DATE         NULL,
  sexo                        ENUM('M','F','X') NULL,
  estado_civil                VARCHAR(30)  NULL,
  domicilio                   VARCHAR(500) NULL,
  puesto                      VARCHAR(150) NULL,
  salario                     DECIMAL(12,2) NULL,
  jornada                     VARCHAR(100) NULL,
  fecha_ingreso               DATE         NULL,
  supervisor_id               BIGINT UNSIGNED NULL,
  foto_path                   VARCHAR(500) NULL,
  ine_frontal_path            VARCHAR(500) NULL,
  ine_posterior_path          VARCHAR(500) NULL,
  comprobante_domicilio_path  VARCHAR(500) NULL,
  sync_status                 ENUM('pendiente','sincronizado','error') NOT NULL DEFAULT 'pendiente',
  last_sync                   DATETIME     NULL,
  created_at                  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at                  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sorc (sorc_id),
  KEY idx_curp (curp),
  KEY idx_empresa (empresa_id),
  KEY idx_unidad (unidad_id),
  CONSTRAINT fk_emp_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
  CONSTRAINT fk_emp_unidad  FOREIGN KEY (unidad_id)  REFERENCES unidades(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  MOTOR DE CONTRATOS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plantillas (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo           ENUM('prueba_30','definitivo','temporal','convenio',
                      'carta_responsiva','aviso_privacidad') NOT NULL,
  nombre         VARCHAR(150) NOT NULL,
  contenido_html MEDIUMTEXT   NOT NULL,
  variables_json JSON         NULL,
  version        INT          NOT NULL DEFAULT 1,
  activo         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contratos (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid             CHAR(36)     NOT NULL,
  folio            VARCHAR(40)  NOT NULL,
  empleado_id      BIGINT UNSIGNED NOT NULL,
  plantilla_id     BIGINT UNSIGNED NOT NULL,
  tipo             ENUM('prueba_30','definitivo','temporal','convenio',
                       'carta_responsiva','aviso_privacidad') NOT NULL DEFAULT 'prueba_30',
  estatus          ENUM('generado','enviado','abierto','firmado',
                       'vencido','cancelado') NOT NULL DEFAULT 'generado',
  fecha_ingreso    DATE         NOT NULL,
  fecha_fin_prueba DATE         NOT NULL,
  pdf_path         VARCHAR(500) NULL,
  hash_pdf         CHAR(64)     NULL,
  created_by       BIGINT UNSIGNED NOT NULL,
  created_at       DATETIME     DEFAULT CURRENT_TIMESTAMP,
  sent_at          DATETIME     NULL,
  opened_at        DATETIME     NULL,
  signed_at        DATETIME     NULL,
  expires_at       DATETIME     NULL,
  UNIQUE KEY uq_uuid (uuid),
  UNIQUE KEY uq_folio (folio),
  KEY idx_estatus (estatus),
  KEY idx_finprueba (fecha_fin_prueba),
  KEY idx_empleado (empleado_id),
  CONSTRAINT fk_con_emp FOREIGN KEY (empleado_id)  REFERENCES empleados(id),
  CONSTRAINT fk_con_plt FOREIGN KEY (plantilla_id) REFERENCES plantillas(id),
  CONSTRAINT fk_con_usr FOREIGN KEY (created_by)   REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contrato_versiones (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id   BIGINT UNSIGNED NOT NULL,
  version       INT          NOT NULL,
  pdf_path      VARCHAR(500) NULL,
  hash          CHAR(64)     NULL,
  snapshot_json JSON         NOT NULL,   -- congela datos del empleado y plantilla
  created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY idx_con (contrato_id),
  CONSTRAINT fk_cv_con FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  FIRMA Y EVIDENCIA
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS firmas (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id     BIGINT UNSIGNED NOT NULL,
  firma_png_path  VARCHAR(500) NULL,
  coordenadas_json JSON        NULL,
  velocidad       DECIMAL(10,4) NULL,
  tiempo_firma_ms INT          NULL,
  num_trazos      INT          NULL,
  hash_firma      CHAR(64)     NULL,
  created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY idx_con (contrato_id),
  CONSTRAINT fk_fir_con FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS evidencias (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id       BIGINT UNSIGNED NOT NULL,
  selfie_path       VARCHAR(500) NULL,
  frames_json       JSON         NULL,
  liveness_result   ENUM('aprobado','rechazado','no_aplicado') NOT NULL DEFAULT 'no_aplicado',
  lat               DECIMAL(10,7) NULL,
  lng               DECIMAL(10,7) NULL,
  precision_gps     DECIMAL(8,2) NULL,
  ip_publica        VARCHAR(45)  NULL,
  navegador         VARCHAR(255) NULL,
  sistema_operativo VARCHAR(120) NULL,
  resolucion        VARCHAR(20)  NULL,
  user_agent        TEXT         NULL,
  hash_selfie       CHAR(64)     NULL,
  capturado_en      DATETIME     NOT NULL,
  KEY idx_con (contrato_id),
  CONSTRAINT fk_ev_con FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enlaces_firma (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  token_uuid  CHAR(36)     NOT NULL,
  token_hash  CHAR(64)     NOT NULL,   -- hash del token; el claro va en la URL
  used        TINYINT(1)   NOT NULL DEFAULT 0,
  revoked     TINYINT(1)   NOT NULL DEFAULT 0,
  expires_at  DATETIME     NOT NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token (token_uuid),
  KEY idx_con (contrato_id),
  CONSTRAINT fk_enl_con FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  ALERTAS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alertas (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id    BIGINT UNSIGNED NOT NULL,
  tipo           ENUM('24h','72h','7d','critica') NOT NULL,
  canal          ENUM('email','whatsapp') NOT NULL,
  destinatarios_json JSON     NULL,
  estatus        ENUM('programada','enviada','error') NOT NULL DEFAULT 'programada',
  scheduled_at   DATETIME     NULL,
  sent_at        DATETIME     NULL,
  created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY idx_con (contrato_id),
  KEY idx_estatus (estatus),
  CONSTRAINT fk_al_con FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  AUDITORÍA (nada ocurre sin registro)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  usuario_id   BIGINT UNSIGNED NULL,
  accion       VARCHAR(50)  NOT NULL,
  entidad      VARCHAR(50)  NULL,
  entidad_id   BIGINT UNSIGNED NULL,
  ip           VARCHAR(45)  NULL,
  dispositivo  VARCHAR(120) NULL,
  navegador    VARCHAR(120) NULL,
  sistema_op   VARCHAR(120) NULL,
  detalle_json JSON         NULL,
  created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP,
  KEY idx_usuario (usuario_id),
  KEY idx_accion (accion),
  KEY idx_fecha (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  INFRAESTRUCTURA: rate limiting
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rl_key       VARCHAR(190) NOT NULL,    -- ej. login:203.0.113.5
  hits         INT          NOT NULL DEFAULT 0,
  window_start DATETIME     NOT NULL,
  UNIQUE KEY uq_key (rl_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;