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