-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `snet_hotspot`
--

DELIMITER $$
END$$
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 ;

--
-- Triggers `active_sessions`
--
DELIMITER $$
END
$$
DELIMITER ;

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

--
-- 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 `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 $$
    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 `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';

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

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

--
-- Structure for view `today_transactions`
--

--
-- 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 `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 `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 table `failed_payments`
--
ALTER TABLE `failed_payments`
  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 `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 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;

