-- 003 — Customer portal + subscription billing
-- Idempotent: safe to re-apply. New tables only; never touches existing data.

-- 1. Companies (the buyers — your customers).
CREATE TABLE IF NOT EXISTS `sn_portal_customers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(160) NOT NULL,
  `slug` VARCHAR(80) NOT NULL,
  `email` VARCHAR(160) NOT NULL,
  `phone` VARCHAR(40) DEFAULT '',
  `country` CHAR(2) DEFAULT '',
  `status` ENUM('trial','active','past_due','cancelled','suspended') NOT NULL DEFAULT 'trial',
  `plan_id` INT UNSIGNED NULL,
  `trial_until` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_slug` (`slug`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. End-client logins (separate from sn_admin_users — that table is CMS-internal staff).
CREATE TABLE IF NOT EXISTS `sn_portal_users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` INT UNSIGNED NOT NULL,
  `email` VARCHAR(160) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(120) NOT NULL,
  `role` ENUM('owner','admin','viewer') NOT NULL DEFAULT 'owner',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `totp_secret` VARCHAR(64) DEFAULT '',
  `totp_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `recovery_codes` JSON NULL,
  `reset_token` VARCHAR(64) DEFAULT '',
  `reset_expiry` DATETIME NULL,
  `last_login` DATETIME NULL,
  `login_attempts` INT NOT NULL DEFAULT 0,
  `locked_until` DATETIME NULL,
  `pw_checked_at` DATETIME NULL,
  `pw_breached_count` INT NOT NULL DEFAULT 0,
  `must_change_pw` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_email` (`email`),
  KEY `idx_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Subscription plans (basic / pro / agency / custom).
CREATE TABLE IF NOT EXISTS `sn_portal_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40) NOT NULL,
  `name` VARCHAR(80) NOT NULL,
  `description` TEXT NULL,
  `price_usd` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `price_lkr` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `billing_period` ENUM('monthly','annual','lifetime') NOT NULL DEFAULT 'monthly',
  `features` JSON NOT NULL,
  `max_sites` INT NOT NULL DEFAULT 1,
  `trial_days` INT NOT NULL DEFAULT 14,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Active subscriptions (one per customer at a time).
CREATE TABLE IF NOT EXISTS `sn_portal_subscriptions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED NOT NULL,
  `status` ENUM('active','trial','past_due','cancelled','expired','pending') NOT NULL DEFAULT 'pending',
  `gateway` ENUM('stripe','paypal','payhere','webxpay','bank','manual','trial') NOT NULL DEFAULT 'manual',
  `gateway_subscription_id` VARCHAR(160) DEFAULT '',
  `current_period_start` DATETIME NULL,
  `current_period_end` DATETIME NULL,
  `cancel_at_period_end` TINYINT(1) NOT NULL DEFAULT 0,
  `cancelled_at` DATETIME NULL,
  `grace_until` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_gateway_sub` (`gateway_subscription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Invoices.
CREATE TABLE IF NOT EXISTS `sn_portal_invoices` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_number` VARCHAR(40) NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `subscription_id` INT UNSIGNED NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` CHAR(3) NOT NULL DEFAULT 'USD',
  `status` ENUM('draft','open','paid','failed','refunded','void') NOT NULL DEFAULT 'draft',
  `gateway` ENUM('stripe','paypal','payhere','webxpay','bank','manual') NOT NULL DEFAULT 'manual',
  `gateway_invoice_id` VARCHAR(160) DEFAULT '',
  `description` VARCHAR(500) DEFAULT '',
  `issued_at` DATETIME NOT NULL,
  `due_at` DATETIME NULL,
  `paid_at` DATETIME NULL,
  `raw` JSON NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_invoice_number` (`invoice_number`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Per-transaction payment log.
CREATE TABLE IF NOT EXISTS `sn_portal_payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` INT UNSIGNED NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `gateway` ENUM('stripe','paypal','payhere','webxpay','bank','manual') NOT NULL,
  `type` ENUM('charge','refund','dispute','payout','subscription_create','subscription_cancel','webhook_received') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` CHAR(3) NOT NULL DEFAULT 'USD',
  `status` VARCHAR(40) NOT NULL,
  `gateway_txn_id` VARCHAR(160) NOT NULL,
  `raw` JSON NULL,
  `received_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_gateway_txn` (`gateway`,`gateway_txn_id`,`type`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Download audit.
CREATE TABLE IF NOT EXISTS `sn_portal_downloads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `release_version` VARCHAR(20) NOT NULL,
  `release_sha256` VARCHAR(64) NOT NULL,
  `decryption_key_hash` VARCHAR(64) NOT NULL,
  `ip` VARCHAR(60) NOT NULL,
  `user_agent` VARCHAR(255) DEFAULT '',
  `downloaded_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_downloaded` (`downloaded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Extend sn_licenses (from migration 002) with a customer_id FK.
ALTER TABLE `sn_licenses`
  ADD COLUMN IF NOT EXISTS `customer_id` INT UNSIGNED NULL AFTER `id`,
  ADD KEY IF NOT EXISTS `idx_customer` (`customer_id`);

-- 9. Seed the 3 default plans (mirrors the License::PLAN_FEATURES matrix).
INSERT IGNORE INTO `sn_portal_plans`
  (`code`,`name`,`description`,`price_usd`,`price_lkr`,`billing_period`,`features`,`max_sites`,`trial_days`,`is_active`,`sort_order`,`created_at`)
VALUES
  ('basic','Basic','1 site, Shop module, all core CMS features.',29.00,8900.00,'monthly',
   JSON_OBJECT('ai',0,'social',0,'shop',1,'migration',0,'multisite',1,'whitelabel',0),1,14,1,1,NOW()),
  ('pro','Pro','Up to 5 sites, AI + Social + Migration.',79.00,24500.00,'monthly',
   JSON_OBJECT('ai',1,'social',1,'shop',1,'migration',1,'multisite',1,'whitelabel',0),5,14,1,2,NOW()),
  ('agency','Agency','Unlimited sites, whitelabel, priority support.',199.00,61900.00,'monthly',
   JSON_OBJECT('ai',1,'social',1,'shop',1,'migration',1,'multisite',1,'whitelabel',1),99,14,1,3,NOW());