-- WiFi Billing System Database Schema
-- Created for TigaPutri Integration

-- Create database
CREATE DATABASE IF NOT EXISTS wifi_billing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wifi_billing;

-- Table: admins (Admin users)
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('super_admin', 'admin', 'operator') DEFAULT 'operator',
    is_active BOOLEAN DEFAULT TRUE,
    two_factor_secret VARCHAR(32) NULL,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table: packages (Internet packages)
CREATE TABLE packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    duration_type ENUM('hours', 'days', 'weeks', 'months') NOT NULL,
    duration_value INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    data_limit BIGINT NULL COMMENT 'in bytes, NULL for unlimited',
    speed_limit VARCHAR(20) DEFAULT '1M/1M' COMMENT 'upload/download speed',
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admins(id)
);

-- Table: vouchers (Generated vouchers)
CREATE TABLE vouchers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(20) UNIQUE NOT NULL,
    package_id INT NOT NULL,
    status ENUM('active', 'used', 'expired') DEFAULT 'active',
    generated_by INT NOT NULL,
    used_by_customer INT NULL,
    used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (package_id) REFERENCES packages(id),
    FOREIGN KEY (generated_by) REFERENCES admins(id),
    INDEX idx_code (code),
    INDEX idx_status (status),
    INDEX idx_expires (expires_at)
);

-- Table: customers (Customer users for mobile app)
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NULL,
    full_name VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    referral_code VARCHAR(10) UNIQUE,
    referred_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES customers(id)
);

-- Table: customer_transactions (Customer balance transactions)
CREATE TABLE customer_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    type ENUM('topup', 'purchase', 'sale', 'commission') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    reference_id VARCHAR(50) NULL,
    tigaputri_trx_id VARCHAR(50) NULL,
    status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_customer (customer_id),
    INDEX idx_type (type),
    INDEX idx_status (status)
);

-- Table: active_users (Currently connected users)
CREATE TABLE active_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    voucher_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    mac_address VARCHAR(17) NOT NULL,
    ip_address VARCHAR(15) NOT NULL,
    session_id VARCHAR(100) UNIQUE NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    bytes_in BIGINT DEFAULT 0,
    bytes_out BIGINT DEFAULT 0,
    session_timeout TIMESTAMP NOT NULL,
    FOREIGN KEY (voucher_id) REFERENCES vouchers(id),
    INDEX idx_session (session_id),
    INDEX idx_mac (mac_address),
    INDEX idx_timeout (session_timeout)
);

-- Table: user_sessions (Historical user sessions)
CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    voucher_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    mac_address VARCHAR(17) NOT NULL,
    ip_address VARCHAR(15) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    login_time TIMESTAMP NOT NULL,
    logout_time TIMESTAMP NULL,
    bytes_in BIGINT DEFAULT 0,
    bytes_out BIGINT DEFAULT 0,
    disconnect_reason ENUM('normal', 'timeout', 'admin', 'error') DEFAULT 'normal',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voucher_id) REFERENCES vouchers(id),
    INDEX idx_voucher (voucher_id),
    INDEX idx_login_time (login_time)
);

-- Table: sales (Voucher sales records)
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    voucher_id INT NOT NULL,
    sold_by_admin INT NULL,
    sold_by_customer INT NULL,
    sale_price DECIMAL(10,2) NOT NULL,
    commission DECIMAL(10,2) DEFAULT 0.00,
    payment_method ENUM('cash', 'transfer', 'balance') DEFAULT 'cash',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voucher_id) REFERENCES vouchers(id),
    FOREIGN KEY (sold_by_admin) REFERENCES admins(id),
    FOREIGN KEY (sold_by_customer) REFERENCES customers(id),
    INDEX idx_sale_date (created_at)
);

-- Table: settings (System settings)
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(50) NOT NULL,
    key_name VARCHAR(100) NOT NULL,
    value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_setting (category, key_name)
);

-- Table: tigaputri_transactions (TigaPutri API transactions)
CREATE TABLE tigaputri_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    trx_id VARCHAR(50) UNIQUE NOT NULL,
    product_code VARCHAR(20) NOT NULL,
    destination VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    tigaputri_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_trx_id (trx_id),
    INDEX idx_status (status)
);

-- Table: notifications (System notifications)
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('admin', 'customer') NOT NULL,
    target_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_target (type, target_id),
    INDEX idx_read (is_read)
);

-- Insert default admin
INSERT INTO admins (username, email, password, full_name, role) VALUES 
('admin', 'admin@wifibilling.com', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj/RK.s5uO.G', 'Super Administrator', 'super_admin');
-- Password: admin123

-- Insert default settings
INSERT INTO settings (category, key_name, value, description) VALUES
('system', 'app_name', 'WiFi Billing System', 'Nama aplikasi'),
('system', 'app_version', '1.0.0', 'Versi aplikasi'),
('system', 'timezone', 'Asia/Jakarta', 'Timezone sistem'),
('hotspot', 'portal_title', 'WiFi Hotspot Login', 'Judul portal login'),
('hotspot', 'portal_message', 'Masukkan kode voucher untuk mengakses internet', 'Pesan portal login'),
('hotspot', 'session_timeout', '3600', 'Session timeout dalam detik'),
('printer', 'voucher_template', 'default', 'Template voucher untuk print'),
('tigaputri', 'commission_rate', '5.00', 'Komisi penjualan dalam persen'),
('notification', 'low_balance_threshold', '10000', 'Threshold saldo rendah');

-- Create indexes for better performance
CREATE INDEX idx_vouchers_expires ON vouchers(expires_at);
CREATE INDEX idx_active_users_timeout ON active_users(session_timeout);
CREATE INDEX idx_sales_date ON sales(created_at);
CREATE INDEX idx_transactions_date ON customer_transactions(created_at);