schema.sql

-- =============================================
-- OMADA MULTI-TENANT WIFI PLATFORM
-- Final Schema
-- =============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- =============================================
-- TABLE 1: clients
-- =============================================
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `business_name`     VARCHAR(100)   NOT NULL,
  `contact_name`      VARCHAR(100)   NOT NULL,
  `email`             VARCHAR(255)   NOT NULL,
  `phone`             VARCHAR(20)    NOT NULL,
  `payment_display_name` VARCHAR(100) DEFAULT NULL,
  `payment_phone`     VARCHAR(20)    DEFAULT NULL,
  `payment_provider`  VARCHAR(32)    DEFAULT NULL,
  `payment_instructions` VARCHAR(255) DEFAULT NULL,
  `payment_qr_image_url` VARCHAR(512) DEFAULT NULL,
  `manual_sales_enabled` TINYINT(1)  NOT NULL DEFAULT 0,
  `portal_payment_mode` VARCHAR(24) NOT NULL DEFAULT 'both',
  `password_hash`     VARCHAR(255)   NOT NULL,
  `commission_rate`   DECIMAL(5,4)   NOT NULL DEFAULT 0.0500,
  `balance`           DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `total_earned`      DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `total_withdrawn`   DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `is_active`         TINYINT(1)     NOT NULL DEFAULT 1,
  `created_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 2: client_staff
-- =============================================
DROP TABLE IF EXISTS `client_staff`;
CREATE TABLE `client_staff` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`     INT UNSIGNED   NOT NULL,
  `name`          VARCHAR(100)   NOT NULL,
  `email`         VARCHAR(255)   NOT NULL,
  `phone`         VARCHAR(20)    DEFAULT NULL,
  `password_hash` VARCHAR(255)   NOT NULL,
  `role`          ENUM('manager','viewer') NOT NULL DEFAULT 'viewer',
  `is_active`     TINYINT(1)     NOT NULL DEFAULT 1,
  `created_at`    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_staff_email` (`email`),
  KEY `idx_staff_client` (`client_id`),
  CONSTRAINT `fk_staff_client` FOREIGN KEY (`client_id`)
    REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 3: devices
-- =============================================
DROP TABLE IF EXISTS `devices`;
CREATE TABLE `devices` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`         INT UNSIGNED   NOT NULL,
  `name`              VARCHAR(100)   NOT NULL,
  `location`          VARCHAR(255)   DEFAULT NULL,
  `omada_site_id`     VARCHAR(64)    DEFAULT NULL,
  `omada_portal_id`   VARCHAR(64)    DEFAULT NULL,
  `device_username`   VARCHAR(50)    DEFAULT NULL,
  `device_password`   VARCHAR(100)   DEFAULT NULL,
  `ssid_name`         VARCHAR(64)    DEFAULT NULL,
  `ssid_password`     VARCHAR(64)    DEFAULT NULL,
  `omada_wlan_id`     VARCHAR(64)    DEFAULT NULL,
  `omada_ssid_id`     VARCHAR(64)    DEFAULT NULL,
  `mac`               VARCHAR(17)    NOT NULL,
  `model`             VARCHAR(50)    DEFAULT NULL,
  `firmware_version`  VARCHAR(100)   DEFAULT NULL,
  `ip`                VARCHAR(45)    DEFAULT NULL,
  `public_ip`         VARCHAR(45)    DEFAULT NULL,
  `portal_html_path`  VARCHAR(255)   DEFAULT NULL,
  `status`            ENUM('pending','adopting','online','offline','removed') NOT NULL DEFAULT 'pending',
  `registered_at`     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `adopted_at`        TIMESTAMP      NULL DEFAULT NULL,
  `last_seen_at`      TIMESTAMP      NULL DEFAULT NULL,
  `billing_status`    ENUM('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial',
  `billing_expires_at` TIMESTAMP     NULL DEFAULT NULL,
  `monthly_fee`       DECIMAL(10,2)  NOT NULL DEFAULT 20000.00,
  `cpu_util`          TINYINT UNSIGNED DEFAULT NULL,
  `mem_util`          TINYINT UNSIGNED DEFAULT NULL,
  `client_count`      SMALLINT UNSIGNED DEFAULT 0,
  `guest_count`       SMALLINT UNSIGNED DEFAULT 0,
  `uptime_seconds`    INT UNSIGNED   DEFAULT 0,
  `download_bytes`    BIGINT UNSIGNED DEFAULT 0,
  `upload_bytes`      BIGINT UNSIGNED DEFAULT 0,
  `needs_upgrade`     TINYINT(1)     NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_device_mac` (`mac`),
  UNIQUE KEY `uk_omada_site` (`omada_site_id`),
  KEY `idx_device_client` (`client_id`),
  KEY `idx_device_status` (`status`),
  KEY `idx_device_billing` (`billing_status`, `billing_expires_at`),
  CONSTRAINT `fk_device_client` FOREIGN KEY (`client_id`)
    REFERENCES `clients`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 4: device_payments
-- =============================================
DROP TABLE IF EXISTS `device_payments`;
CREATE TABLE `device_payments` (
  `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`         INT UNSIGNED   NOT NULL,
  `device_id`         INT UNSIGNED   NOT NULL,
  `amount`            DECIMAL(10,2)  NOT NULL DEFAULT 20000.00,
  `reference`         VARCHAR(64)    NOT NULL,
  `snippe_payment_id` VARCHAR(100)   DEFAULT NULL,
  `period_start`      DATE           NOT NULL,
  `period_end`        DATE           NOT NULL,
  `status`            ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  `notes`             VARCHAR(255)   DEFAULT NULL,
  `created_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at`      TIMESTAMP      NULL DEFAULT NULL,
  UNIQUE KEY `uk_devpay_ref` (`reference`),
  KEY `idx_devpay_client` (`client_id`),
  KEY `idx_devpay_device` (`device_id`),
  KEY `idx_devpay_status` (`status`),
  CONSTRAINT `fk_devpay_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_devpay_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 5: device_status_log
-- =============================================
DROP TABLE IF EXISTS `device_status_log`;
CREATE TABLE `device_status_log` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `device_id`   INT UNSIGNED NOT NULL,
  `old_status`  VARCHAR(20)  NOT NULL,
  `new_status`  VARCHAR(20)  NOT NULL,
  `reason`      VARCHAR(255) DEFAULT NULL,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_devlog_device` (`device_id`),
  KEY `idx_devlog_time` (`created_at`),
  CONSTRAINT `fk_devlog_device` FOREIGN KEY (`device_id`)
    REFERENCES `devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 6: wifi_plans
-- =============================================
DROP TABLE IF EXISTS `wifi_plans`;
CREATE TABLE `wifi_plans` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`         INT UNSIGNED   NOT NULL,
  `device_id`         INT UNSIGNED   NOT NULL,
  `name`              VARCHAR(50)    NOT NULL,
  `duration_seconds`  INT UNSIGNED   NOT NULL,
  `price`             DECIMAL(10,2)  NOT NULL,
  `down_limit`        SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  `down_unit`         TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT '1=Kbps 2=Mbps',
  `up_limit`          SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  `up_unit`           TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT '1=Kbps 2=Mbps',
  `display_order`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `is_active`         TINYINT(1)     NOT NULL DEFAULT 1,
  `created_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_plan_client` (`client_id`),
  KEY `idx_plan_device` (`device_id`),
  KEY `idx_plan_active` (`device_id`, `is_active`, `display_order`),
  CONSTRAINT `fk_plan_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_plan_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 7: payments
-- =============================================
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id`                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reference`           VARCHAR(64)    NOT NULL,
  `client_id`           INT UNSIGNED   NOT NULL,
  `device_id`           INT UNSIGNED   NOT NULL,
  `plan_id`             INT UNSIGNED   NOT NULL,
  `phone`               VARCHAR(20)    DEFAULT NULL,
  `phone_provider`      ENUM('mpesa','selcom','airtel','tigo','halopesa','bank','other') NOT NULL DEFAULT 'mpesa',
  `payment_channel`     ENUM('snippe','tenant_direct_manual') NOT NULL DEFAULT 'snippe',
  `verification_source` ENUM('webhook','tenant_confirmed') DEFAULT NULL,
  `counts_toward_balance` TINYINT(1) NOT NULL DEFAULT 1,
  `confirmed_by_client_id` INT UNSIGNED DEFAULT NULL,
  `confirmed_at`        TIMESTAMP      NULL DEFAULT NULL,
  `notes`               VARCHAR(255)   DEFAULT NULL,
  `gross_amount`        DECIMAL(10,2)  NOT NULL,
  `snippe_fee`          DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `commission_rate`     DECIMAL(5,4)   NOT NULL DEFAULT 0.0500,
  `commission_amount`   DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `client_credit`       DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `status`              ENUM('pending','completed','failed','refunded','voided') NOT NULL DEFAULT 'pending',
  `snippe_payment_id`   VARCHAR(100)   DEFAULT NULL,
  `access_token_code`   VARCHAR(20)    DEFAULT NULL,
  `created_at`          TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at`        TIMESTAMP      NULL DEFAULT NULL,
  UNIQUE KEY `uk_reference` (`reference`),
  KEY `idx_pay_client` (`client_id`),
  KEY `idx_pay_device` (`device_id`),
  KEY `idx_pay_status` (`status`),
  KEY `idx_pay_completed` (`completed_at`),
  CONSTRAINT `fk_pay_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_pay_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`),
  CONSTRAINT `fk_pay_plan` FOREIGN KEY (`plan_id`) REFERENCES `wifi_plans`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 8: access_tokens
-- =============================================
DROP TABLE IF EXISTS `access_tokens`;
CREATE TABLE `access_tokens` (
  `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`         INT UNSIGNED   NOT NULL,
  `device_id`         INT UNSIGNED   NOT NULL,
  `plan_id`           INT UNSIGNED   NOT NULL,
  `payment_id`        BIGINT UNSIGNED DEFAULT NULL,
  `code`              VARCHAR(20)    NOT NULL,
  `duration_seconds`  INT UNSIGNED   NOT NULL,
  `down_limit`        SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  `down_unit`         TINYINT UNSIGNED NOT NULL DEFAULT 2,
  `up_limit`          SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  `up_unit`           TINYINT UNSIGNED NOT NULL DEFAULT 2,
  `status`            ENUM('unused','active','expired','revoked') NOT NULL DEFAULT 'unused',
  `locked_mac`        VARCHAR(17)    DEFAULT NULL,
  `activated_at`      TIMESTAMP      NULL DEFAULT NULL,
  `expires_at`        TIMESTAMP      NULL DEFAULT NULL,
  `total_seconds_used` INT UNSIGNED  NOT NULL DEFAULT 0,
  `total_bytes_down`  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `total_bytes_up`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_token_code` (`code`),
  KEY `idx_token_client` (`client_id`),
  KEY `idx_token_device` (`device_id`),
  KEY `idx_token_status` (`status`),
  KEY `idx_token_expires` (`status`, `expires_at`),
  KEY `idx_token_payment` (`payment_id`),
  CONSTRAINT `fk_token_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_token_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`),
  CONSTRAINT `fk_token_plan` FOREIGN KEY (`plan_id`) REFERENCES `wifi_plans`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 9: sessions
-- =============================================
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `access_token_id`   BIGINT UNSIGNED DEFAULT NULL,
  `client_id`         INT UNSIGNED   NOT NULL,
  `device_id`         INT UNSIGNED   DEFAULT NULL,
  `client_mac`        VARCHAR(17)    NOT NULL,
  `client_ip`         VARCHAR(45)    DEFAULT NULL,
  `client_name`       VARCHAR(100)   DEFAULT NULL,
  `ssid`              VARCHAR(64)    DEFAULT NULL,
  `started_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at`      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ended_at`          TIMESTAMP      NULL DEFAULT NULL,
  `duration_seconds`  INT UNSIGNED   NOT NULL DEFAULT 0,
  `bytes_down`        BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `bytes_up`          BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `rssi`              SMALLINT       DEFAULT NULL,
  `signal_level`      TINYINT UNSIGNED DEFAULT NULL,
  `is_active`         TINYINT(1)     NOT NULL DEFAULT 1,
  KEY `idx_sess_token` (`access_token_id`),
  KEY `idx_sess_client` (`client_id`),
  KEY `idx_sess_device` (`device_id`),
  KEY `idx_sess_mac` (`client_mac`),
  KEY `idx_sess_active` (`is_active`, `client_id`),
  KEY `idx_sess_time` (`started_at`),
  CONSTRAINT `fk_sess_token` FOREIGN KEY (`access_token_id`) REFERENCES `access_tokens`(`id`),
  CONSTRAINT `fk_sess_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_sess_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 10: token_deauth_events
-- =============================================
DROP TABLE IF EXISTS `token_deauth_events`;
CREATE TABLE `token_deauth_events` (
  `token_id`      BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  `client_id`     INT UNSIGNED    NOT NULL,
  `device_id`     INT UNSIGNED    NOT NULL,
  `omada_site_id` VARCHAR(64)     NOT NULL,
  `locked_mac`    VARCHAR(17)     NOT NULL,
  `status`        ENUM('succeeded') NOT NULL DEFAULT 'succeeded',
  `succeeded_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_token_deauth_mac` (`device_id`, `locked_mac`),
  KEY `idx_token_deauth_site` (`omada_site_id`),
  CONSTRAINT `fk_token_deauth_token` FOREIGN KEY (`token_id`) REFERENCES `access_tokens`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_token_deauth_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_token_deauth_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 11: payouts
-- =============================================
DROP TABLE IF EXISTS `payouts`;
CREATE TABLE `payouts` (
  `id`                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`             INT UNSIGNED   NOT NULL,
  `amount`                DECIMAL(10,2)  NOT NULL,
  `payout_fee`            DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `net_amount`            DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `destination_phone`     VARCHAR(20)    NOT NULL,
  `destination_provider`  ENUM('mpesa','airtel','tigo','halopesa') NOT NULL DEFAULT 'mpesa',
  `status`                ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  `snippe_disbursement_id` VARCHAR(100)  DEFAULT NULL,
  `failure_reason`        VARCHAR(255)   DEFAULT NULL,
  `created_at`            TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at`          TIMESTAMP      NULL DEFAULT NULL,
  KEY `idx_payout_client` (`client_id`),
  KEY `idx_payout_status` (`status`),
  CONSTRAINT `fk_payout_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 12: balance_ledger
-- =============================================
DROP TABLE IF EXISTS `balance_ledger`;
CREATE TABLE `balance_ledger` (
  `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`     INT UNSIGNED   NOT NULL,
  `type`          ENUM('wifi_sale','payout','payout_reversal','adjustment','refund') NOT NULL,
  `amount`        DECIMAL(10,2)  NOT NULL,
  `balance_after` DECIMAL(12,2)  NOT NULL,
  `reference_type` ENUM('payment','payout','manual') NOT NULL,
  `reference_id`  BIGINT UNSIGNED NOT NULL,
  `description`   VARCHAR(255)   DEFAULT NULL,
  `created_at`    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ledger_client` (`client_id`),
  KEY `idx_ledger_time` (`created_at`),
  KEY `idx_ledger_ref` (`reference_type`, `reference_id`),
  CONSTRAINT `fk_ledger_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 13: omada_alerts
-- =============================================
DROP TABLE IF EXISTS `omada_alerts`;
CREATE TABLE `omada_alerts` (
  `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`       INT UNSIGNED   NOT NULL,
  `device_id`       INT UNSIGNED   DEFAULT NULL,
  `omada_alert_id`  VARCHAR(64)    NOT NULL,
  `alert_key`       VARCHAR(50)    NOT NULL,
  `module`          VARCHAR(50)    DEFAULT NULL,
  `level`           ENUM('info','warning','error','critical') NOT NULL DEFAULT 'info',
  `content`         TEXT           NOT NULL,
  `device_mac`      VARCHAR(17)    DEFAULT NULL,
  `resolved`        TINYINT(1)     NOT NULL DEFAULT 0,
  `alert_time`      TIMESTAMP      NOT NULL,
  `fetched_at`      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_omada_alert` (`omada_alert_id`),
  KEY `idx_alert_client` (`client_id`),
  KEY `idx_alert_device` (`device_id`),
  KEY `idx_alert_unresolved` (`client_id`, `resolved`),
  CONSTRAINT `fk_alert_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`),
  CONSTRAINT `fk_alert_device` FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 13: sms_log
-- =============================================
DROP TABLE IF EXISTS `sms_log`;
CREATE TABLE `sms_log` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `phone`       VARCHAR(20)    NOT NULL,
  `message`     VARCHAR(320)   NOT NULL,
  `status`      ENUM('sent','delivered','failed') NOT NULL,
  `gateway_id`  VARCHAR(100)   DEFAULT NULL,
  `error`       VARCHAR(255)   DEFAULT NULL,
  `created_at`  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` TIMESTAMP     NULL DEFAULT NULL,
  KEY `idx_sms_phone` (`phone`),
  KEY `idx_sms_status` (`status`),
  KEY `idx_sms_time` (`created_at`),
  KEY `idx_sms_gateway` (`gateway_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TRIGGERS
-- =============================================

DROP TRIGGER IF EXISTS `trg_payment_completed`;
DELIMITER $$
CREATE TRIGGER `trg_payment_completed`
BEFORE UPDATE ON `payments`
FOR EACH ROW
BEGIN
  DECLARE v_new_balance DECIMAL(12,2);

  IF OLD.status = 'pending' AND NEW.status = 'completed' THEN
    SET NEW.snippe_fee        = ROUND(NEW.gross_amount * 0.005, 2);
    SET NEW.commission_amount = ROUND(NEW.gross_amount * NEW.commission_rate, 2);
    SET NEW.client_credit     = NEW.gross_amount - NEW.snippe_fee - NEW.commission_amount;
    SET NEW.completed_at      = CURRENT_TIMESTAMP;

    UPDATE `clients`
    SET `balance`      = `balance` + NEW.client_credit,
        `total_earned` = `total_earned` + NEW.client_credit
    WHERE `id` = NEW.client_id;

    SET v_new_balance = (SELECT `balance` FROM `clients` WHERE `id` = NEW.client_id);

    INSERT INTO `balance_ledger`
      (`client_id`, `type`, `amount`, `balance_after`, `reference_type`, `reference_id`, `description`)
    VALUES
      (NEW.client_id, 'wifi_sale', NEW.client_credit, v_new_balance, 'payment', NEW.id,
       CONCAT('WiFi sale: ', NEW.gross_amount, ' TZS (fee: ', NEW.snippe_fee, ', comm: ', NEW.commission_amount, ')'));
  END IF;
END$$
DELIMITER ;


DROP TRIGGER IF EXISTS `trg_payout_requested`;
DELIMITER $$
CREATE TRIGGER `trg_payout_requested`
AFTER INSERT ON `payouts`
FOR EACH ROW
BEGIN
  DECLARE v_new_balance DECIMAL(12,2);

  IF NEW.status = 'pending' THEN
    UPDATE `clients`
    SET `balance` = `balance` - NEW.amount
    WHERE `id` = NEW.client_id;

    SET v_new_balance = (SELECT `balance` FROM `clients` WHERE `id` = NEW.client_id);

    INSERT INTO `balance_ledger`
      (`client_id`, `type`, `amount`, `balance_after`, `reference_type`, `reference_id`, `description`)
    VALUES
      (NEW.client_id, 'payout', -NEW.amount, v_new_balance, 'payout', NEW.id,
       CONCAT('Payout request: ', NEW.amount, ' TZS to ', NEW.destination_phone));
  END IF;
END$$
DELIMITER ;


DROP TRIGGER IF EXISTS `trg_payout_failed`;
DELIMITER $$
CREATE TRIGGER `trg_payout_failed`
BEFORE UPDATE ON `payouts`
FOR EACH ROW
BEGIN
  DECLARE v_new_balance DECIMAL(12,2);

  IF OLD.status = 'pending' AND NEW.status = 'failed' THEN
    UPDATE `clients`
    SET `balance` = `balance` + OLD.amount
    WHERE `id` = OLD.client_id;

    SET v_new_balance = (SELECT `balance` FROM `clients` WHERE `id` = OLD.client_id);

    INSERT INTO `balance_ledger`
      (`client_id`, `type`, `amount`, `balance_after`, `reference_type`, `reference_id`, `description`)
    VALUES
      (OLD.client_id, 'payout_reversal', OLD.amount, v_new_balance, 'payout', OLD.id,
       CONCAT('Payout failed: ', IFNULL(NEW.failure_reason, 'unknown')));
  END IF;
END$$
DELIMITER ;


DROP TRIGGER IF EXISTS `trg_payout_completed`;
DELIMITER $$
CREATE TRIGGER `trg_payout_completed`
BEFORE UPDATE ON `payouts`
FOR EACH ROW
BEGIN
  IF OLD.status IN ('pending','processing') AND NEW.status = 'completed' THEN
    SET NEW.completed_at = CURRENT_TIMESTAMP;

    UPDATE `clients`
    SET `total_withdrawn` = `total_withdrawn` + OLD.amount
    WHERE `id` = OLD.client_id;
  END IF;
END$$
DELIMITER ;


-- =============================================
-- TABLE 14: device_portal_settings
-- =============================================
DROP TABLE IF EXISTS `device_portal_settings`;
CREATE TABLE `device_portal_settings` (
  `device_id`     INT UNSIGNED  NOT NULL PRIMARY KEY,
  `display_name`  VARCHAR(100)  DEFAULT NULL COMMENT 'Overrides business_name on the portal',
  `welcome_text`  VARCHAR(255)  DEFAULT NULL COMMENT 'Tagline shown under the name',
  `logo_url`      VARCHAR(500)  DEFAULT NULL COMMENT 'Full URL to logo image',
  `support_phone` VARCHAR(20)   DEFAULT NULL COMMENT 'Support number shown to guests',
  `primary_color` VARCHAR(7)    NOT NULL DEFAULT '#4361ee' COMMENT 'Buttons and accents',
  `bg_color`      VARCHAR(7)    NOT NULL DEFAULT '#1a1a2e' COMMENT 'Page background',
  `manual_sales_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Enable direct/manual sales for this hotspot',
  `portal_payment_mode` VARCHAR(24) NOT NULL DEFAULT 'both' COMMENT 'online_only | direct_only | both',
  `updated_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_portal_settings_device`
    FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `device_portal_payment_options`;
CREATE TABLE `device_portal_payment_options` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `device_id`    INT UNSIGNED NOT NULL,
  `provider_name` VARCHAR(100) NOT NULL,
  `image_path`   VARCHAR(255) NOT NULL,
  `display_order` INT NOT NULL DEFAULT 0,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_portal_payment_device` (`device_id`),
  CONSTRAINT `fk_portal_payment_option_device`
    FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 15: password_resets
-- =============================================
DROP TABLE IF EXISTS `password_resets`;
CREATE TABLE `password_resets` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `phone`      VARCHAR(20)  NOT NULL,
  `otp`        VARCHAR(6)   NOT NULL,
  `expires_at` TIMESTAMP    NOT NULL,
  `used`       TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_reset_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =============================================
-- TABLE 15: system_config
-- =============================================
DROP TABLE IF EXISTS `system_config`;
CREATE TABLE `system_config` (
  `key`         VARCHAR(64)   NOT NULL PRIMARY KEY,
  `value`       VARCHAR(255)  NOT NULL,
  `description` VARCHAR(255)  DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `system_config` (`key`, `value`, `description`) VALUES
  ('admin_phone', '+255769590766', 'System admin phone — receives critical error alerts');


SET FOREIGN_KEY_CHECKS = 1;