-- ============================================================
-- WAW — Tabla audit_log para auditoría de acciones de usuarios
-- Ejecutar en phpMyAdmin o MySQL CLI sobre la BD `waw`
-- ============================================================

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT             DEFAULT NULL COMMENT 'NULL = acción anónima (login fallido, etc.)',
  `action`     VARCHAR(100)    NOT NULL     COMMENT 'LOGIN_OK, LOGIN_FAILED, LOGOUT, EDIT_PROFILE, etc.',
  `detail`     VARCHAR(500)    DEFAULT ''   COMMENT 'Información contextual no sensible',
  `ip`         VARCHAR(45)     DEFAULT ''   COMMENT 'IPv4 o IPv6 del cliente',
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_user`    (`user_id`),
  KEY `idx_audit_action`  (`action`),
  KEY `idx_audit_created` (`created_at`),
  CONSTRAINT `fk_audit_user`
    FOREIGN KEY (`user_id`) REFERENCES `usuarios` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Auditoría de acciones de usuarios en la plataforma WAW';

-- Agregar campo `activo` a usuarios si no existe
ALTER TABLE `usuarios`
  ADD COLUMN IF NOT EXISTS `activo` TINYINT(1) NOT NULL DEFAULT 1
    COMMENT '1 = cuenta activa, 0 = suspendida' AFTER `terminos`;

ALTER TABLE `usuarios`
  ADD COLUMN IF NOT EXISTS `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    COMMENT 'Fecha de registro' AFTER `activo`;

ALTER TABLE `usuarios`
  ADD COLUMN IF NOT EXISTS `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

ALTER TABLE `usuarios`
  ADD COLUMN IF NOT EXISTS `last_login_at` DATETIME DEFAULT NULL
    COMMENT 'Último login exitoso' AFTER `updated_at`;
