-- ============================================================================
-- Country-based pricing & USD payment — schema install
-- ============================================================================
-- Everything the country/currency feature needs, as plain SQL. The app also
-- self-heals these columns at runtime (App\Support\CountryProfile::ensure()),
-- so running this file is OPTIONAL — it exists for installs that want the
-- schema in place up front (fresh prod, staging, DR restore).
--
-- USAGE
--   mysql -u root vpandit < country_pricing_schema.sql
--   or paste into phpMyAdmin -> SQL
--
-- IDEMPOTENT: every statement checks information_schema first, so re-running
-- is a no-op. No data is modified, moved or deleted.
-- ============================================================================


-- ────────────────────────────────────────────────────────────────────────────
-- 1. users — the registered country (authoritative for pricing)
-- ────────────────────────────────────────────────────────────────────────────
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'country_code') > 0,
  'DO 0',
  'ALTER TABLE `users` ADD COLUMN `country_code` VARCHAR(2) DEFAULT NULL AFTER `phone`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'country_name') > 0,
  'DO 0',
  'ALTER TABLE `users` ADD COLUMN `country_name` VARCHAR(80) DEFAULT NULL AFTER `country_code`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- city + state on the users row. CurrencyResolver reads users.city first and
-- only falls back to user_profiles, so without these a signed-in user looks like
-- they have no location and the Current Living City picker opens empty.
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'city') > 0,
  'DO 0',
  'ALTER TABLE `users` ADD COLUMN `city` VARCHAR(120) DEFAULT NULL AFTER `country_name`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'state') > 0,
  'DO 0',
  'ALTER TABLE `users` ADD COLUMN `state` VARCHAR(120) DEFAULT NULL AFTER `city`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;


-- ────────────────────────────────────────────────────────────────────────────
-- 2. user_profiles — country on the editable profile
-- ────────────────────────────────────────────────────────────────────────────
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'country') > 0,
  'DO 0',
  'ALTER TABLE `user_profiles` ADD COLUMN `country` VARCHAR(80) DEFAULT NULL AFTER `state`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;


-- ────────────────────────────────────────────────────────────────────────────
-- 3. pay_txns — the ledger keeps BOTH the charge and its INR equivalent
--    amount        = what the payer was charged, in `currency`
--    amount_inr    = the INR catalogue price it came from
--    fx_rate       = USD per 1 INR used at the time
--    base_currency = always 'INR'
-- ────────────────────────────────────────────────────────────────────────────
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'amount_inr') > 0,
  'DO 0',
  'ALTER TABLE `pay_txns` ADD COLUMN `amount_inr` DECIMAL(12,2) DEFAULT NULL AFTER `amount`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'fx_rate') > 0,
  'DO 0',
  'ALTER TABLE `pay_txns` ADD COLUMN `fx_rate` DECIMAL(12,6) DEFAULT NULL AFTER `currency`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'base_currency') > 0,
  'DO 0',
  "ALTER TABLE `pay_txns` ADD COLUMN `base_currency` VARCHAR(8) NOT NULL DEFAULT 'INR' AFTER `currency`");
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;


-- ────────────────────────────────────────────────────────────────────────────
-- 4. user_packages — which currency a package was bought in, and what it
--    was worth in INR
-- ────────────────────────────────────────────────────────────────────────────
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'currency') > 0,
  'DO 0',
  'ALTER TABLE `user_packages` ADD COLUMN `currency` VARCHAR(8) DEFAULT NULL AFTER `payment_method`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'paid_amount_inr') > 0,
  'DO 0',
  'ALTER TABLE `user_packages` ADD COLUMN `paid_amount_inr` DECIMAL(12,2) DEFAULT NULL AFTER `paid_amount`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'fx_rate') > 0,
  'DO 0',
  'ALTER TABLE `user_packages` ADD COLUMN `fx_rate` DECIMAL(12,6) DEFAULT NULL AFTER `paid_amount_inr`');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;


-- ────────────────────────────────────────────────────────────────────────────
-- 5. Index so per-request country lookups stay cheap
-- ────────────────────────────────────────────────────────────────────────────
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_country') > 0,
  'DO 0',
  'ALTER TABLE `users` ADD INDEX `idx_users_country` (`country_code`)');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;


-- ────────────────────────────────────────────────────────────────────────────
-- 6. FX rate setting — USD per 1 INR. 0.012 ≈ ₹1 = $0.012
--    An admin-editable row in site_settings wins over this value.
-- ────────────────────────────────────────────────────────────────────────────
INSERT INTO `site_settings` (`key`, `value`, `group`, `label`)
SELECT 'pricing_usd_per_inr', '0.012', 'pricing', 'USD per 1 INR'
WHERE NOT EXISTS (
  SELECT 1 FROM `site_settings` WHERE `key` = 'pricing_usd_per_inr'
);

-- International USD charges on/off. '0' refuses every non-India checkout.
INSERT INTO `site_settings` (`key`, `value`, `group`, `label`)
SELECT 'pay_international', '1', 'payment', 'Allow international (USD) payments'
WHERE NOT EXISTS (
  SELECT 1 FROM `site_settings` WHERE `key` = 'pay_international'
);


-- ────────────────────────────────────────────────────────────────────────────
-- 7. Backfill — existing users stay on INR.
--    country_code is left NULL on purpose: NULL means "not declared", which
--    is exactly the behaviour those users have today (default INR). Setting
--    every row to 'IN' would lock them to INR permanently and stop them from
--    ever being recognised as foreign later.
-- ────────────────────────────────────────────────────────────────────────────
-- To declare every current user as Indian (only if you are SURE none of them
-- lives abroad), uncomment:
--
-- UPDATE `users` SET `country_code` = 'IN', `country_name` = 'India'
--  WHERE `country_code` IS NULL;
--
-- To fix a single user:
-- UPDATE `users` SET `country_code` = 'CA', `country_name` = 'Canada'
--  WHERE `email` = 'someone@example.com';


-- ────────────────────────────────────────────────────────────────────────────
-- 8. Verify — every row should read OK
-- ────────────────────────────────────────────────────────────────────────────
SELECT 'users.country_code'        AS column_name, IF(COUNT(*) = 1, 'OK', 'MISSING') AS status
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'users' AND COLUMN_NAME = 'country_code'
UNION ALL SELECT 'users.country_name', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'users' AND COLUMN_NAME = 'country_name'
UNION ALL SELECT 'user_profiles.country', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'country'
UNION ALL SELECT 'pay_txns.amount_inr', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'amount_inr'
UNION ALL SELECT 'pay_txns.fx_rate', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'fx_rate'
UNION ALL SELECT 'pay_txns.base_currency', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'pay_txns' AND COLUMN_NAME = 'base_currency'
UNION ALL SELECT 'user_packages.currency', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'currency'
UNION ALL SELECT 'user_packages.paid_amount_inr', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'paid_amount_inr'
UNION ALL SELECT 'user_packages.fx_rate', IF(COUNT(*) = 1, 'OK', 'MISSING')
  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'user_packages' AND COLUMN_NAME = 'fx_rate'
UNION ALL SELECT 'users.idx_users_country', IF(COUNT(*) > 0, 'OK', 'MISSING')
  FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_country'
UNION ALL SELECT 'setting pricing_usd_per_inr', IF(COUNT(*) > 0, 'OK', 'MISSING')
  FROM `site_settings` WHERE `key` = 'pricing_usd_per_inr'
UNION ALL SELECT 'setting pay_international', IF(COUNT(*) > 0, 'OK', 'MISSING')
  FROM `site_settings` WHERE `key` = 'pay_international';