-- INFYTA Billing Database Schema
-- Import this into phpMyAdmin

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

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS hotspot_vouchers;
DROP TABLE IF EXISTS onu_devices;
DROP TABLE IF EXISTS olt_devices;
DROP TABLE IF EXISTS routers;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS packages;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- Admin Users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    mobile VARCHAR(20) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    role ENUM('super_admin','admin','noc','reseller','staff') DEFAULT 'staff',
    parent_id INT DEFAULT NULL COMMENT 'For reseller hierarchy',
    status ENUM('active','inactive') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE SET NULL
);

-- HRM Employees
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_code VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    mobile VARCHAR(20),
    email VARCHAR(100),
    designation VARCHAR(100),
    department VARCHAR(100),
    join_date DATE,
    salary DECIMAL(12,2) DEFAULT 0,
    status ENUM('active','inactive','resigned') DEFAULT 'active',
    address TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Packages
CREATE TABLE packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    speed VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    validity_days INT DEFAULT 30,
    type ENUM('home','corporate','hotspot') DEFAULT 'home',
    description TEXT,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Routers (MikroTik)
CREATE TABLE routers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    ip_address VARCHAR(50) NOT NULL,
    api_port INT DEFAULT 8728,
    username VARCHAR(50) DEFAULT 'admin',
    password VARCHAR(100),
    model VARCHAR(100),
    location VARCHAR(150),
    status ENUM('online','offline','maintenance') DEFAULT 'offline',
    last_check DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- OLT Devices
CREATE TABLE olt_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    ip_address VARCHAR(50) NOT NULL,
    brand VARCHAR(50) DEFAULT 'BDCOM',
    model VARCHAR(100),
    ports INT DEFAULT 8,
    location VARCHAR(150),
    status ENUM('online','offline','maintenance') DEFAULT 'offline',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ONU Devices (with laser/optical power)
CREATE TABLE onu_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    olt_id INT,
    customer_id INT,
    serial_number VARCHAR(100),
    mac_address VARCHAR(50),
    port VARCHAR(20),
    onu_id VARCHAR(20),
    rx_power DECIMAL(6,2) DEFAULT NULL COMMENT 'dBm - laser receive',
    tx_power DECIMAL(6,2) DEFAULT NULL COMMENT 'dBm - laser transmit',
    distance INT DEFAULT NULL COMMENT 'meters',
    status ENUM('online','offline','los','power_fail') DEFAULT 'offline',
    last_check DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (olt_id) REFERENCES olt_devices(id) ON DELETE SET NULL
);

-- Customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    mobile VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    package_id INT,
    router_id INT,
    olt_id INT,
    username VARCHAR(50),
    password VARCHAR(100),
    connection_type ENUM('pppoe','static','hotspot') DEFAULT 'pppoe',
    status ENUM('active','inactive','suspended','left','expired') DEFAULT 'active',
    connection_date DATE,
    expire_date DATE,
    reseller_id INT DEFAULT NULL COMMENT 'Assigned reseller user id',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL,
    FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
    FOREIGN KEY (olt_id) REFERENCES olt_devices(id) ON DELETE SET NULL,
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Hotspot Vouchers
CREATE TABLE hotspot_vouchers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    profile VARCHAR(50) DEFAULT 'default',
    price DECIMAL(10,2) DEFAULT 0,
    validity_hours INT DEFAULT 24,
    data_limit_mb INT DEFAULT 0,
    status ENUM('unused','used','expired','disabled') DEFAULT 'unused',
    used_by VARCHAR(100),
    used_at DATETIME,
    router_id INT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (router_id) REFERENCES routers(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Invoices
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_no VARCHAR(30) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    package_id INT,
    amount DECIMAL(10,2) NOT NULL,
    discount DECIMAL(10,2) DEFAULT 0,
    due_amount DECIMAL(10,2) NOT NULL,
    billing_month VARCHAR(20),
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('unpaid','partial','paid','cancelled') DEFAULT 'unpaid',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL
);

-- Payments
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_no VARCHAR(30) UNIQUE NOT NULL,
    invoice_id INT,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash','bkash','nagad','rocket','bank','other') DEFAULT 'cash',
    transaction_id VARCHAR(100),
    payment_date DATE NOT NULL,
    received_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Sample Data
INSERT INTO users (username, password, full_name, mobile, role, status) VALUES
('superadmin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', '01700000001', 'super_admin', 'active'),
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Main Admin', '01700000002', 'admin', 'active'),
('noc1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'NOC Operator 1', '01700000003', 'noc', 'active'),
('noc2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'NOC Operator 2', '01700000004', 'noc', 'active'),
('reseller1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Reseller Alpha', '01700000005', 'reseller', 'active'),
('reseller2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Reseller Beta', '01700000006', 'reseller', 'active');

INSERT INTO packages (name, speed, price, validity_days, type, description) VALUES
('Basic Home', '10 Mbps', 500.00, 30, 'home', 'Home package for light users'),
('Standard Home', '20 Mbps', 800.00, 30, 'home', 'Popular home package'),
('Premium Home', '40 Mbps', 1200.00, 30, 'home', 'High speed streaming'),
('Business Basic', '50 Mbps', 2000.00, 30, 'corporate', 'Small office'),
('Business Pro', '100 Mbps', 3500.00, 30, 'corporate', 'High performance'),
('Hotspot 1 Day', '5 Mbps', 50.00, 1, 'hotspot', '1 day voucher'),
('Hotspot 7 Day', '5 Mbps', 200.00, 7, 'hotspot', '7 day voucher');

INSERT INTO routers (name, ip_address, api_port, username, password, model, location, status) VALUES
('Main Router - HQ', '192.168.88.1', 8728, 'admin', 'admin', 'CCR1009', 'Head Office', 'online'),
('Branch Router - Mirpur', '192.168.10.1', 8728, 'admin', 'admin', 'RB4011', 'Mirpur Branch', 'online'),
('Hotspot Gateway', '10.10.10.1', 8728, 'admin', 'admin', 'RB951', 'Hotspot Zone', 'offline');

INSERT INTO olt_devices (name, ip_address, brand, model, ports, location, status) VALUES
('OLT-1 Main', '192.168.100.1', 'BDCOM', 'P3310C', 8, 'Head Office POP', 'online'),
('OLT-2 Uttara', '192.168.100.2', 'VSOL', 'V1600D', 16, 'Uttara POP', 'online');

INSERT INTO customers (customer_code, full_name, mobile, email, address, package_id, router_id, olt_id, username, password, connection_type, status, connection_date, expire_date) VALUES
('CUS-1001', 'Rahim Uddin', '01712345678', 'rahim@email.com', 'Mirpur-10, Dhaka', 2, 1, 1, 'rahim1001', 'pass123', 'pppoe', 'active', '2025-06-15', '2026-10-15'),
('CUS-1002', 'Karim Hossain', '01898765432', 'karim@email.com', 'Uttara Sector 7', 3, 2, 2, 'karim1002', 'pass123', 'pppoe', 'active', '2025-08-01', '2026-10-01'),
('CUS-1003', 'Fatema Begum', '01911223344', NULL, 'Mohammadpur', 1, 1, 1, 'fatema1003', 'pass123', 'pppoe', 'active', '2026-01-10', '2026-10-10'),
('CUS-1004', 'Jamal Ahmed', '01655443322', 'jamal@email.com', 'Gulshan-2', 4, 1, 1, 'jamal1004', 'pass123', 'pppoe', 'suspended', '2024-11-20', '2026-08-20'),
('CUS-1005', 'Nusrat Jahan', '01566778899', NULL, 'Banani', 2, 2, 2, 'nusrat1005', 'pass123', 'pppoe', 'expired', '2025-03-01', '2026-09-01');

INSERT INTO onu_devices (olt_id, customer_id, serial_number, mac_address, port, onu_id, rx_power, tx_power, distance, status, last_check) VALUES
(1, 1, 'BDCM12345678', 'AA:BB:CC:11:22:33', '1/1', '1', -18.50, 2.10, 1250, 'online', NOW()),
(1, 3, 'BDCM12345679', 'AA:BB:CC:11:22:34', '1/2', '2', -22.30, 2.05, 2100, 'online', NOW()),
(2, 2, 'VSOL87654321', 'DD:EE:FF:44:55:66', '1/1', '1', -19.80, 1.95, 980, 'online', NOW()),
(1, 4, 'BDCM12345680', 'AA:BB:CC:11:22:35', '1/3', '3', -28.50, 1.80, 3200, 'los', NOW());

INSERT INTO hotspot_vouchers (code, profile, price, validity_hours, data_limit_mb, status, router_id, created_by) VALUES
('HS-DEMO-001', '1day', 50.00, 24, 1024, 'unused', 3, 1),
('HS-DEMO-002', '1day', 50.00, 24, 1024, 'unused', 3, 1),
('HS-DEMO-003', '7day', 200.00, 168, 5120, 'used', 3, 1),
('HS-DEMO-004', '1day', 50.00, 24, 1024, 'unused', 3, 1);

INSERT INTO invoices (invoice_no, customer_id, package_id, amount, due_amount, billing_month, issue_date, due_date, status) VALUES
('INV-202609-001', 1, 2, 800.00, 800.00, '2026-09', '2026-09-01', '2026-09-10', 'unpaid'),
('INV-202609-002', 2, 3, 1200.00, 0.00, '2026-09', '2026-09-01', '2026-09-10', 'paid'),
('INV-202609-003', 3, 1, 500.00, 500.00, '2026-09', '2026-09-01', '2026-09-10', 'unpaid'),
('INV-202608-001', 1, 2, 800.00, 0.00, '2026-08', '2026-08-01', '2026-08-10', 'paid');

INSERT INTO payments (payment_no, invoice_id, customer_id, amount, payment_method, transaction_id, payment_date, received_by) VALUES
('PAY-001', 2, 2, 1200.00, 'bkash', 'TXN987654321', '2026-09-05', 1),
('PAY-002', 4, 1, 800.00, 'cash', NULL, '2026-08-08', 1);

INSERT INTO employees (emp_code, full_name, mobile, email, designation, department, join_date, salary, status) VALUES
('EMP-001', 'Md. Hasan Ali', '01711112222', 'hasan@infyta.com', 'Network Engineer', 'Technical', '2024-01-15', 35000.00, 'active'),
('EMP-002', 'Sadia Rahman', '01822223333', 'sadia@infyta.com', 'Billing Officer', 'Accounts', '2024-03-01', 25000.00, 'active'),
('EMP-003', 'Rakibul Islam', '01933334444', 'rakib@infyta.com', 'Field Technician', 'Technical', '2023-06-10', 18000.00, 'active');