-- MLM System Database Schema for MySQL
-- Create database
CREATE DATABASE IF NOT EXISTS mlm_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mlm_system;

-- Drop tables if they exist (for fresh installation)

-- Create categories table
CREATE TABLE categories (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  category_name VARCHAR(255) NOT NULL UNIQUE,
  category_image TEXT,
  description TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_category_name (category_name),
  INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create products table
CREATE TABLE products (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  product_name VARCHAR(255) NOT NULL,
  product_code VARCHAR(50) UNIQUE NOT NULL,
  category_id VARCHAR(36),
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  quantity INT DEFAULT 0,
  image TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_product_code (product_code),
  INDEX idx_category_id (category_id),
  INDEX idx_is_active (is_active),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create orders table
CREATE TABLE orders (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  order_id VARCHAR(20) UNIQUE NOT NULL,
  user_type ENUM('member', 'customer') NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  user_member_id VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  payment_status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
  order_status ENUM('Pending', 'Approved', 'Rejected', 'Shipped', 'Delivered') DEFAULT 'Pending',
  payment_type VARCHAR(100),
  payment_date DATE,
  transaction_id VARCHAR(100),
  payment_screenshot TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_order_id (order_id),
  INDEX idx_user_id (user_id),
  INDEX idx_user_member_id (user_member_id),
  INDEX idx_payment_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create order_items table
CREATE TABLE order_items (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  order_id VARCHAR(36) NOT NULL,
  product_id VARCHAR(36),
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  subtotal DECIMAL(10, 2) NOT NULL,

  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create transactions table
CREATE TABLE transactions (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_type ENUM('member', 'customer') NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  user_member_id VARCHAR(20) NOT NULL,
  transaction_type ENUM('DSPCI', 'DBR', 'TRI', 'DCI', 'CASHBACK', 'WITHDRAWAL') NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  description TEXT,
  order_id VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_user_id (user_id),
  INDEX idx_user_member_id (user_member_id),
  INDEX idx_transaction_type (transaction_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create withdrawals table
CREATE TABLE withdrawals (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_type ENUM('member', 'customer') NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  user_member_id VARCHAR(20) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
  bank_details JSON,
  admin_remarks TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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