-- ============================================================
-- Club Palestino CRM v2.0 - MODULO CONTROL DE ACCESO
-- ============================================================

-- ── DISPOSITIVOS ZKTECO ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `acceso_dispositivos` (
    `id`            TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre`        VARCHAR(100)     NOT NULL,
    `tipo`          ENUM('torniquete','barrera_entrada','barrera_salida','barrera_doble','camara_lpr') NOT NULL,
    `ubicacion`     VARCHAR(150)     NOT NULL,
    `ip`            VARCHAR(15)      NOT NULL,
    `puerto`        SMALLINT UNSIGNED NOT NULL DEFAULT 4370,
    `serial`        VARCHAR(50)      NULL,
    `password`      VARCHAR(20)      NOT NULL DEFAULT '0',
    `protocolo`     ENUM('udp','adms','mqtt') NOT NULL DEFAULT 'udp',
    `mqtt_topic`    VARCHAR(100)     NULL,
    `activo`        TINYINT(1)       NOT NULL DEFAULT 1,
    `ultimo_ping`   TIMESTAMP        NULL,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_tipo_disp` (`tipo`,`activo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dispositivos reales del Club Palestino
INSERT INTO `acceso_dispositivos` (`nombre`,`tipo`,`ubicacion`,`ip`,`puerto`,`protocolo`) VALUES
('Torniquete 1','torniquete','Porteria principal - Carril 1','192.168.1.101',4370,'udp'),
('Torniquete 2','torniquete','Porteria principal - Carril 2','192.168.1.102',4370,'udp'),
('Torniquete 3','torniquete','Porteria principal - Carril 3','192.168.1.103',4370,'udp'),
('Torniquete 4','torniquete','Porteria principal - Carril 4','192.168.1.104',4370,'udp'),
('Barrera Estac. Socios Entrada','barrera_entrada','Estacionamiento principal - Entrada','192.168.1.110',4370,'udp'),
('Barrera Estac. Socios Salida','barrera_salida','Estacionamiento principal - Salida','192.168.1.111',4370,'udp'),
('Barrera Kennedy Lateral','barrera_doble','Acceso Kennedy lateral','192.168.1.112',4370,'udp'),
('Barrera Estac. Pagado','barrera_doble','Estacionamiento pagado interno','192.168.1.113',4370,'udp'),
('Camara LPR Principal','camara_lpr','Estacionamiento entrada','192.168.1.120',80,'adms'),
('Camara LPR Kennedy','camara_lpr','Kennedy lateral','192.168.1.121',80,'adms');

-- ── REGISTRO DE ACCESOS ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `acceso_log` (
    `id`            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `dispositivo_id` TINYINT UNSIGNED NOT NULL,
    `socio_id`      INT UNSIGNED     NULL,
    `invitado_id`   INT UNSIGNED     NULL,
    `proveedor_id`  INT UNSIGNED     NULL,
    `tipo_persona`  ENUM('socio','invitado','proveedor','empleado','guardia','desconocido') NOT NULL DEFAULT 'desconocido',
    `nombre`        VARCHAR(150)     NULL,
    `rut`           VARCHAR(12)      NULL,
    `patente`       VARCHAR(10)      NULL,
    `metodo`        ENUM('facial','qr','rut_manual','patente_lpr','guardia_manual') NOT NULL,
    `direccion`     ENUM('entrada','salida') NOT NULL DEFAULT 'entrada',
    `autorizado`    TINYINT(1)       NOT NULL DEFAULT 1,
    `motivo_rechazo` VARCHAR(200)    NULL,
    `foto_url`      VARCHAR(500)     NULL,
    `guardia_id`    INT UNSIGNED     NULL COMMENT 'Si fue apertura manual por guardia',
    `evento_id`     INT UNSIGNED     NULL COMMENT 'Si ingresa a evento especifico',
    `timestamp`     TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_socio_acc`    (`socio_id`,`timestamp`),
    KEY `idx_rut_acc`      (`rut`,`timestamp`),
    KEY `idx_patente_acc`  (`patente`,`timestamp`),
    KEY `idx_disp_acc`     (`dispositivo_id`,`timestamp`),
    KEY `idx_ts_acc`       (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

-- ── INVITADOS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `invitados` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `rut`           VARCHAR(12)      NULL,
    `pasaporte`     VARCHAR(20)      NULL,
    `nombre`        VARCHAR(150)     NOT NULL,
    `telefono`      VARCHAR(20)      NULL,
    `email`         VARCHAR(100)     NULL,
    `foto_url`      VARCHAR(500)     NULL,
    `total_visitas_contables` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Sin contar eventos',
    `alerta_admin`  TINYINT(1)       NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_rut_inv`      (`rut`),
    UNIQUE KEY `uk_pasaporte_inv` (`pasaporte`),
    KEY `idx_alerta_inv` (`alerta_admin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVITACIONES (pre-registro por socio) ────────────────────
CREATE TABLE IF NOT EXISTS `invitaciones` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`      INT UNSIGNED     NOT NULL,
    `invitado_id`   INT UNSIGNED     NULL COMMENT 'Si ya existe en sistema',
    `nombre_invitado` VARCHAR(150)   NOT NULL,
    `rut_invitado`  VARCHAR(12)      NULL,
    `pasaporte_invitado` VARCHAR(20) NULL,
    `tipo`          ENUM('dia','piscina','restaurant','cancha','evento_privado','salon','quincho','otro') NOT NULL DEFAULT 'dia',
    `es_evento`     TINYINT(1)       NOT NULL DEFAULT 0 COMMENT 'No contabiliza cuota anual',
    `evento_id`     INT UNSIGNED     NULL,
    `reserva_id`    INT UNSIGNED     NULL,
    `fecha`         DATE             NOT NULL,
    `hora_desde`    TIME             NULL,
    `hora_hasta`    TIME             NULL,
    `qr_token`      VARCHAR(64)      NOT NULL,
    `estado`        ENUM('pendiente','activa','usada','vencida','cancelada') NOT NULL DEFAULT 'activa',
    `usado_at`      TIMESTAMP        NULL,
    `notas`         TEXT             NULL,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_inv_qr` (`qr_token`),
    KEY `idx_socio_inv`    (`socio_id`,`fecha`),
    KEY `idx_invitado_inv` (`invitado_id`,`fecha`),
    KEY `idx_fecha_inv`    (`fecha`,`estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CUOTA ANUAL DE INVITACIONES POR SOCIO ────────────────────
CREATE TABLE IF NOT EXISTS `invitacion_cuotas` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`      INT UNSIGNED     NOT NULL,
    `invitado_id`   INT UNSIGNED     NOT NULL,
    `anio`          YEAR             NOT NULL,
    `usos`          TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `max_usos`      TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `updated_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_cuota` (`socio_id`,`invitado_id`,`anio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TRACKING VISITAS NO-SOCIOS ────────────────────────────────
CREATE TABLE IF NOT EXISTS `invitado_visitas_tracking` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `invitado_id`   INT UNSIGNED     NOT NULL,
    `socio_id`      INT UNSIGNED     NOT NULL,
    `invitacion_id` INT UNSIGNED     NOT NULL,
    `fecha`         DATE             NOT NULL,
    `tipo`          VARCHAR(50)      NOT NULL,
    `es_evento`     TINYINT(1)       NOT NULL DEFAULT 0,
    `contabiliza`   TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_inv_track` (`invitado_id`,`anio` ),
    KEY `idx_socio_track` (`socio_id`,`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fix columna anio en tracking
ALTER TABLE `invitado_visitas_tracking` ADD COLUMN IF NOT EXISTS `anio` YEAR GENERATED ALWAYS AS (YEAR(`fecha`)) STORED;

-- ── PROVEEDORES Y VISITAS DIRECTORIO ─────────────────────────
CREATE TABLE IF NOT EXISTS `acceso_proveedores` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `empresa`       VARCHAR(150)     NOT NULL,
    `rut_empresa`   VARCHAR(12)      NULL,
    `tipo`          ENUM('proveedor','visita_directorio','contratista','servicio','otro') NOT NULL DEFAULT 'proveedor',
    `contacto`      VARCHAR(150)     NULL,
    `telefono`      VARCHAR(20)      NULL,
    `email`         VARCHAR(100)     NULL,
    `activo`        TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── VISITAS PROGRAMADAS (proveedores/directorio) ──────────────
CREATE TABLE IF NOT EXISTS `acceso_visitas` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `proveedor_id`  INT UNSIGNED     NULL,
    `tipo`          ENUM('proveedor','visita_directorio','contratista','otro') NOT NULL DEFAULT 'proveedor',
    `empresa`       VARCHAR(150)     NOT NULL,
    `motivo`        VARCHAR(200)     NOT NULL,
    `fecha`         DATE             NOT NULL,
    `hora_desde`    TIME             NOT NULL,
    `hora_hasta`    TIME             NULL,
    `autorizador`   VARCHAR(150)     NULL COMMENT 'Quien autorizo la visita',
    `qr_token`      VARCHAR(64)      NOT NULL,
    `link_registro` VARCHAR(200)     NULL COMMENT 'Link enviado al proveedor para auto-registro',
    `estado`        ENUM('pendiente','confirmada','en_curso','completada','cancelada') NOT NULL DEFAULT 'pendiente',
    `notas`         TEXT             NULL,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_visita_qr` (`qr_token`),
    KEY `idx_fecha_visita` (`fecha`,`estado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PERSONAS EN VISITA (auto-registro por proveedor) ──────────
CREATE TABLE IF NOT EXISTS `acceso_visita_personas` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `visita_id`     INT UNSIGNED     NOT NULL,
    `nombre`        VARCHAR(150)     NOT NULL,
    `rut`           VARCHAR(12)      NULL,
    `pasaporte`     VARCHAR(20)      NULL,
    `patente`       VARCHAR(10)      NULL,
    `foto_url`      VARCHAR(500)     NULL,
    `ingreso_at`    TIMESTAMP        NULL,
    `salida_at`     TIMESTAMP        NULL,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_visita_pers` (`visita_id`),
    KEY `idx_rut_vpers`   (`rut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── QUIENES ESTAN EN EL CLUB AHORA ───────────────────────────
CREATE TABLE IF NOT EXISTS `acceso_presentes` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`      INT UNSIGNED     NULL,
    `invitado_id`   INT UNSIGNED     NULL,
    `proveedor_id`  INT UNSIGNED     NULL,
    `tipo_persona`  ENUM('socio','invitado','proveedor','empleado','desconocido') NOT NULL,
    `nombre`        VARCHAR(150)     NOT NULL,
    `rut`           VARCHAR(12)      NULL,
    `patente`       VARCHAR(10)      NULL,
    `espacio_id`    INT UNSIGNED     NULL COMMENT 'Donde esta dentro del club',
    `evento_id`     INT UNSIGNED     NULL,
    `entrada_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `acceso_log_id` BIGINT UNSIGNED  NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_rut_presente` (`rut`),
    KEY `idx_espacio_pres` (`espacio_id`),
    KEY `idx_tipo_pres`    (`tipo_persona`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FOTOS FACIALES SOCIOS ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `socio_fotos_facial` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`      INT UNSIGNED     NOT NULL,
    `foto_url`      VARCHAR(500)     NOT NULL,
    `descriptor`    LONGTEXT         NULL COMMENT 'JSON con face descriptor de face-api.js',
    `zkteco_uid`    INT UNSIGNED     NULL COMMENT 'UID en el dispositivo ZKTeco',
    `activa`        TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_socio_foto` (`socio_id`,`activa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ESTACIONAMIENTOS ESPECIALES ───────────────────────────────
CREATE TABLE IF NOT EXISTS `estacionamiento_especial` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `socio_id`      INT UNSIGNED     NOT NULL,
    `tipo`          ENUM('director','administrativo','pagado') NOT NULL,
    `numero_cupo`   VARCHAR(10)      NULL,
    `patentes`      JSON             NULL COMMENT 'Array de patentes autorizadas',
    `desde`         DATE             NOT NULL,
    `hasta`         DATE             NULL,
    `activo`        TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_socio_estac` (`socio_id`,`activo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agregar campo es_director a socios
ALTER TABLE `socios` ADD COLUMN IF NOT EXISTS `es_director` TINYINT(1) NOT NULL DEFAULT 0 AFTER `estado`;
ALTER TABLE `socios` ADD COLUMN IF NOT EXISTS `cupos_estacionamiento` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `es_director`;

-- Parametros del modulo
INSERT INTO `config_sistema` (`clave`,`valor`,`descripcion`) VALUES
('acceso_max_invitaciones_anio','5','Max invitaciones anuales por persona no-socia'),
('acceso_max_invitaciones_socio','5','Max invitaciones por socio a la misma persona'),
('acceso_alerta_invitado_visitas','5','Alertar admin cuando no-socio supera este numero de visitas'),
('acceso_facial_threshold','0.6','Umbral confianza reconocimiento facial (0-1)'),
('acceso_lpr_activo','1','LPR activo para lectura automatica de patentes'),
('zkteco_ip_1','192.168.1.101','IP torniquete 1'),
('zkteco_ip_2','192.168.1.102','IP torniquete 2'),
('zkteco_ip_3','192.168.1.103','IP torniquete 3'),
('zkteco_ip_4','192.168.1.104','IP torniquete 4'),
('barrera_estac_ip','192.168.1.110','IP barrera estacionamiento'),
('barrera_kennedy_ip','192.168.1.112','IP barrera Kennedy'),
('mqtt_broker','condor3033','Broker MQTT para barreras'),
('mqtt_topic_barrera_estac','club/barrera/estac/open','Topic MQTT barrera estac'),
('mqtt_topic_barrera_kennedy','club/barrera/kennedy/open','Topic MQTT barrera Kennedy')
ON DUPLICATE KEY UPDATE descripcion=VALUES(descripcion);