-- ============================================================
--  MASSOLAGUARD SETUP SQL
--  Ejecutar en: massolag_massolaguard
--  Usuario: massolag_amassola
-- ============================================================

USE `massolag_massolaguard`;

-- --------------------------------------------------------
--  Tabla: api_keys
--  Keys para que programador.php acceda al router
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_keys` (
  `id`          int UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_name`    varchar(100) NOT NULL DEFAULT 'Programador Principal',
  `key_value`   varchar(100) NOT NULL,
  `permissions` varchar(255) DEFAULT 'all',
  `activo`      tinyint(1) DEFAULT 1,
  `last_used`   timestamp NULL DEFAULT NULL,
  `created_at`  timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_value` (`key_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Key inicial — CAMBIÁ este valor por uno tuyo o usa el generado en router.php
INSERT IGNORE INTO `api_keys` (`key_name`, `key_value`, `permissions`)
VALUES ('Programador Master', 'mgg_7f3a8b1c9d2e4f5a6b7c8d9e0f1a2b3c4d5e6f7a', 'all');

-- --------------------------------------------------------
--  Tabla: db_connections
--  Conexiones a bases de datos guardadas
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_connections` (
  `id`          int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        varchar(100) NOT NULL COMMENT 'Nombre descriptivo ej: ERP Arroba',
  `host`        varchar(100) NOT NULL DEFAULT 'localhost',
  `port`        smallint UNSIGNED NOT NULL DEFAULT 3306,
  `username`    varchar(100) NOT NULL,
  `password`    text NOT NULL,
  `database`    varchar(100) DEFAULT NULL COMMENT 'BD por defecto (opcional)',
  `descripcion` text,
  `activo`      tinyint(1) DEFAULT 1,
  `created_at`  timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conexión de ejemplo (massolaguard mismo)
INSERT IGNORE INTO `db_connections` (`name`, `host`, `username`, `password`, `database`, `descripcion`)
VALUES ('MassolaGuard', 'localhost', 'massolag_amassola', 'Luyano8906*', 'massolag_massolaguard', 'Base de datos principal del sistema');

-- --------------------------------------------------------
--  Tabla: system_logs
--  Log centralizado de actividad
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `system_logs` (
  `id`         int UNSIGNED NOT NULL AUTO_INCREMENT,
  `level`      enum('debug','info','warning','error','critical') DEFAULT 'info',
  `module`     varchar(50) DEFAULT 'system',
  `message`    text NOT NULL,
  `context`    text,
  `ip`         varchar(45) DEFAULT NULL,
  `user`       varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `level` (`level`),
  KEY `module` (`module`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
--  Asegurar que ia_keys tenga la estructura correcta
-- --------------------------------------------------------
ALTER TABLE `ia_keys`
  MODIFY `api_key` text NOT NULL,
  MODIFY `activo` tinyint(1) DEFAULT 1;

-- --------------------------------------------------------
--  Vista: v_logs_recientes
-- --------------------------------------------------------
CREATE OR REPLACE VIEW `v_logs_recientes` AS
SELECT * FROM `system_logs`
ORDER BY `id` DESC
LIMIT 500;

SELECT 'Setup completado correctamente' AS resultado;