-- =====================================================
-- WakaTraffic Database Schema
-- Phase 2: Complete Database Setup
-- =====================================================

USE wakatraffic;

-- =====================================================
-- 1. API PROVIDERS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS api_providers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  short_name VARCHAR(100),
  description TEXT,
  api_url VARCHAR(255) NOT NULL,
  api_key TEXT NOT NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  last_synced_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_name (name),
  INDEX idx_short_name (short_name),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 2. CATEGORIES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  slug VARCHAR(255),
  description TEXT,
  icon_class VARCHAR(100) DEFAULT 'fas fa-th',
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_name (name),
  INDEX idx_status (status),
  INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 3. SERVICES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  api_provider_id INT NOT NULL,
  api_service_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 4) DEFAULT 0,
  min_quantity INT DEFAULT 10,
  max_quantity INT DEFAULT 100000,
  type VARCHAR(50) DEFAULT 'Default',
  status ENUM('active', 'inactive') DEFAULT 'active',
  extra_data JSON,
  synced_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY unique_service_provider (api_service_id, api_provider_id),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
  FOREIGN KEY (api_provider_id) REFERENCES api_providers(id) ON DELETE CASCADE,

  INDEX idx_category_id (category_id),
  INDEX idx_provider_id (api_provider_id),
  INDEX idx_status (status),
  INDEX idx_api_service_id (api_service_id),
  INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 4. USERS TABLE MODIFICATIONS
-- =====================================================

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  mobile_number VARCHAR(20),
  address VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(100),
  country VARCHAR(100),
  postal_code VARCHAR(20),
  profile_photo VARCHAR(255),
  balance DECIMAL(15, 2) DEFAULT 0,
  role ENUM('user', 'admin') DEFAULT 'user',
  status ENUM('active', 'inactive', 'banned', 'suspended') DEFAULT 'active',
  email_verified BOOLEAN DEFAULT FALSE,
  email_verified_at TIMESTAMP NULL,
  mobile_verified BOOLEAN DEFAULT FALSE,
  mobile_verified_at TIMESTAMP NULL,
  last_login_at TIMESTAMP NULL,
  last_login_ip VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY unique_email (email),
  UNIQUE KEY unique_username (username),
  INDEX idx_role (role),
  INDEX idx_status (status),
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 5. ORDERS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  service_id INT NOT NULL,
  link VARCHAR(1024) NOT NULL,
  quantity INT NOT NULL,
  charge DECIMAL(10, 4) NOT NULL,
  provider_order_id VARCHAR(255),
  status ENUM('pending', 'processing', 'in_progress', 'complete', 'cancelled', 'refunded', 'failed', 'partial') DEFAULT 'pending',
  status_description VARCHAR(255),
  start_count INT,
  remains INT,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT,

  INDEX idx_user_id (user_id),
  INDEX idx_service_id (service_id),
  INDEX idx_status (status),
  INDEX idx_provider_order_id (provider_order_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 6. DEPOSITS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS deposits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  payment_gateway VARCHAR(50) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  charge DECIMAL(10, 2) DEFAULT 0,
  final_amount DECIMAL(10, 2) NOT NULL,
  paystack_reference VARCHAR(255),
  status ENUM('pending', 'approved', 'rejected', 'failed', 'initiated') DEFAULT 'pending',
  admin_note TEXT,
  approved_by INT,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_payment_gateway (payment_gateway),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 7. TRANSACTIONS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  order_id INT,
  deposit_id INT,
  type ENUM('debit', 'credit', 'bonus', 'withdrawal', 'refund') NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  post_balance DECIMAL(15, 2),
  status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
  description TEXT,
  reference VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  FOREIGN KEY (deposit_id) REFERENCES deposits(id) ON DELETE SET NULL,

  INDEX idx_user_id (user_id),
  INDEX idx_type (type),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 8. PAYMENT GATEWAYS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS payment_gateways (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  type ENUM('automatic', 'manual') DEFAULT 'automatic',
  currency VARCHAR(10),
  min_amount DECIMAL(10, 2),
  max_amount DECIMAL(10, 2),
  conversion_rate DECIMAL(10, 4),
  fixed_charge DECIMAL(10, 2),
  percentage_charge DECIMAL(5, 2),
  config JSON,
  status ENUM('enabled', 'disabled') DEFAULT 'enabled',
  instructions TEXT,
  user_inputs JSON,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_name (name),
  INDEX idx_type (type),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 9. LOGIN ACTIVITIES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS login_activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  logout_at TIMESTAMP NULL,
  status ENUM('success', 'failed') DEFAULT 'success',

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

  INDEX idx_user_id (user_id),
  INDEX idx_login_at (login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 10. NOTIFICATIONS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  sender_id INT,
  type VARCHAR(50),
  subject VARCHAR(255),
  message TEXT,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  read_at TIMESTAMP NULL,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

  INDEX idx_user_id (user_id),
  INDEX idx_sent_at (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 11. SITE SETTINGS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS site_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE NOT NULL,
  setting_value LONGTEXT,
  type VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 12. CRON LOGS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS cron_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  duration_seconds INT,
  status VARCHAR(50),
  actions_performed JSON,
  errors JSON,

  INDEX idx_execution_time (execution_time),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 13. EMAIL SUBSCRIBERS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS email_subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  status ENUM('active', 'unsubscribed') DEFAULT 'active',
  subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Insert default payment gateways (Paystack removed - using modern payment methods instead)
-- Payment methods are now managed via payment_methods table
-- See database/seeders/payment_methods_seeder.sql for the 8 payment options
INSERT IGNORE INTO payment_gateways (name, type, status, display_order) VALUES
  ('Bank Transfer', 'manual', 'disabled', 1),
  ('Cryptocurrency', 'manual', 'disabled', 2);

-- Insert default site settings
INSERT IGNORE INTO site_settings (setting_key, setting_value, type) VALUES
  ('site_title', 'WakaTraffic', 'string'),
  ('site_currency', 'NGN', 'string'),
  ('site_currency_symbol', '₦', 'string'),
  ('site_base_color', '#F3852C', 'string'),
  ('site_secondary_color', '#023A5F', 'string'),
  ('timezone', 'UTC', 'string'),
  ('user_registration_enabled', 'true', 'boolean'),
  ('email_verification_required', 'false', 'boolean'),
  ('mobile_verification_required', 'false', 'boolean');

-- =====================================================
-- DATABASE SETUP COMPLETE
-- =====================================================

SHOW TABLES;
