-- ============================================================
--  HOTSPOT MANAGER v3 — Database Schema
--  mysql -u root -p < database.sql
-- ============================================================

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

-- 1. USERS (superadmin + admins)
CREATE TABLE users (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100)  NOT NULL,
  email        VARCHAR(150)  UNIQUE NOT NULL,
  phone        VARCHAR(20)   UNIQUE NOT NULL,
  password     VARCHAR(255)  NOT NULL,
  role         ENUM('superadmin','admin') NOT NULL,
  status       ENUM('active','suspended') DEFAULT 'active',
  created_by   INT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- 2. SUBSCRIPTIONS
CREATE TABLE subscriptions (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  admin_id     INT  NOT NULL UNIQUE,
  expires_at   DATE NOT NULL,
  fee_amount   DECIMAL(10,2) DEFAULT 0.00,
  paid_at      TIMESTAMP NULL,
  status       ENUM('active','expired','grace') DEFAULT 'active',
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. ADMIN_SETTINGS (branding ya kila admin)
CREATE TABLE admin_settings (
  id                    INT AUTO_INCREMENT PRIMARY KEY,
  admin_id              INT NOT NULL UNIQUE,
  hotspot_slug          VARCHAR(60) UNIQUE NOT NULL,
  business_name         VARCHAR(120) DEFAULT 'WiFi Hotspot',
  logo_url              VARCHAR(255) NULL,
  -- Colors
  primary_color         VARCHAR(10) DEFAULT '#f97316',
  secondary_color       VARCHAR(10) DEFAULT '#000000',
  bg_color              VARCHAR(10) DEFAULT '#ffffff',
  -- Content
  welcome_message       TEXT NULL,
  footer_text           VARCHAR(255) NULL,
  -- Branding extras
  font_family           VARCHAR(60) DEFAULT 'Inter',
  border_radius         VARCHAR(10) DEFAULT '12px',
  card_style            ENUM('flat','glass','shadow','bordered') DEFAULT 'shadow',
  -- SMS
  sms_sender_name       VARCHAR(20) DEFAULT 'WIFI',
  -- Voucher delivery options
  voucher_delivery      ENUM('screen','sms','both') DEFAULT 'both',
  -- Payment options (JSON kama ipi imewezeshwa)
  payment_methods       VARCHAR(255) DEFAULT 'mpesa,airtel,halopesa,yas',
  -- RADIUS config
  radius_enabled        TINYINT(1) DEFAULT 0,
  created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. RADIUS_CONFIG (kwa kila admin)
CREATE TABLE radius_config (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  admin_id        INT NOT NULL UNIQUE,
  radius_host     VARCHAR(100) NOT NULL,
  radius_port     SMALLINT DEFAULT 1812,
  radius_secret   VARCHAR(255) NOT NULL,
  acct_port       SMALLINT DEFAULT 1813,
  nas_identifier  VARCHAR(100) DEFAULT 'hotspot-nas',
  is_connected    TINYINT(1) DEFAULT 0,
  last_test       TIMESTAMP NULL,
  test_result     VARCHAR(255) NULL,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 5. MIKROTIK_CONFIG
CREATE TABLE mikrotik_config (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  admin_id     INT NOT NULL UNIQUE,
  router_ip    VARCHAR(45)  NOT NULL,
  router_port  SMALLINT DEFAULT 8728,
  router_user  VARCHAR(80)  NOT NULL,
  router_pass  VARCHAR(255) NOT NULL,
  is_connected TINYINT(1) DEFAULT 0,
  last_sync    TIMESTAMP NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 6. PACKAGES
CREATE TABLE packages (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  admin_id          INT NOT NULL,
  name              VARCHAR(100) NOT NULL,
  duration_minutes  INT NOT NULL,
  price             DECIMAL(10,2) NOT NULL,
  speed_upload      VARCHAR(20) DEFAULT NULL,
  speed_download    VARCHAR(20) DEFAULT NULL,
  is_active         TINYINT(1) DEFAULT 1,
  voucher_mode      ENUM('auto','manual') DEFAULT 'auto',
  mikrotik_profile  VARCHAR(80) DEFAULT 'default',
  radius_group      VARCHAR(80) DEFAULT 'default',
  data_limit_mb     INT DEFAULT 0,
  sort_order        INT DEFAULT 0,
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 7. VOUCHERS
CREATE TABLE vouchers (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  admin_id       INT NOT NULL,
  package_id     INT NOT NULL,
  username       VARCHAR(60) UNIQUE NOT NULL,
  password       VARCHAR(60) NOT NULL,
  status         ENUM('available','sold','used','expired') DEFAULT 'available',
  sold_to_phone  VARCHAR(20) NULL,
  sold_at        TIMESTAMP NULL,
  activated_at   TIMESTAMP NULL,
  expires_at     TIMESTAMP NULL,
  radius_synced  TINYINT(1) DEFAULT 0,
  batch_id       VARCHAR(20) NULL,
  batch_month    VARCHAR(7)  NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id)   REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
);
CREATE INDEX idx_vouchers_batch ON vouchers(admin_id, batch_id);
CREATE INDEX idx_vouchers_month ON vouchers(admin_id, batch_month);

-- 8. WALLETS
CREATE TABLE wallets (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  admin_id   INT NOT NULL UNIQUE,
  balance    DECIMAL(12,2) DEFAULT 0.00,
  total_in   DECIMAL(12,2) DEFAULT 0.00,
  total_out  DECIMAL(12,2) DEFAULT 0.00,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 9. TRANSACTIONS
CREATE TABLE transactions (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  admin_id     INT NOT NULL,
  voucher_id   INT NOT NULL,
  package_id   INT NOT NULL,
  buyer_phone  VARCHAR(20) NOT NULL,
  amount       DECIMAL(10,2) NOT NULL,
  payment_method ENUM('mpesa','airtel','halopesa','yas','manual','cash') DEFAULT 'mpesa',
  transaction_ref VARCHAR(100) NULL,
  type         ENUM('sale','refund') DEFAULT 'sale',
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id)   REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
);

-- 10. PAYMENTS (malipo ya customer)
CREATE TABLE payments (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  admin_id         INT NOT NULL,
  package_id       INT NOT NULL,
  buyer_phone      VARCHAR(20) NOT NULL,
  amount           DECIMAL(10,2) NOT NULL,
  payment_method   ENUM('mpesa','airtel','halopesa','yas','manual') NOT NULL,
  transaction_ref  VARCHAR(100) NOT NULL,
  status           ENUM('pending','verified','failed','manual_pending') DEFAULT 'pending',
  voucher_id       INT NULL,
  notes            VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  verified_at      TIMESTAMP NULL,
  FOREIGN KEY (admin_id)   REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE,
  FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE SET NULL
);

-- 11. WITHDRAWALS
CREATE TABLE withdrawals (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  admin_id      INT NOT NULL,
  amount        DECIMAL(10,2) NOT NULL,
  phone_number  VARCHAR(20) NOT NULL,
  network       ENUM('mpesa','airtel','halopesa','yas') NOT NULL,
  status        ENUM('pending','approved','rejected','paid') DEFAULT 'pending',
  otp_verified  TINYINT(1) DEFAULT 0,
  notes         VARCHAR(255) NULL,
  requested_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  processed_at  TIMESTAMP NULL,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 12. OTP_CODES
CREATE TABLE otp_codes (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  admin_id   INT NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  code       VARCHAR(6) NOT NULL,
  purpose    ENUM('withdrawal','login','other') DEFAULT 'withdrawal',
  is_used    TINYINT(1) DEFAULT 0,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 13. SMS_LOGS
CREATE TABLE sms_logs (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  admin_id    INT NULL,
  recipient   VARCHAR(20) NOT NULL,
  message     TEXT NOT NULL,
  purpose     ENUM('voucher','otp','notification','subscription') NOT NULL,
  status      ENUM('sent','failed','pending') DEFAULT 'pending',
  gateway_ref VARCHAR(100) NULL,
  sent_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 14. HOTSPOT_SESSIONS (real-time tracking - works remotely via DB)
CREATE TABLE hotspot_sessions (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  admin_id         INT NOT NULL,
  voucher_id       INT NOT NULL,
  username         VARCHAR(60) NOT NULL,
  device_mac       VARCHAR(20) DEFAULT NULL,
  device_ip        VARCHAR(45) DEFAULT NULL,
  device_type      VARCHAR(30) DEFAULT NULL,
  status           ENUM('online','offline') DEFAULT 'online',
  connected_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  disconnected_at  TIMESTAMP NULL,
  last_seen        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  bytes_up         BIGINT DEFAULT 0,
  bytes_down       BIGINT DEFAULT 0,
  session_time_sec INT DEFAULT 0,
  nas_ip           VARCHAR(45) DEFAULT NULL,
  FOREIGN KEY (admin_id)   REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE
);

-- SPEED SNAPSHOTS (kwa kuhesabu Mbps ya sasa kwa kila pull)
CREATE TABLE speed_snapshots (
  username     VARCHAR(60) PRIMARY KEY,
  bytes_down   BIGINT DEFAULT 0,
  bytes_up     BIGINT DEFAULT 0,
  snapshot_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 15. RADIUS_USERS (FreeRADIUS compatible tables)
CREATE TABLE radcheck (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(64) NOT NULL DEFAULT '',
  attribute  VARCHAR(64) NOT NULL DEFAULT '',
  op         CHAR(2) NOT NULL DEFAULT ':=',
  value      VARCHAR(253) NOT NULL DEFAULT '',
  admin_id   INT NULL,
  voucher_id INT NULL,
  INDEX idx_username (username(32))
);

CREATE TABLE radreply (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(64) NOT NULL DEFAULT '',
  attribute VARCHAR(64) NOT NULL DEFAULT '',
  op        CHAR(2) NOT NULL DEFAULT '=',
  value     VARCHAR(253) NOT NULL DEFAULT '',
  INDEX idx_username (username(32))
);

CREATE TABLE radgroupcheck (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  groupname VARCHAR(64) NOT NULL DEFAULT '',
  attribute VARCHAR(64) NOT NULL DEFAULT '',
  op        CHAR(2) NOT NULL DEFAULT ':=',
  value     VARCHAR(253) NOT NULL DEFAULT '',
  INDEX idx_groupname (groupname(32))
);

CREATE TABLE radgroupreply (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  groupname VARCHAR(64) NOT NULL DEFAULT '',
  attribute VARCHAR(64) NOT NULL DEFAULT '',
  op        CHAR(2) NOT NULL DEFAULT '=',
  value     VARCHAR(253) NOT NULL DEFAULT '',
  INDEX idx_groupname (groupname(32))
);

CREATE TABLE radusergroup (
  username  VARCHAR(64) NOT NULL DEFAULT '',
  groupname VARCHAR(64) NOT NULL DEFAULT '',
  priority  INT NOT NULL DEFAULT 1,
  INDEX idx_username (username(32))
);

CREATE TABLE radacct (
  radacctid          BIGINT AUTO_INCREMENT PRIMARY KEY,
  acctsessionid      VARCHAR(64) NOT NULL DEFAULT '',
  acctuniqueid       VARCHAR(32) NOT NULL DEFAULT '',
  username           VARCHAR(64) NOT NULL DEFAULT '',
  nasipaddress       VARCHAR(15) NOT NULL DEFAULT '',
  nasportid          VARCHAR(15) NULL,
  nasporttype        VARCHAR(32) NULL,
  acctstarttime      DATETIME NULL,
  acctstoptime       DATETIME NULL,
  acctinterval       INT NULL,
  acctsessiontime    INT UNSIGNED NULL,
  acctauthentic      VARCHAR(32) NULL,
  connectinfo_start  VARCHAR(50) NULL,
  connectinfo_stop   VARCHAR(50) NULL,
  acctinputoctets    BIGINT NULL,
  acctoutputoctets   BIGINT NULL,
  calledstationid    VARCHAR(50) NOT NULL DEFAULT '',
  callingstationid   VARCHAR(50) NOT NULL DEFAULT '',
  acctterminatecause VARCHAR(32) NOT NULL DEFAULT '',
  servicetype        VARCHAR(32) NULL,
  framedprotocol     VARCHAR(32) NULL,
  framedipaddress    VARCHAR(15) NOT NULL DEFAULT '',
  admin_id           INT NULL,
  INDEX idx_username    (username),
  INDEX idx_starttime   (acctstarttime),
  INDEX idx_stoptime    (acctstoptime),
  INDEX idx_nasip       (nasipaddress)
);

-- INDEXES
CREATE INDEX idx_vouchers_admin     ON vouchers(admin_id, status);
CREATE INDEX idx_vouchers_package   ON vouchers(package_id, status);
CREATE INDEX idx_transactions_admin ON transactions(admin_id, created_at);
CREATE INDEX idx_withdrawals_admin  ON withdrawals(admin_id, status);
CREATE INDEX idx_sessions_admin     ON hotspot_sessions(admin_id, status);
CREATE INDEX idx_sessions_user      ON hotspot_sessions(username, status);
CREATE INDEX idx_payments_admin     ON payments(admin_id, status);

-- SUPERADMIN WA KWANZA
-- Password: Admin@1234
INSERT INTO users (name, email, phone, password, role) VALUES (
  'Super Admin', 'superadmin@system.com', '0700000000',
  '$2y$12$LcMcE/Y9sECYhAIRUGVd4uS0OZKkqSg5LJKdyAOa6kTqAV5gDOymi', 'superadmin'
);