-- ===============================================
-- Token System Migration
-- Date: 2026-02-13
-- Description: Transitions from quota-based to token-based system
-- ===============================================

USE `sanibook_vol1`;

-- 1. Modify Subscription Packages (Remove Quotas, Add Tokens)
ALTER TABLE `subscription_packages`
  DROP COLUMN `quota_images`,
  DROP COLUMN `quota_videos`,
  DROP COLUMN `quota_audio`,
  DROP COLUMN `quota_text`,
  ADD COLUMN `token_amount` int(11) NOT NULL DEFAULT 0 AFTER `billing_period`;

-- Update Package Token Amounts
-- Basic: 25,000 (1x)
-- Standard: 45,000 (1.125x)
-- Premium: 90,000 (1.2x)
-- Partner/Enterprise: 300,000 (1.5x)
-- Free: 2,000 (Teaser)

UPDATE `subscription_packages` SET `token_amount` = 2000 WHERE `name` = 'Free';
UPDATE `subscription_packages` SET `token_amount` = 25000 WHERE `name` = 'Basic';
-- Insert Standard if missing or update if renamed
INSERT INTO `subscription_packages` (`name`, `price_kes`, `billing_period`, `token_amount`) 
VALUES ('Standard', 40000.00, 'annual', 45000) 
ON DUPLICATE KEY UPDATE `token_amount` = 45000, `price_kes` = 40000.00;

UPDATE `subscription_packages` SET `token_amount` = 90000, `price_kes` = 75000.00 WHERE `name` = 'Premium';
UPDATE `subscription_packages` SET `token_amount` = 300000, `price_kes` = 200000.00 WHERE `name` IN ('Enterprise', 'Partner');

-- 2. Modify User Subscriptions (Add Token Balance)
ALTER TABLE `user_subscriptions`
  ADD COLUMN `tokens_remaining` decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `status`;

-- Initialize Tokens for EXISTING Active Subscriptions
UPDATE `user_subscriptions` us
JOIN `subscription_packages` sp ON us.package_id = sp.id
SET us.tokens_remaining = sp.token_amount
WHERE us.status = 'active';

-- 3. Create Token Transactions Table
CREATE TABLE IF NOT EXISTS `token_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subscription_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `transaction_type` enum('credit','debit','topup','adjustment') NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `reference_id` varchar(100) DEFAULT NULL COMMENT 'Submission ID or Payment Ref',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `subscription_id` (`subscription_id`),
  CONSTRAINT `fk_token_trans_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Initial Transaction Log (Backfill for existing active subs)
INSERT INTO `token_transactions` (`user_id`, `subscription_id`, `amount`, `transaction_type`, `description`)
SELECT 
  user_id, 
  id, 
  tokens_remaining, 
  'credit', 
  'Initial migration credit'
FROM `user_subscriptions`
WHERE status = 'active' AND tokens_remaining > 0;
