-- ============================================================
-- Subscription + Trial + Merchant Mode upgrade
-- Run this ONCE in phpMyAdmin on your live database
-- Safe to re-run: uses IF NOT EXISTS / checks where possible
-- ============================================================

-- 1) New columns on subscription_plan (full admin control)
ALTER TABLE `subscription_plan`
  ADD COLUMN IF NOT EXISTS `period_type` ENUM('day','week','month','year') NOT NULL DEFAULT 'month' AFTER `expiry`,
  ADD COLUMN IF NOT EXISTS `max_transactions` INT NOT NULL DEFAULT 0 COMMENT '0 = unlimited' AFTER `period_type`,
  ADD COLUMN IF NOT EXISTS `cashier_limit` INT NOT NULL DEFAULT 1 COMMENT 'max merchants/cashiers user can connect' AFTER `max_transactions`,
  ADD COLUMN IF NOT EXISTS `is_trial` TINYINT(1) NOT NULL DEFAULT 0 AFTER `cashier_limit`,
  ADD COLUMN IF NOT EXISTS `status` ENUM('Active','Inactive') NOT NULL DEFAULT 'Active' AFTER `is_trial`;

-- 2) Table that tracks which plan each user currently holds + usage counters
CREATE TABLE IF NOT EXISTS `user_subscriptions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `plan_id` INT(11) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `period_start` DATE NOT NULL COMMENT 'start of the current usage-counting window',
  `transactions_used` INT(11) NOT NULL DEFAULT 0,
  `status` ENUM('Active','Expired','Cancelled') NOT NULL DEFAULT 'Active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) Merchant routing mode on users
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `merchant_mode` ENUM('specific','all','random') NOT NULL DEFAULT 'all' AFTER `fampay_connected`,
  ADD COLUMN IF NOT EXISTS `active_merchant_type` VARCHAR(30) NULL DEFAULT NULL AFTER `merchant_mode`;

-- 4) One default Trial plan (only inserted if none exists yet)
INSERT INTO `subscription_plan` (`plan_name`, `amount`, `expiry`, `period_type`, `max_transactions`, `cashier_limit`, `is_trial`, `status`)
SELECT 'Free Trial', 0, 3, 'day', 5, 1, 1, 'Active'
WHERE NOT EXISTS (SELECT 1 FROM `subscription_plan` WHERE `is_trial` = 1);

-- Notes:
-- - max_transactions = 0 means unlimited for that plan.
-- - period_type + expiry together define the plan length shown to users
--   (expiry is reused as "how many period_type units the plan lasts",
--   e.g. expiry=1, period_type='month' => 1 month plan).
-- - cashier_limit caps how many merchant/cashier accounts (Paytm, PhonePe,
--   BharatPe, GooglePay, SBI, FreeCharge, Mobikwik, HDFC, FamPay, etc.)
--   a user is allowed to connect at once.

-- 5) Checkout page customization (logo, brand color, display name)
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `checkout_logo_url` VARCHAR(500) NULL DEFAULT NULL AFTER `active_merchant_type`,
  ADD COLUMN IF NOT EXISTS `checkout_primary_color` VARCHAR(20) NOT NULL DEFAULT '#0F6B5C' AFTER `checkout_logo_url`,
  ADD COLUMN IF NOT EXISTS `checkout_business_name` VARCHAR(191) NULL DEFAULT NULL AFTER `checkout_primary_color`;

-- 6) Checkout page element toggles (QR code / intent buttons)
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `checkout_show_qr` TINYINT(1) NOT NULL DEFAULT 1 AFTER `checkout_business_name`,
  ADD COLUMN IF NOT EXISTS `checkout_show_googlepay` TINYINT(1) NOT NULL DEFAULT 1 AFTER `checkout_show_qr`,
  ADD COLUMN IF NOT EXISTS `checkout_show_phonepe` TINYINT(1) NOT NULL DEFAULT 1 AFTER `checkout_show_googlepay`,
  ADD COLUMN IF NOT EXISTS `checkout_show_other_upi` TINYINT(1) NOT NULL DEFAULT 1 AFTER `checkout_show_phonepe`;

-- 7) Additional checkout page colors (full color customization)
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `checkout_secondary_color` VARCHAR(20) NOT NULL DEFAULT '#E8A33D' AFTER `checkout_show_other_upi`,
  ADD COLUMN IF NOT EXISTS `checkout_success_color` VARCHAR(20) NOT NULL DEFAULT '#1FAA59' AFTER `checkout_secondary_color`,
  ADD COLUMN IF NOT EXISTS `checkout_bg_color` VARCHAR(20) NOT NULL DEFAULT '#EAF3F0' AFTER `checkout_success_color`,
  ADD COLUMN IF NOT EXISTS `checkout_text_color` VARCHAR(20) NOT NULL DEFAULT '#0B1F1C' AFTER `checkout_bg_color`,
  ADD COLUMN IF NOT EXISTS `checkout_template` VARCHAR(30) NOT NULL DEFAULT 'emerald' AFTER `checkout_text_color`;

-- 8) Test Mode support
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `api_mode` ENUM('live','test') NOT NULL DEFAULT 'live' AFTER `checkout_template`;

ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `is_test` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`;