CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sponsor_id INT NULL,
  fullname VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('superadmin','tenant_admin','merchant','user') DEFAULT 'user',
  status ENUM('active','inactive','blocked') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (sponsor_id)
);

CREATE TABLE subscription_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plan_name VARCHAR(100) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  direct_rate DECIMAL(5,2) DEFAULT 25.00,
  matrix_rate DECIMAL(5,2) DEFAULT 25.00,
  platform_rate DECIMAL(5,2) DEFAULT 50.00,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO subscription_plans (plan_name, price)
VALUES ('Starter Monthly', 1000.00);

CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending','paid','cancelled','expired') DEFAULT 'pending',
  paid_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id),
  INDEX (plan_id),
  INDEX (status)
);

CREATE TABLE sponsor_commissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subscription_id INT NOT NULL,
  sponsor_id INT NOT NULL,
  user_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending','released','cancelled') DEFAULT 'released',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (sponsor_id),
  INDEX (subscription_id)
);

CREATE TABLE matrix_cycles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_user_id INT NOT NULL,
  cycle_no INT NOT NULL DEFAULT 1,
  status ENUM('open','completed','archived') DEFAULT 'open',
  total_slots INT DEFAULT 39,
  filled_slots INT DEFAULT 0,
  pot_amount DECIMAL(12,2) DEFAULT 0.00,
  completed_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_owner_cycle (owner_user_id, cycle_no),
  INDEX (owner_user_id),
  INDEX (status)
);

CREATE TABLE matrix_positions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cycle_id INT NOT NULL,
  owner_user_id INT NOT NULL,
  placed_user_id INT NOT NULL,
  parent_position_id INT NULL,
  level_no TINYINT NOT NULL,
  position_no INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_cycle_user (cycle_id, placed_user_id),
  INDEX (cycle_id),
  INDEX (owner_user_id),
  INDEX (parent_position_id),
  INDEX (level_no)
);

CREATE TABLE matrix_pot_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cycle_id INT NOT NULL,
  owner_user_id INT NOT NULL,
  source_user_id INT NOT NULL,
  subscription_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  type ENUM('contribution','release') DEFAULT 'contribution',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (cycle_id),
  INDEX (owner_user_id),
  INDEX (subscription_id)
);

CREATE TABLE payout_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  source_type ENUM('direct_sponsor','matrix_completion') NOT NULL,
  source_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending','released','paid','cancelled') DEFAULT 'released',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id),
  INDEX (source_type)
);