-- ============================================================
-- NMXpert - Complete MLM & Network Business Management Platform
-- MySQL 8+ Database Schema
-- ============================================================

-- DROP DATABASE IF EXISTS `nmxpert`;
-- CREATE DATABASE `nmxpert` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `nmxpert`;

-- ============================================================
-- COMPANIES
-- ============================================================
CREATE TABLE `companies` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `logo` VARCHAR(500) DEFAULT NULL,
  `favicon` VARCHAR(500) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `postal_code` VARCHAR(20) DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `currency_symbol` VARCHAR(10) DEFAULT 'Rs',
  `timezone` VARCHAR(50) DEFAULT 'Asia/Kolkata',
  `primary_color` VARCHAR(7) DEFAULT '#2563eb',
  `secondary_color` VARCHAR(7) DEFAULT '#10b981',
  `domain` VARCHAR(255) DEFAULT NULL,
  `subscription_plan` VARCHAR(50) DEFAULT 'free',
  `subscription_expires_at` TIMESTAMP NULL DEFAULT NULL,
  `max_members` INT UNSIGNED DEFAULT 1000,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB;

-- ============================================================
-- BRANCHES
-- ============================================================
CREATE TABLE `branches` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `postal_code` VARCHAR(20) DEFAULT NULL,
  `manager_id` INT UNSIGNED DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- ROLES
-- ============================================================
CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `description` VARCHAR(500) DEFAULT NULL,
  `is_system` TINYINT DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `roles` (`name`, `slug`, `description`, `is_system`) VALUES
('Super Admin', 'super-admin', 'Full platform access', 1),
('Company Admin', 'company-admin', 'Company administrator', 1),
('Branch Admin', 'branch-admin', 'Branch administrator', 1),
('Finance Manager', 'finance-manager', 'Finance and accounting', 1),
('Sales Manager', 'sales-manager', 'Sales management', 1),
('KYC Manager', 'kyc-manager', 'KYC verification', 1),
('Warehouse Manager', 'warehouse-manager', 'Inventory management', 1),
('Support Executive', 'support-executive', 'Customer support', 1),
('Auditor', 'auditor', 'Audit and compliance', 1),
('Member', 'member', 'MLM member/distributor', 1),
('Customer', 'customer', 'End customer', 1);

-- ============================================================
-- PERMISSIONS
-- ============================================================
CREATE TABLE `permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `module` VARCHAR(50) NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `permissions` (`name`, `slug`, `module`) VALUES
('View Members', 'members.view', 'members'),
('Create Members', 'members.create', 'members'),
('Edit Members', 'members.edit', 'members'),
('Delete Members', 'members.delete', 'members'),
('View Products', 'products.view', 'products'),
('Create Products', 'products.create', 'products'),
('Edit Products', 'products.edit', 'products'),
('Delete Products', 'products.delete', 'products'),
('View Orders', 'orders.view', 'orders'),
('Create Orders', 'orders.create', 'orders'),
('Edit Orders', 'orders.edit', 'orders'),
('Delete Orders', 'orders.delete', 'orders'),
('View Commissions', 'commissions.view', 'commissions'),
('Approve Commissions', 'commissions.approve', 'commissions'),
('View Wallets', 'wallets.view', 'wallets'),
('Edit Wallets', 'wallets.edit', 'wallets'),
('View Withdrawals', 'withdrawals.view', 'withdrawals'),
('Approve Withdrawals', 'withdrawals.approve', 'withdrawals'),
('Reject Withdrawals', 'withdrawals.reject', 'withdrawals'),
('View KYC', 'kyc.view', 'kyc'),
('Approve KYC', 'kyc.approve', 'kyc'),
('Reject KYC', 'kyc.reject', 'kyc'),
('View Reports', 'reports.view', 'reports'),
('Export Reports', 'reports.export', 'reports'),
('View Settings', 'settings.view', 'settings'),
('Edit Settings', 'settings.edit', 'settings'),
('View Plans', 'plans.view', 'plans'),
('Create Plans', 'plans.create', 'plans'),
('Edit Plans', 'plans.edit', 'plans'),
('Delete Plans', 'plans.delete', 'plans'),
('View Ranks', 'ranks.view', 'ranks'),
('Create Ranks', 'ranks.create', 'ranks'),
('Edit Ranks', 'ranks.edit', 'ranks'),
('Delete Ranks', 'ranks.delete', 'ranks'),
('View Companies', 'companies.view', 'companies'),
('Create Companies', 'companies.create', 'companies'),
('Edit Companies', 'companies.edit', 'companies'),
('Delete Companies', 'companies.delete', 'companies'),
('View Inventory', 'inventory.view', 'inventory'),
('Edit Inventory', 'inventory.edit', 'inventory'),
('View Audit Logs', 'audit.view', 'audit'),
('Manage Support', 'support.manage', 'support'),
('Manage Announcements', 'announcements.manage', 'announcements'),
('Manage Notifications', 'notifications.manage', 'notifications'),
('View Accounting', 'accounting.view', 'accounting'),
('Edit Accounting', 'accounting.edit', 'accounting'),
('View Genealogy', 'genealogy.view', 'genealogy'),
('Export Genealogy', 'genealogy.export', 'genealogy'),
('View Dashboard', 'dashboard.view', 'dashboard'),
('Manage Members', 'members.manage', 'members'),
('Manage Products', 'products.manage', 'products'),
('Manage Orders', 'orders.manage', 'orders'),
('Manage Commissions', 'commissions.manage', 'commissions'),
('Manage Wallets', 'wallets.manage', 'wallets'),
('Manage Withdrawals', 'withdrawals.manage', 'withdrawals'),
('Manage KYC', 'kyc.manage', 'kyc'),
('Manage Plans', 'plans.manage', 'plans'),
('Manage Ranks', 'ranks.manage', 'ranks'),
('Manage Companies', 'companies.manage', 'companies'),
('Manage Inventory', 'inventory.manage', 'inventory'),
('Manage Accounting', 'accounting.manage', 'accounting'),
('Approve Members', 'members.approve', 'members'),
('Reject Members', 'members.reject', 'members'),
('Approve KYC Documents', 'kyc.approve_documents', 'kyc'),
('View Withdrawal Requests', 'withdrawals.view_requests', 'withdrawals'),
('Process Payments', 'payments.process', 'payments'),
('View Payments', 'payments.view', 'payments'),
('Manage Subscriptions', 'subscriptions.manage', 'subscriptions');

-- ============================================================
-- ROLE PERMISSIONS
-- ============================================================
CREATE TABLE `role_permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_role_permission` (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `branch_id` INT UNSIGNED DEFAULT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(500) DEFAULT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `mobile_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `two_factor_enabled` TINYINT DEFAULT 0,
  `two_factor_secret` VARCHAR(255) DEFAULT NULL,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `login_attempts` TINYINT DEFAULT 0,
  `locked_until` TIMESTAMP NULL DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_mobile` (`mobile`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_role` (`role_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_member` (`member_id`)
) ENGINE=InnoDB;

-- ============================================================
-- MEMBERS
-- ============================================================
CREATE TABLE `members` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `branch_id` INT UNSIGNED DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `member_code` VARCHAR(50) NOT NULL,
  `referral_code` VARCHAR(20) NOT NULL,
  `sponsor_id` INT UNSIGNED DEFAULT NULL,
  `placement_id` INT UNSIGNED DEFAULT NULL,
  `position` ENUM('left','right','center') DEFAULT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(500) DEFAULT NULL,
  `gender` ENUM('male','female','other') DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT 'India',
  `state` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `postal_code` VARCHAR(20) DEFAULT NULL,
  `kyc_status` ENUM('pending','approved','rejected','resubmission') DEFAULT 'pending',
  `activation_status` ENUM('inactive','active','suspended') DEFAULT 'inactive',
  `activated_at` TIMESTAMP NULL DEFAULT NULL,
  `total_bv` DECIMAL(12,2) DEFAULT 0.00,
  `total_pv` DECIMAL(12,2) DEFAULT 0.00,
  `total_cv` DECIMAL(12,2) DEFAULT 0.00,
  `personal_bv` DECIMAL(12,2) DEFAULT 0.00,
  `personal_pv` DECIMAL(12,2) DEFAULT 0.00,
  `left_bv` DECIMAL(12,2) DEFAULT 0.00,
  `right_bv` DECIMAL(12,2) DEFAULT 0.00,
  `team_bv` DECIMAL(12,2) DEFAULT 0.00,
  `team_pv` DECIMAL(12,2) DEFAULT 0.00,
  `direct_members` INT UNSIGNED DEFAULT 0,
  `total_team` INT UNSIGNED DEFAULT 0,
  `active_team` INT UNSIGNED DEFAULT 0,
  `inactive_team` INT UNSIGNED DEFAULT 0,
  `total_income` DECIMAL(12,2) DEFAULT 0.00,
  `monthly_income` DECIMAL(12,2) DEFAULT 0.00,
  `current_rank_id` INT UNSIGNED DEFAULT NULL,
  `rank_qualified_at` TIMESTAMP NULL DEFAULT NULL,
  `binary_left_count` INT UNSIGNED DEFAULT 0,
  `binary_right_count` INT UNSIGNED DEFAULT 0,
  `registration_ip` VARCHAR(45) DEFAULT NULL,
  `registration_device` VARCHAR(255) DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_member_code` (`member_code`),
  UNIQUE KEY `uk_referral_code` (`referral_code`),
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_mobile` (`mobile`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`sponsor_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`placement_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_sponsor` (`sponsor_id`),
  INDEX `idx_placement` (`placement_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_activation` (`activation_status`),
  INDEX `idx_rank` (`current_rank_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

ALTER TABLE `users` ADD FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL;

-- ============================================================
-- MEMBER PROFILES
-- ============================================================
CREATE TABLE `member_profiles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `father_name` VARCHAR(255) DEFAULT NULL,
  `mother_name` VARCHAR(255) DEFAULT NULL,
  `spouse_name` VARCHAR(255) DEFAULT NULL,
  `nominee_name` VARCHAR(255) DEFAULT NULL,
  `nominee_relation` VARCHAR(50) DEFAULT NULL,
  `education` VARCHAR(255) DEFAULT NULL,
  `occupation` VARCHAR(255) DEFAULT NULL,
  `annual_income` DECIMAL(12,2) DEFAULT NULL,
  `pan_number` VARCHAR(20) DEFAULT NULL,
  `aadhaar_number` VARCHAR(20) DEFAULT NULL,
  `gst_number` VARCHAR(20) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `social_facebook` VARCHAR(255) DEFAULT NULL,
  `social_instagram` VARCHAR(255) DEFAULT NULL,
  `social_twitter` VARCHAR(255) DEFAULT NULL,
  `social_linkedin` VARCHAR(255) DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uk_member` (`member_id`)
) ENGINE=InnoDB;

-- ============================================================
-- MEMBER DOCUMENTS
-- ============================================================
CREATE TABLE `member_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `document_type` ENUM('pan','aadhaar','passport','driving_license','voter_id','bank_statement','address_proof','photo','other') NOT NULL,
  `document_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `rejection_reason` VARCHAR(500) DEFAULT NULL,
  `verified_by` INT UNSIGNED DEFAULT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- MEMBER BANK ACCOUNTS
-- ============================================================
CREATE TABLE `member_bank_accounts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `bank_name` VARCHAR(255) NOT NULL,
  `account_holder_name` VARCHAR(255) NOT NULL,
  `account_number` VARCHAR(50) NOT NULL,
  `ifsc_code` VARCHAR(20) NOT NULL,
  `branch_name` VARCHAR(255) DEFAULT NULL,
  `account_type` ENUM('savings','current','salary') DEFAULT 'savings',
  `upi_id` VARCHAR(255) DEFAULT NULL,
  `is_primary` TINYINT DEFAULT 0,
  `is_verified` TINYINT DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`)
) ENGINE=InnoDB;

-- ============================================================
-- SPONSORS
-- ============================================================
CREATE TABLE `sponsors` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `sponsor_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `level` INT UNSIGNED DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_sponsor` (`member_id`, `sponsor_id`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sponsor_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_sponsor` (`sponsor_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- PLACEMENTS
-- ============================================================
CREATE TABLE `placements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `placement_parent_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED DEFAULT NULL,
  `position` ENUM('left','right','center') DEFAULT NULL,
  `level` INT UNSIGNED DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_placement` (`member_id`, `placement_parent_id`, `plan_id`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`placement_parent_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_parent` (`placement_parent_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- GENEALOGY NODES
-- ============================================================
CREATE TABLE `genealogy_nodes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED DEFAULT NULL,
  `tree_type` ENUM('sponsor','placement') DEFAULT 'sponsor',
  `position` VARCHAR(20) DEFAULT NULL,
  `level` INT UNSIGNED DEFAULT 0,
  `left_count` INT UNSIGNED DEFAULT 0,
  `right_count` INT UNSIGNED DEFAULT 0,
  `total_descendants` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `genealogy_nodes`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_parent` (`parent_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_tree` (`tree_type`, `company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- RANKS
-- ============================================================
CREATE TABLE `ranks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `level` INT UNSIGNED NOT NULL DEFAULT 0,
  `icon` VARCHAR(500) DEFAULT NULL,
  `color` VARCHAR(7) DEFAULT '#2563eb',
  `description` TEXT DEFAULT NULL,
  `min_personal_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_team_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_direct_members` INT UNSIGNED DEFAULT 0,
  `min_active_members` INT UNSIGNED DEFAULT 0,
  `min_left_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_right_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_monthly_sales` DECIMAL(12,2) DEFAULT 0.00,
  `min_qualified_legs` INT UNSIGNED DEFAULT 0,
  `rank_bonus` DECIMAL(12,2) DEFAULT 0.00,
  `matching_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_slug` (`company_id`, `slug`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_level` (`level`)
) ENGINE=InnoDB;

-- ============================================================
-- RANK RULES
-- ============================================================
CREATE TABLE `rank_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rank_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `rule_type` VARCHAR(50) NOT NULL,
  `rule_key` VARCHAR(100) NOT NULL,
  `rule_value` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `operator` ENUM('>=','<=','=','>','<') DEFAULT '>=',
  `description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`rank_id`) REFERENCES `ranks`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_rank` (`rank_id`)
) ENGINE=InnoDB;

-- ============================================================
-- MEMBER RANKS
-- ============================================================
CREATE TABLE `member_ranks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `rank_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `qualified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `period_start` DATE DEFAULT NULL,
  `period_end` DATE DEFAULT NULL,
  `is_current` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`rank_id`) REFERENCES `ranks`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_rank` (`rank_id`),
  INDEX `idx_current` (`is_current`)
) ENGINE=InnoDB;

-- ============================================================
-- PLANS
-- ============================================================
CREATE TABLE `plans` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `plan_type` ENUM('binary','unilevel','matrix','generation','board','breakaway','matching','rank','leadership','pool','hybrid') NOT NULL,
  `description` TEXT DEFAULT NULL,
  `is_default` TINYINT DEFAULT 0,
  `binary_left_name` VARCHAR(50) DEFAULT 'Left',
  `binary_right_name` VARCHAR(50) DEFAULT 'Right',
  `binary_max_pairs_daily` INT UNSIGNED DEFAULT 0,
  `binary_pair_bonus` DECIMAL(12,2) DEFAULT 0.00,
  `binary_carry_forward` TINYINT DEFAULT 1,
  `binary_flush_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `binary_matching_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `binary_min_personal_bv` DECIMAL(12,2) DEFAULT 0.00,
  `binary_min_team_bv` DECIMAL(12,2) DEFAULT 0.00,
  `unilevel_max_levels` INT UNSIGNED DEFAULT 5,
  `unilevel_level_1` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_2` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_3` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_4` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_5` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_6` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_7` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_8` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_9` DECIMAL(5,2) DEFAULT 0.00,
  `unilevel_level_10` DECIMAL(5,2) DEFAULT 0.00,
  `matrix_rows` INT UNSIGNED DEFAULT 2,
  `matrix_columns` INT UNSIGNED DEFAULT 2,
  `matrix_max_depth` INT UNSIGNED DEFAULT 5,
  `matrix_completion_bonus` DECIMAL(12,2) DEFAULT 0.00,
  `matrix_reentry` TINYINT DEFAULT 0,
  `activation_product_id` INT UNSIGNED DEFAULT NULL,
  `activation_amount` DECIMAL(12,2) DEFAULT 0.00,
  `min_withdrawal` DECIMAL(12,2) DEFAULT 100.00,
  `max_withdrawal` DECIMAL(12,2) DEFAULT 50000.00,
  `withdrawal_fee_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `tds_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `direct_bonus_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `matching_bonus_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `leadership_bonus_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `generation_bonus_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `rank_bonus_enabled` TINYINT DEFAULT 0,
  `pool_bonus_enabled` TINYINT DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_slug` (`company_id`, `slug`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`plan_type`)
) ENGINE=InnoDB;

-- ============================================================
-- PLAN RULES
-- ============================================================
CREATE TABLE `plan_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `plan_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `rule_type` VARCHAR(50) NOT NULL,
  `rule_key` VARCHAR(100) NOT NULL,
  `rule_value` TEXT NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_plan` (`plan_id`)
) ENGINE=InnoDB;

-- ============================================================
-- PLAN LEVELS
-- ============================================================
CREATE TABLE `plan_levels` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `plan_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `level_number` INT UNSIGNED NOT NULL,
  `level_name` VARCHAR(100) DEFAULT NULL,
  `percentage` DECIMAL(5,2) DEFAULT 0.00,
  `fixed_amount` DECIMAL(12,2) DEFAULT 0.00,
  `min_personal_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_team_bv` DECIMAL(12,2) DEFAULT 0.00,
  `min_direct_members` INT UNSIGNED DEFAULT 0,
  `min_active_members` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_plan` (`plan_id`)
) ENGINE=InnoDB;

-- ============================================================
-- PLAN CONDITIONS
-- ============================================================
CREATE TABLE `plan_conditions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `plan_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `condition_type` VARCHAR(50) NOT NULL,
  `condition_key` VARCHAR(100) NOT NULL,
  `condition_value` TEXT NOT NULL,
  `operator` ENUM('>=','<=','=','>','<','in','not_in') DEFAULT '>=',
  `description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_plan` (`plan_id`)
) ENGINE=InnoDB;

-- ============================================================
-- PRODUCTS
-- ============================================================
CREATE TABLE `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `sku` VARCHAR(100) DEFAULT NULL,
  `hsn_code` VARCHAR(20) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `short_description` VARCHAR(1000) DEFAULT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `sale_price` DECIMAL(12,2) DEFAULT NULL,
  `cost_price` DECIMAL(12,2) DEFAULT NULL,
  `bv` DECIMAL(12,2) DEFAULT 0.00,
  `pv` DECIMAL(12,2) DEFAULT 0.00,
  `cv` DECIMAL(12,2) DEFAULT 0.00,
  `tax_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `tax_type` ENUM('inclusive','exclusive') DEFAULT 'exclusive',
  `weight` DECIMAL(8,2) DEFAULT NULL,
  `length` DECIMAL(8,2) DEFAULT NULL,
  `width` DECIMAL(8,2) DEFAULT NULL,
  `height` DECIMAL(8,2) DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `gallery` JSON DEFAULT NULL,
  `is_featured` TINYINT DEFAULT 0,
  `is_active` TINYINT DEFAULT 1,
  `is_subscription` TINYINT DEFAULT 0,
  `subscription_interval` ENUM('monthly','quarterly','annual') DEFAULT NULL,
  `min_stock_alert` INT UNSIGNED DEFAULT 10,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `meta_title` VARCHAR(255) DEFAULT NULL,
  `meta_description` VARCHAR(500) DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_slug` (`company_id`, `slug`),
  UNIQUE KEY `uk_sku` (`company_id`, `sku`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_category` (`category_id`),
  INDEX `idx_status` (`status`),
  FULLTEXT INDEX `ft_search` (`name`, `description`)
) ENGINE=InnoDB;

-- ============================================================
-- PRODUCT CATEGORIES
-- ============================================================
CREATE TABLE `product_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `icon` VARCHAR(100) DEFAULT NULL,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_slug` (`company_id`, `slug`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_parent` (`parent_id`)
) ENGINE=InnoDB;

ALTER TABLE `products` ADD FOREIGN KEY (`category_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL;

-- ============================================================
-- PRODUCT VARIANTS
-- ============================================================
CREATE TABLE `product_variants` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `sku` VARCHAR(100) DEFAULT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `sale_price` DECIMAL(12,2) DEFAULT NULL,
  `stock` INT UNSIGNED DEFAULT 0,
  `image` VARCHAR(500) DEFAULT NULL,
  `attributes` JSON DEFAULT NULL,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_product` (`product_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- WAREHOUSES
-- ============================================================
CREATE TABLE `warehouses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(50) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `manager_id` INT UNSIGNED DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- INVENTORY
-- ============================================================
CREATE TABLE `inventory` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT UNSIGNED NOT NULL,
  `variant_id` INT UNSIGNED DEFAULT NULL,
  `warehouse_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `quantity` INT UNSIGNED DEFAULT 0,
  `reserved` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_product_warehouse` (`product_id`, `variant_id`, `warehouse_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_product` (`product_id`),
  INDEX `idx_warehouse` (`warehouse_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- INVENTORY TRANSACTIONS
-- ============================================================
CREATE TABLE `inventory_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT UNSIGNED NOT NULL,
  `variant_id` INT UNSIGNED DEFAULT NULL,
  `warehouse_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('purchase','sale','return','transfer','adjustment','damage','expired') NOT NULL,
  `quantity` INT NOT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_product` (`product_id`),
  INDEX `idx_warehouse` (`warehouse_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`transaction_type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE `orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `customer_name` VARCHAR(255) DEFAULT NULL,
  `customer_email` VARCHAR(255) DEFAULT NULL,
  `customer_phone` VARCHAR(20) DEFAULT NULL,
  `order_number` VARCHAR(50) NOT NULL,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(12,2) DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) DEFAULT 0.00,
  `shipping_amount` DECIMAL(12,2) DEFAULT 0.00,
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_bv` DECIMAL(12,2) DEFAULT 0.00,
  `total_pv` DECIMAL(12,2) DEFAULT 0.00,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `coupon_id` INT UNSIGNED DEFAULT NULL,
  `coupon_code` VARCHAR(50) DEFAULT NULL,
  `discount_type` ENUM('percentage','fixed') DEFAULT NULL,
  `payment_method` ENUM('wallet','online','cod','bank_transfer','upi') DEFAULT 'online',
  `payment_status` ENUM('pending','paid','partial','failed','refunded') DEFAULT 'pending',
  `order_status` ENUM('pending','confirmed','processing','shipped','delivered','cancelled','returned') DEFAULT 'pending',
  `shipping_name` VARCHAR(255) DEFAULT NULL,
  `shipping_address` TEXT DEFAULT NULL,
  `shipping_city` VARCHAR(100) DEFAULT NULL,
  `shipping_state` VARCHAR(100) DEFAULT NULL,
  `shipping_country` VARCHAR(100) DEFAULT NULL,
  `shipping_postal_code` VARCHAR(20) DEFAULT NULL,
  `tracking_number` VARCHAR(100) DEFAULT NULL,
  `tracking_url` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `internal_notes` TEXT DEFAULT NULL,
  `invoice_generated` TINYINT DEFAULT 0,
  `commission_processed` TINYINT DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_order_number` (`company_id`, `order_number`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`order_status`),
  INDEX `idx_payment` (`payment_status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- ORDER ITEMS
-- ============================================================
CREATE TABLE `order_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `variant_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `sku` VARCHAR(100) DEFAULT NULL,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `tax_amount` DECIMAL(12,2) DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) DEFAULT 0.00,
  `bv` DECIMAL(12,2) DEFAULT 0.00,
  `pv` DECIMAL(12,2) DEFAULT 0.00,
  `cv` DECIMAL(12,2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_order` (`order_id`),
  INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB;

-- ============================================================
-- ORDER PAYMENTS
-- ============================================================
CREATE TABLE `order_payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `payment_method` ENUM('wallet','online','cod','bank_transfer','upi') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `transaction_id` VARCHAR(255) DEFAULT NULL,
  `gateway_response` JSON DEFAULT NULL,
  `status` ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_order` (`order_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- ORDER RETURNS
-- ============================================================
CREATE TABLE `order_returns` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `return_number` VARCHAR(50) NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `refund_amount` DECIMAL(12,2) DEFAULT 0.00,
  `refund_method` ENUM('wallet','bank_transfer','original') DEFAULT 'wallet',
  `refund_status` ENUM('pending','approved','rejected','processed') DEFAULT 'pending',
  `admin_notes` TEXT DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_order` (`order_id`),
  INDEX `idx_status` (`refund_status`)
) ENGINE=InnoDB;

-- ============================================================
-- ORDER RETURN ITEMS
-- ============================================================
CREATE TABLE `order_return_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `return_id` INT UNSIGNED NOT NULL,
  `order_item_id` INT UNSIGNED NOT NULL,
  `quantity` INT UNSIGNED NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `condition` ENUM('good','damaged','defective') DEFAULT 'good',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`return_id`) REFERENCES `order_returns`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`order_item_id`) REFERENCES `order_items`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- BV TRANSACTIONS
-- ============================================================
CREATE TABLE `bv_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `bv_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `transaction_type` ENUM('personal','team','binary_left','binary_right') DEFAULT 'personal',
  `description` VARCHAR(500) DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_order` (`order_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- PV TRANSACTIONS
-- ============================================================
CREATE TABLE `pv_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `pv_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `transaction_type` ENUM('personal','team') DEFAULT 'personal',
  `description` VARCHAR(500) DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- CV TRANSACTIONS
-- ============================================================
CREATE TABLE `cv_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `cv_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `transaction_type` ENUM('personal','team') DEFAULT 'personal',
  `description` VARCHAR(500) DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- COMMISSION RULES
-- ============================================================
CREATE TABLE `commission_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED DEFAULT NULL,
  `commission_type` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `percentage` DECIMAL(5,2) DEFAULT 0.00,
  `fixed_amount` DECIMAL(12,2) DEFAULT 0.00,
  `min_qualification` TEXT DEFAULT NULL,
  `max_payout` DECIMAL(12,2) DEFAULT NULL,
  `frequency` ENUM('daily','weekly','monthly','realtime') DEFAULT 'realtime',
  `is_active` TINYINT DEFAULT 1,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_plan` (`plan_id`),
  INDEX `idx_type` (`commission_type`)
) ENGINE=InnoDB;

-- ============================================================
-- COMMISSION TRANSACTIONS
-- ============================================================
CREATE TABLE `commission_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `commission_rule_id` INT UNSIGNED DEFAULT NULL,
  `commission_type` VARCHAR(50) NOT NULL,
  `source_transaction` VARCHAR(100) DEFAULT NULL,
  `calculation_reference` VARCHAR(255) DEFAULT NULL,
  `from_member_id` INT UNSIGNED DEFAULT NULL,
  `gross_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `deduction` DECIMAL(12,2) DEFAULT 0.00,
  `tax` DECIMAL(12,2) DEFAULT 0.00,
  `net_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `period_start` DATE DEFAULT NULL,
  `period_end` DATE DEFAULT NULL,
  `status` ENUM('pending','approved','paid','cancelled','reversed') DEFAULT 'pending',
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`commission_rule_id`) REFERENCES `commission_rules`(`id`) ON DELETE SET NULL,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`commission_type`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`),
  INDEX `idx_period` (`period_start`, `period_end`)
) ENGINE=InnoDB;

-- ============================================================
-- COMMISSION DETAILS
-- ============================================================
CREATE TABLE `commission_details` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `level` INT UNSIGNED DEFAULT 1,
  `from_member_id` INT UNSIGNED DEFAULT NULL,
  `from_member_name` VARCHAR(255) DEFAULT NULL,
  `source_order_id` INT UNSIGNED DEFAULT NULL,
  `source_amount` DECIMAL(12,2) DEFAULT 0.00,
  `percentage` DECIMAL(5,2) DEFAULT 0.00,
  `calculated_amount` DECIMAL(12,2) DEFAULT 0.00,
  `description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `commission_transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_transaction` (`transaction_id`)
) ENGINE=InnoDB;

-- ============================================================
-- WALLETS
-- ============================================================
CREATE TABLE `wallets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `wallet_type` ENUM('commission','purchase','cashback','reward','main') NOT NULL DEFAULT 'commission',
  `balance` DECIMAL(12,2) DEFAULT 0.00,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `is_active` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_member_type` (`member_id`, `company_id`, `wallet_type`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- WALLET TRANSACTIONS
-- ============================================================
CREATE TABLE `wallet_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `wallet_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('credit','debit','transfer','adjustment','withdrawal','refund','purchase','tax','fee') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `balance_before` DECIMAL(12,2) DEFAULT 0.00,
  `balance_after` DECIMAL(12,2) DEFAULT 0.00,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_wallet` (`wallet_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`transaction_type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- WITHDRAWALS
-- ============================================================
CREATE TABLE `withdrawals` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `wallet_id` INT UNSIGNED NOT NULL,
  `withdrawal_number` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `fee` DECIMAL(12,2) DEFAULT 0.00,
  `tax` DECIMAL(12,2) DEFAULT 0.00,
  `net_amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `payment_method` ENUM('bank_transfer','upi','cheque') NOT NULL,
  `bank_account_id` INT UNSIGNED DEFAULT NULL,
  `bank_name` VARCHAR(255) DEFAULT NULL,
  `account_number` VARCHAR(50) DEFAULT NULL,
  `ifsc_code` VARCHAR(20) DEFAULT NULL,
  `upi_id` VARCHAR(255) DEFAULT NULL,
  `transaction_ref` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','under_review','approved','processing','paid','rejected','cancelled','failed') DEFAULT 'pending',
  `rejection_reason` VARCHAR(500) DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `processed_by` INT UNSIGNED DEFAULT NULL,
  `processed_at` TIMESTAMP NULL DEFAULT NULL,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_withdrawal_number` (`company_id`, `withdrawal_number`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- SUBSCRIPTIONS
-- ============================================================
CREATE TABLE `subscriptions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `price` DECIMAL(12,2) NOT NULL,
  `interval` ENUM('monthly','quarterly','annual') NOT NULL,
  `interval_count` INT UNSIGNED DEFAULT 1,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- SUBSCRIPTION ORDERS
-- ============================================================
CREATE TABLE `subscription_orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `subscription_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `order_number` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` ENUM('wallet','online','bank_transfer') DEFAULT 'online',
  `payment_status` ENUM('pending','paid','failed') DEFAULT 'pending',
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `auto_renew` TINYINT DEFAULT 1,
  `status` ENUM('active','expired','cancelled','suspended') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_end_date` (`end_date`)
) ENGINE=InnoDB;

-- ============================================================
-- COUPONS
-- ============================================================
CREATE TABLE `coupons` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `discount_type` ENUM('percentage','fixed') NOT NULL,
  `discount_value` DECIMAL(12,2) NOT NULL,
  `min_order_amount` DECIMAL(12,2) DEFAULT 0.00,
  `max_discount_amount` DECIMAL(12,2) DEFAULT NULL,
  `usage_limit` INT UNSIGNED DEFAULT NULL,
  `used_count` INT UNSIGNED DEFAULT 0,
  `per_user_limit` INT UNSIGNED DEFAULT 1,
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_code` (`company_id`, `code`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_code` (`code`)
) ENGINE=InnoDB;

-- ============================================================
-- COUPON USAGE
-- ============================================================
CREATE TABLE `coupon_usage` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `coupon_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `discount_amount` DECIMAL(12,2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_coupon` (`coupon_id`),
  INDEX `idx_member` (`member_id`)
) ENGINE=InnoDB;

-- ============================================================
-- PAYMENTS
-- ============================================================
CREATE TABLE `payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `payment_number` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `payment_method` ENUM('wallet','online','cod','bank_transfer','upi') NOT NULL,
  `gateway` VARCHAR(50) DEFAULT NULL,
  `gateway_transaction_id` VARCHAR(255) DEFAULT NULL,
  `gateway_response` JSON DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- PAYMENT TRANSACTIONS
-- ============================================================
CREATE TABLE `payment_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `payment_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('charge','refund','partial_refund','capture','void') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `gateway` VARCHAR(50) DEFAULT NULL,
  `gateway_transaction_id` VARCHAR(255) DEFAULT NULL,
  `gateway_response` JSON DEFAULT NULL,
  `status` ENUM('pending','completed','failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payment_id`) REFERENCES `payments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_payment` (`payment_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- KYC REQUESTS
-- ============================================================
CREATE TABLE `kyc_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `request_number` VARCHAR(50) NOT NULL,
  `status` ENUM('pending','approved','rejected','resubmission') DEFAULT 'pending',
  `rejection_reason` VARCHAR(500) DEFAULT NULL,
  `reviewed_by` INT UNSIGNED DEFAULT NULL,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- KYC DOCUMENTS
-- ============================================================
CREATE TABLE `kyc_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kyc_request_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `document_type` ENUM('pan','aadhaar','passport','driving_license','voter_id','bank_statement','address_proof','photo','other') NOT NULL,
  `document_number` VARCHAR(100) DEFAULT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `rejection_reason` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`kyc_request_id`) REFERENCES `kyc_requests`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_kyc` (`kyc_request_id`),
  INDEX `idx_member` (`member_id`)
) ENGINE=InnoDB;

-- ============================================================
-- INVOICES
-- ============================================================
CREATE TABLE `invoices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED DEFAULT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `invoice_number` VARCHAR(50) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `due_date` DATE DEFAULT NULL,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(12,2) DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) DEFAULT 0.00,
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` DECIMAL(12,2) DEFAULT 0.00,
  `balance_amount` DECIMAL(12,2) DEFAULT 0.00,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `status` ENUM('draft','sent','paid','partial','overdue','cancelled') DEFAULT 'draft',
  `notes` TEXT DEFAULT NULL,
  `terms` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_invoice_number` (`company_id`, `invoice_number`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_order` (`order_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- ============================================================
-- INVOICE ITEMS
-- ============================================================
CREATE TABLE `invoice_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `description` VARCHAR(500) NOT NULL,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `tax_amount` DECIMAL(12,2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_invoice` (`invoice_id`)
) ENGINE=InnoDB;

-- ============================================================
-- EXPENSES
-- ============================================================
CREATE TABLE `expenses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` VARCHAR(500) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `expense_date` DATE NOT NULL,
  `payment_method` ENUM('cash','bank_transfer','upi','card','wallet') DEFAULT 'cash',
  `reference_number` VARCHAR(100) DEFAULT NULL,
  `receipt_path` VARCHAR(500) DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_date` (`expense_date`),
  INDEX `idx_category` (`category`)
) ENGINE=InnoDB;

-- ============================================================
-- ACCOUNT TRANSACTIONS
-- ============================================================
CREATE TABLE `account_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `account_type` ENUM('income','expense','wallet','commission_payable','commission_paid','tax','journal') NOT NULL,
  `transaction_type` ENUM('debit','credit') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `balance` DECIMAL(12,2) DEFAULT 0.00,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `journal_entry` VARCHAR(100) DEFAULT NULL,
  `status` TINYINT DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`account_type`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE `notifications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('info','success','warning','error') DEFAULT 'info',
  `module` VARCHAR(50) DEFAULT NULL,
  `reference_type` VARCHAR(50) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `url` VARCHAR(500) DEFAULT NULL,
  `is_read` TINYINT DEFAULT 0,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_read` (`is_read`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- NOTIFICATION TEMPLATES
-- ============================================================
CREATE TABLE `notification_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(100) NOT NULL,
  `event` VARCHAR(100) NOT NULL,
  `channel` ENUM('in_app','email','sms','push','whatsapp') NOT NULL,
  `subject` VARCHAR(255) DEFAULT NULL,
  `body` TEXT NOT NULL,
  `variables` JSON DEFAULT NULL,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_event` (`event`),
  INDEX `idx_channel` (`channel`)
) ENGINE=InnoDB;

-- ============================================================
-- SUPPORT TICKETS
-- ============================================================
CREATE TABLE `support_tickets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `ticket_number` VARCHAR(50) NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `priority` ENUM('low','medium','high','urgent') DEFAULT 'medium',
  `status` ENUM('open','in_progress','waiting','resolved','closed') DEFAULT 'open',
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_ticket_number` (`company_id`, `ticket_number`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB;

-- ============================================================
-- TICKET MESSAGES
-- ============================================================
CREATE TABLE `ticket_messages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `message` TEXT NOT NULL,
  `is_internal` TINYINT DEFAULT 0,
  `attachment` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_ticket` (`ticket_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- ANNOUNCEMENTS
-- ============================================================
CREATE TABLE `announcements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `type` ENUM('news','announcement','promotion','training','event') DEFAULT 'announcement',
  `target_audience` ENUM('all','members','customers','admins') DEFAULT 'all',
  `is_active` TINYINT DEFAULT 1,
  `publish_at` TIMESTAMP NULL DEFAULT NULL,
  `expire_at` TIMESTAMP NULL DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB;

-- ============================================================
-- REFERRALS
-- ============================================================
CREATE TABLE `referrals` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `referral_code` VARCHAR(20) NOT NULL,
  `referral_url` VARCHAR(500) NOT NULL,
  `total_clicks` INT UNSIGNED DEFAULT 0,
  `total_registrations` INT UNSIGNED DEFAULT 0,
  `total_activations` INT UNSIGNED DEFAULT 0,
  `total_conversions` INT UNSIGNED DEFAULT 0,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_member_code` (`member_id`, `referral_code`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_code` (`referral_code`)
) ENGINE=InnoDB;

-- ============================================================
-- REFERRAL CLICKS
-- ============================================================
CREATE TABLE `referral_clicks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `referral_id` INT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `referrer_url` VARCHAR(500) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `device` VARCHAR(100) DEFAULT NULL,
  `browser` VARCHAR(100) DEFAULT NULL,
  `os` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`referral_id`) REFERENCES `referrals`(`id`) ON DELETE CASCADE,
  INDEX `idx_referral` (`referral_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- RANK ACHIEVEMENTS
-- ============================================================
CREATE TABLE `rank_achievements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `rank_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `achieved_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `celebrated` TINYINT DEFAULT 0,
  `certificate_generated` TINYINT DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`rank_id`) REFERENCES `ranks`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_rank` (`rank_id`),
  INDEX `idx_company` (`company_id`)
) ENGINE=InnoDB;

-- ============================================================
-- BADGES
-- ============================================================
CREATE TABLE `badges` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `icon` VARCHAR(500) DEFAULT NULL,
  `color` VARCHAR(7) DEFAULT '#2563eb',
  `category` ENUM('sales','recruitment','rank','team','milestone','special') DEFAULT 'milestone',
  `criteria` JSON DEFAULT NULL,
  `points` INT UNSIGNED DEFAULT 0,
  `is_active` TINYINT DEFAULT 1,
  `status` TINYINT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_category` (`category`)
) ENGINE=InnoDB;

-- ============================================================
-- MEMBER BADGES
-- ============================================================
CREATE TABLE `member_badges` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `member_id` INT UNSIGNED NOT NULL,
  `badge_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `earned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_member_badge` (`member_id`, `badge_id`),
  FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`badge_id`) REFERENCES `badges`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  INDEX `idx_member` (`member_id`),
  INDEX `idx_badge` (`badge_id`)
) ENGINE=InnoDB;

-- ============================================================
-- AUDIT LOGS
-- ============================================================
CREATE TABLE `audit_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `role` VARCHAR(50) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `device` VARCHAR(255) DEFAULT NULL,
  `action` VARCHAR(50) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `record_id` INT UNSIGNED DEFAULT NULL,
  `record_type` VARCHAR(50) DEFAULT NULL,
  `old_value` JSON DEFAULT NULL,
  `new_value` JSON DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_company` (`company_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_module` (`module`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- LOGIN LOGS
-- ============================================================
CREATE TABLE `login_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `device` VARCHAR(255) DEFAULT NULL,
  `browser` VARCHAR(100) DEFAULT NULL,
  `os` VARCHAR(100) DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `login_type` ENUM('password','otp','token') DEFAULT 'password',
  `status` ENUM('success','failed','blocked') NOT NULL,
  `failure_reason` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_ip` (`ip_address`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB;

-- ============================================================
-- DEVICE SESSIONS
-- ============================================================
CREATE TABLE `device_sessions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `device_id` VARCHAR(255) DEFAULT NULL,
  `device_name` VARCHAR(255) DEFAULT NULL,
  `device_type` ENUM('web','ios','android','desktop') DEFAULT 'web',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `fcm_token` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT DEFAULT 1,
  `last_active_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `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` (`user_id`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB;

-- ============================================================
-- SETTINGS
-- ============================================================
CREATE TABLE `settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `group_name` VARCHAR(100) NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('text','number','boolean','json','file','color') DEFAULT 'text',
  `description` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_company_key` (`company_id`, `group_name`, `setting_key`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
  INDEX `idx_company` (`company_id`),
  INDEX `idx_group` (`group_name`)
) ENGINE=InnoDB;

-- ============================================================
-- SYSTEM SETTINGS
-- ============================================================
CREATE TABLE `system_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `group_name` VARCHAR(100) NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('text','number','boolean','json','file','color') DEFAULT 'text',
  `description` VARCHAR(500) DEFAULT NULL,
  `is_public` TINYINT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_key` (`group_name`, `setting_key`),
  INDEX `idx_group` (`group_name`)
) ENGINE=InnoDB;

INSERT INTO `system_settings` (`group_name`, `setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('general', 'app_name', 'NMXpert', 'text', 'Application name', 1),
('general', 'app_tagline', 'SMART NETWORK. SMARTER GROWTH.', 'text', 'Application tagline', 1),
('general', 'app_version', '1.0.0', 'text', 'Application version', 1),
('general', 'timezone', 'Asia/Kolkata', 'text', 'Default timezone', 0),
('general', 'date_format', 'Y-m-d', 'text', 'Default date format', 0),
('general', 'currency', 'INR', 'text', 'Default currency', 1),
('general', 'currency_symbol', 'Rs', 'text', 'Default currency symbol', 1),
('general', 'decimal_places', '2', 'number', 'Decimal places for currency', 0),
('general', 'logo', 'assets/img/NMXpertlogo.png', 'file', 'Default logo path', 1),
('general', 'favicon', 'assets/img/favicon.png', 'file', 'Default favicon path', 1),
('general', 'primary_color', '#2563eb', 'color', 'Primary brand color', 1),
('general', 'secondary_color', '#10b981', 'color', 'Secondary brand color', 1),
('security', 'max_login_attempts', '5', 'number', 'Max failed login attempts before lock', 0),
('security', 'lock_duration', '30', 'number', 'Account lock duration in minutes', 0),
('security', 'session_timeout', '120', 'number', 'Session timeout in minutes', 0),
('security', 'password_min_length', '8', 'number', 'Minimum password length', 0),
('security', 'require_2fa', '0', 'boolean', 'Require 2FA for all users', 0),
('email', 'smtp_host', '', 'text', 'SMTP host', 0),
('email', 'smtp_port', '587', 'number', 'SMTP port', 0),
('email', 'smtp_username', '', 'text', 'SMTP username', 0),
('email', 'smtp_password', '', 'text', 'SMTP password', 0),
('email', 'smtp_encryption', 'tls', 'text', 'SMTP encryption', 0),
('email', 'from_email', '', 'text', 'From email address', 0),
('email', 'from_name', 'NMXpert', 'text', 'From name', 0),
('sms', 'provider', '', 'text', 'SMS provider', 0),
('sms', 'api_key', '', 'text', 'SMS API key', 0),
('sms', 'sender_id', '', 'text', 'SMS sender ID', 0),
('payment', 'gateway', '', 'text', 'Payment gateway', 0),
('payment', 'merchant_id', '', 'text', 'Merchant ID', 0),
('payment', 'api_key', '', 'text', 'Payment API key', 0),
('payment', 'api_secret', '', 'text', 'Payment API secret', 0),
('tax', 'gst_percentage', '18', 'number', 'Default GST percentage', 0),
('tax', 'tds_percentage', '10', 'number', 'Default TDS percentage', 0),
('tax', 'gst_inclusive', '0', 'boolean', 'Tax inclusive by default', 0),
('registration', 'allow_self_registration', '1', 'boolean', 'Allow self registration', 1),
('registration', 'require_sponsor', '1', 'boolean', 'Require sponsor ID', 1),
('registration', 'require_kyc', '0', 'boolean', 'Require KYC before activation', 0),
('withdrawal', 'min_withdrawal', '100', 'number', 'Minimum withdrawal amount', 1),
('withdrawal', 'max_withdrawal', '50000', 'number', 'Maximum withdrawal amount', 1),
('withdrawal', 'withdrawal_fee', '0', 'number', 'Withdrawal fee percentage', 1),
('withdrawal', 'tds_on_withdrawal', '10', 'number', 'TDS on withdrawal percentage', 1),
('withdrawal', 'daily_limit', '1', 'number', 'Max withdrawals per day', 0),
('withdrawal', 'monthly_limit', '4', 'number', 'Max withdrawals per month', 0);

-- ============================================================
-- API TOKENS
-- ============================================================
CREATE TABLE `api_tokens` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(500) NOT NULL,
  `refresh_token` VARCHAR(500) DEFAULT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `device_id` VARCHAR(255) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `abilities` JSON DEFAULT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `is_revoked` TINYINT DEFAULT 0,
  `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` (`user_id`),
  INDEX `idx_token` (`token`(100)),
  INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB;

-- ============================================================
-- OTP REQUESTS
-- ============================================================
CREATE TABLE `otp_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `otp_code` VARCHAR(10) NOT NULL,
  `purpose` ENUM('login','register','forgot_password','verify_mobile','verify_email') NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `attempts` TINYINT DEFAULT 0,
  `max_attempts` TINYINT DEFAULT 3,
  `is_used` TINYINT DEFAULT 0,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_mobile` (`mobile`),
  INDEX `idx_email` (`email`),
  INDEX `idx_purpose` (`purpose`),
  INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB;

-- ============================================================
-- RATE LIMITS
-- ============================================================
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `cache_key` VARCHAR(255) NOT NULL,
  `attempts` INT UNSIGNED DEFAULT 1,
  `window_start` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_cache_key` (`cache_key`)
) ENGINE=InnoDB;

-- ============================================================
-- SEED DATA: Demo Company
-- ============================================================
INSERT INTO `companies` (`name`, `slug`, `email`, `phone`, `currency`, `currency_symbol`, `status`) VALUES
('Demo Company', 'demo-company', 'admin@demo.com', '+919999999999', 'INR', 'Rs', 1);

SET @company_id = LAST_INSERT_ID();

-- Insert demo ranks
INSERT INTO `ranks` (`company_id`, `name`, `slug`, `level`, `color`, `min_personal_bv`, `min_team_bv`, `min_direct_members`, `sort_order`) VALUES
(@company_id, 'Member', 'member', 1, '#6b7280', 0, 0, 0, 1),
(@company_id, 'Associate', 'associate', 2, '#8b5cf6', 500, 1000, 2, 2),
(@company_id, 'Bronze', 'bronze', 3, '#cd7f32', 1000, 5000, 3, 3),
(@company_id, 'Silver', 'silver', 4, '#c0c0c0', 2000, 15000, 5, 4),
(@company_id, 'Gold', 'gold', 5, '#ffd700', 5000, 50000, 10, 5),
(@company_id, 'Platinum', 'platinum', 6, '#e5e4e2', 10000, 150000, 20, 6),
(@company_id, 'Diamond', 'diamond', 7, '#b9f2ff', 25000, 500000, 50, 7),
(@company_id, 'Crown', 'crown', 8, '#ff6b6b', 50000, 1000000, 100, 8);

-- Insert demo plan
INSERT INTO `plans` (`company_id`, `name`, `slug`, `plan_type`, `is_default`, `binary_left_name`, `binary_right_name`, `binary_pair_bonus`, `unilevel_max_levels`, `unilevel_level_1`, `unilevel_level_2`, `unilevel_level_3`, `unilevel_level_4`, `unilevel_level_5`, `direct_bonus_percentage`, `min_withdrawal`, `max_withdrawal`, `tds_percentage`) VALUES
(@company_id, 'Default Binary Plan', 'default-binary', 'binary', 1, 'Left', 'Right', 100.00, 5, 10.00, 5.00, 3.00, 2.00, 1.00, 10.00, 100.00, 50000.00, 10.00);

-- Insert default admin user (password: Admin@123)
INSERT INTO `users` (`company_id`, `role_id`, `first_name`, `last_name`, `email`, `mobile`, `password`, `status`) VALUES
(@company_id, 1, 'Super', 'Admin', 'admin@nmxpert.com', '9999999999', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- Insert demo member
INSERT INTO `members` (`company_id`, `member_code`, `referral_code`, `first_name`, `last_name`, `email`, `mobile`, `password`, `activation_status`, `status`) VALUES
(@company_id, 'MEM001', 'REF001', 'Demo', 'Member', 'demo@member.com', '8888888888', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', 1);