-- ============================================================
-- RUN_THIS_ALL_IN_ONE.sql
-- Run this ONE file in phpMyAdmin (SQL tab) to catch up on EVERY database
-- change this project needs - including foundational ones from before,
-- not just recent ones. Every statement uses "IF NOT EXISTS" so it's safe
-- to run more than once; already-applied changes are simply skipped.
-- ============================================================

-- ---- 1) Subscription plan columns (limits, trial flag, status) ----
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 tracking which plan each user currently holds + usage ----
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 (which connected gateway a new order uses) ----
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);

-- ---- 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`;

-- ---- 9) Google OAuth (FamPay Sign-in-with-Google connect) ----
ALTER TABLE `site_settings`
  ADD COLUMN IF NOT EXISTS `google_client_id` VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `google_client_secret` VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `google_redirect_uri` VARCHAR(255) DEFAULT NULL;

ALTER TABLE `fampay_tokens`
  ADD COLUMN IF NOT EXISTS `refresh_token` TEXT DEFAULT NULL AFTER `app_password`,
  ADD COLUMN IF NOT EXISTS `access_token` TEXT DEFAULT NULL AFTER `refresh_token`,
  ADD COLUMN IF NOT EXISTS `token_expiry` INT DEFAULT NULL AFTER `access_token`;

-- ---- 10) "Must buy a plan" flag for new accounts ----
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `requires_plan` VARCHAR(5) NOT NULL DEFAULT 'No' AFTER `expiry`;

-- ---- 11) Cron job control table (Website Manage -> Cron Jobs) ----
CREATE TABLE IF NOT EXISTS `cron_jobs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `job_key` VARCHAR(64) NOT NULL UNIQUE,
  `name` VARCHAR(191) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `script_path` VARCHAR(255) NOT NULL,
  `recommended_frequency` VARCHAR(100) DEFAULT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `last_run_at` DATETIME DEFAULT NULL,
  `last_run_summary` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `cron_jobs` (`job_key`, `name`, `description`, `script_path`, `recommended_frequency`, `is_enabled`) VALUES
('subscription_expiry', 'Subscription Auto-Expiry', 'Expires user_subscriptions whose end_date has passed. Works for Day/Week/Month/Year plans alike, since expiry is date-based.', 'crons/subscription_expiry.php', 'Every 5-10 minutes', 1),
('hdfc_session', 'HDFC Session Refresh', 'Refreshes HDFC merchant login sessions.', 'crons/cron.php', 'Twice an hour', 1),
('cron2', 'Cron Job 2', 'Legacy scheduled task.', 'crons/cron2.php', 'Twice a day', 1),
('cron3', 'Cron Job 3', 'Legacy scheduled task.', 'crons/cron3.php', 'Every 1 minute', 1),
('cron4', 'Cron Job 4', 'Legacy scheduled task.', 'crons/cron4.php', 'Every 1 minute', 1),
('cron5', 'Cron Job 5', 'Legacy scheduled task.', 'crons/cron5.php', 'Not specified - confirm with developer', 1),
('cron6', 'Cron Job 6', 'Legacy scheduled task.', 'crons/cron6.php', 'Not specified - confirm with developer', 1),
('cron_all', 'Run All (combined)', 'Triggers cron.php through cron6.php in one call.', 'crons/cron_all.php', 'Not specified - confirm with developer', 1),
('payindia', 'PayIndia Job', 'Legacy scheduled task.', 'crons/payindia.php', 'Not specified - confirm with developer', 1),
('fampay_order_watcher', 'FamPay Order Watcher', 'Independently checks all pending FamPay orders and fires the webhook immediately, without depending on the customer keeping their browser tab open/polling.', 'crons/fampay_order_watcher.php', 'Every 1 minute', 1)
ON DUPLICATE KEY UPDATE job_key = job_key;

-- ---- 12) Static Paylinks (reusable payment links) ----
CREATE TABLE IF NOT EXISTS `static_paylinks` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `link_id` VARCHAR(20) NOT NULL UNIQUE,
  `user_token` VARCHAR(191) NOT NULL,
  `title` VARCHAR(60) NOT NULL,
  `description` VARCHAR(150) DEFAULT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `paylink_type` ENUM('normal','redirect') NOT NULL DEFAULT 'normal',
  `redirect_url` VARCHAR(255) DEFAULT NULL,
  `collect_name` TINYINT(1) NOT NULL DEFAULT 0,
  `collect_mobile` TINYINT(1) NOT NULL DEFAULT 0,
  `collect_email` TINYINT(1) NOT NULL DEFAULT 0,
  `validity` VARCHAR(10) NOT NULL DEFAULT '7d',
  `expires_at` DATETIME DEFAULT NULL,
  `status` ENUM('Active','Disabled') NOT NULL DEFAULT 'Active',
  `orders_count` INT(11) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 13) Static Paylink limit per subscription plan ----
ALTER TABLE `subscription_plan`
  ADD COLUMN IF NOT EXISTS `static_paylink_limit` INT(11) NOT NULL DEFAULT 1 AFTER `cashier_limit`;

-- ---- 14) Card/box background color (separate from page background) ----
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `checkout_card_bg_color` VARCHAR(20) NOT NULL DEFAULT '#F4F6F5' AFTER `checkout_bg_color`;

-- ---- 15) SMTP settings (Website Manage -> Email/SMTP) ----
ALTER TABLE `site_settings`
  ADD COLUMN IF NOT EXISTS `smtp_host` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `smtp_port` INT(11) NOT NULL DEFAULT 587,
  ADD COLUMN IF NOT EXISTS `smtp_username` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `smtp_password` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `smtp_encryption` VARCHAR(10) NOT NULL DEFAULT 'tls',
  ADD COLUMN IF NOT EXISTS `smtp_from_email` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `smtp_from_name` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `register_otp_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `login_otp_enabled` TINYINT(1) NOT NULL DEFAULT 1;

-- ---- 16) Email verification + OTP fields on users ----
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `email_verified` VARCHAR(5) NOT NULL DEFAULT 'No',
  ADD COLUMN IF NOT EXISTS `otp_code` VARCHAR(10) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `otp_expires_at` DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `otp_purpose` VARCHAR(20) DEFAULT NULL;

-- ---- 17) Subscription expiry reminder tracking (avoid duplicate emails same day) ----
ALTER TABLE `user_subscriptions`
  ADD COLUMN IF NOT EXISTS `last_reminder_date` DATE DEFAULT NULL;

-- ---- 18) Subscription reminder cron ----
INSERT INTO `cron_jobs` (`job_key`, `name`, `description`, `script_path`, `recommended_frequency`, `is_enabled`) VALUES
('subscription_reminder', 'Subscription Expiry Reminder Emails', 'Emails users 1-5 days before their plan expires (once per day per user).', 'crons/subscription_reminder.php', 'Once a day', 1)
ON DUPLICATE KEY UPDATE job_key = job_key;

-- ---- 19) WhatsApp gateway config + connected accounts ----
ALTER TABLE `site_settings`
  ADD COLUMN IF NOT EXISTS `whatsapp_gateway_url` VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `whatsapp_gateway_api_key` VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `whatsapp_notifications_enabled` TINYINT(1) NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS `whatsapp_accounts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `account_id` VARCHAR(64) NOT NULL UNIQUE COMMENT 'matches the account_id used on the Node gateway',
  `label` VARCHAR(100) NOT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'not_started',
  `added_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `whatsapp_number` VARCHAR(20) DEFAULT NULL;

-- ---- 22) White-label custom domain ----
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `custom_domain` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `custom_domain_verified` TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `custom_domain_cpanel_added` TINYINT(1) NOT NULL DEFAULT 0;

-- ---- 23) cPanel API credentials (for automatic Addon Domain creation) ----
ALTER TABLE `site_settings`
  ADD COLUMN IF NOT EXISTS `cpanel_host` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `cpanel_port` INT(11) NOT NULL DEFAULT 2083,
  ADD COLUMN IF NOT EXISTS `cpanel_username` VARCHAR(191) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `cpanel_api_token` VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `cpanel_docroot` VARCHAR(255) DEFAULT NULL COMMENT 'existing folder (relative to cPanel home) that the main site already lives in - e.g. neopay.mgamer.online';

-- ---- 21) Webhook delivery log (for the Test button + Recent Deliveries list) ----
CREATE TABLE IF NOT EXISTS `webhook_deliveries` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `event` VARCHAR(50) NOT NULL COMMENT 'payment.success / payment.failed / test',
  `order_id` VARCHAR(100) DEFAULT NULL,
  `url` VARCHAR(500) NOT NULL,
  `payload` TEXT NOT NULL,
  `status` ENUM('Delivered','Failed') NOT NULL,
  `http_code` INT(11) DEFAULT NULL,
  `response_snippet` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 20) Order timeout safety-net cron (marks stale Pending orders as
-- FAILURE and notifies the merchant - mainly needed for FamPay orders,
-- which never get explicitly marked failed any other way) ----
INSERT INTO `cron_jobs` (`job_key`, `name`, `description`, `script_path`, `recommended_frequency`, `is_enabled`) VALUES
('order_timeout_check', 'Order Timeout Safety Net', 'Marks orders stuck Pending for 10+ minutes as failed and emails/WhatsApps the merchant - catches abandoned FamPay payments that never get an explicit failure signal.', 'crons/order_timeout_check.php', 'Every 10 minutes', 1)
ON DUPLICATE KEY UPDATE job_key = job_key;
