-- ============================================================
-- BDOZ PHP – Complete Database Import / Migration Script
-- Run this once on your existing or new database.
-- ============================================================

-- 1. Core tables (safe to re-run)
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(60) NOT NULL UNIQUE,
    `email` VARCHAR(120) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('subagent','agent','admin','superadmin') NOT NULL DEFAULT 'subagent',
    `phone` VARCHAR(20) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `is_approved` TINYINT(1) NOT NULL DEFAULT 1,
    `api_key` VARCHAR(64) NULL,
    `parent_id` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `wallets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
    `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(30) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `description` VARCHAR(255) NULL,
    `reference` VARCHAR(64) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `store_id` BIGINT UNSIGNED NULL,
    `reference` VARCHAR(40) NOT NULL UNIQUE,
    `network` VARCHAR(20) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `package_name` VARCHAR(100) NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `status` ENUM('pending','processing','completed','failed','on_hold') NOT NULL DEFAULT 'pending',
    `provider_ref` VARCHAR(100) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `network` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `size` VARCHAR(50) NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `providers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `api_url` VARCHAR(255) NULL,
    `api_key` VARCHAR(255) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `withdrawals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `network` VARCHAR(20) NULL,
    `phone` VARCHAR(20) NULL,
    `account_name` VARCHAR(100) NULL,
    `status` ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
    `reference` VARCHAR(40) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Partner stores
CREATE TABLE IF NOT EXISTS `student_stores` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `store_name` VARCHAR(100) NOT NULL,
    `subdomain` VARCHAR(50) NOT NULL UNIQUE,
    `status` ENUM('pending','approved','suspended') NOT NULL DEFAULT 'pending',
    `markup_mtn` DECIMAL(5,2) NOT NULL DEFAULT 0,
    `markup_telecel` DECIMAL(5,2) NOT NULL DEFAULT 0,
    `markup_airteltigo` DECIMAL(5,2) NOT NULL DEFAULT 0,
    `api_token` VARCHAR(64) NULL,
    `logo` VARCHAR(255) NULL,
    `primary_color` VARCHAR(20) NULL DEFAULT '#2563eb',
    `custom_domain` VARCHAR(100) NULL,
    `gateway_mode` ENUM('bdoz','custom','both') NOT NULL DEFAULT 'bdoz',
    `custom_gateway_key` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Worker prices
CREATE TABLE IF NOT EXISTS `store_worker_prices` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NOT NULL,
    `package_id` BIGINT UNSIGNED NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_store_package` (`store_id`, `package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Promo codes
CREATE TABLE IF NOT EXISTS `promo_codes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(30) NOT NULL UNIQUE,
    `discount_type` ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
    `discount_value` DECIMAL(10,2) NOT NULL,
    `max_uses` INT NULL,
    `used_count` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `expires_at` DATETIME NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Worker invites
CREATE TABLE IF NOT EXISTS `worker_invites` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NOT NULL,
    `email` VARCHAR(120) NULL,
    `phone` VARCHAR(20) NULL,
    `token` VARCHAR(64) NOT NULL UNIQUE,
    `status` ENUM('pending','accepted','expired') NOT NULL DEFAULT 'pending',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Activity log
CREATE TABLE IF NOT EXISTS `activity_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `details` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Settings
CREATE TABLE IF NOT EXISTS `settings` (
    `key` VARCHAR(50) NOT NULL PRIMARY KEY,
    `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO settings (`key`, `value`) VALUES ('maintenance_mode', '0');

-- 8. Support tickets
CREATE TABLE IF NOT EXISTS `support_tickets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `subject` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `status` ENUM('open','closed') NOT NULL DEFAULT 'open',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Announcements
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `body` TEXT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. Safe column additions (if tables already exist)
ALTER TABLE student_stores 
    ADD COLUMN IF NOT EXISTS logo VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS primary_color VARCHAR(20) NULL DEFAULT '#2563eb',
    ADD COLUMN IF NOT EXISTS custom_domain VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS gateway_mode ENUM('bdoz','custom','both') NOT NULL DEFAULT 'bdoz',
    ADD COLUMN IF NOT EXISTS custom_gateway_key VARCHAR(255) NULL;

ALTER TABLE orders 
    MODIFY COLUMN status ENUM('pending','processing','completed','failed','on_hold') NOT NULL DEFAULT 'pending';

-- Done
SELECT 'BDOZ PHP database migration complete' AS message;