-- ============================================================
-- Full MySQL Database Export - Tansparks Investment Platform
-- Compatible with phpMyAdmin Import
-- Generated: 2026-02-16
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- TABLE: admin_accounts (replaces Supabase auth.users for MySQL)
-- ============================================================
DROP TABLE IF EXISTS `admin_accounts`;
CREATE TABLE `admin_accounts` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admin_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: profiles
-- ============================================================
DROP TABLE IF EXISTS `profiles`;
CREATE TABLE `profiles` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `telegram_id` BIGINT NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) DEFAULT NULL,
  `username` VARCHAR(255) DEFAULT NULL,
  `photo_url` TEXT DEFAULT NULL,
  `balance` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `is_banned` TINYINT(1) NOT NULL DEFAULT 0,
  `kyc_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `referral_code` VARCHAR(50) DEFAULT NULL,
  `referred_by` CHAR(36) DEFAULT NULL,
  `transaction_pin` VARCHAR(10) DEFAULT NULL,
  `wallet_address` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_telegram_id` (`telegram_id`),
  KEY `idx_referral_code` (`referral_code`),
  KEY `idx_referred_by` (`referred_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: user_roles
-- ============================================================
DROP TABLE IF EXISTS `user_roles`;
CREATE TABLE `user_roles` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `role` ENUM('admin','moderator','user') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_roles_user_id` (`user_id`),
  CONSTRAINT `fk_user_roles_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: site_settings
-- ============================================================
DROP TABLE IF EXISTS `site_settings`;
CREATE TABLE `site_settings` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `site_name` VARCHAR(255) NOT NULL DEFAULT 'InvestPro',
  `currency` VARCHAR(10) DEFAULT 'USD',
  `currency_symbol` VARCHAR(10) DEFAULT '$',
  `timezone` VARCHAR(50) DEFAULT 'UTC',
  `min_deposit` DECIMAL(18,2) NOT NULL DEFAULT 50,
  `min_withdrawal` DECIMAL(18,2) NOT NULL DEFAULT 10,
  `withdrawal_fee` DECIMAL(18,2) NOT NULL DEFAULT 2,
  `referral_commission` DECIMAL(5,2) NOT NULL DEFAULT 5,
  `deposits_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `withdrawals_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `maintenance_mode` TINYINT(1) NOT NULL DEFAULT 0,
  `pin_required` TINYINT(1) NOT NULL DEFAULT 1,
  `kyc_verification` TINYINT(1) DEFAULT 0,
  `user_registration` TINYINT(1) DEFAULT 1,
  `show_onboarding` TINYINT(1) NOT NULL DEFAULT 1,
  `show_announcement` TINYINT(1) NOT NULL DEFAULT 1,
  `enable_theme_toggle` TINYINT(1) NOT NULL DEFAULT 1,
  `enable_analytics` TINYINT(1) NOT NULL DEFAULT 1,
  `enable_transaction_export` TINYINT(1) NOT NULL DEFAULT 1,
  `announcement_message` TEXT DEFAULT NULL,
  `bot_url` TEXT DEFAULT NULL,
  `bot_token` TEXT DEFAULT NULL,
  `bot_start_message` TEXT DEFAULT 'Welcome! 🎉 Start investing with us today.',
  `bot_webhook_url` TEXT DEFAULT NULL,
  `bot_cmd_balance` TINYINT(1) NOT NULL DEFAULT 1,
  `bot_cmd_help` TINYINT(1) NOT NULL DEFAULT 1,
  `bot_cmd_referral` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: payment_gateways
-- ============================================================
DROP TABLE IF EXISTS `payment_gateways`;
CREATE TABLE `payment_gateways` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `name` VARCHAR(255) NOT NULL,
  `image_url` TEXT DEFAULT NULL,
  `currency` VARCHAR(20) NOT NULL DEFAULT 'USD',
  `rate` DECIMAL(18,6) NOT NULL DEFAULT 1,
  `min_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `max_amount` DECIMAL(18,2) NOT NULL DEFAULT 100000,
  `fixed_charge` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `percent_charge` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `instruction` TEXT DEFAULT NULL,
  `user_data` JSON DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: withdrawal_methods
-- ============================================================
DROP TABLE IF EXISTS `withdrawal_methods`;
CREATE TABLE `withdrawal_methods` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `name` VARCHAR(255) NOT NULL,
  `image_url` TEXT DEFAULT NULL,
  `currency` VARCHAR(20) NOT NULL DEFAULT 'USD',
  `rate` DECIMAL(18,6) NOT NULL DEFAULT 1,
  `min_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `max_amount` DECIMAL(18,2) NOT NULL DEFAULT 100000,
  `fixed_charge` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `percent_charge` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `instruction` TEXT DEFAULT NULL,
  `user_data` JSON DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: investment_plans
-- ============================================================
DROP TABLE IF EXISTS `investment_plans`;
CREATE TABLE `investment_plans` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(50) DEFAULT 'General',
  `min_amount` DECIMAL(18,2) NOT NULL DEFAULT 50,
  `max_amount` DECIMAL(18,2) NOT NULL DEFAULT 10000,
  `daily_roi` DECIMAL(10,4) NOT NULL DEFAULT 0,
  `duration_days` INT NOT NULL DEFAULT 30,
  `time_unit` VARCHAR(20) DEFAULT 'Day',
  `icon` VARCHAR(50) DEFAULT 'Zap',
  `color` VARCHAR(100) DEFAULT 'from-primary to-primary',
  `capital_back` TINYINT(1) DEFAULT 1,
  `compound_interest` TINYINT(1) DEFAULT 0,
  `featured` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `repeat_times` INT DEFAULT 30,
  `return_type` VARCHAR(20) DEFAULT 'Repeat',
  `interest_type` VARCHAR(20) DEFAULT 'Percent',
  `invest_type` VARCHAR(20) DEFAULT 'Range',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: investments
-- ============================================================
DROP TABLE IF EXISTS `investments`;
CREATE TABLE `investments` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `plan_id` CHAR(36) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `earned` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `started_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ends_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_investments_user_id` (`user_id`),
  KEY `idx_investments_plan_id` (`plan_id`),
  KEY `idx_investments_status` (`status`),
  CONSTRAINT `fk_investments_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_investments_plan_id` FOREIGN KEY (`plan_id`) REFERENCES `investment_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: deposits
-- ============================================================
DROP TABLE IF EXISTS `deposits`;
CREATE TABLE `deposits` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `method` VARCHAR(100) NOT NULL DEFAULT 'usdt',
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `tx_hash` TEXT DEFAULT NULL,
  `proof_url` TEXT DEFAULT NULL,
  `admin_note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_deposits_user_id` (`user_id`),
  KEY `idx_deposits_status` (`status`),
  CONSTRAINT `fk_deposits_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: withdrawals
-- ============================================================
DROP TABLE IF EXISTS `withdrawals`;
CREATE TABLE `withdrawals` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `fee` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `wallet_address` VARCHAR(255) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `admin_note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_withdrawals_user_id` (`user_id`),
  KEY `idx_withdrawals_status` (`status`),
  CONSTRAINT `fk_withdrawals_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: notifications
-- ============================================================
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT 'info',
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notifications_user_id` (`user_id`),
  CONSTRAINT `fk_notifications_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: kyc_fields
-- ============================================================
DROP TABLE IF EXISTS `kyc_fields`;
CREATE TABLE `kyc_fields` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `label` VARCHAR(255) NOT NULL,
  `field_type` VARCHAR(20) NOT NULL DEFAULT 'text',
  `options` TEXT DEFAULT NULL,
  `required` TINYINT(1) NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: kyc_submissions
-- ============================================================
DROP TABLE IF EXISTS `kyc_submissions`;
CREATE TABLE `kyc_submissions` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `data` JSON NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `admin_note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_kyc_submissions_user_id` (`user_id`),
  CONSTRAINT `fk_kyc_submissions_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: referral_commissions
-- ============================================================
DROP TABLE IF EXISTS `referral_commissions`;
CREATE TABLE `referral_commissions` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `commission_type` VARCHAR(50) NOT NULL,
  `level` INT NOT NULL DEFAULT 1,
  `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: redeem_codes
-- ============================================================
DROP TABLE IF EXISTS `redeem_codes`;
CREATE TABLE `redeem_codes` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `code` VARCHAR(100) NOT NULL,
  `reward_type` VARCHAR(50) NOT NULL DEFAULT 'investment',
  `reward_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `plan_id` CHAR(36) DEFAULT NULL,
  `max_uses` INT NOT NULL DEFAULT 1,
  `per_user_limit` INT NOT NULL DEFAULT 1,
  `used_count` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_redeem_code` (`code`),
  KEY `idx_redeem_codes_plan_id` (`plan_id`),
  CONSTRAINT `fk_redeem_codes_plan_id` FOREIGN KEY (`plan_id`) REFERENCES `investment_plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: redeem_history
-- ============================================================
DROP TABLE IF EXISTS `redeem_history`;
CREATE TABLE `redeem_history` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `user_id` CHAR(36) NOT NULL,
  `code_id` CHAR(36) NOT NULL,
  `investment_id` CHAR(36) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_redeem_history_user_id` (`user_id`),
  KEY `idx_redeem_history_code_id` (`code_id`),
  CONSTRAINT `fk_redeem_history_user_id` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_redeem_history_code_id` FOREIGN KEY (`code_id`) REFERENCES `redeem_codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_redeem_history_investment_id` FOREIGN KEY (`investment_id`) REFERENCES `investments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: time_settings
-- ============================================================
DROP TABLE IF EXISTS `time_settings`;
CREATE TABLE `time_settings` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `unit` VARCHAR(20) NOT NULL,
  `label` VARCHAR(50) NOT NULL,
  `multiplier_days` DECIMAL(10,6) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: onboarding_slides
-- ============================================================
DROP TABLE IF EXISTS `onboarding_slides`;
CREATE TABLE `onboarding_slides` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `image_url` TEXT DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: cron_logs
-- ============================================================
DROP TABLE IF EXISTS `cron_logs`;
CREATE TABLE `cron_logs` (
  `id` CHAR(36) NOT NULL DEFAULT (UUID()),
  `status` VARCHAR(20) NOT NULL DEFAULT 'success',
  `message` TEXT DEFAULT NULL,
  `processed` INT NOT NULL DEFAULT 0,
  `matured` INT NOT NULL DEFAULT 0,
  `errors` INT NOT NULL DEFAULT 0,
  `total_active` INT NOT NULL DEFAULT 0,
  `duration_ms` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- DEMO DATA
-- ============================================================

-- ------------------------------------------------------------
-- Site Settings
-- ------------------------------------------------------------
INSERT INTO `site_settings` (`id`, `site_name`, `currency`, `currency_symbol`, `timezone`, `min_deposit`, `min_withdrawal`, `withdrawal_fee`, `referral_commission`, `deposits_enabled`, `withdrawals_enabled`, `maintenance_mode`, `pin_required`, `kyc_verification`, `user_registration`, `show_onboarding`, `show_announcement`, `enable_theme_toggle`, `enable_analytics`, `enable_transaction_export`, `announcement_message`, `bot_start_message`, `bot_cmd_balance`, `bot_cmd_help`, `bot_cmd_referral`) VALUES
('c8feb8bb-684e-4c37-b3c5-b1f55cbd0f8f', 'Tansparks', 'INR', '₹', 'UTC', 50, 10, 2, 5, 1, 1, 0, 0, 1, 1, 1, 1, 0, 1, 1, 'Welcome to tan sparks', 'Welcome! 🎉 Start investing with us today.', 1, 1, 1);

-- ------------------------------------------------------------
-- Time Settings
-- ------------------------------------------------------------
INSERT INTO `time_settings` (`id`, `unit`, `label`, `multiplier_days`) VALUES
('92dd653a-4859-414c-b182-feb2b9d19835', 'Minute', 'Minutes', 0.000694),
('480379b2-c97e-4dbc-83be-d8e33fb87b7f', 'Hour', 'Hours', 0.041667),
('b0d1f73e-48fb-4670-9618-26d26dd3b198', 'Day', 'Days', 1),
('4c2e9c10-e6fd-4002-b045-98e7aa2af169', 'Week', 'Weekly', 7),
('c6b39955-149b-44a8-b4ba-664cda14c09c', 'Month', 'Month', 30);

-- ------------------------------------------------------------
-- Payment Gateways
-- ------------------------------------------------------------
INSERT INTO `payment_gateways` (`id`, `name`, `currency`, `rate`, `min_amount`, `max_amount`, `fixed_charge`, `percent_charge`, `instruction`, `user_data`, `is_active`) VALUES
('341bff9b-0dd3-45e7-b983-e7bea6ab702f', 'USDT ERC20', 'USDT', 0.012000, 50, 100000, 0, 1.50, 'Send USDT to the ERC20 address below. Ensure you select the ERC20 (Ethereum) network. Gas fees apply.', '[{"label":"Transaction Hash","name":"TxHash","required":true,"type":"text"},{"label":"Payment Screenshot","name":"screenshot","required":true,"type":"image"}]', 1),
('4c11daac-20a0-4688-a157-cffa51c501d0', 'USDT BEP20', 'USDT', 0.012000, 10, 100000, 0, 0.50, 'Send USDT to the BEP20 address below. Ensure you select the BEP20 (BSC) network. Low fees, fast confirmation.', '[{"label":"Transaction Hash","name":"TxHash","required":true,"type":"text"},{"label":"Payment Screenshot","name":"screenshot","required":true,"type":"image"}]', 1),
('44686bd8-9b41-4b91-a75f-5b40a583671a', 'TON', 'TON', 0.004400, 5, 50000, 0, 1.00, 'Send TON to the address below. Include the memo/comment if provided. Confirms in seconds.', '[{"label":"Transaction Hash","name":"TxHash","required":true,"type":"text"},{"label":"Memo/Comment","name":"memo","required":false,"type":"text"},{"label":"Payment Screenshot","name":"screenshot","required":true,"type":"image"}]', 1),
('ff04e92a-1772-4cb5-bd14-ee5c526b017c', 'USDT TRC20', 'USDT', 0.012000, 10, 100000, 0, 1.00, 'Send USDT to the TRC20 address below. Ensure you select the TRC20 (TRON) network. Transaction typically confirms in 1-3 minutes.', '[{"label":"Transaction Hash","name":"TxHash","required":true,"type":"text"},{"label":"Payment Screenshot","name":"screenshot","required":true,"type":"image"}]', 1);

-- ------------------------------------------------------------
-- Withdrawal Methods
-- ------------------------------------------------------------
INSERT INTO `withdrawal_methods` (`id`, `name`, `currency`, `rate`, `min_amount`, `max_amount`, `fixed_charge`, `percent_charge`, `instruction`, `user_data`, `is_active`) VALUES
('8e4f9ce8-a460-428e-a1a0-b7bcbc6c754e', 'USDT TRC20', 'USDT', 0.012000, 10, 50000, 1, 0.50, 'Provide your TRC20 wallet address. Withdrawals are processed within 24 hours.', '[{"label":"TRC20 Wallet Address","name":"wallet_address","required":true,"type":"text"}]', 1),
('5c7bbdc5-49dc-4043-a4e0-9662742f9d1f', 'USDT ERC20', 'USDT', 0.012000, 50, 50000, 5, 1.00, 'Provide your ERC20 wallet address. Gas fees included in charges.', '[{"label":"ERC20 Wallet Address","name":"wallet_address","required":true,"type":"text"}]', 1),
('09968af5-c543-4552-9051-49513a0c0574', 'USDT BEP20', 'USDT', 0.012000, 10, 50000, 0.50, 0.30, 'Provide your BEP20 wallet address. Fast and low-cost withdrawals.', '[{"label":"BEP20 Wallet Address","name":"wallet_address","required":true,"type":"text"}]', 1),
('dd1348a4-3bca-48d4-a0a7-b02890cdae42', 'TON', 'TON', 0.004400, 5, 50000, 0.50, 0.50, 'Provide your TON wallet address. Processed within 24 hours.', '[{"label":"TON Wallet Address","name":"wallet_address","required":true,"type":"text"},{"label":"Memo (if required)","name":"memo","required":false,"type":"text"}]', 1);

-- ------------------------------------------------------------
-- Investment Plans - Starter (Minutes)
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Quick Flip', 'Starter', 10, 500, 0.5000, 30, 'Minute', 'Zap', 'from-yellow-500 to-orange-500', 1, 0, 0, 1, 30, 'Repeat', 'Percent', 'Range'),
('Flash Deal', 'Starter', 5, 200, 1.0000, 15, 'Minute', 'Star', 'from-pink-500 to-rose-500', 0, 0, 0, 1, 15, 'Repeat', 'Percent', 'Range'),
('Micro Burst', 'Starter', 10, 300, 0.6000, 45, 'Minute', 'TrendingUp', 'from-amber-400 to-orange-600', 1, 0, 0, 1, 45, 'Repeat', 'Percent', 'Range'),
('Rapid Fire', 'Starter', 25, 750, 1.2000, 20, 'Minute', 'Gem', 'from-red-400 to-pink-600', 0, 0, 1, 1, 20, 'Repeat', 'Percent', 'Range'),
('Speed Boost', 'Starter', 20, 1000, 0.8000, 60, 'Minute', 'Rocket', 'from-orange-500 to-red-500', 1, 0, 0, 1, 60, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - Hourly
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Hour Power', 'Hourly', 25, 2000, 0.3000, 12, 'Hour', 'Zap', 'from-lime-500 to-green-600', 1, 0, 0, 1, 12, 'Repeat', 'Percent', 'Range'),
('Hourly Hustle', 'Hourly', 30, 3000, 0.4000, 48, 'Hour', 'Star', 'from-cyan-500 to-sky-600', 1, 0, 0, 1, 48, 'Repeat', 'Percent', 'Range'),
('Express Earn', 'Hourly', 50, 5000, 0.5000, 24, 'Hour', 'Rocket', 'from-green-400 to-emerald-600', 1, 0, 1, 1, 24, 'Repeat', 'Percent', 'Range'),
('Fast Lane', 'Hourly', 75, 7500, 0.6000, 8, 'Hour', 'Crown', 'from-sky-500 to-blue-600', 1, 0, 0, 1, 8, 'Repeat', 'Percent', 'Range'),
('Turbo Trade', 'Hourly', 100, 10000, 0.8000, 6, 'Hour', 'TrendingUp', 'from-teal-400 to-cyan-600', 0, 0, 0, 1, 6, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - Daily
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Daily Growth', 'Daily', 50, 5000, 2.5000, 7, 'Day', 'TrendingUp', 'from-green-500 to-emerald-600', 1, 0, 0, 1, 7, 'Repeat', 'Percent', 'Range'),
('Cash Flow', 'Daily', 50, 3000, 2.0000, 10, 'Day', 'Banknote', 'from-emerald-400 to-green-600', 1, 0, 0, 1, 10, 'Repeat', 'Percent', 'Range'),
('Morning Star', 'Daily', 100, 8000, 2.8000, 5, 'Day', 'Star', 'from-sky-400 to-blue-600', 0, 0, 0, 1, 5, 'Repeat', 'Percent', 'Range'),
('Steady Income', 'Daily', 100, 10000, 3.0000, 14, 'Day', 'Shield', 'from-teal-500 to-cyan-600', 1, 0, 1, 1, 14, 'Repeat', 'Percent', 'Range'),
('Power Daily', 'Daily', 200, 20000, 3.5000, 30, 'Day', 'Zap', 'from-blue-500 to-indigo-600', 1, 0, 0, 1, 30, 'Repeat', 'Percent', 'Range'),
('Daily Elite', 'Daily', 500, 50000, 4.0000, 21, 'Day', 'Crown', 'from-indigo-500 to-purple-600', 1, 0, 1, 1, 21, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - Weekly
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Weekly Wealth', 'Weekly', 100, 15000, 5.0000, 4, 'Week', 'Gem', 'from-purple-500 to-violet-600', 1, 0, 0, 1, 4, 'Repeat', 'Percent', 'Range'),
('7-Day Fortune', 'Weekly', 250, 25000, 7.0000, 2, 'Week', 'Crown', 'from-violet-500 to-fuchsia-600', 1, 0, 1, 1, 2, 'Repeat', 'Percent', 'Range'),
('Compound Week', 'Weekly', 200, 20000, 6.0000, 6, 'Week', 'RefreshCw', 'from-rose-400 to-red-600', 1, 0, 0, 1, 6, 'Repeat', 'Percent', 'Range'),
('Lucky Seven', 'Weekly', 77, 7777, 7.7000, 7, 'Week', 'Star', 'from-amber-500 to-yellow-600', 1, 0, 1, 1, 7, 'Repeat', 'Percent', 'Range'),
('Weekly Premium', 'Weekly', 500, 50000, 8.5000, 8, 'Week', 'Rocket', 'from-fuchsia-500 to-pink-600', 1, 0, 0, 1, 8, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - Premium
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Gold Reserve', 'Premium', 1000, 100000, 5.0000, 30, 'Day', 'Shield', 'from-yellow-500 to-amber-600', 1, 0, 1, 1, 30, 'Repeat', 'Percent', 'Range'),
('Elite Earnings', 'Premium', 2000, 200000, 7.0000, 14, 'Day', 'TrendingUp', 'from-emerald-500 to-teal-600', 1, 0, 0, 1, 14, 'Repeat', 'Percent', 'Range'),
('Platinum Vault', 'Premium', 2500, 250000, 6.5000, 60, 'Day', 'Crown', 'from-slate-400 to-zinc-600', 1, 0, 0, 1, 60, 'Repeat', 'Percent', 'Range'),
('Diamond Fund', 'Premium', 5000, 500000, 8.0000, 90, 'Day', 'Gem', 'from-cyan-400 to-blue-600', 1, 0, 1, 1, 90, 'Repeat', 'Percent', 'Range'),
('VIP Portfolio', 'Premium', 10000, 1000000, 10.0000, 45, 'Day', 'Rocket', 'from-amber-400 to-yellow-600', 1, 0, 0, 1, 45, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - Monthly
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Monthly Master', 'Monthly', 500, 50000, 15.0000, 1, 'Month', 'Shield', 'from-indigo-500 to-blue-600', 1, 0, 0, 1, 1, 'Repeat', 'Percent', 'Range'),
('Savings Pro', 'Monthly', 250, 25000, 12.0000, 2, 'Month', 'Banknote', 'from-fuchsia-400 to-violet-600', 1, 0, 0, 1, 2, 'Repeat', 'Percent', 'Range'),
('Quarterly Gain', 'Monthly', 1000, 100000, 20.0000, 3, 'Month', 'Gem', 'from-purple-500 to-indigo-600', 1, 0, 1, 1, 3, 'Repeat', 'Percent', 'Range'),
('Long Haul', 'Monthly', 2000, 200000, 25.0000, 6, 'Month', 'Crown', 'from-violet-500 to-purple-600', 1, 0, 0, 1, 6, 'Repeat', 'Percent', 'Range'),
('Annual Titan', 'Monthly', 5000, 500000, 50.0000, 12, 'Month', 'Rocket', 'from-rose-500 to-pink-600', 1, 0, 1, 1, 12, 'Repeat', 'Percent', 'Range');

-- ------------------------------------------------------------
-- Investment Plans - General
-- ------------------------------------------------------------
INSERT INTO `investment_plans` (`name`, `category`, `min_amount`, `max_amount`, `daily_roi`, `duration_days`, `time_unit`, `icon`, `color`, `capital_back`, `compound_interest`, `featured`, `is_active`, `repeat_times`, `return_type`, `interest_type`, `invest_type`) VALUES
('Gold', 'General', 199, 10000, 15.0000, 30, 'Minute', 'Zap', 'from-primary to-primary', 1, 1, 1, 1, 30, 'Repeat', 'Percent', 'Fixed');

-- ------------------------------------------------------------
-- Referral Commissions
-- ------------------------------------------------------------
INSERT INTO `referral_commissions` (`commission_type`, `level`, `percentage`) VALUES
('deposit', 1, 5), ('deposit', 2, 4), ('deposit', 3, 3), ('deposit', 4, 2), ('deposit', 5, 1),
('invest', 1, 5), ('invest', 2, 4), ('invest', 3, 3), ('invest', 4, 2), ('invest', 5, 1),
('interest', 1, 1), ('interest', 2, 2), ('interest', 3, 3), ('interest', 4, 4), ('interest', 5, 5);

-- ------------------------------------------------------------
-- KYC Fields
-- ------------------------------------------------------------
INSERT INTO `kyc_fields` (`id`, `label`, `field_type`, `options`, `required`, `is_active`, `sort_order`) VALUES
('b2e5514d-b974-40e3-8a98-67ab8bd95995', 'Aadhar number', 'number', NULL, 1, 1, 0),
('e834e499-d8ed-428e-8cbe-e04e19a27164', 'Upload proof', 'image', NULL, 1, 1, 1);

-- ------------------------------------------------------------
-- Onboarding Slides
-- ------------------------------------------------------------
INSERT INTO `onboarding_slides` (`title`, `description`, `sort_order`, `is_active`) VALUES
('Welcome to InvestPro! 🎉', 'Start growing your wealth with our trusted investment plans and earn daily returns effortlessly.', 0, 1),
('Choose Your Plan 📊', 'Pick from multiple investment plans starting at just $50. Fixed or flexible — you decide how to grow.', 1, 1),
('Earn Daily Returns 💰', 'Watch your earnings grow every single day. Track your profits in real-time from your dashboard.', 2, 1),
('Refer & Earn More 🤝', 'Share your referral code with friends and earn bonus commission on every deposit they make.', 3, 1),
('Secure & Instant ⚡', 'Your funds are protected. Deposit and withdraw anytime with fast, reliable processing.', 4, 1);

-- ------------------------------------------------------------
-- Demo Users
-- ------------------------------------------------------------
INSERT INTO `profiles` (`id`, `telegram_id`, `first_name`, `last_name`, `username`, `balance`, `is_admin`, `referral_code`) VALUES
('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 1000000001, 'Admin', 'User', 'admin_user', 50000.00, 1, 'ADMIN01'),
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 1000000002, 'Demo', 'Investor', 'demo_investor', 5000.00, 0, 'DEMO01'),
('c3d4e5f6-a7b8-9012-cdef-123456789012', 1000000003, 'Test', 'User', 'test_user', 1500.00, 0, 'TEST01');

-- Admin role for admin user
INSERT INTO `user_roles` (`user_id`, `role`) VALUES
('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 'admin');

-- Admin login account (password: admin123 — change in production!)
-- Password hash is bcrypt of 'admin123'
INSERT INTO `admin_accounts` (`id`, `email`, `password_hash`) VALUES
('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 'admin@tansparks.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- ------------------------------------------------------------
-- Demo Deposits
-- ------------------------------------------------------------
INSERT INTO `deposits` (`user_id`, `amount`, `method`, `status`) VALUES
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 1000.00, 'USDT TRC20', 'approved'),
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 500.00, 'USDT BEP20', 'pending'),
('c3d4e5f6-a7b8-9012-cdef-123456789012', 2000.00, 'TON', 'approved'),
('c3d4e5f6-a7b8-9012-cdef-123456789012', 300.00, 'USDT ERC20', 'rejected');

-- ------------------------------------------------------------
-- Demo Withdrawals
-- ------------------------------------------------------------
INSERT INTO `withdrawals` (`user_id`, `amount`, `fee`, `wallet_address`, `status`) VALUES
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 200.00, 2.00, '0xAbC123...dEf456', 'approved'),
('c3d4e5f6-a7b8-9012-cdef-123456789012', 100.00, 2.00, 'TRX789...xyz012', 'pending');

-- ------------------------------------------------------------
-- Demo Notifications
-- ------------------------------------------------------------
INSERT INTO `notifications` (`user_id`, `title`, `message`, `type`) VALUES
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 'Deposit Approved', 'Your deposit of ₹1,000 has been approved.', 'success'),
('b2c3d4e5-f6a7-8901-bcde-f12345678901', 'Welcome!', 'Welcome to Tansparks! Start investing today.', 'info'),
('c3d4e5f6-a7b8-9012-cdef-123456789012', 'KYC Required', 'Please complete your KYC verification.', 'warning'),
(NULL, 'System Update', 'New investment plans are now available!', 'info');


SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- END OF EXPORT
-- ============================================================