-- ============================================================
-- CloudPulse Check — Database Schema
-- MySQL 8.0+ | UTF8MB4 | InnoDB
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- ============================================================
-- USERS & AUTH
-- ============================================================

CREATE TABLE IF NOT EXISTS `user_roles` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `label` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user_roles` (`name`, `label`) VALUES
('admin', 'Administrator'),
('reseller', 'Reseller'),
('client', 'Client');

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `label` VARCHAR(150) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_perm_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` TINYINT UNSIGNED NOT NULL,
  `permission_id` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `user_roles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` TINYINT UNSIGNED NOT NULL DEFAULT 3,
  `email` VARCHAR(255) NOT NULL,
  `username` VARCHAR(80) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(100),
  `last_name` VARCHAR(100),
  `phone` VARCHAR(30),
  `country_code` CHAR(2) DEFAULT 'BR',
  `language` VARCHAR(10) DEFAULT 'pt_BR',
  `timezone` VARCHAR(60) DEFAULT 'America/Sao_Paulo',
  `avatar_url` VARCHAR(500),
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `email_verified_at` TIMESTAMP NULL,
  `verification_token` VARCHAR(100),
  `status` ENUM('active','suspended','banned','pending') NOT NULL DEFAULT 'pending',
  `last_login_at` TIMESTAMP NULL,
  `last_login_ip` VARCHAR(45),
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_email` (`email`),
  UNIQUE KEY `uq_user_username` (`username`),
  KEY `idx_user_status` (`status`),
  KEY `idx_user_role` (`role_id`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `user_roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `company_name` VARCHAR(200),
  `document` VARCHAR(30),
  `address_line1` VARCHAR(255),
  `address_line2` VARCHAR(255),
  `city` VARCHAR(100),
  `state` VARCHAR(100),
  `postal_code` VARCHAR(20),
  `country` VARCHAR(100),
  `bio` TEXT,
  `website` VARCHAR(300),
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_profile_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 `user_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `session_token` VARCHAR(128) NOT NULL,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `device_name` VARCHAR(200),
  `is_current` TINYINT(1) DEFAULT 0,
  `last_active_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_session_token` (`session_token`),
  KEY `idx_session_user` (`user_id`),
  CONSTRAINT `fk_session_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 `user_preferences` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `theme` ENUM('dark','light','auto') DEFAULT 'dark',
  `notifications_email` TINYINT(1) DEFAULT 1,
  `notifications_push` TINYINT(1) DEFAULT 1,
  `notifications_payments` TINYINT(1) DEFAULT 1,
  `notifications_checks` TINYINT(1) DEFAULT 1,
  `notifications_promotions` TINYINT(1) DEFAULT 1,
  `notifications_security` TINYINT(1) DEFAULT 1,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_prefs_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 `password_resets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token` VARCHAR(128) NOT NULL,
  `ip_address` VARCHAR(45),
  `used` TINYINT(1) DEFAULT 0,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reset_token` (`token`),
  KEY `idx_reset_user` (`user_id`),
  CONSTRAINT `fk_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SERVICES & CATEGORIES
-- ============================================================

CREATE TABLE IF NOT EXISTS `service_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `icon` VARCHAR(100),
  `color` VARCHAR(20),
  `sort_order` SMALLINT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cat_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_providers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(50) NOT NULL DEFAULT 'ifreeicloud',
  `api_url` VARCHAR(500) NOT NULL,
  `api_key_encrypted` TEXT NOT NULL,
  `timeout_seconds` SMALLINT DEFAULT 30,
  `is_active` TINYINT(1) DEFAULT 1,
  `balance_cache` DECIMAL(18,4),
  `balance_cached_at` TIMESTAMP NULL,
  `last_sync_at` TIMESTAMP NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `services` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider_id` INT UNSIGNED,
  `category_id` INT UNSIGNED,
  `provider_service_id` VARCHAR(100),
  `public_service_id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `short_description` VARCHAR(500),
  `estimated_time` VARCHAR(100),
  `status` ENUM('active','inactive','hidden','maintenance') DEFAULT 'active',
  `is_free` TINYINT(1) DEFAULT 0,
  `free_daily_limit` SMALLINT DEFAULT 0,
  `free_ip_limit` SMALLINT DEFAULT 0,
  `expose_api` TINYINT(1) DEFAULT 1,
  `expose_dhru` TINYINT(1) DEFAULT 1,
  `expose_php_api` TINYINT(1) DEFAULT 1,
  `show_price_public` TINYINT(1) DEFAULT 1,
  `sort_order` SMALLINT DEFAULT 0,
  `icon` VARCHAR(100),
  `tags` JSON,
  `meta` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_service_public_id` (`public_service_id`),
  UNIQUE KEY `uq_service_slug` (`slug`),
  KEY `idx_service_category` (`category_id`),
  KEY `idx_service_provider` (`provider_id`),
  KEY `idx_service_status` (`status`),
  CONSTRAINT `fk_svc_provider` FOREIGN KEY (`provider_id`) REFERENCES `api_providers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_svc_category` FOREIGN KEY (`category_id`) REFERENCES `service_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service_fields` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `label` VARCHAR(200) NOT NULL,
  `type` ENUM('text','number','imei','serial','email','select','textarea') DEFAULT 'text',
  `placeholder` VARCHAR(255),
  `validation_regex` VARCHAR(500),
  `is_required` TINYINT(1) DEFAULT 1,
  `sort_order` SMALLINT DEFAULT 0,
  `options` JSON,
  PRIMARY KEY (`id`),
  KEY `idx_field_service` (`service_id`),
  CONSTRAINT `fk_field_service` FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service_pricing` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `user_group_id` INT UNSIGNED,
  `cost_price` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `margin_percent` DECIMAL(5,2) DEFAULT 0,
  `fixed_addition` DECIMAL(18,4) DEFAULT 0,
  `final_price` DECIMAL(18,4) NOT NULL,
  `api_price` DECIMAL(18,4),
  `currency` CHAR(3) DEFAULT 'USD',
  `rounding` ENUM('none','0.01','0.05','0.10','0.50','1.00') DEFAULT '0.01',
  `is_default` TINYINT(1) DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pricing_service` (`service_id`),
  CONSTRAINT `fk_pricing_service` FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- WALLET & FINANCIAL
-- ============================================================

CREATE TABLE IF NOT EXISTS `wallets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `balance` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `total_deposited` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `total_used` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `total_bonus` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `currency` CHAR(3) DEFAULT 'USD',
  `is_frozen` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wallet_user` (`user_id`),
  CONSTRAINT `fk_wallet_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 `wallet_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `wallet_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('deposit','debit','bonus','refund','adjustment','transfer') NOT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `balance_before` DECIMAL(18,4) NOT NULL,
  `balance_after` DECIMAL(18,4) NOT NULL,
  `reference_type` VARCHAR(50),
  `reference_id` BIGINT UNSIGNED,
  `description` VARCHAR(500),
  `notes` TEXT,
  `admin_id` BIGINT UNSIGNED,
  `idempotency_key` VARCHAR(128),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_txn_idempotency` (`idempotency_key`),
  KEY `idx_txn_wallet` (`wallet_id`),
  KEY `idx_txn_user` (`user_id`),
  KEY `idx_txn_type` (`type`),
  KEY `idx_txn_created` (`created_at`),
  CONSTRAINT `fk_txn_wallet` FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `recharge_packages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `bonus_amount` DECIMAL(18,4) DEFAULT 0,
  `bonus_percent` DECIMAL(5,2) DEFAULT 0,
  `description` VARCHAR(500),
  `is_featured` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `min_uses` INT DEFAULT 0,
  `max_uses` INT DEFAULT 0,
  `sort_order` SMALLINT DEFAULT 0,
  `color` VARCHAR(20),
  `icon` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pkg_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `recharge_packages` (`name`, `slug`, `amount`, `bonus_percent`, `description`, `is_featured`, `sort_order`, `color`) VALUES
('Starter', 'starter', 10.00, 0, 'Entry level package', 0, 1, '#087BFF'),
('Basic', 'basic', 25.00, 5, 'Best for occasional users', 0, 2, '#16D9FF'),
('Professional', 'professional', 50.00, 10, 'Most popular choice', 1, 3, '#7957FF'),
('Business', 'business', 100.00, 15, 'For power users', 0, 4, '#22C98B'),
('Enterprise', 'enterprise', 250.00, 20, 'Maximum value', 0, 5, '#087BFF');

CREATE TABLE IF NOT EXISTS `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `package_id` INT UNSIGNED,
  `payment_id` VARCHAR(128) NOT NULL,
  `external_id` VARCHAR(255),
  `provider` VARCHAR(50) DEFAULT 'misticpay',
  `method` VARCHAR(50) DEFAULT 'pix',
  `amount` DECIMAL(18,4) NOT NULL,
  `bonus_amount` DECIMAL(18,4) DEFAULT 0,
  `fee_amount` DECIMAL(18,4) DEFAULT 0,
  `total_amount` DECIMAL(18,4) NOT NULL,
  `currency` CHAR(3) DEFAULT 'BRL',
  `status` ENUM('pending','processing','approved','failed','cancelled','expired','refunded') DEFAULT 'pending',
  `pix_code` TEXT,
  `pix_qrcode_url` VARCHAR(500),
  `expires_at` TIMESTAMP NULL,
  `paid_at` TIMESTAMP NULL,
  `credited_at` TIMESTAMP NULL,
  `webhook_received_at` TIMESTAMP NULL,
  `meta` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_id` (`payment_id`),
  KEY `idx_payment_user` (`user_id`),
  KEY `idx_payment_status` (`status`),
  CONSTRAINT `fk_payment_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payment_webhooks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` BIGINT UNSIGNED,
  `external_payment_id` VARCHAR(255),
  `provider` VARCHAR(50),
  `event_type` VARCHAR(100),
  `payload` JSON,
  `signature` VARCHAR(500),
  `is_valid` TINYINT(1),
  `processed` TINYINT(1) DEFAULT 0,
  `processed_at` TIMESTAMP NULL,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wh_payment` (`payment_id`),
  UNIQUE KEY `uq_wh_ext_event` (`external_payment_id`, `event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CHECK REQUESTS
-- ============================================================

CREATE TABLE IF NOT EXISTS `check_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `public_id` VARCHAR(20) NOT NULL,
  `user_id` BIGINT UNSIGNED,
  `service_id` INT UNSIGNED NOT NULL,
  `api_key_id` BIGINT UNSIGNED,
  `source` ENUM('web','api','telegram','dhru','php_api') DEFAULT 'web',
  `input_data` JSON NOT NULL,
  `masked_input` VARCHAR(255),
  `status` ENUM('pending','processing','completed','failed','refunded','cancelled') DEFAULT 'pending',
  `credits_charged` DECIMAL(18,4) DEFAULT 0,
  `credits_refunded` DECIMAL(18,4) DEFAULT 0,
  `provider_order_id` VARCHAR(255),
  `is_free` TINYINT(1) DEFAULT 0,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `idempotency_key` VARCHAR(128),
  `processed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_check_public_id` (`public_id`),
  UNIQUE KEY `uq_check_idempotency` (`idempotency_key`),
  KEY `idx_check_user` (`user_id`),
  KEY `idx_check_service` (`service_id`),
  KEY `idx_check_status` (`status`),
  KEY `idx_check_created` (`created_at`),
  CONSTRAINT `fk_check_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_check_service` FOREIGN KEY (`service_id`) REFERENCES `services`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `check_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `check_id` BIGINT UNSIGNED NOT NULL,
  `raw_response` JSON,
  `formatted_result` JSON,
  `error_code` VARCHAR(100),
  `error_message` TEXT,
  `provider_status` VARCHAR(50),
  `processing_time_ms` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_result_check` (`check_id`),
  CONSTRAINT `fk_result_check` FOREIGN KEY (`check_id`) REFERENCES `check_requests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider_id` INT UNSIGNED,
  `check_id` BIGINT UNSIGNED,
  `method` VARCHAR(10),
  `endpoint` VARCHAR(500),
  `request_payload` JSON,
  `response_body` TEXT,
  `response_code` SMALLINT UNSIGNED,
  `duration_ms` INT UNSIGNED,
  `error` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_apilog_provider` (`provider_id`),
  KEY `idx_apilog_check` (`check_id`),
  KEY `idx_apilog_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FREE CHECKS RATE LIMITING
-- ============================================================

CREATE TABLE IF NOT EXISTS `free_check_usage` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_id` BIGINT UNSIGNED,
  `count` SMALLINT DEFAULT 1,
  `window_start` TIMESTAMP NOT NULL,
  `last_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fcu_service_ip` (`service_id`, `ip_address`),
  KEY `idx_fcu_window` (`window_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` VARCHAR(80) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `data` JSON,
  `icon` VARCHAR(100),
  `color` VARCHAR(20),
  `action_url` VARCHAR(500),
  `is_read` TINYINT(1) DEFAULT 0,
  `read_at` TIMESTAMP NULL,
  `is_archived` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user` (`user_id`),
  KEY `idx_notif_read` (`is_read`),
  KEY `idx_notif_created` (`created_at`),
  CONSTRAINT `fk_notif_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 `push_subscriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `endpoint` TEXT NOT NULL,
  `p256dh` TEXT NOT NULL,
  `auth` VARCHAR(255) NOT NULL,
  `browser` VARCHAR(100),
  `os` VARCHAR(100),
  `device_name` VARCHAR(200),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `idx_push_user` (`user_id`),
  CONSTRAINT `fk_push_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EMAIL
-- ============================================================

CREATE TABLE IF NOT EXISTS `email_settings` (
  `id` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `smtp_host` VARCHAR(255),
  `smtp_port` SMALLINT DEFAULT 587,
  `smtp_username` VARCHAR(255),
  `smtp_password_encrypted` TEXT,
  `smtp_encryption` ENUM('tls','ssl','none') DEFAULT 'tls',
  `from_name` VARCHAR(200),
  `from_email` VARCHAR(255),
  `reply_to` VARCHAR(255),
  `timeout` SMALLINT DEFAULT 30,
  `is_active` TINYINT(1) DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `email_settings` (`id`, `from_name`, `from_email`) VALUES (1, 'CloudPulse Check', 'noreply@cloudpulsecheck.com');

CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(100) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `subject` VARCHAR(300) NOT NULL,
  `html_body` LONGTEXT NOT NULL,
  `text_body` TEXT,
  `variables` JSON,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_template_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED,
  `template_slug` VARCHAR(100),
  `to_email` VARCHAR(255) NOT NULL,
  `subject` VARCHAR(300),
  `status` ENUM('queued','sent','failed') DEFAULT 'queued',
  `error` TEXT,
  `sent_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_emaillog_user` (`user_id`),
  KEY `idx_emaillog_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TELEGRAM
-- ============================================================

CREATE TABLE IF NOT EXISTS `telegram_settings` (
  `id` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `bot_token_encrypted` TEXT,
  `bot_username` VARCHAR(100),
  `webhook_url` VARCHAR(500),
  `webhook_secret` VARCHAR(255),
  `is_active` TINYINT(1) DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `telegram_settings` (`id`) VALUES (1);

CREATE TABLE IF NOT EXISTS `telegram_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `telegram_id` BIGINT NOT NULL,
  `telegram_username` VARCHAR(100),
  `telegram_first_name` VARCHAR(100),
  `telegram_language` VARCHAR(10),
  `is_active` TINYINT(1) DEFAULT 1,
  `linked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_activity_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tg_user` (`user_id`),
  UNIQUE KEY `uq_tg_telegram_id` (`telegram_id`),
  CONSTRAINT `fk_tg_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 `telegram_link_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `used` TINYINT(1) DEFAULT 0,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tg_token` (`token`),
  KEY `idx_tg_token_user` (`user_id`),
  CONSTRAINT `fk_tg_token_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 `telegram_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT,
  `user_id` BIGINT UNSIGNED,
  `update_type` VARCHAR(50),
  `command` VARCHAR(100),
  `message` TEXT,
  `response` TEXT,
  `error` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tglog_user` (`user_id`),
  KEY `idx_tglog_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `telegram_sessions` (
  `telegram_id` BIGINT NOT NULL,
  `state` VARCHAR(50),
  `data` JSON,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT
-- ============================================================

CREATE TABLE IF NOT EXISTS `support_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `support_categories` (`name`) VALUES ('General'),('Billing'),('Technical'),('API'),('Account');

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `public_id` VARCHAR(20) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `admin_id` BIGINT UNSIGNED,
  `category_id` INT UNSIGNED,
  `subject` VARCHAR(300) NOT NULL,
  `priority` ENUM('low','normal','high','urgent') DEFAULT 'normal',
  `status` ENUM('open','in_progress','pending','resolved','closed') DEFAULT 'open',
  `last_reply_at` TIMESTAMP NULL,
  `last_reply_by` ENUM('user','admin'),
  `resolved_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ticket_public_id` (`public_id`),
  KEY `idx_ticket_user` (`user_id`),
  KEY `idx_ticket_status` (`status`),
  CONSTRAINT `fk_ticket_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `is_internal` TINYINT(1) DEFAULT 0,
  `message` TEXT NOT NULL,
  `is_admin` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_ticket` (`ticket_id`),
  CONSTRAINT `fk_msg_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_attachments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id` BIGINT UNSIGNED NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100),
  `size_bytes` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_attach_msg` FOREIGN KEY (`message_id`) REFERENCES `support_messages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- API KEYS (CLIENT API)
-- ============================================================

CREATE TABLE IF NOT EXISTS `api_keys` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `key_hash` VARCHAR(255) NOT NULL,
  `key_prefix` VARCHAR(20) NOT NULL,
  `type` ENUM('dhru','php','general') DEFAULT 'general',
  `permissions` JSON,
  `rate_limit_per_minute` SMALLINT DEFAULT 60,
  `rate_limit_per_day` INT DEFAULT 10000,
  `allowed_ips` JSON,
  `is_active` TINYINT(1) DEFAULT 1,
  `last_used_at` TIMESTAMP NULL,
  `request_count` BIGINT UNSIGNED DEFAULT 0,
  `expires_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_apikey_hash` (`key_hash`),
  KEY `idx_apikey_user` (`user_id`),
  CONSTRAINT `fk_apikey_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 `api_request_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `api_key_id` BIGINT UNSIGNED,
  `user_id` BIGINT UNSIGNED,
  `endpoint` VARCHAR(300),
  `method` VARCHAR(10),
  `ip_address` VARCHAR(45),
  `request_data` JSON,
  `response_code` SMALLINT,
  `response_time_ms` INT,
  `error` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_arllog_key` (`api_key_id`),
  KEY `idx_arllog_user` (`user_id`),
  KEY `idx_arllog_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LANGUAGES & TRANSLATIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `languages` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(10) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `native_name` VARCHAR(100) NOT NULL,
  `flag` VARCHAR(10),
  `direction` ENUM('ltr','rtl') DEFAULT 'ltr',
  `is_active` TINYINT(1) DEFAULT 1,
  `is_default` TINYINT(1) DEFAULT 0,
  `sort_order` TINYINT DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_lang_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `languages` (`code`, `name`, `native_name`, `flag`, `direction`, `is_active`, `is_default`, `sort_order`) VALUES
('pt_BR', 'Portuguese (Brazil)', 'Português (Brasil)', '🇧🇷', 'ltr', 1, 1, 1),
('en', 'English', 'English', '🇺🇸', 'ltr', 1, 0, 2),
('es', 'Spanish', 'Español', '🇪🇸', 'ltr', 1, 0, 3),
('de', 'German', 'Deutsch', '🇩🇪', 'ltr', 1, 0, 4),
('fr', 'French', 'Français', '🇫🇷', 'ltr', 1, 0, 5),
('it', 'Italian', 'Italiano', '🇮🇹', 'ltr', 1, 0, 6),
('zh', 'Chinese', '中文', '🇨🇳', 'ltr', 1, 0, 7),
('ja', 'Japanese', '日本語', '🇯🇵', 'ltr', 1, 0, 8),
('ar', 'Arabic', 'العربية', '🇸🇦', 'rtl', 1, 0, 9);

-- ============================================================
-- CONTENT & CMS
-- ============================================================

CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(300) NOT NULL,
  `content` TEXT NOT NULL,
  `type` ENUM('info','warning','success','danger') DEFAULT 'info',
  `target` ENUM('all','clients','admins') DEFAULT 'all',
  `is_active` TINYINT(1) DEFAULT 1,
  `starts_at` TIMESTAMP NULL,
  `ends_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `homepage_sections` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template` VARCHAR(50) DEFAULT 'default',
  `section_key` VARCHAR(100) NOT NULL,
  `title` VARCHAR(300),
  `subtitle` TEXT,
  `content` LONGTEXT,
  `data` JSON,
  `is_active` TINYINT(1) DEFAULT 1,
  `sort_order` SMALLINT DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_section_key` (`section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `homepage_sections` (`section_key`, `title`, `subtitle`, `data`, `sort_order`) VALUES
('hero', 'Professional Device & IMEI Check Services', 'Instant results. Global coverage. Trusted by thousands.', '{"badge":"Trusted Platform","cta_primary":"Get Started Free","cta_secondary":"View Services","stats_checks":"50K+","stats_services":"100+","stats_users":"10K+","stats_accuracy":"99.9%"}', 1),
('stats', 'Platform Statistics', NULL, '{"items":[{"label":"Checks Completed","value":"50,000+","icon":"check-circle"},{"label":"Services Available","value":"100+","icon":"layers"},{"label":"Registered Users","value":"10,000+","icon":"users"},{"label":"Accuracy Rate","value":"99.9%","icon":"shield"}]}', 2),
('features', 'Why Choose CloudPulse Check', NULL, '{"items":[{"title":"Instant Results","desc":"Get check results in seconds","icon":"zap"},{"title":"Secure Platform","desc":"Enterprise-grade security","icon":"shield"},{"title":"API Access","desc":"Integrate with your systems","icon":"code"},{"title":"24/7 Support","desc":"Always here to help","icon":"headphones"}]}', 3),
('how_it_works', 'How It Works', 'Simple three-step process', '{"steps":[{"step":1,"title":"Choose Service","desc":"Browse our catalog"},{"step":2,"title":"Enter Details","desc":"Provide required info"},{"step":3,"title":"Get Results","desc":"Instant secure results"}]}', 4),
('faq', 'Frequently Asked Questions', NULL, '{"items":[{"q":"What is CloudPulse Check?","a":"A professional platform for device information services including IMEI checks, carrier info, and more."},{"q":"How accurate are the results?","a":"We maintain 99.9% accuracy by partnering with official carriers and databases."},{"q":"How do credits work?","a":"You purchase credits and use them per check. Unused credits never expire."},{"q":"Is there an API?","a":"Yes, we offer full API access including Dhru Fusion compatibility."}]}', 5),
('footer', 'CloudPulse Check', 'Professional device checking platform', '{"company":"CloudPulse Check","tagline":"Professional. Fast. Reliable.","links_services":["IMEI Check","Carrier Check","Blacklist Check","SIM Lock Status"],"links_platform":["Pricing","API","Documentation","Status"],"links_support":["Help Center","Contact","Terms","Privacy"]}', 99);

-- ============================================================
-- ACTIVITY LOGS & SECURITY
-- ============================================================

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED,
  `admin_id` BIGINT UNSIGNED,
  `action` VARCHAR(100) NOT NULL,
  `module` VARCHAR(50),
  `description` TEXT,
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_actlog_user` (`user_id`),
  KEY `idx_actlog_action` (`action`),
  KEY `idx_actlog_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255),
  `ip_address` VARCHAR(45) NOT NULL,
  `success` TINYINT(1) DEFAULT 0,
  `user_agent` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_ip` (`ip_address`),
  KEY `idx_login_email` (`email`),
  KEY `idx_login_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) NOT NULL,
  `hits` INT UNSIGNED DEFAULT 1,
  `window_start` TIMESTAMP NOT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ratelimit_key` (`key`),
  KEY `idx_rl_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CRON JOBS
-- ============================================================

CREATE TABLE IF NOT EXISTS `cron_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_name` VARCHAR(100) NOT NULL,
  `status` ENUM('started','completed','failed') DEFAULT 'started',
  `items_processed` INT DEFAULT 0,
  `error` TEXT,
  `duration_ms` INT UNSIGNED,
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cron_job` (`job_name`),
  KEY `idx_cron_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SYSTEM SETTINGS
-- ============================================================

CREATE TABLE IF NOT EXISTS `system_settings` (
  `key` VARCHAR(100) NOT NULL,
  `value` TEXT,
  `type` ENUM('string','integer','boolean','json','encrypted') DEFAULT 'string',
  `group` VARCHAR(50) DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `system_settings` (`key`, `value`, `type`, `group`) VALUES
('site_name', 'CloudPulse Check', 'string', 'general'),
('site_url', 'https://cloudpulsecheck.com', 'string', 'general'),
('site_email', 'contact@cloudpulsecheck.com', 'string', 'general'),
('site_timezone', 'UTC', 'string', 'general'),
('default_language', 'en', 'string', 'general'),
('default_currency', 'USD', 'string', 'general'),
('maintenance_mode', '0', 'boolean', 'general'),
('registration_enabled', '1', 'boolean', 'auth'),
('email_verification_required', '1', 'boolean', 'auth'),
('free_checks_enabled', '1', 'boolean', 'checks'),
('vapid_public_key', '', 'string', 'push'),
('vapid_private_key', '', 'encrypted', 'push'),
('misticpay_api_key', '', 'encrypted', 'payment'),
('misticpay_webhook_secret', '', 'encrypted', 'payment'),
('encryption_key', '', 'encrypted', 'security'),
('max_login_attempts', '5', 'integer', 'security'),
('login_lockout_minutes', '15', 'integer', 'security'),
('session_lifetime_hours', '24', 'integer', 'security'),
('check_result_retention_days', '365', 'integer', 'checks'),
('api_rate_limit_minute', '60', 'integer', 'api'),
('api_rate_limit_day', '10000', 'integer', 'api');

-- ============================================================
-- USER FAVORITES & GROUPS
-- ============================================================

CREATE TABLE IF NOT EXISTS `user_favorites` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `service_id` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `service_id`),
  CONSTRAINT `fk_fav_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fav_service` FOREIGN KEY (`service_id`) REFERENCES `services`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_groups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `discount_percent` DECIMAL(5,2) DEFAULT 0,
  `api_rate_multiplier` DECIMAL(5,2) DEFAULT 1.00,
  `is_default` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `user_groups` (`name`, `description`, `is_default`) VALUES ('Default', 'Standard users', 1);

CREATE TABLE IF NOT EXISTS `user_group_members` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `group_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`, `group_id`),
  CONSTRAINT `fk_ugm_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ugm_group` FOREIGN KEY (`group_id`) REFERENCES `user_groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT RESPONSE TEMPLATES
-- ============================================================

CREATE TABLE IF NOT EXISTS `support_response_templates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `content` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Default admin user (password: Admin@123456 — CHANGE AFTER INSTALL)
-- ============================================================
INSERT INTO `users` (`role_id`, `email`, `username`, `password_hash`, `first_name`, `last_name`, `email_verified`, `email_verified_at`, `status`)
VALUES (1, 'admin@cloudpulsecheck.com', 'admin', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'CloudPulse', 1, NOW(), 'active');

INSERT INTO `user_profiles` (`user_id`) SELECT id FROM users WHERE username = 'admin';
INSERT INTO `user_preferences` (`user_id`) SELECT id FROM users WHERE username = 'admin';
INSERT INTO `wallets` (`user_id`) SELECT id FROM users WHERE username = 'admin';