-- ============================================================================
-- AI Token System - Complete Database Migration
-- ============================================================================
-- Database: ashlya_ashlya_academy_new
-- Execute this file to add all missing token system tables and columns
-- ============================================================================

USE ashlya_ashlya_academy_new;

-- Step 1: Add token columns to user table
ALTER TABLE `user` 
ADD COLUMN `ai_tokens_balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Current available token balance',
ADD COLUMN `ai_tokens_purchased` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Total tokens purchased',
ADD COLUMN `ai_tokens_consumed` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Total tokens consumed';


-- Step 2: Create ai_token_packages table
CREATE TABLE `ai_token_packages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `package_name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `token_amount` DECIMAL(10,2) NOT NULL,
  `bonus_tokens` DECIMAL(10,2) DEFAULT 0.00,
  `total_tokens` DECIMAL(10,2) GENERATED ALWAYS AS (`token_amount` + `bonus_tokens`) STORED,
  `price_npr` DECIMAL(10,2) NOT NULL,
  `price_usd` DECIMAL(10,2) NOT NULL,
  `discount_percentage` INT DEFAULT 0,
  `is_popular` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `validity_days` INT DEFAULT NULL,
  `features` JSON DEFAULT NULL,
  `display_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_active_packages` (`is_active`, `display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Step 3: Create ai_token_transactions table
CREATE TABLE `ai_token_transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `transaction_type` ENUM('purchase', 'consumption', 'bonus', 'refund', 'subscription') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `balance_before` DECIMAL(10,2) NOT NULL,
  `balance_after` DECIMAL(10,2) NOT NULL,
  `package_id` INT(11) DEFAULT NULL,
  `description` TEXT,
  `metadata` JSON DEFAULT NULL,
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `payment_reference` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'completed',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_transactions` (`user_id`, `created_at` DESC),
  INDEX `idx_transaction_type` (`transaction_type`),
  INDEX `idx_status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`package_id`) REFERENCES `ai_token_packages`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Step 4: Insert sample token packages
INSERT INTO `ai_token_packages` (
  `package_name`, `description`, `token_amount`, `bonus_tokens`, 
  `price_npr`, `price_usd`, `discount_percentage`, `is_popular`, 
  `validity_days`, `features`, `display_order`
) VALUES
  (
    'Starter Pack',
    'Perfect for trying out AI features',
    100.00,
    10.00,
    500.00,
    5.00,
    0,
    0,
    30,
    JSON_ARRAY(
      'Access to all AI models',
      '110 total tokens',
      'Valid for 30 days',
      'Email support'
    ),
    1
  ),
  (
    'Pro Pack',
    'Best value for regular users',
    500.00,
    100.00,
    2000.00,
    20.00,
    10,
    1,
    60,
    JSON_ARRAY(
      'Access to all AI models',
      '600 total tokens',
      'Valid for 60 days',
      '10% bonus tokens',
      'Priority support'
    ),
    2
  ),
  (
    'Enterprise Pack',
    'For power users and teams',
    2000.00,
    500.00,
    7000.00,
    70.00,
    15,
    0,
    90,
    JSON_ARRAY(
      'Access to all AI models',
      '2,500 total tokens',
      'Valid for 90 days',
      '25% bonus tokens',
      '24/7 priority support',
      'Dedicated account manager'
    ),
    3
  ),
  (
    'Unlimited Monthly',
    'Unlimited AI usage for one month',
    999999.00,
    0.00,
    5000.00,
    50.00,
    0,
    1,
    30,
    JSON_ARRAY(
      'Unlimited AI model access',
      'No token limits',
      'Valid for 30 days',
      'All premium models included',
      '24/7 priority support'
    ),
    4
  );

-- Migration complete!
