-- 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: 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: 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: 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)
);
-- 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_sales_date ON sales(created_at);
CREATE INDEX idx_transactions_date ON customer_transactions(created_at);