-- ============================================
-- DATABASE: rtrw_net
-- ============================================
CREATE DATABASE IF NOT EXISTS rtrw_net CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rtrw_net;

-- ============================================
-- TABEL: branches (Cabang)
-- ============================================
CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    balance DECIMAL(15,2) DEFAULT 0.00,
    pending_withdrawal DECIMAL(15,2) DEFAULT 0.00,
    bank_name VARCHAR(50) DEFAULT NULL,
    bank_account VARCHAR(50) DEFAULT NULL,
    bank_holder VARCHAR(100) DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================
-- TABEL: users
-- ============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT DEFAULT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    role ENUM('super_admin','branch_admin','staff') DEFAULT 'staff',
    status ENUM('active','inactive') DEFAULT 'active',
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================
-- TABEL: packages (Paket Internet)
-- ============================================
CREATE TABLE packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    speed VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    mikrotik_profile VARCHAR(50) DEFAULT 'default',
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================
-- TABEL: customers (Pelanggan)
-- ============================================
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    customer_number VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    pppoe_username VARCHAR(50) UNIQUE NOT NULL,
    pppoe_password VARCHAR(100) NOT NULL,
    package_id INT NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) DEFAULT NULL,
    address TEXT,
    ip_address VARCHAR(45) DEFAULT NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    internet_status ENUM('connected','disconnected') DEFAULT 'disconnected',
    registered_at DATE NOT NULL,
    mikrotik_synced TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (package_id) REFERENCES packages(id)
) ENGINE=InnoDB;

-- ============================================
-- TABEL: bills (Tagihan)
-- ============================================
CREATE TABLE bills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    bill_number VARCHAR(50) UNIQUE NOT NULL,
    period_month INT NOT NULL,
    period_year INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    penalty DECIMAL(10,2) DEFAULT 0.00,
    due_date DATE NOT NULL,
    status ENUM('unpaid','paid','overdue','cancelled') DEFAULT 'unpaid',
    paid_at TIMESTAMP NULL,
    suspended_at TIMESTAMP NULL,
    restored_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- TABEL: payments (Pembayaran via iPaymu)
-- ============================================
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bill_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50),
    payment_channel VARCHAR(50),
    reference_id VARCHAR(100) UNIQUE,
    transaction_id VARCHAR(100),
    ipaymu_session_id VARCHAR(100),
    payment_url TEXT,
    va_number VARCHAR(50),
    status ENUM('pending','success','failed','expired','refunded') DEFAULT 'pending',
    paid_at TIMESTAMP NULL,
    callback_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bill_id) REFERENCES bills(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

-- ============================================
-- TABEL: withdrawal_requests (Penarikan Cabang)
-- ============================================
CREATE TABLE withdrawal_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    bank_name VARCHAR(50) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_holder VARCHAR(100) NOT NULL,
    notes TEXT,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    can_approve_at TIMESTAMP NOT NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    processed_by INT DEFAULT NULL,
    rejection_reason TEXT,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (processed_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================
-- TABEL: notification_logs
-- ============================================
CREATE TABLE notification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,
    type ENUM('reminder_h3','reminder_due','payment_success','suspension','restoration','custom') NOT NULL,
    message TEXT NOT NULL,
    status ENUM('sent','failed') DEFAULT 'sent',
    wablas_id VARCHAR(100),
    wablas_response TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================
-- TABEL: activity_logs
-- ============================================
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    target_type VARCHAR(50),
    target_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================
-- TABEL: settings
-- ============================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================
-- TABEL: mikrotik_logs (Log sinkronisasi MikroTik)
-- ============================================
CREATE TABLE mikrotik_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    action ENUM('add_secret','remove_secret','enable_secret','disable_secret','delete_active','kick_session') NOT NULL,
    status ENUM('success','failed') NOT NULL,
    response TEXT,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================
-- DEFAULT DATA
-- ============================================

-- Super Admin default (password: admin123)
INSERT INTO users (username, password, name, email, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Administrator', 'admin@rtrw.net', 'super_admin');

-- Default packages
INSERT INTO packages (name, speed, price, mikrotik_profile) VALUES
('Home 10 Mbps', '10/10 Mbps', 150000.00, '10Mbps'),
('Home 20 Mbps', '20/20 Mbps', 200000.00, '20Mbps'),
('Home 30 Mbps', '30/30 Mbps', 250000.00, '30Mbps'),
('Business 50 Mbps', '50/50 Mbps', 400000.00, '50Mbps');

-- Default settings
INSERT INTO settings (setting_key, setting_value, description) VALUES
('app_name', 'PT Lintas Fiber Nusantara', 'Nama perusahaan'),
('due_day', '3', 'Tanggal jatuh tempo setiap bulan (1-28)'),
('reminder_days', '3', 'Hari sebelum jatuh tempo untuk kirim reminder'),
('penalty_per_day', '5000', 'Denda per hari keterlambatan'),
('suspension_grace_days', '3', 'Hari tenggang sebelum suspend'),
('min_withdrawal', '100000', 'Minimum penarikan cabang'),
('withdrawal_waiting_hours', '24', 'Waktu tunggu penarikan (jam)'),
('ipaymu_mode', 'sandbox', 'Mode iPaymu: sandbox/production'),
('mikrotik_pppoe_service', 'pppoe-service', 'Nama PPPoE service di MikroTik');