-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 02, 2025 at 09:27 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

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: `billing_app_2`
--

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

--
-- Table structure for table `activity_logs`
--

CREATE TABLE `activity_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `action_type` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `related_id` bigint(20) UNSIGNED DEFAULT NULL,
  `related_table` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `log_date` date NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (year(`log_date`) * 100 + month(`log_date`))
(
PARTITION p2025_01 VALUES LESS THAN (202502) ENGINE=InnoDB,
PARTITION p2025_02 VALUES LESS THAN (202503) ENGINE=InnoDB,
PARTITION p2025_03 VALUES LESS THAN (202504) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

--
-- Dumping data for table `activity_logs`
--

INSERT INTO `activity_logs` (`id`, `user_id`, `action_type`, `description`, `related_id`, `related_table`, `ip_address`, `log_date`, `created_at`) VALUES
(1, 1, 'Package Created', 'Package \'GPON-25-MBPS-800TK\' created (Price: 800). Router: PIEAL-VI-RT-ACCESS. Pool: GPON-25-MBPS-800TK.', 2, 'packages', '::1', '0000-00-00', '2025-12-03 02:23:14');

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

--
-- Table structure for table `advance_bills`
--

CREATE TABLE `advance_bills` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `month_year` varchar(7) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` datetime DEFAULT current_timestamp(),
  `used` tinyint(1) DEFAULT 0,
  `used_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `bills`
--

CREATE TABLE `bills` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `month_year` char(7) NOT NULL COMMENT 'YYYY-MM',
  `status` tinyint(1) DEFAULT 0 COMMENT '0=unpaid, 1=paid',
  `due_date` date NOT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_date` date NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (year(`created_date`) * 100 + month(`created_date`))
(
PARTITION p2025_12 VALUES LESS THAN (202601) ENGINE=InnoDB,
PARTITION p2026_01 VALUES LESS THAN (202602) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

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

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `nid_number` varchar(20) DEFAULT NULL,
  `photo` varchar(200) DEFAULT NULL,
  `connection_type` enum('FTTH','Wireless','Home WiFi') DEFAULT NULL,
  `customer_category` enum('Home','Corporate','Shop','Office') DEFAULT NULL,
  `area` varchar(100) 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 ROW_FORMAT=COMPRESSED;

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

--
-- Table structure for table `dept_permission_defaults`
--

CREATE TABLE `dept_permission_defaults` (
  `id` int(10) UNSIGNED NOT NULL,
  `dept_role` varchar(50) NOT NULL,
  `permission_key` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `bill_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `invoice_number` varchar(30) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `month_year` char(7) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `invoice_branding`
--

CREATE TABLE `invoice_branding` (
  `owner_id` int(10) UNSIGNED NOT NULL,
  `business_name` varchar(100) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `mac_cache`
--

CREATE TABLE `mac_cache` (
  `oui` varchar(6) NOT NULL,
  `vendor` varchar(100) DEFAULT NULL,
  `device` varchar(100) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `source` varchar(50) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `packages` (
  `id` int(10) UNSIGNED NOT NULL,
  `router_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `remote_pool` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `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`, `remote_pool`, `price`, `created_at`, `updated_at`) VALUES
(1, 1, 'GPON-25-MBPS-800TK', 'GPON-25-MBPS-800TK', 800.00, '2025-12-02 20:22:37', '2025-12-02 20:22:37');

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

--
-- Table structure for table `panel_users`
--

CREATE TABLE `panel_users` (
  `id` int(10) UNSIGNED NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `company_name` varchar(200) DEFAULT NULL,
  `main_role` enum('superadmin','admin','reseller','subreseller') NOT NULL,
  `dept_role` enum('none','technical','noc','accounts','sales') DEFAULT 'none',
  `status` tinyint(1) DEFAULT 1 COMMENT '0=inactive, 1=active',
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `sms_permission` tinyint(1) DEFAULT 0,
  `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 `panel_users`
--

INSERT INTO `panel_users` (`id`, `email`, `password`, `name`, `company_name`, `main_role`, `dept_role`, `status`, `parent_id`, `phone`, `address`, `sms_permission`, `created_at`, `updated_at`) VALUES
(1, 'admin@gmail.com', 'admin', 'System Admin', NULL, 'superadmin', 'none', 1, NULL, NULL, NULL, 0, '2025-12-02 19:30:01', '2025-12-02 19:59:14');

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `bill_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `method` varchar(20) DEFAULT 'cash',
  `paid_date` date NOT NULL,
  `paid_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (year(`paid_date`) * 100 + month(`paid_date`))
(
PARTITION p2025_12 VALUES LESS THAN (202601) ENGINE=InnoDB,
PARTITION p2026_01 VALUES LESS THAN (202602) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

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

--
-- Table structure for table `permissions_master`
--

CREATE TABLE `permissions_master` (
  `id` int(10) UNSIGNED NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `parent_group` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `pppoe_status`
--

CREATE TABLE `pppoe_status` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `status` tinyint(1) DEFAULT 0 COMMENT '0=offline, 1=online',
  `uptime` varchar(100) DEFAULT NULL,
  `last_logged_out` varchar(100) DEFAULT NULL,
  `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 `pppoe_users`
--

CREATE TABLE `pppoe_users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `router_id` int(10) UNSIGNED NOT NULL,
  `profile` varchar(50) NOT NULL,
  `expiry_date` date NOT NULL,
  `owner_id` int(10) UNSIGNED NOT NULL,
  `status` tinyint(1) DEFAULT 1 COMMENT '0=disabled, 1=enabled',
  `created_by` int(10) UNSIGNED NOT 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 ROW_FORMAT=COMPRESSED;

--
-- Dumping data for table `pppoe_users`
--

INSERT INTO `pppoe_users` (`id`, `username`, `password`, `router_id`, `profile`, `expiry_date`, `owner_id`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'mahabub', '1234', 1, 'GPON-25-MBPS-800TK', '2026-01-02', 1, 1, 1, '2025-12-02 20:25:27', '2025-12-02 20:25:27'),
(8, 'rased2025', '2025', 1, 'GPON-25-MBPS-800TK', '2026-01-02', 1, 1, 1, '2025-12-02 20:26:09', '2025-12-02 20:26:09'),
(9, 'rased', '2025', 1, 'GPON-25-MBPS-800TK', '2026-01-02', 1, 1, 1, '2025-12-02 20:26:27', '2025-12-02 20:26:27');

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

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

CREATE TABLE `routers` (
  `id` int(10) UNSIGNED NOT NULL,
  `owner_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `api_port` smallint(5) UNSIGNED DEFAULT 8728,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `status` tinyint(1) 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 `routers`
--

INSERT INTO `routers` (`id`, `owner_id`, `name`, `ip`, `api_port`, `username`, `password`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'PIEAL-VI-RT-ACCESS', '103.58.75.74', 7475, 'gFcL', 'gfcl321#gfcl', 1, '2025-12-02 20:08:31', '2025-12-02 20:11:36');

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

--
-- Table structure for table `sms_gateways`
--

CREATE TABLE `sms_gateways` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `api_url` varchar(255) NOT NULL,
  `api_user` varchar(100) DEFAULT NULL,
  `api_pass` varchar(100) DEFAULT NULL,
  `sender_id` varchar(50) DEFAULT NULL,
  `type` enum('masking','non-masking') DEFAULT 'non-masking',
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sms_gateways`
--

INSERT INTO `sms_gateways` (`id`, `name`, `api_url`, `api_user`, `api_pass`, `sender_id`, `type`, `is_active`) VALUES
(1, 'Default Gateway', 'https://api.example.com/sms', NULL, NULL, NULL, 'non-masking', 1);

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

--
-- Table structure for table `sms_gateway_assigned`
--

CREATE TABLE `sms_gateway_assigned` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `gateway_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `sms_logs`
--

CREATE TABLE `sms_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `gateway_id` int(10) UNSIGNED DEFAULT NULL,
  `owner_id` int(10) UNSIGNED DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `log_date` date NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (year(`log_date`) * 100 + month(`log_date`))
(
PARTITION p2025_12 VALUES LESS THAN (202601) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

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

--
-- Table structure for table `sms_schedule`
--

CREATE TABLE `sms_schedule` (
  `id` int(10) UNSIGNED NOT NULL,
  `template_id` int(10) UNSIGNED DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `send_to` varchar(50) DEFAULT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `schedule_time` time DEFAULT NULL,
  `schedule_date` date DEFAULT NULL,
  `recurrence` varchar(20) DEFAULT 'once',
  `is_sent` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `sms_templates`
--

CREATE TABLE `sms_templates` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `action` varchar(50) NOT NULL DEFAULT 'custom',
  `template` text DEFAULT NULL,
  `role` varchar(30) DEFAULT 'global',
  `reseller_id` int(10) UNSIGNED DEFAULT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `temp_extend`
--

CREATE TABLE `temp_extend` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `extend_days` smallint(5) UNSIGNED NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `month_year` char(7) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_permissions`
--

CREATE TABLE `user_permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `allowed` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_traffic_logs`
--

CREATE TABLE `user_traffic_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `session_bytes_in` bigint(20) UNSIGNED DEFAULT 0,
  `session_bytes_out` bigint(20) UNSIGNED DEFAULT 0,
  `created_at` datetime 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
PARTITION BY RANGE (year(`created_at`) * 100 + month(`created_at`))
(
PARTITION p2025_12 VALUES LESS THAN (202601) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD PRIMARY KEY (`id`,`log_date`),
  ADD KEY `idx_user_action` (`user_id`,`action_type`),
  ADD KEY `idx_related` (`related_table`,`related_id`);

--
-- Indexes for table `advance_bills`
--
ALTER TABLE `advance_bills`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_month` (`user_id`,`month_year`),
  ADD KEY `idx_used` (`used`);

--
-- Indexes for table `bills`
--
ALTER TABLE `bills`
  ADD PRIMARY KEY (`id`,`created_date`),
  ADD KEY `idx_user_month` (`user_id`,`month_year`),
  ADD KEY `idx_status_due` (`status`,`due_date`),
  ADD KEY `idx_month_year` (`month_year`),
  ADD KEY `idx_owner_month_status` (`user_id`,`month_year`,`status`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_area` (`area`),
  ADD KEY `idx_connection_type` (`connection_type`),
  ADD KEY `idx_phone_area` (`phone`,`area`);

--
-- Indexes for table `dept_permission_defaults`
--
ALTER TABLE `dept_permission_defaults`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_dept_permission` (`dept_role`,`permission_key`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_number` (`invoice_number`),
  ADD KEY `idx_bill_id` (`bill_id`),
  ADD KEY `idx_user_month` (`user_id`,`month_year`);

--
-- Indexes for table `invoice_branding`
--
ALTER TABLE `invoice_branding`
  ADD PRIMARY KEY (`owner_id`);

--
-- Indexes for table `mac_cache`
--
ALTER TABLE `mac_cache`
  ADD PRIMARY KEY (`oui`),
  ADD KEY `idx_updated` (`updated_at`);

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_router` (`router_id`),
  ADD KEY `idx_name` (`name`);

--
-- Indexes for table `panel_users`
--
ALTER TABLE `panel_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_parent_status` (`parent_id`,`status`),
  ADD KEY `idx_role` (`main_role`,`dept_role`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`,`paid_date`),
  ADD KEY `idx_bill_id` (`bill_id`),
  ADD KEY `idx_user_date` (`user_id`,`paid_date`);

--
-- Indexes for table `permissions_master`
--
ALTER TABLE `permissions_master`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `permission_key` (`permission_key`),
  ADD KEY `idx_group` (`parent_group`);

--
-- Indexes for table `pppoe_status`
--
ALTER TABLE `pppoe_status`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `pppoe_users`
--
ALTER TABLE `pppoe_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `idx_router_owner` (`router_id`,`owner_id`),
  ADD KEY `idx_expiry_status` (`expiry_date`,`status`),
  ADD KEY `idx_owner_status` (`owner_id`,`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_username_status` (`username`,`status`),
  ADD KEY `idx_expiry_owner` (`expiry_date`,`owner_id`);

--
-- Indexes for table `routers`
--
ALTER TABLE `routers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_owner_status` (`owner_id`,`status`),
  ADD KEY `idx_ip` (`ip`);

--
-- Indexes for table `sms_gateways`
--
ALTER TABLE `sms_gateways`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_active` (`is_active`);

--
-- Indexes for table `sms_gateway_assigned`
--
ALTER TABLE `sms_gateway_assigned`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_user_gateway` (`user_id`,`gateway_id`);

--
-- Indexes for table `sms_logs`
--
ALTER TABLE `sms_logs`
  ADD PRIMARY KEY (`id`,`log_date`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_owner_date` (`owner_id`,`log_date`);

--
-- Indexes for table `sms_schedule`
--
ALTER TABLE `sms_schedule`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_schedule_date` (`schedule_date`,`is_sent`);

--
-- Indexes for table `sms_templates`
--
ALTER TABLE `sms_templates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_reseller` (`reseller_id`);

--
-- Indexes for table `temp_extend`
--
ALTER TABLE `temp_extend`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_user_month` (`user_id`,`month_year`),
  ADD KEY `idx_end_date` (`end_date`);

--
-- Indexes for table `user_permissions`
--
ALTER TABLE `user_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_user_permission` (`user_id`,`permission_key`),
  ADD KEY `idx_user` (`user_id`);

--
-- Indexes for table `user_traffic_logs`
--
ALTER TABLE `user_traffic_logs`
  ADD PRIMARY KEY (`id`,`created_at`),
  ADD KEY `idx_user_date` (`user_id`,`created_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_logs`
--
ALTER TABLE `activity_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `advance_bills`
--
ALTER TABLE `advance_bills`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bills`
--
ALTER TABLE `bills`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `pppoe_status`
--
ALTER TABLE `pppoe_status`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `pppoe_users`
--
ALTER TABLE `pppoe_users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- 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_gateways`
--
ALTER TABLE `sms_gateways`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

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

--
-- AUTO_INCREMENT for table `sms_logs`
--
ALTER TABLE `sms_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

--
-- AUTO_INCREMENT for table `temp_extend`
--
ALTER TABLE `temp_extend`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `user_traffic_logs`
--
ALTER TABLE `user_traffic_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
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 */;