CREATE TABLE IF NOT EXISTS `config_tr069` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `acs_url` varchar(255) DEFAULT 'http://tu-acs-server:7547', `acs_user` varchar(100) DEFAULT 'acs', `acs_pass` varchar(100) DEFAULT 'acs', `cpe_user` varchar(100) DEFAULT 'it', `cpe_pass` varchar(100) DEFAULT 'it123', PRIMARY KEY (`id`), UNIQUE KEY `unique_cfg` (`olt_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `discovered_onus` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `frame` int(11) NOT NULL, `slot` int(11) NOT NULL, `port` int(11) NOT NULL, `serial_number` varchar(30) NOT NULL, `model` varchar(50) DEFAULT NULL, `ont_type` varchar(20) DEFAULT NULL, `found_at` timestamp NOT NULL DEFAULT current_timestamp(), `fecha_deteccion` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `unique_discovery` (`olt_id`,`serial_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `naps` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) DEFAULT 0, `frame` int(11) DEFAULT 0, `slot` int(11) NOT NULL, `port` int(11) NOT NULL, `alias_nombre` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_nap_olt` (`olt_id`,`frame`,`slot`,`port`), UNIQUE KEY `unique_olt_fsp` (`olt_id`,`frame`,`slot`,`port`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `olts` (`id` int(11) NOT NULL AUTO_INCREMENT, `nombre` varchar(50) NOT NULL, `ip_address` varchar(20) NOT NULL, `snmp_port` int(11) DEFAULT 161, `snmp_community` varchar(50) DEFAULT 'public', `telnet_user` varchar(50) DEFAULT NULL, `telnet_pass` varchar(50) DEFAULT NULL, `estado` int(11) DEFAULT 1, `ssh_user` varchar(50) DEFAULT '', `ssh_pass` varchar(100) DEFAULT '', `ssh_port` int(11) DEFAULT 22, `rx_warning` decimal(5,2) DEFAULT -27.00, `rx_critical` decimal(5,2) DEFAULT -30.00, `snmp_write_community` varchar(50) DEFAULT 'private', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `onu_signal_history` (`id` int(11) NOT NULL AUTO_INCREMENT, `onu_db_id` int(11) NOT NULL, `rx_power` decimal(5,2) NOT NULL, `timestamp` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_onu_time` (`onu_db_id`,`timestamp`), KEY `idx_time` (`timestamp`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `onus` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) DEFAULT 0, `nombre_cliente` varchar(100) DEFAULT NULL, `serial_number` varchar(100) DEFAULT NULL, `frame` int(11) DEFAULT 0, `slot` int(11) DEFAULT NULL, `port` int(11) DEFAULT NULL, `onu_id` int(11) DEFAULT NULL, `estado_actual` varchar(20) DEFAULT NULL, `potencia_rx` decimal(5,2) DEFAULT NULL, `ultima_causa_caida` varchar(50) DEFAULT NULL, `ultima_actualizacion` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `ip_address` varchar(20) DEFAULT '0.0.0.0', `uptime` bigint(20) DEFAULT 0, `last_down_cause` varchar(100) DEFAULT 'Unknown', `fecha_estado` datetime DEFAULT current_timestamp(), `modelo` varchar(50) DEFAULT 'ONT GPON', `wan_mode` varchar(50) DEFAULT '---', `wan_ip` varchar(50) DEFAULT '---', `wan_vlan` varchar(50) DEFAULT '---', `pppoe_user` varchar(100) DEFAULT '---', `pppoe_pass` varchar(100) DEFAULT '---', `tr069_status` varchar(50) DEFAULT '---', `billing_state` varchar(20) DEFAULT 'ACTIVO', `caja_nap_mw` varchar(100) DEFAULT NULL, `caja_nap` varchar(100) DEFAULT NULL, `puerto_nap` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_serial_unico` (`olt_id`,`serial_number`), KEY `idx_busqueda` (`nombre_cliente`,`serial_number`,`ip_address`), KEY `idx_fsp` (`frame`,`slot`,`port`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `profiles_line` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `profile_id` int(11) NOT NULL, `profile_name` varchar(100) DEFAULT NULL, `activo` tinyint(4) DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `unique_line` (`olt_id`,`profile_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `profiles_service` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `profile_id` int(11) NOT NULL, `profile_name` varchar(100) DEFAULT NULL, `activo` tinyint(4) DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `unique_srv` (`olt_id`,`profile_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `profiles_speed` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `traffic_id` int(11) NOT NULL, `tid_up` int(11) DEFAULT 0, `cir_kbps` int(11) DEFAULT NULL, `pir_kbps` int(11) DEFAULT NULL, `cir_up_kbps` int(11) DEFAULT 0, `pir_up_kbps` int(11) DEFAULT 0, `name` varchar(100) DEFAULT NULL, `activo` tinyint(4) DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `unique_speed` (`olt_id`,`traffic_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `profiles_vlan` (`id` int(11) NOT NULL AUTO_INCREMENT, `olt_id` int(11) NOT NULL, `vlan_id` int(11) NOT NULL, `description` varchar(100) DEFAULT NULL, `type` varchar(50) DEFAULT 'smart', `activo` tinyint(4) DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `unique_vlan` (`olt_id`,`vlan_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `system_logs` (`id` int(11) NOT NULL AUTO_INCREMENT, `fecha` datetime DEFAULT current_timestamp(), `tipo` varchar(20) DEFAULT NULL, `mensaje` text DEFAULT NULL, `olt_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `system_settings` (`id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(50) DEFAULT NULL, `setting_value` text DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `password` varchar(255) NOT NULL, `role` enum('admin','tecnico') NOT NULL DEFAULT 'admin', `active` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS `wan_service_types` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `label` varchar(100) DEFAULT NULL, `activo` tinyint(4) DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO `wan_service_types` (`name`, `label`, `activo`) VALUES ('route_pppoe', 'Router - PPPoE', 1), ('route_dhcp', 'Router - DHCP', 1), ('route_static', 'Router - IP Estática', 1), ('bridge', 'Modo Bridge', 1);
