-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 06, 2026 at 09:51 AM
-- 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: `snet_hotspot`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`snetcotz`@`localhost` PROCEDURE `clean_expired_vouchers` ()   BEGIN
    UPDATE vouchers 
    SET status = 'expired' 
    WHERE status = 'active' 
    AND expires_at < NOW();
END$$

CREATE DEFINER=`snetcotz`@`localhost` PROCEDURE `get_daily_revenue` (IN `start_date` DATE, IN `end_date` DATE)   BEGIN
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as transaction_count,
        SUM(amount) as total_amount,
        COUNT(DISTINCT phone) as unique_customers
    FROM transactions
    WHERE status = 'completed'
    AND DATE(created_at) BETWEEN start_date AND end_date
    GROUP BY DATE(created_at)
    ORDER BY date DESC;
END$$

CREATE DEFINER=`snetcotz`@`localhost` PROCEDURE `get_popular_packages` (IN `limit_count` INT)   BEGIN
    SELECT 
        p.name,
        p.price,
        COUNT(t.id) as sales_count,
        SUM(t.amount) as total_revenue
    FROM packages p
    LEFT JOIN transactions t ON p.id = t.package_id AND t.status = 'completed'
    GROUP BY p.id, p.name, p.price
    ORDER BY sales_count DESC
    LIMIT limit_count;
END$$

DELIMITER ;

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

--
-- Table structure for table `active_sessions`
--

CREATE TABLE `active_sessions` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `mac_address` varchar(50) NOT NULL COMMENT 'Client MAC address',
  `ip_address` varchar(45) NOT NULL COMMENT 'Client IP address',
  `session_id` varchar(100) DEFAULT NULL COMMENT 'MikroTik session ID',
  `bytes_in` bigint(20) DEFAULT 0,
  `bytes_out` bigint(20) DEFAULT 0,
  `login_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_activity` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `status` enum('active','idle','logout') DEFAULT 'active',
  `expires_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Currently active user sessions';

--
-- Triggers `active_sessions`
--
DELIMITER $$
CREATE TRIGGER `update_voucher_on_use` AFTER INSERT ON `active_sessions` FOR EACH ROW BEGIN
    UPDATE vouchers 
    SET status = 'used', 
        used_at = NOW(),
        used_by_mac = NEW.mac_address,
        used_by_ip = NEW.ip_address
    WHERE voucher_code = NEW.username 
    AND status = 'active';
END
$$
DELIMITER ;

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

--
-- Stand-in structure for view `active_vouchers`
-- (See below for the actual view)
--
CREATE TABLE `active_vouchers` (
`id` int(11)
,`voucher_code` varchar(50)
,`password` varchar(50)
,`package_id` int(11)
,`package_name` varchar(100)
,`hours` int(11)
,`status` enum('active','used','expired','cancelled')
,`transaction_id` int(11)
,`used_by_mac` varchar(50)
,`used_by_ip` varchar(45)
,`used_at` timestamp
,`created_at` timestamp
,`expires_at` timestamp
,`phone` varchar(20)
,`amount` decimal(10,2)
);

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

--
-- Table structure for table `admin_users`
--

CREATE TABLE `admin_users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL COMMENT 'Hashed password',
  `email` varchar(100) DEFAULT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `role` enum('admin','manager','viewer') DEFAULT 'viewer',
  `is_active` tinyint(1) DEFAULT 1,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Administrator users';

--
-- Dumping data for table `admin_users`
--

INSERT INTO `admin_users` (`id`, `username`, `password`, `email`, `full_name`, `role`, `is_active`, `last_login`, `created_at`) VALUES
(1, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@snet.co.tz', 'System Administrator', 'admin', 1, NULL, '2026-06-03 11:00:07');

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

--
-- Table structure for table `failed_payments`
--

CREATE TABLE `failed_payments` (
  `id` int(11) NOT NULL,
  `external_id` varchar(100) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `package_id` int(11) NOT NULL,
  `error_code` varchar(50) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `retry_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Failed payment attempts';

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

--
-- Table structure for table `hotspot_users`
--

CREATE TABLE `hotspot_users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `voucher_id` int(11) DEFAULT NULL COMMENT 'Related voucher ID',
  `package_id` int(11) DEFAULT NULL,
  `hours` int(11) DEFAULT NULL COMMENT 'Duration in hours',
  `bytes_used` bigint(20) DEFAULT 0 COMMENT 'Bytes used',
  `bytes_remaining` bigint(20) DEFAULT NULL COMMENT 'Bytes remaining if limited',
  `status` enum('active','disabled','expired') DEFAULT 'active',
  `last_login` timestamp NULL DEFAULT NULL,
  `last_logout` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Local copy of MikroTik hotspot users';

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

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

CREATE TABLE `packages` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL COMMENT 'Package name',
  `price` decimal(10,2) NOT NULL COMMENT 'Price in TZS',
  `hours` int(11) NOT NULL COMMENT 'Duration in hours',
  `data_limit` int(11) DEFAULT NULL COMMENT 'Data limit in MB (NULL = unlimited)',
  `speed_limit` int(11) DEFAULT NULL COMMENT 'Speed limit in kbps (NULL = no limit)',
  `is_active` tinyint(1) DEFAULT 1 COMMENT 'Whether package is available',
  `display_order` int(11) DEFAULT 0 COMMENT 'Order to display',
  `description` text DEFAULT NULL COMMENT 'Package description',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Internet packages for sale';

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

INSERT INTO `packages` (`id`, `name`, `price`, `hours`, `data_limit`, `speed_limit`, `is_active`, `display_order`, `description`, `created_at`, `updated_at`) VALUES
(1, 'S-NET 6 HOUR', 500.00, 6, NULL, NULL, 1, 1, '6 hours of high-speed internet access', '2026-06-03 11:00:06', NULL),
(2, 'S-NET 24 HOURS', 1000.00, 24, NULL, NULL, 1, 2, 'Full day of unlimited internet', '2026-06-03 11:00:06', NULL),
(3, 'S-NET 3 DAYS', 3000.00, 72, NULL, NULL, 1, 3, '3 days of continuous access', '2026-06-03 11:00:06', NULL),
(4, 'S-NET 7 DAYS', 5000.00, 168, NULL, NULL, 1, 4, 'One week of high-speed WiFi', '2026-06-03 11:00:06', NULL),
(5, 'S-NET 15 DAYS', 10000.00, 360, NULL, NULL, 1, 5, '15 days of unlimited browsing', '2026-06-03 11:00:06', NULL),
(6, 'S-NET 30 DAYS', 20000.00, 720, NULL, NULL, 1, 6, 'Full month of premium internet', '2026-06-03 11:00:06', NULL);

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

--
-- Table structure for table `payment_logs`
--

CREATE TABLE `payment_logs` (
  `id` int(11) NOT NULL,
  `external_id` varchar(100) DEFAULT NULL COMMENT 'Transaction external ID',
  `request_type` varchar(50) DEFAULT NULL COMMENT 'API endpoint called',
  `request_payload` text DEFAULT NULL COMMENT 'Request sent to AzamPay',
  `response_payload` text DEFAULT NULL COMMENT 'Response from AzamPay',
  `http_code` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Logs of all AzamPay API interactions';

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

--
-- Stand-in structure for view `revenue_summary`
-- (See below for the actual view)
--
CREATE TABLE `revenue_summary` (
`date` date
,`total_transactions` bigint(21)
,`total_revenue` decimal(32,2)
,`average_transaction` decimal(14,6)
,`unique_customers` bigint(21)
);

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL COMMENT 'Setting name',
  `setting_value` text DEFAULT NULL COMMENT 'Setting value',
  `setting_type` enum('string','integer','boolean','json') DEFAULT 'string',
  `description` text DEFAULT NULL COMMENT 'Setting description',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Application settings';

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_type`, `description`, `updated_at`) VALUES
(1, 'demo_mode', 'true', 'boolean', 'Enable/disable demo mode', '2026-06-03 11:00:07'),
(2, 'site_url', 'https://s-net.co.tz', 'string', 'Captive portal URL', '2026-06-03 11:00:07'),
(3, 'support_phone', '0786933185', 'string', 'Customer support phone number', '2026-06-03 11:00:07'),
(4, 'marquee_text', '⚡ S-NET • HighSpeed WiFi • M-Pesa, Airtel, YAS, Halotel • 24/7 Support', 'string', 'Scrolling banner text', '2026-06-03 11:00:07'),
(5, 'enable_sms', 'false', 'boolean', 'Send SMS after payment', '2026-06-03 11:00:07'),
(6, 'enable_email', 'false', 'boolean', 'Send email after payment', '2026-06-03 11:00:07'),
(7, 'payment_timeout', '60', 'integer', 'Payment timeout in seconds', '2026-06-03 11:00:07'),
(8, 'max_retry_attempts', '3', 'integer', 'Maximum payment retry attempts', '2026-06-03 11:00:07');

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

--
-- Stand-in structure for view `today_transactions`
-- (See below for the actual view)
--
CREATE TABLE `today_transactions` (
`id` int(11)
,`transaction_id` varchar(100)
,`external_id` varchar(100)
,`phone` varchar(20)
,`amount` decimal(10,2)
,`package_id` int(11)
,`package_name` varchar(100)
,`hours` int(11)
,`provider` varchar(50)
,`status` enum('pending','completed','failed','cancelled')
,`payment_method` varchar(50)
,`additional_data` text
,`ip_address` varchar(45)
,`mac_address` varchar(50)
,`created_at` timestamp
,`updated_at` timestamp
,`voucher_code` varchar(50)
);

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

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

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `transaction_id` varchar(100) NOT NULL COMMENT 'AzamPay transaction ID',
  `external_id` varchar(100) NOT NULL COMMENT 'Unique ID generated by S-NET',
  `phone` varchar(20) NOT NULL COMMENT 'Customer phone number',
  `amount` decimal(10,2) NOT NULL COMMENT 'Amount paid in TZS',
  `package_id` int(11) NOT NULL COMMENT 'ID of purchased package',
  `package_name` varchar(100) NOT NULL COMMENT 'Name of package',
  `hours` int(11) NOT NULL COMMENT 'Duration in hours',
  `provider` varchar(50) DEFAULT NULL COMMENT 'Mobile network provider',
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending' COMMENT 'Payment status',
  `payment_method` varchar(50) DEFAULT 'mobile_money' COMMENT 'Payment method',
  `additional_data` text DEFAULT NULL COMMENT 'Additional JSON data',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'Customer IP address',
  `mac_address` varchar(50) DEFAULT NULL COMMENT 'Customer MAC address',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Payment transactions from AzamPay';

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` (`id`, `transaction_id`, `external_id`, `phone`, `amount`, `package_id`, `package_name`, `hours`, `provider`, `status`, `payment_method`, `additional_data`, `ip_address`, `mac_address`, `created_at`, `updated_at`) VALUES
(1, 'DEMO_TXN_1780498402_6256', 'DEMO_1780498402_5940', '0788344848', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-03 14:53:22', NULL),
(2, 'DEMO_TXN_1780636951_5717', 'DEMO_1780636951_8908', '0788344848', 10000.00, 5, 'S-NET 15 DAYS', 360, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-05 05:22:31', NULL),
(3, 'DEMO_TXN_1780663397_2010', 'DEMO_1780663397_2804', '0741123456', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'unknown', '2026-06-05 12:43:17', NULL),
(4, 'DEMO_TXN_1780663606_5757', 'DEMO_1780663606_3336', '0741123456', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'unknown', '2026-06-05 12:46:46', NULL),
(5, 'DEMO_TXN_1780663969_3078', 'DEMO_1780663969_8194', '0788344848', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-05 12:52:49', NULL),
(6, 'DEMO_TXN_1780666330_6783', 'DEMO_1780666330_6324', '0741248242', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-05 13:32:10', NULL),
(7, 'DEMO_TXN_1780667616_4303', 'DEMO_1780667616_6654', '0741248242', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-05 13:53:36', NULL),
(8, 'DEMO_TXN_1780673742_1402', 'DEMO_1780673742_7503', '0741248242', 500.00, 1, 'S-NET 6 HOUR', 6, '0', 'completed', 'mobile_money', NULL, NULL, 'xx:xx:xx:xx:xx:xx', '2026-06-05 15:35:42', NULL);

--
-- Triggers `transactions`
--
DELIMITER $$
CREATE TRIGGER `update_usage_stats` AFTER UPDATE ON `transactions` FOR EACH ROW BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        INSERT INTO usage_stats (date, total_payments, total_revenue, total_users)
        VALUES (CURDATE(), 1, NEW.amount, 1)
        ON DUPLICATE KEY UPDATE
            total_payments = total_payments + 1,
            total_revenue = total_revenue + NEW.amount,
            total_users = (SELECT COUNT(DISTINCT phone) FROM transactions WHERE status = 'completed' AND DATE(created_at) = CURDATE());
    END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `usage_stats`
--

CREATE TABLE `usage_stats` (
  `id` int(11) NOT NULL,
  `date` date NOT NULL,
  `total_payments` int(11) DEFAULT 0 COMMENT 'Number of successful payments',
  `total_revenue` decimal(15,2) DEFAULT 0.00 COMMENT 'Total revenue for the day',
  `total_users` int(11) DEFAULT 0 COMMENT 'Number of unique users',
  `total_data_used` bigint(20) DEFAULT 0 COMMENT 'Total data used in MB',
  `peak_connections` int(11) DEFAULT 0 COMMENT 'Peak concurrent connections',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Daily usage statistics';

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

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

CREATE TABLE `vouchers` (
  `id` int(11) NOT NULL,
  `voucher_code` varchar(50) NOT NULL COMMENT 'Username for hotspot login',
  `password` varchar(50) NOT NULL COMMENT 'Password for hotspot login',
  `package_id` int(11) NOT NULL COMMENT 'Package ID',
  `package_name` varchar(100) DEFAULT NULL COMMENT 'Package name',
  `hours` int(11) NOT NULL COMMENT 'Valid duration in hours',
  `status` enum('active','used','expired','cancelled') DEFAULT 'active' COMMENT 'Voucher status',
  `transaction_id` int(11) DEFAULT NULL COMMENT 'Related transaction ID',
  `used_by_mac` varchar(50) DEFAULT NULL COMMENT 'MAC address that used this voucher',
  `used_by_ip` varchar(45) DEFAULT NULL COMMENT 'IP address that used this voucher',
  `used_at` timestamp NULL DEFAULT NULL COMMENT 'When voucher was used',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL COMMENT 'Voucher expiry time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='Hotspot vouchers generated after payment';

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

INSERT INTO `vouchers` (`id`, `voucher_code`, `password`, `package_id`, `package_name`, `hours`, `status`, `transaction_id`, `used_by_mac`, `used_by_ip`, `used_at`, `created_at`, `expires_at`) VALUES
(1, 'SNET_D005F14D', '62CY7Z', 1, 'S-NET 6 HOUR', 6, 'active', 1, NULL, NULL, NULL, '2026-06-03 14:53:22', '2026-06-03 20:53:22'),
(2, 'SNET_E24E45D6', 'J4U0EB', 5, 'S-NET 15 DAYS', 360, 'active', 2, NULL, NULL, NULL, '2026-06-05 05:22:31', '2026-06-20 05:22:31'),
(3, 'SNET_3DA7FE87', 'DZK74N', 1, 'S-NET 6 HOUR', 6, 'active', 3, NULL, NULL, NULL, '2026-06-05 12:43:17', '2026-06-05 18:43:17'),
(4, 'SNET_49D6B72F', '25NTQD', 1, 'S-NET 6 HOUR', 6, 'active', 4, NULL, NULL, NULL, '2026-06-05 12:46:46', '2026-06-05 18:46:46'),
(5, 'SNET_0AD32815', '43SQRF', 1, 'S-NET 6 HOUR', 6, 'active', 5, NULL, NULL, NULL, '2026-06-05 12:52:49', '2026-06-05 18:52:49'),
(6, 'SNET_D39A2A01', 'Q9DMLZ', 1, 'S-NET 6 HOUR', 6, 'active', 6, NULL, NULL, NULL, '2026-06-05 13:32:10', '2026-06-05 19:32:10'),
(7, 'SNET_3494B8EB', 'PU92VD', 1, 'S-NET 6 HOUR', 6, 'active', 7, NULL, NULL, NULL, '2026-06-05 13:53:36', '2026-06-05 19:53:36'),
(8, 'SNET_544CCD65', '98C2DG', 1, 'S-NET 6 HOUR', 6, 'active', 8, NULL, NULL, NULL, '2026-06-05 15:35:42', '2026-06-05 21:35:42');

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

--
-- Table structure for table `voucher_usage_history`
--

CREATE TABLE `voucher_usage_history` (
  `id` int(11) NOT NULL,
  `voucher_id` int(11) NOT NULL,
  `mac_address` varchar(50) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `session_duration` int(11) DEFAULT NULL COMMENT 'Session duration in seconds',
  `data_used` bigint(20) DEFAULT 0 COMMENT 'Data used in bytes',
  `logout_reason` varchar(100) DEFAULT NULL,
  `login_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `logout_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='History of voucher usage';

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

--
-- Table structure for table `webhook_logs`
--

CREATE TABLE `webhook_logs` (
  `id` int(11) NOT NULL,
  `external_id` varchar(100) DEFAULT NULL,
  `payload` text DEFAULT NULL COMMENT 'Raw webhook payload',
  `processed` tinyint(1) DEFAULT 0 COMMENT 'Whether webhook was processed',
  `error_message` text DEFAULT NULL COMMENT 'Error if processing failed',
  `received_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_swedish_ci COMMENT='AzamPay webhook call logs';

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

--
-- Structure for view `active_vouchers`
--
DROP TABLE IF EXISTS `active_vouchers`;

CREATE ALGORITHM=UNDEFINED DEFINER=`snetcotz`@`localhost` SQL SECURITY DEFINER VIEW `active_vouchers`  AS SELECT `v`.`id` AS `id`, `v`.`voucher_code` AS `voucher_code`, `v`.`password` AS `password`, `v`.`package_id` AS `package_id`, `v`.`package_name` AS `package_name`, `v`.`hours` AS `hours`, `v`.`status` AS `status`, `v`.`transaction_id` AS `transaction_id`, `v`.`used_by_mac` AS `used_by_mac`, `v`.`used_by_ip` AS `used_by_ip`, `v`.`used_at` AS `used_at`, `v`.`created_at` AS `created_at`, `v`.`expires_at` AS `expires_at`, `t`.`phone` AS `phone`, `t`.`amount` AS `amount` FROM (`vouchers` `v` left join `transactions` `t` on(`v`.`transaction_id` = `t`.`id`)) WHERE `v`.`status` = 'active' AND `v`.`expires_at` > current_timestamp() ORDER BY `v`.`expires_at` ASC ;

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

--
-- Structure for view `revenue_summary`
--
DROP TABLE IF EXISTS `revenue_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`snetcotz`@`localhost` SQL SECURITY DEFINER VIEW `revenue_summary`  AS SELECT cast(`transactions`.`created_at` as date) AS `date`, count(0) AS `total_transactions`, sum(`transactions`.`amount`) AS `total_revenue`, avg(`transactions`.`amount`) AS `average_transaction`, count(distinct `transactions`.`phone`) AS `unique_customers` FROM `transactions` WHERE `transactions`.`status` = 'completed' GROUP BY cast(`transactions`.`created_at` as date) ;

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

--
-- Structure for view `today_transactions`
--
DROP TABLE IF EXISTS `today_transactions`;

CREATE ALGORITHM=UNDEFINED DEFINER=`snetcotz`@`localhost` SQL SECURITY DEFINER VIEW `today_transactions`  AS SELECT `t`.`id` AS `id`, `t`.`transaction_id` AS `transaction_id`, `t`.`external_id` AS `external_id`, `t`.`phone` AS `phone`, `t`.`amount` AS `amount`, `t`.`package_id` AS `package_id`, `t`.`package_name` AS `package_name`, `t`.`hours` AS `hours`, `t`.`provider` AS `provider`, `t`.`status` AS `status`, `t`.`payment_method` AS `payment_method`, `t`.`additional_data` AS `additional_data`, `t`.`ip_address` AS `ip_address`, `t`.`mac_address` AS `mac_address`, `t`.`created_at` AS `created_at`, `t`.`updated_at` AS `updated_at`, `v`.`voucher_code` AS `voucher_code` FROM (`transactions` `t` left join `vouchers` `v` on(`t`.`id` = `v`.`transaction_id`)) WHERE cast(`t`.`created_at` as date) = curdate() ORDER BY `t`.`created_at` DESC ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `active_sessions`
--
ALTER TABLE `active_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_mac_address` (`mac_address`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_login_time` (`login_time`),
  ADD KEY `idx_active_sessions_mac_status` (`mac_address`,`status`),
  ADD KEY `idx_active_sessions_expires` (`expires_at`),
  ADD KEY `idx_active_sessions_status` (`status`);

--
-- Indexes for table `admin_users`
--
ALTER TABLE `admin_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_username` (`username`),
  ADD KEY `idx_is_active` (`is_active`);

--
-- Indexes for table `failed_payments`
--
ALTER TABLE `failed_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_external_id` (`external_id`);

--
-- Indexes for table `hotspot_users`
--
ALTER TABLE `hotspot_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_username` (`username`),
  ADD KEY `idx_voucher_id` (`voucher_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_display_order` (`display_order`);

--
-- Indexes for table `payment_logs`
--
ALTER TABLE `payment_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_external_id` (`external_id`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_request_type` (`request_type`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_setting_key` (`setting_key`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_transaction_id` (`transaction_id`),
  ADD UNIQUE KEY `idx_external_id` (`external_id`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_package_id` (`package_id`),
  ADD KEY `idx_transactions_status_created` (`status`,`created_at`);

--
-- Indexes for table `usage_stats`
--
ALTER TABLE `usage_stats`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_date` (`date`),
  ADD KEY `idx_date_range` (`date`),
  ADD KEY `idx_usage_stats_date` (`date`);

--
-- Indexes for table `vouchers`
--
ALTER TABLE `vouchers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_voucher_code` (`voucher_code`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_expires_at` (`expires_at`),
  ADD KEY `idx_transaction_id` (`transaction_id`),
  ADD KEY `idx_used_by_mac` (`used_by_mac`),
  ADD KEY `idx_vouchers_status_expires` (`status`,`expires_at`);

--
-- Indexes for table `voucher_usage_history`
--
ALTER TABLE `voucher_usage_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_voucher_id` (`voucher_id`),
  ADD KEY `idx_mac_address` (`mac_address`),
  ADD KEY `idx_login_time` (`login_time`);

--
-- Indexes for table `webhook_logs`
--
ALTER TABLE `webhook_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_external_id` (`external_id`),
  ADD KEY `idx_processed` (`processed`),
  ADD KEY `idx_received_at` (`received_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `active_sessions`
--
ALTER TABLE `active_sessions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin_users`
--
ALTER TABLE `admin_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `failed_payments`
--
ALTER TABLE `failed_payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `hotspot_users`
--
ALTER TABLE `hotspot_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `packages`
--
ALTER TABLE `packages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `payment_logs`
--
ALTER TABLE `payment_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `usage_stats`
--
ALTER TABLE `usage_stats`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `voucher_usage_history`
--
ALTER TABLE `voucher_usage_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `webhook_logs`
--
ALTER TABLE `webhook_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `hotspot_users`
--
ALTER TABLE `hotspot_users`
  ADD CONSTRAINT `fk_hotspot_users_voucher` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `vouchers`
--
ALTER TABLE `vouchers`
  ADD CONSTRAINT `fk_vouchers_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `voucher_usage_history`
--
ALTER TABLE `voucher_usage_history`
  ADD CONSTRAINT `fk_voucher_usage` FOREIGN KEY (`voucher_id`) REFERENCES `vouchers` (`id`) ON DELETE 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 */;