CREATE TABLE IF NOT EXISTS `pbx_connections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL COMMENT 'ID del usuario/staff (sys_users.id)',
  `bearer_token` text NOT NULL COMMENT 'Token JWT encriptado con AES-256-GCM',
  `expires_at` datetime DEFAULT NULL COMMENT 'Fecha de expiración del token',
  `status` enum('active','inactive','expired') NOT NULL DEFAULT 'active' COMMENT 'Estado del token',
  `last_sync` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Última sincronización con PBX',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  PRIMARY KEY (`id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Almacena tokens de autenticación PBX encriptados';

/*CREATE TABLE `pbx_cdr_cache` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `cache_key` VARCHAR(64) NOT NULL,
  `params_json` TEXT NOT NULL,
  `data_json` LONGTEXT NOT NULL,
  `meta_json` TEXT NULL,
  `total` INT NULL,
  `timezone` VARCHAR(64) NULL,
  `fetched_at` DATETIME NOT NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_cache_key` (`cache_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;*/

CREATE TABLE IF NOT EXISTS `pbx_cdr_cache_agg_daily` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `cdr_date` DATE NOT NULL,
  `group_key` VARCHAR(10) NOT NULL,
  `member_id` INT NOT NULL,
  `member_name` VARCHAR(200) NOT NULL,
  `incoming` INT NOT NULL DEFAULT 0,
  `outgoing` INT NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_date_member` (`cdr_date`, `group_key`, `member_id`),
  KEY `idx_cdr_date` (`cdr_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `pbx_cdr_sync_state` (
  `id` INT NOT NULL,
  `last_sync_at` DATETIME NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
