-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 20, 2026 at 07:27 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `hotspot_zone`
--

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(10) UNSIGNED NOT NULL,
  `username` varchar(80) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(120) DEFAULT NULL,
  `role` enum('super_admin','admin','manager','editor') NOT NULL DEFAULT 'admin',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `username`, `password_hash`, `full_name`, `role`, `is_active`, `last_login_at`, `created_at`, `updated_at`) VALUES
(1, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Super Admin', 'super_admin', 1, NULL, '2026-11-20 16:57:14', '2026-11-20 16:57:14'),
(2, 'noman', '$2y$10$jd1Zlrd4pwItaj0kWYSASuf0mQn3qsDfu.XQB2izEIQo2mFG7bTyi', 'System Super Admin', 'super_admin', 1, NULL, '2026-11-20 16:57:14', '2026-11-20 16:57:14');

-- --------------------------------------------------------

--
-- Table structure for table `packages`
--

CREATE TABLE `packages` (
  `id` int(10) UNSIGNED NOT NULL,
  `router_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `duration_minutes` int(10) UNSIGNED NOT NULL DEFAULT 60,
  `speed_limit` varchar(60) DEFAULT NULL,
  `mikrotik_profile` varchar(80) NOT NULL DEFAULT 'default',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `packages`
--

INSERT INTO `packages` (`id`, `router_id`, `name`, `price`, `duration_minutes`, `speed_limit`, `mikrotik_profile`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 1, '1 Hour Express', 10.00, 60, '10M/10M', 'default', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15'),
(2, 1, '6 Hours Stream', 25.00, 360, '15M/15M', 'default', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15'),
(3, 1, '1 Day Unlimited', 50.00, 1440, '20M/20M', 'default', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15'),
(4, 1, '7 Days Mega Pack', 200.00, 10080, '25M/25M', 'default', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15');

-- --------------------------------------------------------

--
-- Table structure for table `payment_methods`
--

CREATE TABLE `payment_methods` (
  `id` int(10) UNSIGNED NOT NULL,
  `provider` varchar(40) NOT NULL,
  `label` varchar(100) NOT NULL,
  `account_number` varchar(60) NOT NULL,
  `account_type` enum('Personal','Agent','Merchant') NOT NULL DEFAULT 'Personal',
  `instruction` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_methods`
--

INSERT INTO `payment_methods` (`id`, `provider`, `label`, `account_number`, `account_type`, `instruction`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'bkash', 'bKash Auto', '01700000000', 'Personal', 'Send Money করে TrxID দিন', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15'),
(2, 'nagad', 'Nagad Auto', '01800000000', 'Personal', 'Send Money করে TrxID দিন', 1, '2026-11-20 16:57:15', '2026-11-20 16:57:15');

-- --------------------------------------------------------

--
-- Table structure for table `portal_settings`
--

CREATE TABLE `portal_settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `portal_name` varchar(120) NOT NULL DEFAULT 'Hotspot Zone',
  `portal_tagline` varchar(255) DEFAULT 'High-Speed Wi-Fi & Instant Auto-Billing',
  `logo_path` varchar(255) DEFAULT NULL,
  `support_phone` varchar(40) DEFAULT NULL,
  `ad_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `ad_badge` varchar(80) DEFAULT 'Special Offer',
  `ad_title` varchar(160) DEFAULT 'Superfast Fiber Wi-Fi',
  `ad_text` text DEFAULT NULL,
  `ad_image_path` varchar(255) DEFAULT NULL,
  `ad_link_url` varchar(255) DEFAULT NULL,
  `popup_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `popup_frequency` enum('always','session') NOT NULL DEFAULT 'always',
  `popup_delay_ms` int(10) UNSIGNED NOT NULL DEFAULT 400,
  `popup_badge` varchar(80) DEFAULT 'Flash Offer',
  `popup_title` varchar(160) DEFAULT 'Welcome to High-Speed Wi-Fi!',
  `popup_text` text DEFAULT NULL,
  `popup_image_path` varchar(255) DEFAULT NULL,
  `popup_cta_text` varchar(80) DEFAULT 'View Packages',
  `popup_cta_url` varchar(255) DEFAULT '#packages',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `sms_webhook_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `sms_webhook_secret` varchar(128) NOT NULL DEFAULT '',
  `sms_sender_filter` varchar(255) NOT NULL DEFAULT 'bKash,Nagad,Rocket'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `portal_settings`
--

INSERT INTO `portal_settings` (`id`, `portal_name`, `portal_tagline`, `logo_path`, `support_phone`, `ad_enabled`, `ad_badge`, `ad_title`, `ad_text`, `ad_image_path`, `ad_link_url`, `popup_enabled`, `popup_frequency`, `popup_delay_ms`, `popup_badge`, `popup_title`, `popup_text`, `popup_image_path`, `popup_cta_text`, `popup_cta_url`, `updated_at`, `sms_webhook_enabled`, `sms_webhook_secret`, `sms_sender_filter`) VALUES
(1, 'Hotspot Zone', 'High-Speed Wi-Fi & Instant Auto-Billing', NULL, '01700000000', 1, 'Special Offer', 'Instant High-Speed Wi-Fi', 'Pay via bKash or Nagad and get your Wi-Fi PIN automatically.', NULL, '', 1, 'always', 400, 'Flash Offer', 'Welcome to High-Speed Wi-Fi!', 'Choose a Wi-Fi package, pay via bKash or Nagad, and connect immediately.', 'assets/uploads/popup_banner_1795195041_b2cfc0.jpg', 'Buy Package Now', '#packages', '2026-11-20 18:25:35', 1, 'whk_8bd7cf6e176d5a2012bb510495d7ecf7', 'bKash,Nagad,Rocket');

-- --------------------------------------------------------

--
-- Table structure for table `resellers`
--

CREATE TABLE `resellers` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(120) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `username` varchar(80) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `zone_name` varchar(120) DEFAULT NULL,
  `commission_percent` decimal(5,2) NOT NULL DEFAULT 10.00,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `resellers`
--

INSERT INTO `resellers` (`id`, `name`, `phone`, `username`, `password_hash`, `zone_name`, `commission_percent`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Main Zone Reseller', '01700000001', 'reseller_1', '$2y$10$YeX8W/tsqmD0AiXY95FH5uBJ0J4YPuEBAIeAmOpaXzgOyfQOBKUpK', 'Main Hotspot Zone', 10.00, 965.00, 1, '2026-11-20 16:57:14', '2026-11-20 17:34:21');

-- --------------------------------------------------------

--
-- Table structure for table `reseller_ledger`
--

CREATE TABLE `reseller_ledger` (
  `id` int(10) UNSIGNED NOT NULL,
  `reseller_id` int(10) UNSIGNED NOT NULL,
  `type` enum('credit','debit','commission') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_after` decimal(12,2) NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_by` varchar(80) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `reseller_ledger`
--

INSERT INTO `reseller_ledger` (`id`, `reseller_id`, `type`, `amount`, `balance_after`, `note`, `created_by`, `created_at`) VALUES
(1, 1, 'credit', 10.00, 10.00, 'Admin Wallet Top-Up', 'noman', '2026-11-20 17:34:04'),
(2, 1, 'credit', 1000.00, 1010.00, 'Admin Wallet Top-Up', 'noman', '2026-11-20 17:34:11'),
(3, 1, 'debit', 45.00, 965.00, 'Generated 5x 1 Hour Express on Core Hotspot Router (R1-261120-183421)', 'Main Zone Reseller', '2026-11-20 17:34:21');

-- --------------------------------------------------------

--
-- Table structure for table `reseller_topups`
--

CREATE TABLE `reseller_topups` (
  `id` int(10) UNSIGNED NOT NULL,
  `reseller_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `trx_id` varchar(80) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `reseller_topups`
--

INSERT INTO `reseller_topups` (`id`, `reseller_id`, `amount`, `payment_method`, `trx_id`, `status`, `admin_note`, `created_at`, `processed_at`) VALUES
(1, 1, 500.00, 'bKash', 'KJHJKHDJS', 'rejected', NULL, '2026-11-20 18:01:52', '2026-11-21 00:02:16');

-- --------------------------------------------------------

--
-- Table structure for table `routers`
--

CREATE TABLE `routers` (
  `id` int(10) UNSIGNED NOT NULL,
  `reseller_id` int(10) UNSIGNED DEFAULT NULL,
  `zone_name` varchar(120) NOT NULL,
  `ip_address` varchar(64) NOT NULL,
  `api_port` int(10) UNSIGNED NOT NULL DEFAULT 8728,
  `username` varchar(80) NOT NULL,
  `password` varchar(255) NOT NULL,
  `hotspot_profile` varchar(80) NOT NULL DEFAULT 'default',
  `status` enum('online','offline') NOT NULL DEFAULT 'online',
  `last_checked_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `routers`
--

INSERT INTO `routers` (`id`, `reseller_id`, `zone_name`, `ip_address`, `api_port`, `username`, `password`, `hotspot_profile`, `status`, `last_checked_at`, `created_at`, `updated_at`) VALUES
(1, NULL, 'Core Hotspot Router', '192.168.88.1', 8728, 'admin', '', 'default', 'offline', NULL, '2026-11-20 16:57:15', '2026-11-20 16:59:52');

-- --------------------------------------------------------

--
-- Table structure for table `sms_webhooks`
--

CREATE TABLE `sms_webhooks` (
  `id` int(10) UNSIGNED NOT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `trx_id` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `provider` varchar(32) DEFAULT NULL,
  `raw_message` text NOT NULL,
  `matched_status` enum('pending','matched','ignored','failed') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `trx_id` varchar(80) NOT NULL,
  `provider` varchar(40) NOT NULL,
  `sender_phone` varchar(30) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `package_id` int(10) UNSIGNED DEFAULT NULL,
  `voucher_id` int(10) UNSIGNED DEFAULT NULL,
  `mac_address` varchar(32) DEFAULT NULL,
  `status` enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `raw_payload` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `vouchers`
--

CREATE TABLE `vouchers` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `package_id` int(10) UNSIGNED DEFAULT NULL,
  `reseller_id` int(10) UNSIGNED DEFAULT NULL,
  `batch_code` varchar(64) DEFAULT NULL,
  `mac_address` varchar(32) DEFAULT NULL,
  `purchased_by_phone` varchar(30) DEFAULT NULL,
  `status` enum('unused','active','used','expired') NOT NULL DEFAULT 'unused',
  `pushed_to_mikrotik` tinyint(1) NOT NULL DEFAULT 0,
  `activated_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `vouchers`
--

INSERT INTO `vouchers` (`id`, `code`, `password`, `package_id`, `reseller_id`, `batch_code`, `mac_address`, `purchased_by_phone`, `status`, `pushed_to_mikrotik`, `activated_at`, `expires_at`, `created_at`, `updated_at`) VALUES
(1, 'HZ58146', 'HZ58146', 1, NULL, 'BATCH-261120-175936', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 16:59:39', '2026-11-20 16:59:39'),
(2, 'HZBC8FE', 'HZBC8FE', 1, NULL, 'BATCH-261120-175936', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 16:59:39', '2026-11-20 16:59:39'),
(3, 'HZ9DDC5', 'HZ9DDC5', 1, NULL, 'BATCH-261120-175936', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 16:59:39', '2026-11-20 16:59:39'),
(4, 'HZD5079', 'HZD5079', 1, NULL, 'BATCH-261120-175936', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 16:59:39', '2026-11-20 16:59:39'),
(5, 'HZBCF77', 'HZBCF77', 1, NULL, 'BATCH-261120-175936', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 16:59:39', '2026-11-20 16:59:39'),
(6, 'RS538A5', 'RS538A5', 1, 1, 'R1-261120-183421', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 17:34:23', '2026-11-20 17:34:23'),
(7, 'RS72AB0', 'RS72AB0', 1, 1, 'R1-261120-183421', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 17:34:23', '2026-11-20 17:34:23'),
(8, 'RSFCF27', 'RSFCF27', 1, 1, 'R1-261120-183421', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 17:34:23', '2026-11-20 17:34:23'),
(9, 'RSCEB74', 'RSCEB74', 1, 1, 'R1-261120-183421', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 17:34:23', '2026-11-20 17:34:23'),
(10, 'RS285D2', 'RS285D2', 1, 1, 'R1-261120-183421', NULL, NULL, 'unused', 0, NULL, NULL, '2026-11-20 17:34:23', '2026-11-20 17:34:23');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_admins_username` (`username`),
  ADD KEY `idx_admins_role_active` (`role`,`is_active`);

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_packages_active_price` (`is_active`,`price`),
  ADD KEY `fk_packages_router` (`router_id`);

--
-- Indexes for table `payment_methods`
--
ALTER TABLE `payment_methods`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_payment_methods_provider_active` (`provider`,`is_active`);

--
-- Indexes for table `portal_settings`
--
ALTER TABLE `portal_settings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `resellers`
--
ALTER TABLE `resellers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_resellers_username` (`username`),
  ADD KEY `idx_resellers_phone` (`phone`),
  ADD KEY `idx_resellers_active` (`is_active`);

--
-- Indexes for table `reseller_ledger`
--
ALTER TABLE `reseller_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ledger_reseller` (`reseller_id`,`created_at`);

--
-- Indexes for table `reseller_topups`
--
ALTER TABLE `reseller_topups`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_topups_status` (`status`,`created_at`),
  ADD KEY `fk_topups_reseller` (`reseller_id`);

--
-- Indexes for table `routers`
--
ALTER TABLE `routers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_routers_status` (`status`),
  ADD KEY `idx_routers_ip_port` (`ip_address`,`api_port`),
  ADD KEY `idx_routers_reseller` (`reseller_id`);

--
-- Indexes for table `sms_webhooks`
--
ALTER TABLE `sms_webhooks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_trx_id` (`trx_id`),
  ADD KEY `idx_matched_status` (`matched_status`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_transactions_trx_id` (`trx_id`),
  ADD KEY `idx_transactions_status_created` (`status`,`created_at`),
  ADD KEY `idx_transactions_sender_phone` (`sender_phone`),
  ADD KEY `fk_transactions_package` (`package_id`),
  ADD KEY `fk_transactions_voucher` (`voucher_id`);

--
-- Indexes for table `vouchers`
--
ALTER TABLE `vouchers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_vouchers_code` (`code`),
  ADD KEY `idx_vouchers_status` (`status`),
  ADD KEY `idx_vouchers_mac` (`mac_address`),
  ADD KEY `idx_vouchers_phone` (`purchased_by_phone`),
  ADD KEY `idx_vouchers_batch` (`batch_code`),
  ADD KEY `fk_vouchers_package` (`package_id`),
  ADD KEY `fk_vouchers_reseller` (`reseller_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `packages`
--
ALTER TABLE `packages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `payment_methods`
--
ALTER TABLE `payment_methods`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `portal_settings`
--
ALTER TABLE `portal_settings`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `resellers`
--
ALTER TABLE `resellers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `reseller_ledger`
--
ALTER TABLE `reseller_ledger`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `reseller_topups`
--
ALTER TABLE `reseller_topups`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `routers`
--
ALTER TABLE `routers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `sms_webhooks`
--
ALTER TABLE `sms_webhooks`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vouchers`
--
ALTER TABLE `vouchers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `packages`
--
ALTER TABLE `packages`
  ADD CONSTRAINT `fk_packages_router` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `reseller_ledger`
--
ALTER TABLE `reseller_ledger`
  ADD CONSTRAINT `fk_ledger_reseller` FOREIGN KEY (`reseller_id`) REFERENCES `resellers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `reseller_topups`
--
ALTER TABLE `reseller_topups`
  ADD CONSTRAINT `fk_topups_reseller` FOREIGN KEY (`reseller_id`) REFERENCES `resellers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `routers`
--
ALTER TABLE `routers`
  ADD CONSTRAINT `fk_routers_reseller` FOREIGN KEY (`reseller_id`) REFERENCES `resellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `fk_transactions_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transactions_voucher` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `vouchers`
--
ALTER TABLE `vouchers`
  ADD CONSTRAINT `fk_vouchers_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_vouchers_reseller` FOREIGN KEY (`reseller_id`) REFERENCES `resellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
