-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `smartbulls2026`
--

-- --------------------------------------------------------

--
-- Table structure for table `access_token`
--

CREATE TABLE `access_token` (
  `id` int NOT NULL,
  `api_key` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `access_token` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cdate` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `id` int NOT NULL,
  `username` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('Yes','No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `cal_reward`
--

CREATE TABLE `cal_reward` (
  `id` int NOT NULL,
  `actual_user` int NOT NULL,
  `users_in_level` int NOT NULL,
  `reward_per_pool` float NOT NULL,
  `reward_per_winner` float NOT NULL,
  `reward` float NOT NULL,
  `corder` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contests`
--

CREATE TABLE `contests` (
  `cid` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_datetime` int NOT NULL,
  `end_datetime` int NOT NULL,
  `max_users` int NOT NULL,
  `min_users` int NOT NULL,
  `entryfee` double(8,2) NOT NULL,
  `type` enum('practice') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'practice',
  `max_pool_amount` double(15,2) NOT NULL,
  `current_pool_amount` double(15,2) NOT NULL,
  `fund_value` double(30,2) NOT NULL,
  `reward_type` enum('guaranteed','flexible') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `segment` enum('equity','nfo','crypto','all') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `notify` enum('no','yes') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','upcoming','starting soon','live','in review','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `contest_type` enum('public','private') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'public',
  `trade_limit` bigint NOT NULL DEFAULT '0',
  `user_group` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contest_join`
--

CREATE TABLE `contest_join` (
  `id` int NOT NULL,
  `cid` int NOT NULL,
  `uid` int NOT NULL,
  `utid` int NOT NULL,
  `net_balance` decimal(30,2) GENERATED ALWAYS AS (((((`initial_fund` + `topup_balance`) + `winning`) - `uprofit`) - `invested`)) STORED NOT NULL,
  `invested` double(30,2) NOT NULL,
  `winning` double(30,2) NOT NULL DEFAULT '0.00',
  `crank` int NOT NULL DEFAULT '0',
  `reward_amount` bigint UNSIGNED DEFAULT '0',
  `corder` bigint NOT NULL,
  `uprofit` double(30,2) NOT NULL COMMENT 'CHARGES',
  `crypto_charges` double(30,2) NOT NULL DEFAULT '0.00',
  `fno_charges` double(30,2) NOT NULL DEFAULT '0.00',
  `equity_charges` double(30,2) NOT NULL DEFAULT '0.00',
  `crypto_profit` double(30,2) NOT NULL DEFAULT '0.00' COMMENT 'REALIZED',
  `fno_profit` double(30,2) NOT NULL DEFAULT '0.00' COMMENT 'REALIZED',
  `equity_profit` double(30,2) NOT NULL DEFAULT '0.00' COMMENT 'REALIZED',
  `buy_order_limit` int DEFAULT '0',
  `sell_order_limit` int DEFAULT '0',
  `initial_fund` decimal(30,2) NOT NULL DEFAULT '0.00',
  `topup_balance` int NOT NULL DEFAULT '0',
  `is_test_user` int NOT NULL DEFAULT '0' COMMENT '0 = Regular user, 1 = Test user',
  `fund_value` double(30,2) NOT NULL COMMENT 'BALANCE',
  `balance` double(30,2) GENERATED ALWAYS AS ((`fund_value` + `winning`)) STORED COMMENT 'FUND_VALUE + WINNING'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contest_schedule`
--

CREATE TABLE `contest_schedule` (
  `csid` int NOT NULL,
  `max_users` int DEFAULT NULL,
  `min_users` int DEFAULT NULL,
  `entryfee` decimal(10,2) DEFAULT NULL,
  `max_pool_amount` decimal(10,2) DEFAULT NULL,
  `current_pool_amount` decimal(10,2) DEFAULT NULL,
  `fund_value` decimal(10,2) DEFAULT NULL,
  `gid` int DEFAULT '0',
  `auto_launch` int DEFAULT '1',
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `segment` enum('equity','nfo','crypto') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `type` varchar(50) DEFAULT 'Practice',
  `reward_type` enum('Guaranteed','Flexible') DEFAULT 'Guaranteed'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `equity_stock`
--

CREATE TABLE `equity_stock` (
  `sid` int NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `symbol` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `instrument_token` bigint NOT NULL,
  `exchange` enum('NSE','BSE','NFO','BITSTAMP','BINANCE') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiry` bigint NOT NULL,
  `strike` bigint NOT NULL,
  `instrument_type` enum('CE','PE','FUT','EQ','CRYPTO') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lot_size` int NOT NULL,
  `tick_size` float NOT NULL,
  `ucl` decimal(30,2) NOT NULL DEFAULT '9999999999.00',
  `lcl` decimal(30,2) NOT NULL DEFAULT '0.00',
  `stock_type` enum('future','option','equity','indices','crypto') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `qty_freeze` bigint NOT NULL,
  `fno_status` enum('No','Yes') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parentid` bigint DEFAULT NULL,
  `status` enum('Yes','No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` int UNSIGNED NOT NULL,
  `holidayname` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` bigint NOT NULL,
  `status` enum('Yes','No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kyc`
--

CREATE TABLE `kyc` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `mobile_verified` tinyint NOT NULL DEFAULT '0',
  `email_verified` tinyint NOT NULL DEFAULT '0',
  `pancard_front` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pancard_back` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pancard` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pancard_full_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pancard_verified` tinyint DEFAULT '0',
  `bank_account_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_account_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_ifsc_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_verified` tinyint DEFAULT NULL,
  `kyc_verified` tinyint DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `learn`
--

CREATE TABLE `learn` (
  `id` int NOT NULL,
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('image','video') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `corder` int UNSIGNED NOT NULL,
  `status` enum('Active','Inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `nfo_margin`
--

CREATE TABLE `nfo_margin` (
  `nfoid` int NOT NULL,
  `sid` int NOT NULL,
  `lot_size` int NOT NULL,
  `cash_price` decimal(12,4) DEFAULT NULL,
  `cdate` bigint NOT NULL,
  `margin` decimal(8,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `link` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `is_notified` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `cdate` int NOT NULL,
  `status` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notification_schedule`
--

CREATE TABLE `notification_schedule` (
  `nsid` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `link` varchar(255) DEFAULT NULL,
  `channel` varchar(50) NOT NULL,
  `scheduled_at` int NOT NULL,
  `status` enum('pending','processing','completed') DEFAULT 'pending',
  `count` int NOT NULL DEFAULT '0',
  `created_at` int DEFAULT (unix_timestamp())
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `offers`
--

CREATE TABLE `offers` (
  `ofrid` int NOT NULL,
  `ofr_name` varchar(255) NOT NULL,
  `ofr_amount` int DEFAULT '0',
  `claim_amount` int NOT NULL DEFAULT '0',
  `ofr_description` text,
  `ofr_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'offer_coin.png',
  `ofr_status` tinyint DEFAULT '1',
  `created_at` int DEFAULT NULL,
  `updated_at` int DEFAULT NULL,
  `expired_at` int DEFAULT NULL,
  `allowed_view_utids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `allowed_redeem_utids` text,
  `ofr_category` varchar(100) NOT NULL DEFAULT '',
  `max_rdm_per_usr` int NOT NULL DEFAULT '1',
  `ofrcd_category` varchar(50) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `offers_coupon_code`
--

CREATE TABLE `offers_coupon_code` (
  `ofrcd_id` int NOT NULL,
  `category` varchar(50) NOT NULL,
  `code` varchar(100) NOT NULL,
  `expired_at` int NOT NULL,
  `price` int NOT NULL,
  `status` enum('active','redeemed','expired') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'active',
  `created_at` int NOT NULL DEFAULT '0',
  `updated_at` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `offer_redeem`
--

CREATE TABLE `offer_redeem` (
  `redm_id` int NOT NULL,
  `uid` int NOT NULL,
  `ofrid` int NOT NULL,
  `redeemed_cost` int DEFAULT '0',
  `redeemed_amount` int DEFAULT '0',
  `status` enum('pending','delivered','cancelled','rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `created_at` int DEFAULT NULL,
  `updated_at` int DEFAULT NULL,
  `coupon_code` varchar(100) NOT NULL DEFAULT '',
  `coupon_expiry` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `options`
--

CREATE TABLE `options` (
  `id` int NOT NULL,
  `question_id` int NOT NULL,
  `option_text` text NOT NULL,
  `is_correct` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int NOT NULL,
  `cid` int NOT NULL,
  `uid` int NOT NULL,
  `sid` int NOT NULL,
  `qty` int NOT NULL,
  `entry_price` decimal(30,2) NOT NULL,
  `exit_price` decimal(30,2) NOT NULL,
  `investment` decimal(30,2) NOT NULL,
  `margin` decimal(30,2) DEFAULT '0.00',
  `target` decimal(30,2) NOT NULL,
  `stoploss` decimal(30,2) NOT NULL,
  `variety` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `order_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` enum('buy','sell') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','executed','cancelled','rejected','deleted') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `suborder` enum('No','Yes') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `orderid` int DEFAULT NULL,
  `squareoff` enum('No','Yes') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `charge` decimal(30,2) NOT NULL DEFAULT '0.00',
  `profit` decimal(30,2) NOT NULL DEFAULT '0.00',
  `odate` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `orders_schedule`
--

CREATE TABLE `orders_schedule` (
  `id` int NOT NULL,
  `cid` int NOT NULL,
  `gid` int NOT NULL,
  `sid` int NOT NULL,
  `qty` int NOT NULL,
  `type` int NOT NULL,
  `trigger_price` varchar(100) NOT NULL DEFAULT '0',
  `variety` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `order_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` enum('buy','sell') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','running','completed','cancelled','rejected','exited','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `execution_time` bigint NOT NULL DEFAULT '0',
  `created_at` bigint NOT NULL,
  `updated_at` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `otp`
--

CREATE TABLE `otp` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `otp` bigint NOT NULL,
  `expiry` int NOT NULL,
  `ts` int NOT NULL COMMENT 'otp time',
  `attempt` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `positions`
--

CREATE TABLE `positions` (
  `pid` int NOT NULL,
  `cid` int NOT NULL,
  `uid` int NOT NULL,
  `sid` int NOT NULL,
  `qty` int NOT NULL,
  `price` decimal(30,6) NOT NULL,
  `action` enum('buy','sell') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` int NOT NULL,
  `quiz_id` int NOT NULL,
  `situation` text,
  `question_text` text NOT NULL,
  `question_image` text,
  `answer_text` text,
  `answer_image` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quizzes`
--

CREATE TABLE `quizzes` (
  `id` int NOT NULL,
  `quiz_name` varchar(255) NOT NULL,
  `description` text,
  `image_url` text,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ratings`
--

CREATE TABLE `ratings` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `star` int NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cdate` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rewards`
--

CREATE TABLE `rewards` (
  `id` int NOT NULL,
  `cid` int NOT NULL,
  `min_rank` int NOT NULL,
  `max_rank` int NOT NULL,
  `amount` double(20,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sb_plans`
--

CREATE TABLE `sb_plans` (
  `sbid` int NOT NULL,
  `utid` int NOT NULL,
  `feature` varchar(100) NOT NULL,
  `display_order` int NOT NULL DEFAULT '0',
  `status` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sb_plan_join`
--

CREATE TABLE `sb_plan_join` (
  `pjid` int NOT NULL,
  `uid` int NOT NULL,
  `utid` int NOT NULL,
  `joindate` int NOT NULL,
  `validity` int NOT NULL,
  `payment_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tnx_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_status` enum('pending','success','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_amount` int NOT NULL,
  `plan_price` int NOT NULL,
  `save_bonus` int NOT NULL DEFAULT '0',
  `status` enum('active','expired','pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `bonus_amount` int NOT NULL DEFAULT '0',
  `updated_at` int UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `setting`
--

CREATE TABLE `setting` (
  `id` int NOT NULL,
  `referral_amount` int NOT NULL,
  `reference_amount` int NOT NULL,
  `first_order_amount` int NOT NULL,
  `android_latest_version` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0.0',
  `ios_latest_version` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0.0',
  `force_update` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supports`
--

CREATE TABLE `supports` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `type` enum('general','account','contest','fund','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `image` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cdate` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `topup_plan`
--

CREATE TABLE `topup_plan` (
  `tpid` int NOT NULL,
  `credit_amount` bigint NOT NULL,
  `display_order` int DEFAULT '0',
  `tp_price` int NOT NULL,
  `sb_price` int NOT NULL DEFAULT '0',
  `status` tinyint(1) DEFAULT '1',
  `created_at` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `topup_tnx`
--

CREATE TABLE `topup_tnx` (
  `ttid` bigint NOT NULL,
  `uid` bigint NOT NULL,
  `tpid` int NOT NULL,
  `razorpay_payment_id` varchar(100) NOT NULL,
  `payment_type` varchar(50) NOT NULL,
  `amount` int NOT NULL,
  `credit_amount` bigint NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=success, 0=failed',
  `created_at` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `fund_type` enum('bonus','smartcoin','topup_rupee') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','success','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('credit','debit') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` double NOT NULL,
  `tdate` int DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tcategory` enum('Winnings','Bonus Added','Bonus Redeemed','Rupee Added','Topup Added','Rupee Redeemed','Smartcoins Redeemed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tid` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `uid` int NOT NULL,
  `utid` int NOT NULL DEFAULT '1',
  `mobile` bigint NOT NULL,
  `referral_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `joindate` int NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `profile` tinyint(1) NOT NULL DEFAULT '0' COMMENT '''0''=> not profile fill,''1''=> profile fill',
  `level` int DEFAULT '1',
  `balance` float(15,2) NOT NULL DEFAULT '0.00',
  `referred_by` int DEFAULT NULL,
  `contest_joined` int NOT NULL DEFAULT '0',
  `contest_won` int NOT NULL DEFAULT '0',
  `sb_verified` tinyint(1) DEFAULT '0' COMMENT 'smartbulls verified ',
  `state` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'photo.jpg',
  `dob` bigint DEFAULT '946668661',
  `gender` enum('male','female','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'male',
  `referred_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `winning` float(15,2) NOT NULL DEFAULT '0.00',
  `bonus` float(15,2) NOT NULL DEFAULT '0.00',
  `smartcoins` int NOT NULL DEFAULT '0',
  `topup_rupee` decimal(20,2) NOT NULL DEFAULT '0.00',
  `fcmtoken` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `apptoken` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `resourceId` int DEFAULT NULL,
  `blocked` int DEFAULT '0',
  `status` int DEFAULT NULL,
  `version` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '0',
  `os` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `userslevel`
-- (See below for the actual view)
--
CREATE TABLE `userslevel` (
`current_rank` bigint unsigned
,`smartcoins` int
,`uid` int
);

-- --------------------------------------------------------

--
-- Table structure for table `users_past`
--

CREATE TABLE `users_past` (
  `puid` int NOT NULL,
  `uid` int NOT NULL,
  `name` varchar(191) DEFAULT '',
  `mobile` varchar(15) NOT NULL,
  `joindate` int DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `state` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `dob` varchar(100) DEFAULT NULL,
  `gender` enum('male','female','other') NOT NULL DEFAULT 'other',
  `smartcoins` int NOT NULL,
  `bonus` float(15,2) NOT NULL DEFAULT '0.00',
  `winning` float(15,2) NOT NULL DEFAULT '0.00',
  `referred_code` varchar(50) DEFAULT NULL,
  `referred_by` varchar(50) DEFAULT NULL,
  `referral_code` varchar(50) DEFAULT NULL,
  `deleted_at` int DEFAULT (unix_timestamp())
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_activity`
--

CREATE TABLE `user_activity` (
  `uid` int NOT NULL,
  `active_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_course_progress`
--

CREATE TABLE `user_course_progress` (
  `ucp` int NOT NULL,
  `uid` int NOT NULL,
  `course_type` int NOT NULL,
  `completed_lessons` int DEFAULT '0',
  `updated_at` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_groups`
--

CREATE TABLE `user_groups` (
  `gid` int NOT NULL,
  `gname` varchar(255) NOT NULL,
  `created_at` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_group_members`
--

CREATE TABLE `user_group_members` (
  `id` int NOT NULL,
  `gid` int NOT NULL,
  `uid` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_group_schedule`
--

CREATE TABLE `user_group_schedule` (
  `id` int NOT NULL,
  `cid` int NOT NULL,
  `gid` int NOT NULL,
  `status` enum('pending','running','completed','failed') DEFAULT 'pending',
  `execution_time` bigint NOT NULL,
  `created_at` bigint NOT NULL,
  `updated_at` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_types`
--

CREATE TABLE `user_types` (
  `id` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `buy_order_limit` int DEFAULT '10',
  `sell_order_limit` int DEFAULT '10',
  `validity` int NOT NULL DEFAULT '30',
  `mrp` int NOT NULL DEFAULT '0',
  `price` int NOT NULL DEFAULT '0',
  `practests_per_day` int NOT NULL DEFAULT '1',
  `bonus_amount` int NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_user_balance`
-- (See below for the actual view)
--
CREATE TABLE `vw_user_balance` (
`cid` int
,`initial_fund` decimal(30,2)
,`net_balance` decimal(61,2)
,`topup_balance` bigint
,`total_charges` decimal(52,2)
,`total_fund` decimal(31,2)
,`total_invested` decimal(59,2)
,`total_pending_order_amount` decimal(52,2)
,`total_profit` decimal(52,2)
,`uid` int
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_user_seg_charges`
-- (See below for the actual view)
--
CREATE TABLE `vw_user_seg_charges` (
`cid` int
,`crypto_charges` decimal(52,2)
,`equity_charges` decimal(52,2)
,`fno_charges` decimal(52,2)
,`total_charges` decimal(52,2)
,`uid` int
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_user_seg_invested`
-- (See below for the actual view)
--
CREATE TABLE `vw_user_seg_invested` (
`cid` int
,`cjid` int
,`crypto_invested` decimal(59,2)
,`equity_invested` decimal(59,2)
,`fno_invested` decimal(59,2)
,`total_invested` decimal(59,2)
,`uid` int
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_user_seg_pending_amt`
-- (See below for the actual view)
--
CREATE TABLE `vw_user_seg_pending_amt` (
`cid` int
,`crypto_pending` decimal(52,2)
,`equity_pending` decimal(52,2)
,`fno_pending` decimal(52,2)
,`total_pending` decimal(52,2)
,`uid` int
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_user_seg_profit`
-- (See below for the actual view)
--
CREATE TABLE `vw_user_seg_profit` (
`cid` int
,`crypto_profit` decimal(52,2)
,`equity_profit` decimal(52,2)
,`fno_profit` decimal(52,2)
,`total_profit` decimal(52,2)
,`uid` int
);

-- --------------------------------------------------------

--
-- Table structure for table `watchlists`
--

CREATE TABLE `watchlists` (
  `id` int NOT NULL,
  `sid` int NOT NULL,
  `uid` int NOT NULL,
  `ono` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `withdrawal`
--

CREATE TABLE `withdrawal` (
  `wid` int NOT NULL,
  `uid` int NOT NULL,
  `amount` double(8,2) NOT NULL,
  `wdate` int NOT NULL,
  `status` enum('pending','rejected','completed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tid` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Structure for view `userslevel`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `userslevel`  AS SELECT dense_rank() OVER (ORDER BY `u`.`smartcoins` desc ) AS `current_rank`, `u`.`uid` AS `uid`, `u`.`smartcoins` AS `smartcoins` FROM `users` AS `u` WHERE (`u`.`contest_won` > 0)  ;

-- --------------------------------------------------------

--
-- Structure for view `vw_user_balance`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_user_balance`  AS SELECT `cj`.`cid` AS `cid`, `cj`.`uid` AS `uid`, round(`cj`.`initial_fund`,2) AS `initial_fund`, round(`cj`.`topup_balance`,2) AS `topup_balance`, round((`cj`.`initial_fund` + `cj`.`topup_balance`),2) AS `total_fund`, round(coalesce(`inv`.`total_invested`,0),2) AS `total_invested`, round(coalesce(`pend`.`total_pending`,0),2) AS `total_pending_order_amount`, round(coalesce(`pro`.`total_profit`,0),2) AS `total_profit`, round(coalesce(`chg`.`total_charges`,0),2) AS `total_charges`, round((((((`cj`.`initial_fund` + `cj`.`topup_balance`) + coalesce(`pro`.`total_profit`,0)) - coalesce(`chg`.`total_charges`,0)) - coalesce(`inv`.`total_invested`,0)) - coalesce(`pend`.`total_pending`,0)),2) AS `net_balance` FROM ((((`contest_join` `cj` left join `vw_user_seg_invested` `inv` on(((`inv`.`cid` = `cj`.`cid`) and (`inv`.`uid` = `cj`.`uid`)))) left join `vw_user_seg_profit` `pro` on(((`pro`.`cid` = `cj`.`cid`) and (`pro`.`uid` = `cj`.`uid`)))) left join `vw_user_seg_charges` `chg` on(((`chg`.`cid` = `cj`.`cid`) and (`chg`.`uid` = `cj`.`uid`)))) left join `vw_user_seg_pending_amt` `pend` on(((`pend`.`cid` = `cj`.`cid`) and (`pend`.`uid` = `cj`.`uid`))))  ;

-- --------------------------------------------------------

--
-- Structure for view `vw_user_seg_charges`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_user_seg_charges`  AS SELECT `o`.`cid` AS `cid`, `o`.`uid` AS `uid`, round(sum((case when (`es`.`stock_type` in ('future','option','indices')) then `o`.`charge` else 0 end)),2) AS `fno_charges`, round(sum((case when (`es`.`stock_type` = 'equity') then `o`.`charge` else 0 end)),2) AS `equity_charges`, round(sum((case when (`es`.`stock_type` = 'crypto') then `o`.`charge` else 0 end)),2) AS `crypto_charges`, round(sum(`o`.`charge`),2) AS `total_charges` FROM (`orders` `o` join `equity_stock` `es` on((`es`.`sid` = `o`.`sid`))) WHERE ((`o`.`status` = 'executed') AND (`o`.`suborder` = 'No')) GROUP BY `o`.`cid`, `o`.`uid`  ;

-- --------------------------------------------------------

--
-- Structure for view `vw_user_seg_invested`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_user_seg_invested`  AS SELECT `cj`.`id` AS `cjid`, `cj`.`cid` AS `cid`, `cj`.`uid` AS `uid`, round(sum((case when (`es`.`stock_type` = 'future') then round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'buy')) then round((`p`.`price` * abs(`p`.`qty`)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'sell') and (`es`.`instrument_type` = 'CE')) then round((round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) + round(((`nm`.`cash_price` - `es`.`strike`) * abs(`p`.`qty`)),2)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'sell') and (`es`.`instrument_type` = 'PE')) then round((round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) + round(((`es`.`strike` - `nm`.`cash_price`) * abs(`p`.`qty`)),2)),2) else 0 end)),2) AS `fno_invested`, round(sum((case when (`es`.`stock_type` = 'equity') then round(abs((`p`.`qty` * `p`.`price`)),2) else 0 end)),2) AS `equity_invested`, round(sum((case when (`es`.`stock_type` = 'crypto') then round(abs((`p`.`qty` * `p`.`price`)),2) else 0 end)),2) AS `crypto_invested`, round(sum((case when (`es`.`stock_type` = 'future') then round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'buy')) then round((`p`.`price` * abs(`p`.`qty`)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'sell') and (`es`.`instrument_type` = 'CE')) then round((round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) + round(((`nm`.`cash_price` - `es`.`strike`) * abs(`p`.`qty`)),2)),2) when ((`es`.`stock_type` = 'option') and (lower(`p`.`action`) = 'sell') and (`es`.`instrument_type` = 'PE')) then round((round(((`nm`.`cash_price` * abs(`p`.`qty`)) * (`nm`.`margin` / 100)),2) + round(((`es`.`strike` - `nm`.`cash_price`) * abs(`p`.`qty`)),2)),2) when (`es`.`stock_type` in ('equity','crypto')) then round(abs((`p`.`qty` * `p`.`price`)),2) else 0 end)),2) AS `total_invested` FROM (((`contest_join` `cj` join `positions` `p` on(((`p`.`cid` = `cj`.`cid`) and (`p`.`uid` = `cj`.`uid`)))) join `equity_stock` `es` on((`es`.`sid` = `p`.`sid`))) left join `nfo_margin` `nm` on((`nm`.`sid` = `es`.`parentid`))) GROUP BY `cj`.`id`, `cj`.`cid`, `cj`.`uid``uid`  ;

-- --------------------------------------------------------

--
-- Structure for view `vw_user_seg_pending_amt`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_user_seg_pending_amt`  AS SELECT `o`.`cid` AS `cid`, `o`.`uid` AS `uid`, ifnull(sum((case when (`es`.`stock_type` = 'equity') then abs(`o`.`margin`) else 0 end)),0) AS `equity_pending`, ifnull(sum((case when (`es`.`stock_type` in ('future','option','indices')) then abs(`o`.`margin`) else 0 end)),0) AS `fno_pending`, ifnull(sum((case when (`es`.`stock_type` = 'crypto') then abs(`o`.`margin`) else 0 end)),0) AS `crypto_pending`, ifnull(sum(abs(`o`.`margin`)),0) AS `total_pending` FROM (`orders` `o` left join `equity_stock` `es` on((`es`.`sid` = `o`.`sid`))) WHERE (`o`.`status` = 'pending') GROUP BY `o`.`cid`, `o`.`uid``uid`  ;

-- --------------------------------------------------------

--
-- Structure for view `vw_user_seg_profit`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_user_seg_profit`  AS SELECT `o`.`cid` AS `cid`, `o`.`uid` AS `uid`, round(sum((case when (`es`.`stock_type` = 'equity') then `o`.`profit` else 0 end)),2) AS `equity_profit`, round(sum((case when (`es`.`stock_type` in ('future','option','indices')) then `o`.`profit` else 0 end)),2) AS `fno_profit`, round(sum((case when (`es`.`stock_type` = 'crypto') then `o`.`profit` else 0 end)),2) AS `crypto_profit`, round(sum(`o`.`profit`),2) AS `total_profit` FROM (`orders` `o` join `equity_stock` `es` on((`es`.`sid` = `o`.`sid`))) WHERE ((`o`.`status` = 'executed') AND (`o`.`suborder` = 'Yes')) GROUP BY `o`.`cid`, `o`.`uid``uid`  ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `access_token`
--
ALTER TABLE `access_token`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_apikey` (`api_key`),
  ADD KEY `idx_cdate` (`cdate`);

--
-- Indexes for table `admin`
--
ALTER TABLE `admin`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `cal_reward`
--
ALTER TABLE `cal_reward`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_actualuser_corder` (`actual_user`,`corder`);

--
-- Indexes for table `contests`
--
ALTER TABLE `contests`
  ADD PRIMARY KEY (`cid`),
  ADD KEY `idx_contests_segment` (`segment`),
  ADD KEY `idx_contests_cid` (`cid`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_status_type_cid` (`status`,`contest_type`,`cid`),
  ADD KEY `idx_contests_name_status` (`name`,`status`),
  ADD KEY `idx_start_end` (`start_datetime`,`end_datetime`),
  ADD KEY `idx_live_contests` (`status`,`start_datetime`,`end_datetime`);

--
-- Indexes for table `contest_join`
--
ALTER TABLE `contest_join`
  ADD PRIMARY KEY (`id`),
  ADD KEY `contest_id` (`cid`),
  ADD KEY `user_id` (`uid`),
  ADD KEY `idx_contest_join_cid_uid` (`cid`,`uid`),
  ADD KEY `idx_contest_join_uid` (`uid`),
  ADD KEY `idx_contest_join_cid` (`cid`),
  ADD KEY `idx_cid_winning` (`cid`,`winning` DESC),
  ADD KEY `idx_uid_crank` (`uid`,`crank`),
  ADD KEY `idx_corder` (`corder`),
  ADD KEY `idx_status_uid` (`is_test_user`,`uid`),
  ADD KEY `idx_uid_id` (`uid`,`id`),
  ADD KEY `idx_cj_uid_cid` (`uid`,`cid`),
  ADD KEY `idx_contest_user` (`cid`,`uid`),
  ADD KEY `cid` (`cid`,`uid`),
  ADD KEY `idx_crank_cid` (`crank`,`cid`);

--
-- Indexes for table `contest_schedule`
--
ALTER TABLE `contest_schedule`
  ADD PRIMARY KEY (`csid`);

--
-- Indexes for table `equity_stock`
--
ALTER TABLE `equity_stock`
  ADD PRIMARY KEY (`sid`),
  ADD UNIQUE KEY `symbol` (`symbol`),
  ADD UNIQUE KEY `sid` (`sid`),
  ADD KEY `idx_equity_stock_instrument_token` (`instrument_token`),
  ADD KEY `idx_equity_stock_sid` (`sid`),
  ADD KEY `idx_symbol_exchange` (`symbol`,`exchange`),
  ADD KEY `idx_expiry_instrument` (`expiry`,`instrument_type`),
  ADD KEY `idx_stocktype_status` (`stock_type`,`status`),
  ADD KEY `idx_equity_stock_stock_type` (`stock_type`),
  ADD KEY `sid_2` (`sid`,`parentid`),
  ADD KEY `idx_equity_token` (`instrument_token`,`sid`);

--
-- Indexes for table `holidays`
--
ALTER TABLE `holidays`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_date_status` (`date`,`status`);

--
-- Indexes for table `kyc`
--
ALTER TABLE `kyc`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`),
  ADD KEY `idx_uid_kycstatus` (`uid`,`kyc_verified`);

--
-- Indexes for table `learn`
--
ALTER TABLE `learn`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `nfo_margin`
--
ALTER TABLE `nfo_margin`
  ADD PRIMARY KEY (`nfoid`),
  ADD KEY `sid` (`sid`),
  ADD KEY `sid_2` (`sid`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`),
  ADD KEY `idx_notifications_status_notified` (`status`,`is_notified`),
  ADD KEY `idx_cdate_status` (`cdate` DESC,`status`),
  ADD KEY `idx_isnotified_status` (`is_notified`,`status`);

--
-- Indexes for table `notification_schedule`
--
ALTER TABLE `notification_schedule`
  ADD PRIMARY KEY (`nsid`);

--
-- Indexes for table `offers`
--
ALTER TABLE `offers`
  ADD PRIMARY KEY (`ofrid`),
  ADD KEY `idx_ofr_status` (`ofr_status`),
  ADD KEY `idx_expired_at` (`expired_at`);

--
-- Indexes for table `offers_coupon_code`
--
ALTER TABLE `offers_coupon_code`
  ADD PRIMARY KEY (`ofrcd_id`),
  ADD UNIQUE KEY `code` (`code`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_expired_at` (`expired_at`),
  ADD KEY `idx_category_status_expired` (`category`,`status`,`expired_at`);

--
-- Indexes for table `offer_redeem`
--
ALTER TABLE `offer_redeem`
  ADD PRIMARY KEY (`redm_id`),
  ADD KEY `idx_uid` (`uid`),
  ADD KEY `idx_ofrid` (`ofrid`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_ofrid_uid` (`ofrid`,`uid`);

--
-- Indexes for table `options`
--
ALTER TABLE `options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_options_question_id` (`question_id`),
  ADD KEY `idx_options_question_correct` (`question_id`,`is_correct`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `contest_id` (`cid`),
  ADD KEY `user_id` (`uid`),
  ADD KEY `sid` (`sid`),
  ADD KEY `idx_orders_status` (`status`),
  ADD KEY `idx_orders_cid_uid_sid` (`cid`,`uid`,`sid`),
  ADD KEY `idx_orders_cid_uid_status` (`cid`,`uid`,`status`),
  ADD KEY `idx_orders_essential` (`cid`,`uid`,`sid`,`status`,`suborder`,`squareoff`),
  ADD KEY `idx_orders_orderid` (`orderid`),
  ADD KEY `idx_orders_odate` (`odate`),
  ADD KEY `idx_uid_odate` (`uid`,`odate` DESC),
  ADD KEY `idx_action_status` (`action`,`status`),
  ADD KEY `idx_orders_uid_cid` (`uid`,`cid`),
  ADD KEY `idx_orders_uid_cid_status_suborder` (`uid`,`cid`,`status`,`suborder`,`sid`),
  ADD KEY `idx_orders_main` (`cid`,`uid`,`status`,`sid`),
  ADD KEY `idx_orders_profit` (`cid`,`uid`,`suborder`,`status`),
  ADD KEY `idx_view_profit` (`status`,`suborder`,`squareoff`,`cid`,`uid`),
  ADD KEY `idx_view_pending` (`status`,`cid`,`uid`),
  ADD KEY `idx_orders_pending_token` (`status`,`sid`,`action`,`order_type`);

--
-- Indexes for table `orders_schedule`
--
ALTER TABLE `orders_schedule`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_orders_schedule_status` (`status`);

--
-- Indexes for table `otp`
--
ALTER TABLE `otp`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`);

--
-- Indexes for table `positions`
--
ALTER TABLE `positions`
  ADD PRIMARY KEY (`pid`),
  ADD KEY `cid` (`cid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `sid` (`sid`),
  ADD KEY `idx_positions_cid_uid_sid` (`cid`,`uid`,`sid`),
  ADD KEY `idx_positions_uid_cid` (`uid`,`cid`),
  ADD KEY `idx_positions_sid` (`sid`),
  ADD KEY `idx_positions_main` (`cid`,`uid`,`sid`),
  ADD KEY `cid_2` (`cid`,`uid`,`sid`);

--
-- Indexes for table `questions`
--
ALTER TABLE `questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_questions_quiz_id` (`quiz_id`),
  ADD KEY `idx_questions_quiz_id_id` (`quiz_id`,`id`);

--
-- Indexes for table `quizzes`
--
ALTER TABLE `quizzes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quizzes_status` (`status`);

--
-- Indexes for table `ratings`
--
ALTER TABLE `ratings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`);

--
-- Indexes for table `rewards`
--
ALTER TABLE `rewards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `contest_id` (`cid`);

--
-- Indexes for table `sb_plans`
--
ALTER TABLE `sb_plans`
  ADD PRIMARY KEY (`sbid`),
  ADD KEY `idx_utid` (`utid`),
  ADD KEY `idx_sbid` (`sbid`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_utid_sbid_status` (`utid`,`sbid`,`status`);

--
-- Indexes for table `sb_plan_join`
--
ALTER TABLE `sb_plan_join`
  ADD PRIMARY KEY (`pjid`),
  ADD UNIQUE KEY `idx_tnx_id` (`tnx_id`),
  ADD KEY `utid` (`utid`),
  ADD KEY `idx_uid_status` (`uid`,`status`),
  ADD KEY `idx_uid_joindate` (`uid`,`joindate`);

--
-- Indexes for table `setting`
--
ALTER TABLE `setting`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `supports`
--
ALTER TABLE `supports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`);

--
-- Indexes for table `topup_plan`
--
ALTER TABLE `topup_plan`
  ADD PRIMARY KEY (`tpid`);

--
-- Indexes for table `topup_tnx`
--
ALTER TABLE `topup_tnx`
  ADD PRIMARY KEY (`ttid`),
  ADD KEY `uid_idx` (`uid`),
  ADD KEY `tpid_idx` (`tpid`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `uid` (`uid`),
  ADD KEY `idx_transactions_uid_tcategory` (`uid`,`tcategory`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`uid`),
  ADD UNIQUE KEY `mobile_no` (`mobile`),
  ADD KEY `user_type_id` (`utid`),
  ADD KEY `idx_users_uid` (`uid`),
  ADD KEY `idx_users_referred_code` (`referred_code`),
  ADD KEY `idx_users_apptoken` (`apptoken`(100));

--
-- Indexes for table `users_past`
--
ALTER TABLE `users_past`
  ADD PRIMARY KEY (`puid`),
  ADD KEY `idx_users_past_mobile` (`mobile`);

--
-- Indexes for table `user_activity`
--
ALTER TABLE `user_activity`
  ADD PRIMARY KEY (`uid`,`active_date`),
  ADD KEY `idx_active_date` (`active_date`);

--
-- Indexes for table `user_course_progress`
--
ALTER TABLE `user_course_progress`
  ADD PRIMARY KEY (`ucp`),
  ADD UNIQUE KEY `unique_user_course` (`uid`,`course_type`),
  ADD KEY `idx_uid` (`uid`),
  ADD KEY `idx_ucp` (`ucp`);

--
-- Indexes for table `user_groups`
--
ALTER TABLE `user_groups`
  ADD PRIMARY KEY (`gid`);

--
-- Indexes for table `user_group_members`
--
ALTER TABLE `user_group_members`
  ADD PRIMARY KEY (`id`),
  ADD KEY `gid` (`gid`),
  ADD KEY `uid` (`uid`);

--
-- Indexes for table `user_group_schedule`
--
ALTER TABLE `user_group_schedule`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user_types`
--
ALTER TABLE `user_types`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `watchlists`
--
ALTER TABLE `watchlists`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`uid`),
  ADD KEY `stock_id` (`sid`),
  ADD KEY `idx_watchlist_uid` (`uid`);

--
-- Indexes for table `withdrawal`
--
ALTER TABLE `withdrawal`
  ADD PRIMARY KEY (`wid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `idx_withdrawal_uid_status` (`uid`,`status`),
  ADD KEY `idx_wdate_status` (`wdate` DESC,`status`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `access_token`
--
ALTER TABLE `access_token`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin`
--
ALTER TABLE `admin`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cal_reward`
--
ALTER TABLE `cal_reward`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contests`
--
ALTER TABLE `contests`
  MODIFY `cid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contest_join`
--
ALTER TABLE `contest_join`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contest_schedule`
--
ALTER TABLE `contest_schedule`
  MODIFY `csid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `equity_stock`
--
ALTER TABLE `equity_stock`
  MODIFY `sid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `holidays`
--
ALTER TABLE `holidays`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `kyc`
--
ALTER TABLE `kyc`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `learn`
--
ALTER TABLE `learn`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `nfo_margin`
--
ALTER TABLE `nfo_margin`
  MODIFY `nfoid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notification_schedule`
--
ALTER TABLE `notification_schedule`
  MODIFY `nsid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `offers`
--
ALTER TABLE `offers`
  MODIFY `ofrid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `offers_coupon_code`
--
ALTER TABLE `offers_coupon_code`
  MODIFY `ofrcd_id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `offer_redeem`
--
ALTER TABLE `offer_redeem`
  MODIFY `redm_id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `options`
--
ALTER TABLE `options`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders_schedule`
--
ALTER TABLE `orders_schedule`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `otp`
--
ALTER TABLE `otp`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `positions`
--
ALTER TABLE `positions`
  MODIFY `pid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `questions`
--
ALTER TABLE `questions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `quizzes`
--
ALTER TABLE `quizzes`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ratings`
--
ALTER TABLE `ratings`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rewards`
--
ALTER TABLE `rewards`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sb_plans`
--
ALTER TABLE `sb_plans`
  MODIFY `sbid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sb_plan_join`
--
ALTER TABLE `sb_plan_join`
  MODIFY `pjid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `setting`
--
ALTER TABLE `setting`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supports`
--
ALTER TABLE `supports`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `topup_plan`
--
ALTER TABLE `topup_plan`
  MODIFY `tpid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `topup_tnx`
--
ALTER TABLE `topup_tnx`
  MODIFY `ttid` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `uid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_past`
--
ALTER TABLE `users_past`
  MODIFY `puid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_course_progress`
--
ALTER TABLE `user_course_progress`
  MODIFY `ucp` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_groups`
--
ALTER TABLE `user_groups`
  MODIFY `gid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_group_members`
--
ALTER TABLE `user_group_members`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_group_schedule`
--
ALTER TABLE `user_group_schedule`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_types`
--
ALTER TABLE `user_types`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `watchlists`
--
ALTER TABLE `watchlists`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `withdrawal`
--
ALTER TABLE `withdrawal`
  MODIFY `wid` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contest_join`
--
ALTER TABLE `contest_join`
  ADD CONSTRAINT `contest_join_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`),
  ADD CONSTRAINT `contest_join_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `kyc`
--
ALTER TABLE `kyc`
  ADD CONSTRAINT `kyc_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `nfo_margin`
--
ALTER TABLE `nfo_margin`
  ADD CONSTRAINT `nfo_margin_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `equity_stock` (`sid`);

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `options`
--
ALTER TABLE `options`
  ADD CONSTRAINT `fk_options_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`),
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`),
  ADD CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`sid`) REFERENCES `equity_stock` (`sid`);

--
-- Constraints for table `otp`
--
ALTER TABLE `otp`
  ADD CONSTRAINT `otp_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `positions`
--
ALTER TABLE `positions`
  ADD CONSTRAINT `positions_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `contests` (`cid`),
  ADD CONSTRAINT `positions_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`),
  ADD CONSTRAINT `positions_ibfk_3` FOREIGN KEY (`sid`) REFERENCES `equity_stock` (`sid`);

--
-- Constraints for table `questions`
--
ALTER TABLE `questions`
  ADD CONSTRAINT `fk_questions_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `ratings`
--
ALTER TABLE `ratings`
  ADD CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `sb_plans`
--
ALTER TABLE `sb_plans`
  ADD CONSTRAINT `sb_plans_ibfk_1` FOREIGN KEY (`utid`) REFERENCES `user_types` (`id`);

--
-- Constraints for table `sb_plan_join`
--
ALTER TABLE `sb_plan_join`
  ADD CONSTRAINT `sb_plan_join_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE,
  ADD CONSTRAINT `sb_plan_join_ibfk_2` FOREIGN KEY (`utid`) REFERENCES `user_types` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `supports`
--
ALTER TABLE `supports`
  ADD CONSTRAINT `supports_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`utid`) REFERENCES `user_types` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_group_members`
--
ALTER TABLE `user_group_members`
  ADD CONSTRAINT `user_group_members_ibfk_1` FOREIGN KEY (`gid`) REFERENCES `user_groups` (`gid`),
  ADD CONSTRAINT `user_group_members_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`);
COMMIT;

