-- GOM Trading - Gold Ornaments Making & Trading
-- MySQL schema (MySQLi, normalized, with indexes)


-- --------------------------------------------------------
-- Wallets (gold balance, internal value)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `gold_grams` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `balance_value` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT 'Internal value in base currency',
  `currency` varchar(8) NOT NULL DEFAULT 'INR',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `gold_grams` (`gold_grams`),
  CONSTRAINT `wallets_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Gold prices (admin-controlled, 22K / 24K)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `gold_prices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `carat` varchar(8) NOT NULL COMMENT '22K or 24K',
  `price_per_gram` decimal(18,2) NOT NULL,
  `currency` varchar(8) NOT NULL DEFAULT 'INR',
  `effective_from` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `set_by_admin_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `carat` (`carat`),
  KEY `effective_from` (`effective_from`),
  CONSTRAINT `gold_prices_admin_fk` FOREIGN KEY (`set_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Crypto payments (NOWPayments: USDT top-up)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crypto_payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `payment_id` varchar(128) NOT NULL COMMENT 'NOWPayments payment_id',
  `pay_address` varchar(255) NOT NULL,
  `currency` varchar(16) NOT NULL DEFAULT 'USDT',
  `amount_usd` decimal(18,2) NOT NULL COMMENT 'Amount in USD/USDT',
  `amount_inr` decimal(18,2) NOT NULL COMMENT 'Equivalent INR for top-up',
  `amount_crypto` decimal(18,8) NOT NULL COMMENT 'Exact crypto amount to send',
  `received_amount` decimal(18,8) NOT NULL DEFAULT 0.00000000 COMMENT 'Received so far (partial)',
  `status` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=pending, 1=paid, 2=partial, 3=cancelled, 4=expired',
  `procetime` datetime NOT NULL COMMENT 'Payment expiry',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_id` (`payment_id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  CONSTRAINT `crypto_payments_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Plans (activation, daily %, caps, cycles - admin config)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `plans` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `slug` varchar(64) NOT NULL,
  `activation_amount` decimal(18,2) NOT NULL COMMENT 'Min top-up to activate',
  `daily_dividend_pct` decimal(5,2) NOT NULL COMMENT 'Base daily % e.g. 1.00',
  `max_working_days_pct` decimal(6,2) NOT NULL COMMENT 'e.g. 200 = 200%',
  `max_income_cap_pct` decimal(6,2) NOT NULL COMMENT 'e.g. 300 = 300%',
  `gsc_cycle_amount` decimal(18,2) NOT NULL DEFAULT 100.00 COMMENT 'GSC bonus per cycle',
  `gsc_qualify_direct_refs` int unsigned NOT NULL DEFAULT 3,
  `booster_gsc_qualify_direct_refs` int unsigned NOT NULL DEFAULT 12,
  `booster_gsc_per_level_amount` decimal(18,2) NOT NULL DEFAULT 5.00,
  `booster_gsc_levels` int unsigned NOT NULL DEFAULT 10,
  `level_dividend_1_pct` decimal(5,2) NOT NULL DEFAULT 5.00,
  `level_dividend_2_pct` decimal(5,2) NOT NULL DEFAULT 1.00,
  `level_dividend_3_pct` decimal(5,2) NOT NULL DEFAULT 1.00,
  `level_dividend_4_pct` decimal(5,2) NOT NULL DEFAULT 1.00,
  `level_dividend_5_pct` decimal(5,2) NOT NULL DEFAULT 1.00,
  `level_dividend_direct_refs_required` int unsigned NOT NULL DEFAULT 12,
  `min_withdrawal` decimal(18,2) NOT NULL DEFAULT 10.00,
  `withdrawal_multiple` decimal(18,2) NOT NULL DEFAULT 10.00,
  `withdrawals_per_day` int unsigned NOT NULL DEFAULT 1,
  `system_deduction_pct` decimal(5,2) NOT NULL DEFAULT 30.00,
  `processing_fee_pct` decimal(5,2) NOT NULL DEFAULT 5.00,
  `dividend_liquidity_pct` decimal(5,2) NOT NULL DEFAULT 5.00,
  `topup_wallet_pct` decimal(5,2) NOT NULL DEFAULT 20.00,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `is_default` (`is_default`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Referrals (direct referral link user_id -> referred user_id)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sponsor_id` int unsigned NOT NULL,
  `referred_id` int unsigned NOT NULL,
  `level` int unsigned NOT NULL DEFAULT 1 COMMENT '1=direct',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sponsor_referred` (`sponsor_id`,`referred_id`),
  KEY `referred_id` (`referred_id`),
  KEY `sponsor_level` (`sponsor_id`,`level`),
  CONSTRAINT `referrals_sponsor_fk` FOREIGN KEY (`sponsor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `referrals_referred_fk` FOREIGN KEY (`referred_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Team structure (full tree for level/rank calculations)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `team_structure` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ancestor_id` int unsigned NOT NULL COMMENT 'Upline user_id',
  `descendant_id` int unsigned NOT NULL COMMENT 'Downline user_id',
  `depth` int unsigned NOT NULL COMMENT '1=direct, 2=second level, etc',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ancestor_descendant` (`ancestor_id`,`descendant_id`),
  KEY `descendant_id` (`descendant_id`),
  KEY `ancestor_depth` (`ancestor_id`,`depth`),
  CONSTRAINT `team_ancestor_fk` FOREIGN KEY (`ancestor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_descendant_fk` FOREIGN KEY (`descendant_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Ranks (D1, D2, D3 - qualification and bonus per ID)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ranks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(16) NOT NULL COMMENT 'D1, D2, D3',
  `name` varchar(64) NOT NULL,
  `sort_order` int unsigned NOT NULL DEFAULT 0,
  `direct_referrals_required` int unsigned NOT NULL DEFAULT 0,
  `total_team_required` int unsigned NOT NULL DEFAULT 0,
  `self_topup_required` decimal(18,2) NOT NULL DEFAULT 0.00,
  `parent_rank_qualifiers_required` int unsigned NOT NULL DEFAULT 0 COMMENT 'e.g. 3 D1 for D2',
  `parent_rank_slug` varchar(16) DEFAULT NULL COMMENT 'D1 for D2, D2 for D3',
  `bonus_per_id` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT 'Infinity bonus per ID',
  `unlimited_levels` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- User rank assignment (current rank per user)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_ranks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `rank_id` int unsigned NOT NULL,
  `achieved_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `rank_id` (`rank_id`),
  CONSTRAINT `user_ranks_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_ranks_rank_fk` FOREIGN KEY (`rank_id`) REFERENCES `ranks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Clubs (Society Club, Booster GSC - qualification)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `clubs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(32) NOT NULL COMMENT 'gsc, booster_gsc',
  `name` varchar(128) NOT NULL,
  `direct_refs_required` int unsigned NOT NULL DEFAULT 0,
  `bonus_per_cycle` decimal(18,2) NOT NULL DEFAULT 0.00,
  `cycle_trigger_refs_or_topups` int unsigned NOT NULL DEFAULT 3,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Transactions (buy/sell, top-up, withdrawal, credits)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `type` enum('topup','buy_gold','sell_gold','withdrawal','credit_income','debit','refund','adjustment') NOT NULL,
  `amount` decimal(18,2) NOT NULL COMMENT 'Currency amount',
  `gold_grams` decimal(18,6) DEFAULT NULL,
  `rate_per_gram` decimal(18,2) DEFAULT NULL,
  `status` enum('pending','completed','failed','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `reference_type` varchar(64) DEFAULT NULL COMMENT 'e.g. self_dividend, gsc, rank_bonus',
  `reference_id` int unsigned DEFAULT NULL,
  `admin_id` int unsigned DEFAULT NULL,
  `notes` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `type` (`type`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  KEY `reference` (`reference_type`,`reference_id`),
  CONSTRAINT `transactions_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_admin_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Incomes (detailed log: type, amount, from_user, level, cap)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `incomes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL COMMENT 'Earner',
  `income_type` varchar(64) NOT NULL COMMENT 'self_dividend, booster_dividend, level_dividend, gsc, booster_gsc, rank_infinity',
  `amount` decimal(18,2) NOT NULL,
  `from_user_id` int unsigned DEFAULT NULL COMMENT 'Source of income (e.g. downline)',
  `level` int unsigned DEFAULT NULL COMMENT 'Level in tree',
  `plan_id` int unsigned DEFAULT NULL,
  `transaction_id` int unsigned DEFAULT NULL,
  `working_day` int unsigned DEFAULT NULL COMMENT 'Which working day for dividend',
  `capped` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `income_type` (`income_type`),
  KEY `from_user_id` (`from_user_id`),
  KEY `created_at` (`created_at`),
  KEY `plan_id` (`plan_id`),
  CONSTRAINT `incomes_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `incomes_from_user_fk` FOREIGN KEY (`from_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `incomes_plan_fk` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `incomes_transaction_fk` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add transaction_id FK after incomes exists (optional, already in schema)
-- ALTER TABLE incomes ADD CONSTRAINT incomes_transaction_fk FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL;

-- --------------------------------------------------------
-- User activation / dividend state (working days, cap reached)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_activations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `plan_id` int unsigned NOT NULL,
  `activation_amount` decimal(18,2) NOT NULL,
  `gold_grams_at_activation` decimal(18,6) NOT NULL,
  `working_days_count` int unsigned NOT NULL DEFAULT 0,
  `total_dividend_earned` decimal(18,2) NOT NULL DEFAULT 0.00,
  `cap_reached` tinyint(1) NOT NULL DEFAULT 0,
  `cap_reached_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`),
  KEY `cap_reached` (`cap_reached`),
  CONSTRAINT `user_activations_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_activations_plan_fk` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- GSC cycles (track 3 refs/topups per cycle)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `gsc_cycles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `club_slug` varchar(32) NOT NULL,
  `cycle_count` int unsigned NOT NULL DEFAULT 0,
  `trigger_count` int unsigned NOT NULL DEFAULT 0 COMMENT 'Refs or topups in current cycle',
  `last_cycle_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_club` (`user_id`,`club_slug`),
  CONSTRAINT `gsc_cycles_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- KYC documents
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `kyc_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `document_type` enum('id_proof','address_proof','other') NOT NULL,
  `file_path` varchar(512) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `rejected_reason` text,
  `reviewed_by_admin_id` int unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  CONSTRAINT `kyc_documents_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `kyc_documents_admin_fk` FOREIGN KEY (`reviewed_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- CMS pages (editable from admin)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cms_pages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(64) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` longtext,
  `meta_description` varchar(512) DEFAULT NULL,
  `is_published` tinyint(1) NOT NULL DEFAULT 1,
  `updated_by_admin_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `is_published` (`is_published`),
  CONSTRAINT `cms_pages_admin_fk` FOREIGN KEY (`updated_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- CMS page images (per-page image slots editable from admin)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cms_page_images` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `page_slug` varchar(64) NOT NULL,
  `image_key` varchar(64) NOT NULL,
  `image_url` varchar(512) NOT NULL DEFAULT '',
  `alt_text` varchar(255) DEFAULT NULL,
  `updated_by_admin_id` int unsigned DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_image` (`page_slug`,`image_key`),
  KEY `page_slug` (`page_slug`),
  CONSTRAINT `cms_page_images_admin_fk` FOREIGN KEY (`updated_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- System / audit logs
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `system_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `action` varchar(64) NOT NULL,
  `entity_type` varchar(64) DEFAULT NULL,
  `entity_id` int unsigned DEFAULT NULL,
  `admin_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `old_value` json DEFAULT NULL,
  `new_value` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `action` (`action`),
  KEY `entity` (`entity_type`,`entity_id`),
  KEY `admin_id` (`admin_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `system_logs_admin_fk` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL,
  CONSTRAINT `system_logs_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Sell requests (user requests to sell gold)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sell_requests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `gold_grams` decimal(18,6) NOT NULL,
  `rate_per_gram` decimal(18,2) NOT NULL,
  `total_amount` decimal(18,2) NOT NULL,
  `status` enum('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending',
  `processed_by_admin_id` int unsigned DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `reject_reason` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  CONSTRAINT `sell_requests_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sell_requests_admin_fk` FOREIGN KEY (`processed_by_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- Seed default plan (from PDF: $100 topup, 1% daily, 200% days, 300% cap)
-- --------------------------------------------------------
INSERT INTO `plans` (
  `name`, `slug`, `activation_amount`, `daily_dividend_pct`, `max_working_days_pct`, `max_income_cap_pct`,
  `gsc_cycle_amount`, `gsc_qualify_direct_refs`, `booster_gsc_qualify_direct_refs`, `booster_gsc_per_level_amount`, `booster_gsc_levels`,
  `level_dividend_1_pct`, `level_dividend_2_pct`, `level_dividend_3_pct`, `level_dividend_4_pct`, `level_dividend_5_pct`,
  `level_dividend_direct_refs_required`, `min_withdrawal`, `withdrawal_multiple`, `withdrawals_per_day`,
  `system_deduction_pct`, `processing_fee_pct`, `dividend_liquidity_pct`, `topup_wallet_pct`, `is_default`, `is_active`
) VALUES (
  'Standard Gold Plan', 'standard', 100.00, 1.00, 200.00, 300.00,
  100.00, 3, 12, 5.00, 10,
  5.00, 1.00, 1.00, 1.00, 1.00,
  12, 10.00, 10.00, 1,
  30.00, 5.00, 5.00, 20.00, 1, 1
) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- --------------------------------------------------------
-- Seed ranks (D1, D2, D3)
-- --------------------------------------------------------
INSERT INTO `ranks` (`slug`, `name`, `sort_order`, `direct_referrals_required`, `total_team_required`, `self_topup_required`, `parent_rank_qualifiers_required`, `parent_rank_slug`, `bonus_per_id`, `unlimited_levels`) VALUES
('D1', 'D-1 Rank', 1, 12, 150, 100.00, 0, NULL, 3.00, 1),
('D2', 'D-2 Rank', 2, 0, 0, 0.00, 3, 'D1', 4.00, 1),
('D3', 'D-3 Rank', 3, 0, 0, 0.00, 3, 'D2', 5.00, 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- --------------------------------------------------------
-- Seed clubs
-- --------------------------------------------------------
INSERT INTO `clubs` (`slug`, `name`, `direct_refs_required`, `bonus_per_cycle`, `cycle_trigger_refs_or_topups`) VALUES
('gsc', 'Global Society Club', 3, 100.00, 3),
('booster_gsc', 'Booster GSC', 12, 100.00, 3)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);
