-- ========================================
-- HotspotSaaS - Database Schema
-- ========================================

CREATE DATABASE IF NOT EXISTS hotspot_saas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hotspot_saas;

-- ========================================
-- SUBSCRIPTION PLANS (set by admin)
-- ========================================
CREATE TABLE `tbl_subscription_plans` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `duration_days` INT NOT NULL DEFAULT 30,
    `max_routers` INT NOT NULL DEFAULT 1,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tbl_subscription_plans` (`name`, `description`, `price`, `duration_days`, `max_routers`)
VALUES 
('Basic', '1 Router - 30 days', 200.00, 30, 1),
('Standard', '5 Routers - 30 days', 500.00, 30, 5),
('Premium', 'Unlimited Routers - 30 days', 1000.00, 30, 999);

-- ========================================
-- BALANCE TRANSACTIONS (admin adds balance)
-- ========================================
CREATE TABLE `tbl_balance_transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `type` ENUM('credit','debit') NOT NULL,
    `description` VARCHAR(500) DEFAULT NULL,
    `added_by_admin` INT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- ROUTER SUBSCRIPTION PAYMENTS
-- ========================================
CREATE TABLE `tbl_router_payments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `router_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `duration_days` INT NOT NULL,
    `paid_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`),
    FOREIGN KEY (`router_id`) REFERENCES `tbl_routers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- HOTSPOT PLANS (per router, created by ISP user)
-- ========================================
CREATE TABLE `tbl_hotspot_plans` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `router_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `profile_name` VARCHAR(255) NOT NULL COMMENT 'MikroTik hotspot profile name',
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `validity` INT NOT NULL DEFAULT 1,
    `validity_unit` ENUM('Hours','Days','Weeks','Months') NOT NULL DEFAULT 'Days',
    `typebp` ENUM('Unlimited','Limited') NOT NULL DEFAULT 'Unlimited',
    `limit_type` ENUM('Time_Limit','Data_Limit','Both_Limit') DEFAULT NULL,
    `time_limit` INT DEFAULT NULL,
    `time_unit` ENUM('Mins','Hrs') DEFAULT NULL,
    `data_limit` DECIMAL(10,2) DEFAULT NULL,
    `data_unit` ENUM('MB','GB') DEFAULT NULL,
    `shared_users` INT NOT NULL DEFAULT 1,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`router_id`) REFERENCES `tbl_routers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- HOTSPOT TOKENS (generated by ISP user)
-- ========================================
CREATE TABLE `tbl_hotspot_tokens` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `router_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `token_number` VARCHAR(255) NOT NULL UNIQUE,
    `serial_number` VARCHAR(255) NOT NULL UNIQUE,
    `prefix` VARCHAR(50) DEFAULT NULL,
    `value` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('none','active','used','blocked') DEFAULT 'none',
    `generated_by` INT NOT NULL DEFAULT 0,
    `expiry_date` DATETIME DEFAULT NULL,
    `used_by_mac` VARCHAR(50) DEFAULT NULL,
    `used_date` DATETIME DEFAULT NULL,
    `first_used` DATETIME DEFAULT NULL,
    `last_used` DATETIME DEFAULT NULL,
    `used_count` INT NOT NULL DEFAULT 0,
    `activity_log` TEXT DEFAULT NULL COMMENT 'JSON log',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`router_id`) REFERENCES `tbl_routers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`plan_id`) REFERENCES `tbl_hotspot_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- HOTSPOT USERS (created when token used)
-- ========================================
CREATE TABLE `tbl_hotspot_users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT 'ISP user owner',
    `router_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `token_id` INT NOT NULL,
    `username` VARCHAR(255) NOT NULL COMMENT 'MAC address used as username',
    `mac_address` VARCHAR(50) NOT NULL,
    `ip_address` VARCHAR(50) DEFAULT NULL,
    `phone` VARCHAR(50) DEFAULT NULL,
    `status` ENUM('active','expired','disconnected') DEFAULT 'active',
    `connected_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME DEFAULT NULL,
    `last_seen` DATETIME DEFAULT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`router_id`) REFERENCES `tbl_routers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`token_id`) REFERENCES `tbl_hotspot_tokens`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- INCOME / SALES LOG
-- ========================================
CREATE TABLE `tbl_income_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT 'ISP user',
    `router_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `token_id` INT NOT NULL,
    `hotspot_user_id` INT DEFAULT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `mac_address` VARCHAR(50) DEFAULT NULL,
    `transaction_ref` VARCHAR(100) DEFAULT NULL,
    `note` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- ACTIVITY LOG
-- ========================================
CREATE TABLE `tbl_activity_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `router_id` INT DEFAULT NULL,
    `action` VARCHAR(255) NOT NULL,
    `details` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(50) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- SYSTEM SETTINGS
-- ========================================
CREATE TABLE `tbl_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT DEFAULT NULL,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tbl_settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'HotspotSaaS'),
('site_url', 'http://localhost'),
('currency', 'BDT'),
('currency_symbol', '৳'),
('mac_lock_enabled', '0'),
('registration_enabled', '1'),
('smtp_host', ''),
('smtp_port', '587'),
('smtp_user', ''),
('smtp_pass', ''),
('smtp_from', '');
