-- 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)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS withdrawals;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS admin_users;

-- Create members table
CREATE TABLE members (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  member_id VARCHAR(20) UNIQUE NOT NULL,
  status VARCHAR(50) DEFAULT 'Pending',

  -- Basic Details
  title VARCHAR(50) NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  relative_title VARCHAR(50),
  relative_name VARCHAR(255),
  gender VARCHAR(50),
  dob DATE,
  profession VARCHAR(100),
  marital_status VARCHAR(50),
  mobile VARCHAR(15) UNIQUE NOT NULL,
  whatsapp VARCHAR(15),
  email VARCHAR(255) UNIQUE NOT NULL,

  -- Nominee Details
  nominee_name VARCHAR(255),
  nominee_relation VARCHAR(100),
  nominee_dob DATE,
  nominee_contact VARCHAR(15),

  -- Address Details
  village VARCHAR(255),
  post_office VARCHAR(255),
  police_station VARCHAR(255),
  district VARCHAR(255),
  state VARCHAR(255),
  landmark VARCHAR(255),
  pincode VARCHAR(10),

  -- Bank & Document Details (optional)
  bank_holder_name VARCHAR(255),
  bank_name VARCHAR(255),
  account_number VARCHAR(50),
  ifsc_code VARCHAR(15),
  branch_name VARCHAR(255),
  aadhaar_front TEXT,
  aadhaar_back TEXT,
  aadhaar_number VARCHAR(12),
  pan_image TEXT,
  pan_number VARCHAR(10),
  passbook_image TEXT,

  -- Sponsor & MLM Data
  sponsor_id VARCHAR(20),
  sponsor_name VARCHAR(255),
  wallet_balance DECIMAL(10, 2) DEFAULT 0.00,
  total_income DECIMAL(10, 2) DEFAULT 0.00,
  dspci_income DECIMAL(10, 2) DEFAULT 0.00,
  dbr_income DECIMAL(10, 2) DEFAULT 0.00,
  tri_income DECIMAL(10, 2) DEFAULT 0.00,
  dci_income DECIMAL(10, 2) DEFAULT 0.00,
  total_direct INT DEFAULT 0,
  total_downline INT DEFAULT 0,

  -- Auth
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_member_id (member_id),
  INDEX idx_sponsor_id (sponsor_id),
  INDEX idx_mobile (mobile),
  INDEX idx_email (email),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create customers table
CREATE TABLE customers (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  customer_id VARCHAR(20) UNIQUE NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  mobile VARCHAR(15) UNIQUE NOT NULL,
  address TEXT,
  city VARCHAR(100),
  state VARCHAR(100),
  pincode VARCHAR(10),
  sponsor_member_id VARCHAR(20) NOT NULL,
  wallet_balance DECIMAL(10, 2) DEFAULT 0.00,
  total_cashback DECIMAL(10, 2) DEFAULT 0.00,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_customer_id (customer_id),
  INDEX idx_sponsor_member_id (sponsor_member_id),
  INDEX idx_mobile (mobile),
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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;

-- Create admin_users table
CREATE TABLE admin_users (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  username VARCHAR(100) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: admin123)
-- Password hash for 'admin123' using bcrypt (rounds=10)
INSERT INTO admin_users (username, email, password_hash)
VALUES ('admin', 'admin@mlm.com', '$2b$10$rKz9qE0yP1qYzJ5K8Z8wXO7Q1Xz6Z8wXO7Q1Xz6Z8wXO7Q1Xz6Z8w');