-- ============================================================================
-- Migration: 009 - Modular Subscription System with AI Token Billing
-- Description: Implements flexible subscription system supporting multiple
--              subscription types (AI tokens, courses, premium features, etc.)
--              with comprehensive token tracking and billing
-- Author: System
-- Date: 2025-11-26
-- ============================================================================

-- ============================================================================
-- SECTION 1: ALTER EXISTING USERS TABLE
-- Add AI token management columns
-- ============================================================================

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `ai_tokens_balance` DECIMAL(12,2) DEFAULT 0.00 
  COMMENT 'Available AI tokens for consumption';

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `ai_tokens_purchased` DECIMAL(12,2) DEFAULT 0.00 
  COMMENT 'Total tokens purchased (lifetime)';

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `ai_tokens_consumed` DECIMAL(12,2) DEFAULT 0.00 
  COMMENT 'Total tokens consumed (lifetime)';

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `ai_features_enabled` BOOLEAN DEFAULT TRUE 
  COMMENT 'Whether AI features are enabled for this user';

-- Add indexes for token queries
ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_ai_tokens_balance` (`ai_tokens_balance`);

ALTER TABLE `users` 
ADD INDEX IF NOT EXISTS `idx_ai_features_enabled` (`ai_features_enabled`);


-- ============================================================================
-- SECTION 2: SUBSCRIPTION TYPES TABLE
-- Core table defining different types of subscriptions available
-- ============================================================================

CREATE TABLE IF NOT EXISTS `subscription_types` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `type_key` VARCHAR(50) UNIQUE NOT NULL 
    COMMENT 'Unique identifier: ai_tokens, course_access, premium_features',
  `type_name` VARCHAR(255) NOT NULL 
    COMMENT 'Display name: AI Tokens, Course Access, Premium Features',
  `description` TEXT,
  `category` ENUM('ai', 'education', 'premium', 'business', 'other') DEFAULT 'other',
  `icon` VARCHAR(255) DEFAULT NULL COMMENT 'Icon URL or name for UI',
  `color` VARCHAR(50) DEFAULT NULL COMMENT 'Brand color for UI (hex code)',
  `is_active` BOOLEAN DEFAULT TRUE,
  `display_order` INT DEFAULT 0,
  `metadata` JSON DEFAULT NULL COMMENT 'Type-specific configuration',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY `idx_type_key` (`type_key`),
  KEY `idx_category` (`category`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Defines types of subscriptions available on the platform';

-- Seed initial subscription types
INSERT INTO `subscription_types` (`type_key`, `type_name`, `description`, `category`, `icon`, `color`, `display_order`) VALUES
('ai_tokens', 'AI Tokens', 'Access to AI-powered features with token allocation', 'ai', 'robot', '#8B5CF6', 1),
('course_access', 'Course Library', 'Full access to premium courses and learning materials', 'education', 'book', '#3B82F6', 2),
('premium_features', 'Premium Features', 'Unlock advanced platform features and tools', 'premium', 'star', '#F59E0B', 3),
('live_classes', 'Live Classes', 'Attend live instructor-led classes and Q&A sessions', 'education', 'video', '#10B981', 4),
('certificates', 'Certificates', 'Earn verified certificates for completed courses', 'education', 'award', '#EF4444', 5);


-- ============================================================================
-- SECTION 3: SUBSCRIPTION PLANS TABLE
-- Flexible plans supporting multiple subscription types
-- ============================================================================

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `subscription_type_id` INT NOT NULL COMMENT 'References subscription_types table',
  `plan_name` VARCHAR(255) NOT NULL COMMENT 'e.g., AI Starter, AI Pro, Full Course Access',
  `plan_slug` VARCHAR(100) UNIQUE NOT NULL,
  `tier` ENUM('free', 'basic', 'premium', 'enterprise') NOT NULL,
  `description` TEXT,
  
  -- Pricing
  `price_monthly_npr` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `price_yearly_npr` DECIMAL(10,2) DEFAULT NULL COMMENT 'Discounted annual price',
  `price_monthly_usd` DECIMAL(10,2) DEFAULT NULL,
  `price_yearly_usd` DECIMAL(10,2) DEFAULT NULL,
  `discount_yearly_percent` DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Yearly vs monthly savings %',
  
  -- Plan Benefits (Type-specific, stored as JSON for flexibility)
  `benefits` JSON NOT NULL COMMENT 'Plan-specific benefits configuration',
  `features_list` JSON DEFAULT NULL COMMENT 'User-facing feature list for UI',
  
  -- Display & Marketing
  `is_popular` BOOLEAN DEFAULT FALSE,
  `badge_text` VARCHAR(50) DEFAULT NULL COMMENT 'e.g., BEST VALUE, MOST POPULAR',
  `is_active` BOOLEAN DEFAULT TRUE,
  `display_order` INT DEFAULT 0,
  
  -- Limits & Restrictions
  `max_users_per_subscription` INT DEFAULT 1 COMMENT 'For team/enterprise plans',
  `trial_days` INT DEFAULT 0 COMMENT 'Free trial period in days',
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY `idx_subscription_type_id` (`subscription_type_id`),
  KEY `idx_plan_slug` (`plan_slug`),
  KEY `idx_tier` (`tier`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_popular` (`is_popular`),
  
  FOREIGN KEY (`subscription_type_id`) REFERENCES `subscription_types`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Flexible subscription plans supporting multiple types';

-- Seed Plans: AI Tokens
INSERT INTO `subscription_plans` 
  (`subscription_type_id`, `plan_name`, `plan_slug`, `tier`, `description`, 
   `price_monthly_npr`, `price_yearly_npr`, `benefits`, `features_list`, `is_popular`, `display_order`) 
VALUES
  (1, 'AI Free Plan', 'ai-free', 'free', 'Get started with AI features', 
   0.00, 0.00, 
   '{"ai_tokens": {"monthly_allowance": 1000, "model_access": ["geminisearch"], "priority_processing": false}}',
   '["1,000 AI tokens/month", "Basic AI chat", "Limited quiz generation", "Community support"]', 
   FALSE, 1),
  (1, 'AI Starter', 'ai-starter', 'basic', 'Perfect for students and casual users', 
   299.00, 2990.00,
   '{"ai_tokens": {"monthly_allowance": 50000, "model_access": ["geminisearch", "gpt-4o"], "priority_processing": true}, "support": {"priority": true, "chat_support": true}}',
   '["50,000 AI tokens/month", "All AI models", "Priority processing", "Email support", "No ads"]',
   TRUE, 2),
  (1, 'AI Professional', 'ai-pro', 'premium', 'For power users and professionals', 
   999.00, 9990.00,
   '{"ai_tokens": {"monthly_allowance": 200000, "model_access": ["geminisearch", "gpt-4o", "claude-3.5-sonnet"], "priority_processing": true, "rollover_enabled": true}, "features": {"api_access": true, "analytics_advanced": true}, "support": {"priority": true, "chat_support": true, "phone_support": true}}',
   '["200,000 AI tokens/month", "All premium models", "Token rollover", "API access", "Advanced analytics", "Priority support"]',
   FALSE, 3);

-- Seed Plans: Course Access
INSERT INTO `subscription_plans` 
  (`subscription_type_id`, `plan_name`, `plan_slug`, `tier`, `description`, 
   `price_monthly_npr`, `price_yearly_npr`, `benefits`, `features_list`, `display_order`) 
VALUES
  (2, 'Course Explorer', 'course-explorer', 'basic', 'Access to selected course categories', 
   199.00, 1990.00,
   '{"courses": {"access_type": "category", "category_ids": [1, 2, 3], "download_enabled": false}}',
   '["Access to 3 course categories", "Unlimited course viewing", "Progress tracking", "Quizzes and assessments"]',
   4),
  (2, 'Full Library Access', 'course-full', 'premium', 'Complete access to all courses', 
   499.00, 4990.00,
   '{"courses": {"access_type": "full", "course_limit": null, "download_enabled": true}, "certificates": {"enabled": true, "verified": true}}',
   '["Unlimited course access", "Downloadable content", "Verified certificates", "Offline viewing", "Early access to new courses"]',
   5);

-- Seed Plans: Bundle (AI + Courses)
INSERT INTO `subscription_plans` 
  (`subscription_type_id`, `plan_name`, `plan_slug`, `tier`, `description`, 
   `price_monthly_npr`, `price_yearly_npr`, `benefits`, `features_list`, `badge_text`, `is_popular`, `display_order`) 
VALUES
  (3, 'Ultimate Learner Bundle', 'ultimate-bundle', 'enterprise', 'Everything you need to master your skills', 
   1499.00, 14990.00,
   '{"ai_tokens": {"monthly_allowance": 500000, "model_access": ["all"], "priority_processing": true}, "courses": {"access_type": "full", "download_enabled": true}, "features": {"api_access": true, "white_label": true, "analytics_advanced": true}, "support": {"priority": true, "chat_support": true, "phone_support": true, "dedicated_manager": true}}',
   '["500,000 AI tokens/month", "All courses included", "API access", "White-label option", "Dedicated account manager", "24/7 priority support"]',
   'BEST VALUE',
   TRUE,
   6);


-- ============================================================================
-- SECTION 4: USER SUBSCRIPTIONS TABLE
-- Tracks active subscriptions (supports multiple concurrent subscriptions)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `user_subscriptions` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `plan_id` INT NOT NULL,
  `subscription_type_id` INT NOT NULL COMMENT 'For quick filtering by type',
  `status` ENUM('active', 'trial', 'cancelled', 'expired', 'paused', 'pending') DEFAULT 'active',
  `billing_cycle` ENUM('monthly', 'yearly', 'lifetime') DEFAULT 'monthly',
  
  -- Subscription Period
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `trial_ends_at` TIMESTAMP DEFAULT NULL,
  `current_period_start` TIMESTAMP NOT NULL,
  `current_period_end` TIMESTAMP NOT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `next_billing_date` DATE DEFAULT NULL,
  
  -- Payment
  `auto_renew` BOOLEAN DEFAULT TRUE,
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `last_payment_id` VARCHAR(255) DEFAULT NULL,
  `last_payment_amount` DECIMAL(10,2) DEFAULT NULL,
  `last_payment_date` TIMESTAMP DEFAULT NULL,
  
  -- Cancellation
  `cancel_at_period_end` BOOLEAN DEFAULT FALSE COMMENT 'Let subscription run until period ends',
  `cancelled_at` TIMESTAMP DEFAULT NULL,
  `cancellation_reason` TEXT DEFAULT NULL,
  `cancelled_by` ENUM('user', 'admin', 'payment_failed', 'system') DEFAULT NULL,
  
  -- Usage Tracking (for consumption-based benefits like AI tokens)
  `usage_data` JSON DEFAULT NULL COMMENT 'Track usage within current period',
  
  -- Metadata
  `metadata` JSON DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY `idx_user_id` (`user_id`),
  KEY `idx_plan_id` (`plan_id`),
  KEY `idx_subscription_type_id` (`subscription_type_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_next_billing_date` (`next_billing_date`),
  KEY `idx_user_type_status` (`user_id`, `subscription_type_id`, `status`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`subscription_type_id`) REFERENCES `subscription_types`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User subscriptions - supports multiple concurrent subscriptions of different types';


-- ============================================================================
-- SECTION 5: AI TOKEN TRANSACTIONS TABLE
-- Comprehensive logging of all token-related transactions
-- ============================================================================

CREATE TABLE IF NOT EXISTS `ai_token_transactions` (
  `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `transaction_type` ENUM('purchase', 'subscription', 'consumption', 'refund', 'bonus', 'admin_credit') NOT NULL,
  `amount` DECIMAL(12,4) NOT NULL COMMENT 'Token amount (negative for consumption)',
  `balance_before` DECIMAL(12,2) NOT NULL,
  `balance_after` DECIMAL(12,2) NOT NULL,
  
  -- Consumption Details
  `endpoint` VARCHAR(255) DEFAULT NULL COMMENT 'API endpoint that consumed tokens',
  `ai_model` VARCHAR(100) DEFAULT NULL COMMENT 'AI model used',
  `provider` VARCHAR(100) DEFAULT NULL,
  `session_id` INT DEFAULT NULL,
  `message_id` INT DEFAULT NULL,
  `request_tokens` INT DEFAULT 0 COMMENT 'Input tokens',
  `response_tokens` INT DEFAULT 0 COMMENT 'Output tokens',
  `total_tokens` INT DEFAULT 0 COMMENT 'Total tokens (input + output)',
  
  -- Pricing
  `cost_per_1k_tokens` DECIMAL(8,4) DEFAULT 0.0000 COMMENT 'Cost per 1000 tokens',
  `total_cost` DECIMAL(12,4) GENERATED ALWAYS AS (total_tokens * cost_per_1k_tokens / 1000) STORED,
  
  -- Purchase Details
  `purchase_package_id` INT DEFAULT NULL,
  `payment_id` VARCHAR(255) DEFAULT NULL,
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `payment_amount` DECIMAL(10,2) DEFAULT NULL,
  
  -- Metadata
  `metadata` JSON DEFAULT NULL COMMENT 'Additional context',
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_by` INT DEFAULT NULL COMMENT 'Admin ID for manual credits',
  
  KEY `idx_user_id` (`user_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  `idx_endpoint` (`endpoint`),
  KEY `idx_ai_model` (`ai_model`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_session_id` (`session_id`),
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Comprehensive AI token transaction log';


-- ============================================================================
-- SECTION 6: TOKEN PACKAGES TABLE
-- One-time token purchase packages
-- ============================================================================

CREATE TABLE IF NOT EXISTS `ai_token_packages` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `package_name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `token_amount` DECIMAL(12,2) NOT NULL,
  `bonus_tokens` DECIMAL(12,2) DEFAULT 0.00 COMMENT 'Extra tokens included',
  `total_tokens` DECIMAL(12,2) GENERATED ALWAYS AS (token_amount + bonus_tokens) STORED,
  `price_npr` DECIMAL(10,2) NOT NULL COMMENT 'Price in Nepali Rupees',
  `price_usd` DECIMAL(10,2) DEFAULT NULL,
  `discount_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `is_active` BOOLEAN DEFAULT TRUE,
  `is_popular` BOOLEAN DEFAULT FALSE,
  `validity_days` INT DEFAULT NULL COMMENT 'Expiry days (NULL = never expires)',
  `features` JSON DEFAULT NULL COMMENT 'Special features included',
  `display_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_popular` (`is_popular`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='One-time AI token purchase packages';

-- Seed token packages
INSERT INTO `ai_token_packages` 
  (`package_name`, `description`, `token_amount`, `bonus_tokens`, `price_npr`, `discount_percentage`, `is_popular`, `display_order`) 
VALUES
  ('Starter Pack', 'Perfect for trying out AI features', 10000, 2000, 100.00, 0, FALSE, 1),
  ('Student Pack', 'Ideal for students', 50000, 15000, 450.00, 10, TRUE, 2),
  ('Professional Pack', 'For power users', 150000, 60000, 1200.00, 20, FALSE, 3),
  ('Enterprise Pack', 'Unlimited for businesses', 500000, 250000, 3500.00, 30, FALSE, 4);


-- ============================================================================
-- SECTION 7: UPDATE AI MODELS TABLE
-- Add pricing columns to existing ai_models table
-- ============================================================================

ALTER TABLE `ai_models` 
ADD COLUMN IF NOT EXISTS `cost_per_1k_input_tokens` DECIMAL(8,4) DEFAULT 0.0000 
  COMMENT 'Cost per 1000 input tokens';

ALTER TABLE `ai_models` 
ADD COLUMN IF NOT EXISTS `cost_per_1k_output_tokens` DECIMAL(8,4) DEFAULT 0.0000 
  COMMENT 'Cost per 1000 output tokens';

ALTER TABLE `ai_models` 
ADD COLUMN IF NOT EXISTS `free_tier_enabled` BOOLEAN DEFAULT TRUE 
  COMMENT 'Whether this model is accessible on free tier';

ALTER TABLE `ai_models` 
ADD COLUMN IF NOT EXISTS `required_subscription_tier` ENUM('free', 'basic', 'premium', 'enterprise') DEFAULT 'free'
  COMMENT 'Minimum subscription tier required to access this model';

-- Update existing models with pricing (adjust based on actual costs)
UPDATE `ai_models` SET 
  `cost_per_1k_input_tokens` = 0.001,
  `cost_per_1k_output_tokens` = 0.002,
  `free_tier_enabled` = TRUE,
  `required_subscription_tier` = 'free'
WHERE `name` = 'geminisearch';

UPDATE `ai_models` SET 
  `cost_per_1k_input_tokens` = 0.005,
  `cost_per_1k_output_tokens` = 0.015,
  `free_tier_enabled` = FALSE,
  `required_subscription_tier` = 'premium'
WHERE `name` = 'gpt-4o';

UPDATE `ai_models` SET 
  `cost_per_1k_input_tokens` = 0.003,
  `cost_per_1k_output_tokens` = 0.015,
  `free_tier_enabled` = FALSE,
  `required_subscription_tier` = 'premium'
WHERE `name` = 'claude-3.5-sonnet';


-- ============================================================================
-- SECTION 8: VIEWS FOR EASY QUERYING
-- Useful views for subscription and token analytics
-- ============================================================================

-- View: Active Subscriptions Summary
CREATE OR REPLACE VIEW `v_user_active_subscriptions` AS
SELECT 
  us.user_id,
  us.id AS subscription_id,
  st.type_key,
  st.type_name,
  st.color,
  sp.plan_name,
  sp.tier,
  us.status,
  us.billing_cycle,
  us.current_period_end,
  us.expires_at,
  sp.benefits,
  us.usage_data
FROM user_subscriptions us
JOIN subscription_plans sp ON us.plan_id = sp.id
JOIN subscription_types st ON us.subscription_type_id = st.id
WHERE us.status IN ('active', 'trial');

-- View: Token Usage Summary
CREATE OR REPLACE VIEW `v_user_token_summary` AS
SELECT 
  u.id AS user_id,
  u.ai_tokens_balance,
  u.ai_tokens_purchased,
  u.ai_tokens_consumed,
  COALESCE(SUM(CASE WHEN att.transaction_type = 'consumption' AND att.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
    THEN ABS(att.amount) ELSE 0 END), 0) AS tokens_used_last_30_days,
  COALESCE(SUM(CASE WHEN att.transaction_type = 'purchase' AND att.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
    THEN att.amount ELSE 0 END), 0) AS tokens_purchased_last_30_days
FROM users u
LEFT JOIN ai_token_transactions att ON u.id = att.user_id
GROUP BY u.id;


-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

-- Log migration completion
INSERT INTO `migration_log` (`migration_name`, `applied_at`) 
VALUES ('009_modular_subscription_system', NOW())
ON DUPLICATE KEY UPDATE applied_at = NOW();
