-- =====================================================
-- Coupon System Migration
-- Creates coupon table and related functionality
-- =====================================================

-- Create coupons table
CREATE TABLE IF NOT EXISTS `coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL UNIQUE,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `initial_balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL COMMENT 'Admin ID who created the coupon',
  `valid_from` datetime DEFAULT CURRENT_TIMESTAMP,
  `valid_until` datetime DEFAULT NULL,
  `usage_limit` int(11) DEFAULT NULL COMMENT 'Maximum number of times this coupon can be used',
  `used_count` int(11) DEFAULT 0,
  `min_purchase_amount` decimal(10,2) DEFAULT 0.00 COMMENT 'Minimum purchase amount to use this coupon',
  `max_discount_amount` decimal(10,2) DEFAULT NULL COMMENT 'Maximum discount amount for percentage coupons',
  `coupon_type` enum('balance','percentage','fixed') DEFAULT 'balance' COMMENT 'balance: deduct from balance, percentage: % discount, fixed: fixed amount discount',
  `discount_value` decimal(10,2) DEFAULT 0.00 COMMENT 'For percentage or fixed discount coupons',
  `applicable_courses` text DEFAULT NULL COMMENT 'JSON array of course IDs this coupon applies to, null means all courses',
  `applicable_institutes` text DEFAULT NULL COMMENT 'JSON array of institute IDs this coupon applies to, null means all institutes',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_coupon_code` (`code`),
  KEY `idx_coupon_active` (`is_active`),
  KEY `idx_coupon_validity` (`valid_from`, `valid_until`),
  KEY `idx_coupon_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create coupon usage history table
CREATE TABLE IF NOT EXISTS `coupon_usage_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `course_id` int(11) DEFAULT NULL,
  `enrollment_id` int(11) DEFAULT NULL,
  `amount_used` decimal(10,2) NOT NULL,
  `original_price` decimal(10,2) NOT NULL,
  `final_price` decimal(10,2) NOT NULL,
  `discount_applied` decimal(10,2) NOT NULL,
  `usage_type` enum('enrollment','refund') DEFAULT 'enrollment',
  `transaction_id` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_coupon_usage_coupon` (`coupon_id`),
  KEY `idx_coupon_usage_user` (`user_id`),
  KEY `idx_coupon_usage_course` (`course_id`),
  KEY `idx_coupon_usage_enrollment` (`enrollment_id`),
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user coupon assignments table (for assigning specific coupons to users)
CREATE TABLE IF NOT EXISTS `user_coupon_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `coupon_id` int(11) NOT NULL,
  `assigned_by` int(11) DEFAULT NULL COMMENT 'Admin ID who assigned the coupon',
  `assigned_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_used` tinyint(1) DEFAULT 0,
  `used_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_coupon` (`user_id`, `coupon_id`),
  KEY `idx_user_coupon_user` (`user_id`),
  KEY `idx_user_coupon_coupon` (`coupon_id`),
  KEY `idx_user_coupon_assigned_by` (`assigned_by`),
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample coupons
INSERT INTO `coupons` (`code`, `name`, `description`, `balance`, `initial_balance`, `is_active`, `valid_from`, `valid_until`, `usage_limit`, `coupon_type`, `discount_value`, `notes`) VALUES
('WELCOME100', 'Welcome Bonus', 'Welcome bonus for new users', 100.00, 100.00, 1, NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), 1, 'balance', 0.00, 'One-time welcome bonus for new users'),
('STUDENT50', 'Student Discount', '50% discount for students', 0.00, 0.00, 1, NOW(), DATE_ADD(NOW(), INTERVAL 6 MONTH), NULL, 'percentage', 50.00, 'Student discount coupon'),
('SAVE20', 'Save 20 NPR', 'Fixed 20 NPR discount', 0.00, 0.00, 1, NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), NULL, 'fixed', 20.00, 'Fixed discount coupon'),
('PREMIUM500', 'Premium Balance', 'Premium user balance coupon', 500.00, 500.00, 1, NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), 1, 'balance', 0.00, 'Premium balance for VIP users');

-- Insert demo user coupon assignments
INSERT INTO `user_coupon_assignments` (`user_id`, `coupon_id`, `assigned_by`, `notes`) VALUES
(60, 1, 1, 'Welcome bonus for user 60'),
(60, 2, 1, 'Student discount for user 60'),
(60, 3, 1, 'Save 20 NPR coupon for user 60'),
(60, 4, 1, 'Premium balance for VIP user 60');

-- Insert demo coupon usage history (example transactions)
INSERT INTO `coupon_usage_history` (`coupon_id`, `user_id`, `course_id`, `enrollment_id`, `amount_used`, `original_price`, `final_price`, `discount_applied`, `usage_type`, `transaction_id`, `notes`) VALUES
(1, 60, 1, 8, 39.99, 39.99, 0.00, 39.99, 'enrollment', 'TXN_WELCOME_001', 'Used welcome bonus for course enrollment'),
(2, 60, 2, 9, 19.99, 39.99, 20.00, 19.99, 'enrollment', 'TXN_STUDENT_001', 'Used student discount for course enrollment'),
(3, 60, 3, 10, 20.00, 39.99, 19.99, 20.00, 'enrollment', 'TXN_SAVE20_001', 'Used fixed discount for course enrollment');

-- Insert demo enrollments with coupon usage (update existing enrollments)
UPDATE `enrollments` SET 
    `coupon_id` = 1, 
    `discount_applied` = 39.99,
    `payment_status` = 'free'
WHERE `id` = 8;

UPDATE `enrollments` SET 
    `coupon_id` = 2, 
    `discount_applied` = 19.99,
    `payment_amount` = 20.00
WHERE `id` = 9;

UPDATE `enrollments` SET 
    `coupon_id` = 3, 
    `discount_applied` = 20.00,
    `payment_amount` = 19.99
WHERE `id` = 10;

-- Create triggers for coupon usage tracking
DELIMITER $$

CREATE TRIGGER `update_coupon_usage_count` AFTER INSERT ON `coupon_usage_history` 
FOR EACH ROW 
BEGIN
    UPDATE `coupons` 
    SET `used_count` = `used_count` + 1 
    WHERE `id` = NEW.coupon_id;
END$$

CREATE TRIGGER `update_coupon_balance` AFTER INSERT ON `coupon_usage_history` 
FOR EACH ROW 
BEGIN
    -- Only update balance for balance-type coupons
    IF (SELECT `coupon_type` FROM `coupons` WHERE `id` = NEW.coupon_id) = 'balance' THEN
        UPDATE `coupons` 
        SET `balance` = `balance` - NEW.amount_used 
        WHERE `id` = NEW.coupon_id;
    END IF;
END$$

CREATE TRIGGER `mark_user_coupon_used` AFTER INSERT ON `coupon_usage_history` 
FOR EACH ROW 
BEGIN
    -- Mark user coupon assignment as used if exists
    UPDATE `user_coupon_assignments` 
    SET `is_used` = 1, `used_at` = NOW() 
    WHERE `user_id` = NEW.user_id AND `coupon_id` = NEW.coupon_id AND `is_used` = 0;
END$$

DELIMITER ;

-- Add indexes for better performance
CREATE INDEX `idx_enrollments_user_course` ON `enrollments` (`user_id`, `course_id`);
CREATE INDEX `idx_enrollments_payment_status` ON `enrollments` (`payment_status`);
CREATE INDEX `idx_enrollments_enrolled_at` ON `enrollments` (`enrolled_at`);

-- Add coupon_id column to enrollments table to track which coupon was used
ALTER TABLE `enrollments` ADD COLUMN `coupon_id` int(11) DEFAULT NULL AFTER `payment_id`;
ALTER TABLE `enrollments` ADD COLUMN `discount_applied` decimal(10,2) DEFAULT 0.00 AFTER `coupon_id`;
ALTER TABLE `enrollments` ADD KEY `idx_enrollments_coupon` (`coupon_id`);
