-- Create PPPoE Pools Table
CREATE TABLE IF NOT EXISTS `pppoe_pools` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `pool_name` varchar(100) NOT NULL,
  `router_id` bigint unsigned NOT NULL,
  `ip_range_start` varchar(45) NOT NULL,
  `ip_range_end` varchar(45) NOT NULL,
  `subnet_mask` varchar(20) DEFAULT NULL,
  `gateway` varchar(45) DEFAULT NULL,
  `dns_primary` varchar(45) DEFAULT NULL,
  `dns_secondary` varchar(45) DEFAULT NULL,
  `total_ips` int NOT NULL DEFAULT '0',
  `used_ips` int NOT NULL DEFAULT '0',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_pools_router_id_index` (`router_id`),
  KEY `pppoe_pools_status_index` (`status`),
  KEY `pppoe_pools_pool_name_index` (`pool_name`),
  CONSTRAINT `pppoe_pools_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create PPPoE Plans Table
CREATE TABLE IF NOT EXISTS `pppoe_plans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(100) NOT NULL,
  `bandwidth_upload` varchar(50) NOT NULL,
  `bandwidth_download` varchar(50) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `validity_days` int NOT NULL DEFAULT '30',
  `description` text,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_plans_status_index` (`status`),
  KEY `pppoe_plans_plan_name_index` (`plan_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create PPPoE Customers Table
CREATE TABLE IF NOT EXISTS `pppoe_customers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text,
  `plan_id` bigint unsigned NOT NULL,
  `router_id` bigint unsigned NOT NULL,
  `pool_id` bigint unsigned DEFAULT NULL,
  `assigned_ip` varchar(45) DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `expiry_date` datetime DEFAULT NULL,
  `status` enum('active','expired','suspended','disabled') NOT NULL DEFAULT 'active',
  `auto_renew` tinyint(1) NOT NULL DEFAULT '0',
  `last_sync_at` timestamp NULL DEFAULT NULL,
  `sync_status` enum('pending','synced','failed') NOT NULL DEFAULT 'pending',
  `sync_error` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pppoe_customers_username_unique` (`username`),
  KEY `pppoe_customers_plan_id_index` (`plan_id`),
  KEY `pppoe_customers_router_id_index` (`router_id`),
  KEY `pppoe_customers_pool_id_index` (`pool_id`),
  KEY `pppoe_customers_status_index` (`status`),
  KEY `pppoe_customers_expiry_date_index` (`expiry_date`),
  KEY `pppoe_customers_sync_status_index` (`sync_status`),
  CONSTRAINT `pppoe_customers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `pppoe_plans` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `pppoe_customers_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pppoe_customers_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pppoe_pools` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create PPPoE IP Addresses Table
CREATE TABLE IF NOT EXISTS `pppoe_ip_addresses` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` bigint unsigned NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `customer_id` bigint unsigned DEFAULT NULL,
  `status` enum('available','assigned','reserved') NOT NULL DEFAULT 'available',
  `assigned_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pppoe_ip_addresses_pool_id_ip_address_unique` (`pool_id`,`ip_address`),
  KEY `pppoe_ip_addresses_customer_id_index` (`customer_id`),
  KEY `pppoe_ip_addresses_status_index` (`status`),
  CONSTRAINT `pppoe_ip_addresses_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pppoe_pools` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pppoe_ip_addresses_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `pppoe_customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create PPPoE Balance Ledger Table
CREATE TABLE IF NOT EXISTS `pppoe_balance_ledger` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint unsigned NOT NULL,
  `transaction_type` enum('credit','debit','refund','adjustment') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `balance_before` decimal(10,2) NOT NULL,
  `balance_after` decimal(10,2) NOT NULL,
  `description` text,
  `reference_id` varchar(100) DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_balance_ledger_customer_id_index` (`customer_id`),
  KEY `pppoe_balance_ledger_transaction_type_index` (`transaction_type`),
  KEY `pppoe_balance_ledger_reference_id_index` (`reference_id`),
  CONSTRAINT `pppoe_balance_ledger_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `pppoe_customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

