-- ================================================================
-- TCOM ISP Billing Platform — Single Database Setup
-- Import this file into phpMyAdmin on cPanel and you're ready.
-- ================================================================
-- Generated: 2026-03-06
-- Database: tcomco_mwananchi (all tables in one database)
-- ================================================================


-- Using your existing database
USE `tcomco_mwananchi`;

-- ----------------------------------------------------------------
-- Table: platform_users  (super-admin accounts — platform level)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `platform_users` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role`          VARCHAR(50)  NOT NULL DEFAULT 'admin',
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: audit_log  (platform-level activity log)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `username`   VARCHAR(100) DEFAULT NULL,
  `action`     VARCHAR(255) NOT NULL,
  `target`     VARCHAR(255) DEFAULT NULL,
  `details`    TEXT         DEFAULT NULL,
  `ip_address` VARCHAR(45)  DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: config  (key-value settings per tenant)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `config` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_name`    VARCHAR(100) NOT NULL,
  `key_value`   TEXT         DEFAULT NULL,
  `description` TEXT         DEFAULT NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key_name` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: packages  (WiFi plans)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `packages` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`            VARCHAR(50)  NOT NULL COMMENT 'Unique package code (e.g. 10_1hr)',
  `name`            VARCHAR(100) NOT NULL COMMENT 'Display name (e.g. 1 Hour)',
  `price`           DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `duration`        VARCHAR(50)  NOT NULL COMMENT 'Duration string (e.g. 1h, 24h, 7d, 30d)',
  `description`     TEXT         DEFAULT NULL,
  `note`            TEXT         DEFAULT NULL COMMENT 'Short note shown on login page',
  `color`           VARCHAR(30)  DEFAULT 'blue',
  `category`        VARCHAR(50)  DEFAULT 'quota' COMMENT 'quota, duration, etc.',
  `package_tier`    VARCHAR(20)  DEFAULT 'silver' COMMENT 'silver, bronze, premium',
  `base_package_id` INT UNSIGNED DEFAULT NULL COMMENT 'FK to parent package for multi-device tiers',
  `max_devices`     INT UNSIGNED NOT NULL DEFAULT 1,
  `download_speed`  INT UNSIGNED NOT NULL DEFAULT 2048 COMMENT 'kbps',
  `upload_speed`    INT UNSIGNED NOT NULL DEFAULT 1024 COMMENT 'kbps',
  `sort_order`      INT          NOT NULL DEFAULT 0,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_tier_active` (`package_tier`, `is_active`),
  KEY `idx_sort` (`sort_order`, `price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: mpesa_transactions  (M-Pesa / MegaPay payment records)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mpesa_transactions` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `checkout_request_id`   VARCHAR(100) DEFAULT NULL,
  `merchant_request_id`   VARCHAR(100) DEFAULT NULL,
  `phone_number`          VARCHAR(20)  NOT NULL,
  `amount`                DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `account_reference`     VARCHAR(100) DEFAULT NULL,
  `transaction_desc`      VARCHAR(255) DEFAULT NULL,
  `package_type`          VARCHAR(50)  DEFAULT NULL COMMENT 'Package code used',
  `mac_address`           VARCHAR(20)  DEFAULT NULL,
  `router_name`           VARCHAR(100) DEFAULT NULL COMMENT 'Router that handled activation',
  `status`                ENUM('pending','success','failed','cancelled') NOT NULL DEFAULT 'pending',
  `result_code`           VARCHAR(20)  DEFAULT NULL,
  `result_desc`           TEXT         DEFAULT NULL,
  `mpesa_receipt_number`  VARCHAR(50)  DEFAULT NULL COMMENT 'M-Pesa confirmation code',
  `transaction_date`      VARCHAR(50)  DEFAULT NULL,
  `callback_received`     TINYINT(1)   NOT NULL DEFAULT 0,
  `user_created`          TINYINT(1)   NOT NULL DEFAULT 0,
  `username`              VARCHAR(50)  DEFAULT NULL COMMENT 'Hotspot username assigned',
  `password`              VARCHAR(50)  DEFAULT NULL COMMENT 'Hotspot password assigned',
  `created_at`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_checkout`       (`checkout_request_id`),
  KEY `idx_merchant`       (`merchant_request_id`),
  KEY `idx_phone`          (`phone_number`),
  KEY `idx_status`         (`status`),
  KEY `idx_receipt`        (`mpesa_receipt_number`),
  KEY `idx_created`        (`created_at`),
  KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: hotspot_users  (active/expired MikroTik hotspot accounts)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `hotspot_users` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` INT UNSIGNED DEFAULT NULL COMMENT 'FK to mpesa_transactions.id',
  `username`       VARCHAR(64)  NOT NULL,
  `password`       VARCHAR(64)  NOT NULL,
  `mac_address`    VARCHAR(20)  DEFAULT NULL,
  `phone_number`   VARCHAR(20)  DEFAULT NULL,
  `package_type`   VARCHAR(50)  NOT NULL COMMENT 'Package code',
  `amount_paid`    DECIMAL(10,2) DEFAULT NULL,
  `profile`        VARCHAR(100) NOT NULL COMMENT 'MikroTik hotspot profile name',
  `expires_at`     DATETIME     DEFAULT NULL,
  `status`         ENUM('active','expired') NOT NULL DEFAULT 'active',
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_username`   (`username`),
  KEY `idx_status`     (`status`),
  KEY `idx_expires`    (`expires_at`),
  KEY `idx_phone`      (`phone_number`),
  KEY `idx_txn`        (`transaction_id`),
  KEY `idx_status_exp` (`status`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: vouchers  (pre-generated voucher codes)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vouchers` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`         VARCHAR(30)  NOT NULL COMMENT 'Voucher code (e.g. ABCD-1234-EFGH)',
  `package_type` VARCHAR(50)  NOT NULL COMMENT 'Package code this voucher unlocks',
  `phone_number` VARCHAR(20)  DEFAULT NULL,
  `max_uses`     INT UNSIGNED NOT NULL DEFAULT 1,
  `use_count`    INT UNSIGNED NOT NULL DEFAULT 0,
  `used`         TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '1 when fully redeemed',
  `note`         TEXT         DEFAULT NULL,
  `expires_at`   DATETIME     DEFAULT NULL COMMENT 'NULL = no expiry',
  `used_by_mac`  VARCHAR(20)  DEFAULT NULL,
  `used_at`      DATETIME     DEFAULT NULL,
  `router_name`  VARCHAR(100) DEFAULT NULL,
  `username`     VARCHAR(64)  DEFAULT NULL COMMENT 'Hotspot username assigned on use',
  `password`     VARCHAR(64)  DEFAULT NULL COMMENT 'Hotspot password assigned on use',
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_used`     (`used`),
  KEY `idx_package`  (`package_type`),
  KEY `idx_created`  (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: free_tests  (one free hour per MAC address)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `free_tests` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mac_address` VARCHAR(17)  NOT NULL,
  `username`    VARCHAR(64)  NOT NULL,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mac` (`mac_address`),
  KEY `idx_mac` (`mac_address`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: package_voucher_usage  (tracks each device use of a voucher)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `package_voucher_usage` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `voucher_id`    INT UNSIGNED NOT NULL COMMENT 'FK to vouchers.id',
  `username`      VARCHAR(64)  NOT NULL,
  `password`      VARCHAR(64)  NOT NULL,
  `mac_address`   VARCHAR(20)  NOT NULL,
  `router_name`   VARCHAR(100) DEFAULT NULL,
  `device_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `used_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_voucher`  (`voucher_id`),
  KEY `idx_mac`      (`mac_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Table: system_logs  (application logs)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `system_logs` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level`      VARCHAR(20)  NOT NULL DEFAULT 'info' COMMENT 'info, warning, error, debug',
  `message`    TEXT         NOT NULL,
  `context`    TEXT         DEFAULT NULL COMMENT 'JSON additional data',
  `ip_address` VARCHAR(45)  DEFAULT NULL,
  `user_agent` TEXT         DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_level`   (`level`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System configuration
INSERT IGNORE INTO `config` (`key_name`, `key_value`, `description`) VALUES
('system_name',              'Mtaani WiFi',                                 'WiFi network display name'),
('router_name',              'Mtaani-Router',                               'Default MikroTik router identity'),
('megapay_api_key',          'MGPYLEmzCFXM',                                'MegaPay API key'),
('megapay_email',            'jramtech25@gmail.com',                        'MegaPay account email'),
('callback_url',             'https://mwananchi.tcom.co.ke/mpesa-callback.php',       'Payment callback URL'),
('mpesa_business_shortcode', '4189519',                                     'M-Pesa Till/Paybill number (shown on login page)'),
('support_phone',            '',                                            'Support phone number'),
('payment_provider',         'daraja',                                      'Payment provider: megapay or daraja'),
('daraja_consumer_key',      '8lVguz5cNQ0BDb63CbImV6sEqfoLQeo5AoFFvpHLkJoyqBys', 'Daraja API consumer key'),
('daraja_consumer_secret',   'ajGR7bGvglYS4VSp6i613w968KlDiHu4oV3KACOYRongpYYhWBLDZp3Pft9Vs0lt', 'Daraja API consumer secret'),
('daraja_passkey',           'c1adef2bce5e688feacaa06b26b7c5099ade683b4116cb9ea4da0c3b3d8e6754', 'Daraja API passkey for STK Push'),
('daraja_shortcode',         '4189519',                                     'Daraja business shortcode'),
('daraja_environment',       'production',                                  'Daraja environment: sandbox or production');

-- ================================================================
-- PACKAGES  (Silver = 1 device, Bronze = 3 devices, Premium = 3 devices fast)
-- ================================================================

-- Silver tier (1 device)
INSERT IGNORE INTO `packages` (`code`, `name`, `price`, `duration`, `description`, `note`, `color`, `category`, `package_tier`, `max_devices`, `download_speed`, `upload_speed`, `sort_order`, `is_active`) VALUES
('10_1hr',    '1 Hour',     10.00,  '1h',   '1 hour internet access',        '1 Device • Unlimited Data',  'green',  'quota', 'silver', 1, 2048,  1024, 1, 1),
('20_3hr',    '3 Hours',    20.00,  '3h',   '3 hours internet access',       '1 Device • Unlimited Data',  'blue',   'quota', 'silver', 1, 2048,  1024, 2, 1),
('35_12hr',   '12 Hours',   35.00,  '12h',  '12 hours internet access',      '1 Device • Unlimited Data',  'blue',   'quota', 'silver', 1, 3072,  1536, 3, 1),
('50_24hr',   '24 Hours',   50.00,  '24h',  '24 hours internet access',      '1 Device • Unlimited Data',  'purple', 'quota', 'silver', 1, 3072,  1536, 4, 1),
('100_3d',    '3 Days',    100.00,  '3d',   '3 days internet access',        '1 Device • Unlimited Data',  'orange', 'quota', 'silver', 1, 4096,  2048, 5, 1),
('250_7d',    '7 Days',    250.00,  '7d',   '7 days internet access',        '1 Device • Unlimited Data',  'red',    'quota', 'silver', 1, 5120,  2560, 6, 1),
('500_14d',   '14 Days',   500.00,  '14d',  '14 days internet access',       '1 Device • Unlimited Data',  'red',    'quota', 'silver', 1, 5120,  2560, 7, 1),
('1000_30d',  '30 Days',  1000.00,  '30d',  '30 days internet access',       '1 Device • Unlimited Data',  'gold',   'quota', 'silver', 1, 8192,  4096, 8, 1);

-- Bronze tier (3 devices — family)
INSERT IGNORE INTO `packages` (`code`, `name`, `price`, `duration`, `description`, `note`, `color`, `category`, `package_tier`, `base_package_id`, `max_devices`, `download_speed`, `upload_speed`, `sort_order`, `is_active`) VALUES
('B_50_24hr',  '24 Hours',   80.00,  '24h',  '24h family pack',   '3 Devices • Family Pack',  'blue',   'quota', 'bronze', 4, 3, 3072, 1536, 1, 1),
('B_100_3d',   '3 Days',    180.00,  '3d',   '3 day family pack', '3 Devices • Family Pack',  'orange', 'quota', 'bronze', 5, 3, 4096, 2048, 2, 1),
('B_250_7d',   '7 Days',    450.00,  '7d',   '7 day family pack', '3 Devices • Family Pack',  'red',    'quota', 'bronze', 6, 3, 5120, 2560, 3, 1),
('B_1000_30d', '30 Days',  1800.00,  '30d',  '30 day family',     '3 Devices • Family Pack',  'gold',   'quota', 'bronze', 8, 3, 8192, 4096, 4, 1);

-- Premium tier (3 devices — fast business)
INSERT IGNORE INTO `packages` (`code`, `name`, `price`, `duration`, `description`, `note`, `color`, `category`, `package_tier`, `base_package_id`, `max_devices`, `download_speed`, `upload_speed`, `sort_order`, `is_active`) VALUES
('P_100_24hr',  '24 Hours',   150.00,  '24h', '24h business pack',  '3 Devices • Fast Business Pack', 'blue',   'quota', 'premium', 4, 3,  8192, 4096, 1, 1),
('P_250_3d',    '3 Days',     350.00,  '3d',  '3 day business',     '3 Devices • Fast Business Pack', 'orange', 'quota', 'premium', 5, 3, 10240, 5120, 2, 1),
('P_500_7d',    '7 Days',     700.00,  '7d',  '7 day business',     '3 Devices • Fast Business Pack', 'red',    'quota', 'premium', 6, 3, 10240, 5120, 3, 1),
('P_2000_30d',  '30 Days',   2500.00,  '30d', '30 day business',    '3 Devices • Fast Business Pack', 'gold',   'quota', 'premium', 8, 3, 15360, 8192, 4, 1);


-- ================================================================
-- DONE!  Next steps:
-- 1. Import this SQL file via phpMyAdmin (into tcomco_mwananchi)
-- 2. Make sure tcomco_kim user has ALL PRIVILEGES on tcomco_mwananchi
-- 3. Upload the tcom/ folder to public_html (so files are at mwananchi.tcom.co.ke/*)
-- 4. Login: https://mwananchi.tcom.co.ke/admin/login.php  →  admin / admin123
-- 5. Go to Settings and update MikroTik router credentials
-- 6. Set up cron: */2 * * * * php /home/tcomco/public_html/expiry-cron.php
-- ================================================================
