SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS roles (
  id CHAR(36) PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, description TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id CHAR(36) PRIMARY KEY, code VARCHAR(120) NOT NULL UNIQUE, description TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id CHAR(36) NOT NULL, role_id CHAR(36) NOT NULL, PRIMARY KEY (user_id,role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id CHAR(36) NOT NULL, permission_id CHAR(36) NOT NULL, PRIMARY KEY (role_id,permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
  id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL, role ENUM('SUPER_ADMIN','ADMIN','OPERADOR','VISUALIZADOR') NOT NULL DEFAULT 'ADMIN',
  active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS routers (
  id CHAR(36) PRIMARY KEY, name VARCHAR(150) NOT NULL, router_code VARCHAR(100) NOT NULL UNIQUE,
  code VARCHAR(100) GENERATED ALWAYS AS (router_code) STORED, model VARCHAR(150), location VARCHAR(255),
  hotspot_server VARCHAR(150) NOT NULL DEFAULT 'hotspot-wifi', host VARCHAR(255), local_host VARCHAR(255), vpn_host VARCHAR(255),
  connection_mode ENUM('local','vpn') NOT NULL DEFAULT 'local', api_port INT NOT NULL DEFAULT 8728,
  api_ssl TINYINT(1) NOT NULL DEFAULT 0, api_username VARCHAR(100) NOT NULL DEFAULT 'wifi-agent',
  api_status ENUM('CONNECTED','DISCONNECTED','ERROR','UNCONFIGURED') NOT NULL DEFAULT 'UNCONFIGURED',
  credentials_configured_at DATETIME(3), last_connection_test_at DATETIME(3), last_connection_error TEXT, agent_id VARCHAR(100),
  vpn_enabled TINYINT(1) NOT NULL DEFAULT 0, vpn_status ENUM('ONLINE','OFFLINE','ERROR','DISABLED') NOT NULL DEFAULT 'DISABLED',
  last_vpn_seen_at DATETIME(3), status ENUM('ONLINE','OFFLINE','ERROR') NOT NULL DEFAULT 'OFFLINE', last_seen_at DATETIME(3),
  routeros_version VARCHAR(100), board_name VARCHAR(150), serial VARCHAR(150), cpu_load INT, memory_total BIGINT, memory_used BIGINT,
  uptime_seconds BIGINT, ssid VARCHAR(255),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_routers_code (code), KEY idx_routers_vpn_host (vpn_host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_credentials (
  router_id CHAR(36) PRIMARY KEY,
  password_ciphertext TEXT NOT NULL,
  encryption_iv VARCHAR(64) NOT NULL,
  authentication_tag VARCHAR(64) NOT NULL,
  algorithm VARCHAR(50) NOT NULL DEFAULT 'aes-256-gcm',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_router_credentials_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_vpn_connections (
  id CHAR(36) PRIMARY KEY, router_id CHAR(36) NOT NULL, connection_type VARCHAR(50) NOT NULL DEFAULT 'WIREGUARD',
  server_host VARCHAR(255), router_host VARCHAR(255) NOT NULL, interface_name VARCHAR(100),
  status ENUM('ONLINE','OFFLINE','ERROR','DISABLED') NOT NULL DEFAULT 'OFFLINE', last_seen_at DATETIME(3), latency_ms INT, error TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_router_vpn (router_id), CONSTRAINT fk_router_vpn_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agents (
  id VARCHAR(100) PRIMARY KEY, router_id CHAR(36), agent_name VARCHAR(150), mode ENUM('local','server') NOT NULL DEFAULT 'local', version VARCHAR(100),
  status ENUM('ONLINE','OFFLINE','CONNECTING') NOT NULL DEFAULT 'OFFLINE', last_heartbeat_at DATETIME(3), connected_at DATETIME(3),
  disconnected_at DATETIME(3), ip_address VARCHAR(64), reconnect_count INT DEFAULT 0,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_agents_router (router_id), CONSTRAINT fk_agents_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_tokens (
  agent_id VARCHAR(100) PRIMARY KEY, token_hash CHAR(64) NOT NULL UNIQUE, token_prefix VARCHAR(16) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1, last_used_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_agent_tokens_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS access_points (
  id CHAR(36) PRIMARY KEY, name VARCHAR(150) NOT NULL, model VARCHAR(150), mac VARCHAR(32), management_ip VARCHAR(64), location VARCHAR(255),
  router_id CHAR(36), ssid_24 VARCHAR(255), ssid_5 VARCHAR(255), status ENUM('ONLINE','OFFLINE','ERROR') NOT NULL DEFAULT 'OFFLINE', notes TEXT,
  system_name VARCHAR(255), description TEXT, uptime_seconds BIGINT, last_seen_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_ap_router (router_id), CONSTRAINT fk_ap_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
  id CHAR(36) PRIMARY KEY, name VARCHAR(150), surname VARCHAR(150), email VARCHAR(190), phone VARCHAR(50), cpf_masked VARCHAR(32), cpf_hash VARCHAR(255),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_customers_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
  id CHAR(36) PRIMARY KEY, customer_id CHAR(36), router_id CHAR(36), mac_address VARCHAR(32) NOT NULL UNIQUE, hostname VARCHAR(255), nickname VARCHAR(255),
  ip_address VARCHAR(64), vendor VARCHAR(150), status ENUM('ONLINE','OFFLINE','BLOCKED','PAGANDO','EXPIRADO','ERRO') NOT NULL DEFAULT 'OFFLINE',
  is_blocked TINYINT(1) NOT NULL DEFAULT 0, desired_blocked TINYINT(1) NOT NULL DEFAULT 0,
  network_sync_status ENUM('PENDING','SYNCING','SYNCED','ERROR','MIKROTIK_OFFLINE') NOT NULL DEFAULT 'SYNCED', network_sync_error TEXT,
  last_synced_at DATETIME(3), last_command_id CHAR(36), block_reason TEXT, blocked_by_name VARCHAR(150), blocked_at DATETIME(3),
  first_seen_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3), last_seen_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3), observations TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_devices_router (router_id), KEY idx_devices_customer (customer_id),
  CONSTRAINT fk_devices_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
  CONSTRAINT fk_devices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
  id CHAR(36) PRIMARY KEY, name VARCHAR(150) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL DEFAULT 0,
  duration_minutes INT, download_limit VARCHAR(50) DEFAULT '2M', upload_limit VARCHAR(50) DEFAULT '2M', device_limit INT DEFAULT 1,
  enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, featured TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_plan_profiles (
  id CHAR(36) PRIMARY KEY, router_id CHAR(36) NOT NULL, plan_id CHAR(36) NOT NULL, profile_name VARCHAR(150) NOT NULL,
  sync_status ENUM('PENDING','SYNCED','ERROR') DEFAULT 'PENDING', last_synced_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_router_plan (router_id,plan_id), CONSTRAINT fk_rpp_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE,
  CONSTRAINT fk_rpp_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id CHAR(36) PRIMARY KEY, payment_session_id CHAR(36), device_id CHAR(36), plan_id CHAR(36), router_id CHAR(36), mac_address VARCHAR(32) NOT NULL,
  amount DECIMAL(10,2) NOT NULL, gateway VARCHAR(50) NOT NULL DEFAULT 'MOCK', gateway_payment_id VARCHAR(150), external_reference VARCHAR(190) UNIQUE,
  qr_code LONGTEXT, qr_code_base64 LONGTEXT, ticket_url TEXT, status ENUM('PENDING','APPROVED','REJECTED','CANCELLED','EXPIRED','REFUNDED') NOT NULL DEFAULT 'PENDING',
  status_detail VARCHAR(255), idempotency_key VARCHAR(190), approved_at DATETIME(3), expires_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_payments_status (status), KEY idx_payments_created (created_at), KEY idx_payments_mac (mac_address),
  CONSTRAINT fk_payments_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_sessions (
  id CHAR(36) PRIMARY KEY, customer_id CHAR(36), device_id CHAR(36) NOT NULL, router_id CHAR(36), mac_address VARCHAR(32) NOT NULL,
  ip_address VARCHAR(64) NOT NULL, plan_id CHAR(36) NOT NULL, payment_id CHAR(36),
  status ENUM('CREATED','PENDING','APPROVED','REJECTED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'CREATED', external_reference VARCHAR(190) UNIQUE,
  customer_name VARCHAR(255), customer_email VARCHAR(190), customer_cpf_masked VARCHAR(32),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_payment_sessions_mac (mac_address), CONSTRAINT fk_ps_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_ps_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
  CONSTRAINT fk_ps_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS temporary_accesses (
  id CHAR(36) PRIMARY KEY, device_id CHAR(36) NOT NULL, payment_id CHAR(36), router_id CHAR(36), mac_address VARCHAR(32) NOT NULL,
  ip_address VARCHAR(64) NOT NULL, started_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), expires_at DATETIME(3) NOT NULL,
  revoked_at DATETIME(3), reason TEXT, attempt_number INT NOT NULL DEFAULT 1, rate_download VARCHAR(50) DEFAULT '2M', rate_upload VARCHAR(50) DEFAULT '2M',
  status ENUM('PENDING','ACTIVE','EXPIRED','REVOKED','COMPLETED','ERROR') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_temp_device (device_id), KEY idx_temp_status (status), CONSTRAINT fk_temp_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_temp_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_temp_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS access_grants (
  id CHAR(36) PRIMARY KEY, device_id CHAR(36) NOT NULL, plan_id CHAR(36), payment_id CHAR(36), router_id CHAR(36),
  status ENUM('PENDING','ACTIVE','EXPIRED','REVOKED','MANUAL','ERROR') NOT NULL DEFAULT 'PENDING', started_at DATETIME(3), expires_at DATETIME(3),
  duration_seconds BIGINT, access_type ENUM('TEMPORARY','UNLIMITED','PAID') NOT NULL DEFAULT 'TEMPORARY',
  time_mode ENUM('WALL_CLOCK','USAGE_TIME') NOT NULL DEFAULT 'WALL_CLOCK', mikrotik_username VARCHAR(255), mikrotik_profile VARCHAR(255),
  remaining_seconds BIGINT, consumed_seconds BIGINT NOT NULL DEFAULT 0,
  timer_status ENUM('PENDING','RUNNING','PAUSED','UNLIMITED','EXPIRED') NOT NULL DEFAULT 'PENDING', timer_synced_at DATETIME(3),
  revoked_at DATETIME(3), revoke_reason TEXT, sync_command_id CHAR(36), sync_error TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_grant_device (device_id), KEY idx_grant_status (status), CONSTRAINT fk_grant_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_grant_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL, CONSTRAINT fk_grant_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
  CONSTRAINT fk_grant_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotspot_sessions (
  id CHAR(36) PRIMARY KEY, device_id CHAR(36), router_id CHAR(36), access_grant_id CHAR(36), mac_address VARCHAR(32) NOT NULL,
  username VARCHAR(255), ip_address VARCHAR(64), login_at DATETIME(3), logout_at DATETIME(3), bytes_in BIGINT DEFAULT 0, bytes_out BIGINT DEFAULT 0,
  uptime_seconds BIGINT DEFAULT 0, session_id VARCHAR(190), status VARCHAR(50) DEFAULT 'ACTIVE',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  KEY idx_hs_device (device_id), CONSTRAINT fk_hs_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
  CONSTRAINT fk_hs_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
  CONSTRAINT fk_hs_grant FOREIGN KEY (access_grant_id) REFERENCES access_grants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_connections (
  id CHAR(36) PRIMARY KEY, device_id CHAR(36) NOT NULL, router_id CHAR(36), event_type VARCHAR(50) NOT NULL, ip_address VARCHAR(64),
  signal_strength INT, interface_name VARCHAR(150), metadata JSON, connected_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3), disconnected_at DATETIME(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY idx_dc_device (device_id),
  CONSTRAINT fk_dc_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_dc_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS manual_grants (
  id CHAR(36) PRIMARY KEY, device_id CHAR(36) NOT NULL, admin_id CHAR(36), admin_name VARCHAR(150), duration_minutes INT NOT NULL,
  reason TEXT, granted_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), expires_at DATETIME(3) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_manual_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_manual_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_commands (
  id CHAR(36) PRIMARY KEY, router_id CHAR(36) NOT NULL, device_id CHAR(36), agent_id VARCHAR(100), command VARCHAR(100) NOT NULL,
  payload JSON, status ENUM('PENDING','SENT','SUCCESS','FAILED') NOT NULL DEFAULT 'PENDING', result JSON, error TEXT,
  sent_at DATETIME(3), completed_at DATETIME(3), created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), KEY idx_rc_router (router_id), KEY idx_rc_status (status),
  CONSTRAINT fk_rc_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE,
  CONSTRAINT fk_rc_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
  CONSTRAINT fk_rc_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_snapshots (
  router_id CHAR(36) PRIMARY KEY, snapshot_hash CHAR(64) NOT NULL, snapshot JSON NOT NULL, collected_at DATETIME(3) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_snapshots_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS router_events (
  id CHAR(36) PRIMARY KEY, router_id CHAR(36), agent_id VARCHAR(100), event_type VARCHAR(100) NOT NULL, payload JSON,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY idx_router_events_router (router_id),
  CONSTRAINT fk_router_events_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
  CONSTRAINT fk_router_events_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_events (
  id CHAR(36) PRIMARY KEY, agent_id VARCHAR(100), router_id CHAR(36), event_type VARCHAR(100) NOT NULL, payload JSON,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_agent_events_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL,
  CONSTRAINT fk_agent_events_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id CHAR(36) PRIMARY KEY, admin_id CHAR(36), admin_name VARCHAR(150), action VARCHAR(100) NOT NULL, entity_type VARCHAR(100),
  entity_id CHAR(36), router_id CHAR(36), device_id CHAR(36), admin_ip VARCHAR(64), old_data JSON, new_data JSON,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY idx_audit_created (created_at),
  CONSTRAINT fk_audit_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_router FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_settings (
  `key` VARCHAR(100) PRIMARY KEY, value TEXT NOT NULL, category VARCHAR(50) NOT NULL DEFAULT 'GERAL', is_public TINYINT(1) NOT NULL DEFAULT 0,
  description TEXT, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_events (
  id CHAR(36) PRIMARY KEY, gateway VARCHAR(50) NOT NULL, event_id VARCHAR(190), event_type VARCHAR(100), payload JSON, signature TEXT,
  processed TINYINT(1) NOT NULL DEFAULT 0, processed_at DATETIME(3), result JSON, error TEXT,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY uq_webhook_gateway_event (gateway,event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_info (
  id CHAR(36) PRIMARY KEY, payment_session_id CHAR(36), name VARCHAR(150), surname VARCHAR(150), email VARCHAR(190), cpf_masked VARCHAR(32), cpf_hash VARCHAR(255),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_customer_info_session FOREIGN KEY (payment_session_id) REFERENCES payment_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO roles (id,name,description) VALUES
('20000000-0000-4000-8000-000000000001','SUPER_ADMIN','Acesso total'),
('20000000-0000-4000-8000-000000000002','ADMIN','Administração'),
('20000000-0000-4000-8000-000000000003','OPERADOR','Operação de rede'),
('20000000-0000-4000-8000-000000000004','VISUALIZADOR','Somente leitura');

INSERT IGNORE INTO permissions (id,code,description) VALUES
('30000000-0000-4000-8000-000000000001','ADMIN_ALL','Administração completa'),
('30000000-0000-4000-8000-000000000002','NETWORK_WRITE','Alterar estado da rede'),
('30000000-0000-4000-8000-000000000003','PAYMENTS_WRITE','Gerenciar pagamentos'),
('30000000-0000-4000-8000-000000000004','REPORTS_READ','Consultar relatórios');

INSERT IGNORE INTO system_settings (`key`,value,category,is_public,description) VALUES
('network_name','IDEART WiFi','GERAL',1,'Nome da rede'),('company_name','IDEART','GERAL',1,'Nome da empresa'),
('currency','BRL','GERAL',1,'Moeda'),('timezone','America/Sao_Paulo','GERAL',1,'Fuso horário'),
('temporary_access_minutes','2','HOTSPOT',0,'Tempo temporário'),('temporary_access_max_attempts','3','HOTSPOT',0,'Tentativas máximas'),
('temporary_access_rate_download','2M','HOTSPOT',0,'Download temporário'),('temporary_access_rate_upload','2M','HOTSPOT',0,'Upload temporário'),
('payment_gateway','MOCK','PIX',0,'Gateway'),('payment_mode','mock','PIX',0,'Ambiente de pagamento'),
('pix_validity_minutes','15','PIX',0,'Validade do PIX'),('pix_auto_release','true','PIX',0,'Liberação automática'),
('mikrotik_mode','real','MIKROTIK',0,'Modo MikroTik'),('mikrotik_connection_mode','local','MIKROTIK',0,'Conexão local ou VPN'),
('mikrotik_sync_interval','30','MIKROTIK',0,'Sincronização'),('mikrotik_sync_mode','SYSTEM_PRIMARY','MIKROTIK',0,'Fonte principal');

-- Administrador inicial solicitado para esta instalação.
START TRANSACTION;
SET @ideart_admin_email = 'thgdsztls@gmail.com';
SET @ideart_admin_name = 'Administrador';
SET @ideart_admin_hash = '$2b$12$g26FWiUz1sfOLbBy0cgcAOpzxg7Mz/3ZetgWH9.0FHf3/vemo.KaO';
SET @ideart_admin_id = COALESCE(
  (SELECT user_id FROM admins WHERE BINARY email=BINARY @ideart_admin_email LIMIT 1),
  (SELECT id FROM users WHERE BINARY email=BINARY @ideart_admin_email LIMIT 1),
  UUID()
);

INSERT INTO users (id,name,email,password_hash,active)
VALUES (@ideart_admin_id,@ideart_admin_name,@ideart_admin_email,@ideart_admin_hash,TRUE)
ON DUPLICATE KEY UPDATE name=VALUES(name),password_hash=VALUES(password_hash),active=TRUE;

SET @ideart_admin_id = (SELECT id FROM users WHERE BINARY email=BINARY @ideart_admin_email LIMIT 1);

INSERT INTO admins (id,user_id,name,email,password_hash,role,active)
VALUES (@ideart_admin_id,@ideart_admin_id,@ideart_admin_name,@ideart_admin_email,@ideart_admin_hash,'SUPER_ADMIN',TRUE)
ON DUPLICATE KEY UPDATE user_id=VALUES(user_id),name=VALUES(name),password_hash=VALUES(password_hash),role='SUPER_ADMIN',active=TRUE;

INSERT IGNORE INTO user_roles (user_id,role_id)
VALUES (@ideart_admin_id,'20000000-0000-4000-8000-000000000001');
COMMIT;