-- Migration: Add Basic Promo Code System (Simplified)
-- Date: 2025-11-04
-- Description: Simple promo code system for partner registrations and basic discounts

-- Phase 1: Add pricing to stages table (CRITICAL - Currently Missing)
ALTER TABLE `stages` 
ADD COLUMN `price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Stage registration price',
ADD COLUMN `currency` VARCHAR(3) DEFAULT 'EUR' COMMENT 'Price currency code';

-- Phase 2: Create simplified promo_codes table
CREATE TABLE `promo_codes` (
  `id` CHAR(36) NOT NULL COMMENT 'UUID primary key',
  `code` VARCHAR(50) NOT NULL COMMENT 'Promo code string (e.g., PARTNER2025)',
  `description` TEXT COMMENT 'Human-readable description of the promo',
  `discount_type` ENUM('percentage', 'fixed_amount', 'free') NOT NULL DEFAULT 'percentage' COMMENT 'Type of discount calculation',
  `discount_value` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Discount amount (percentage or fixed value)',
  `max_uses` INT DEFAULT NULL COMMENT 'Maximum number of times code can be used (NULL = unlimited)',
  `uses_count` INT NOT NULL DEFAULT 0 COMMENT 'Current number of times code has been used',
  `valid_from` DATETIME NOT NULL COMMENT 'Code becomes valid from this date/time',
  `valid_until` DATETIME NOT NULL COMMENT 'Code expires after this date/time',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether code is currently active',
  `created_by` CHAR(36) NOT NULL COMMENT 'User ID who created the promo code',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code` (`code`) COMMENT 'Ensure promo codes are unique',
  KEY `idx_code_active` (`code`, `is_active`) COMMENT 'Fast lookup for validation',
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `fk_promo_codes_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Promotional discount codes';

-- Phase 3: Modify registrations table to include pricing and promo code info
ALTER TABLE `registrations` 
ADD COLUMN `promo_code_id` CHAR(36) DEFAULT NULL COMMENT 'Promo code used for this registration',
ADD COLUMN `original_price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Original stage price before discount',
ADD COLUMN `final_price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Final price after discount',
ADD COLUMN `discount_applied` DECIMAL(10,2) DEFAULT NULL COMMENT 'Amount of discount applied';

-- Add indexes and foreign key for registrations
ALTER TABLE `registrations` 
ADD KEY `idx_promo_code_id` (`promo_code_id`) COMMENT 'Promo code usage lookup';

ALTER TABLE `registrations` 
ADD CONSTRAINT `fk_registrations_promo` FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes` (`id`) ON DELETE SET NULL;

-- Add useful indexes for performance
CREATE INDEX `idx_promo_codes_validation` ON `promo_codes` (`code`, `is_active`, `valid_from`, `valid_until`);
CREATE INDEX `idx_stages_pricing` ON `stages` (`price`, `currency`);

-- Insert partner promo codes for testing
INSERT INTO `promo_codes` (`id`, `code`, `description`, `discount_type`, `discount_value`, `max_uses`, `valid_from`, `valid_until`, `created_by`) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'PARTNER2025', 'Free registration for partners', 'free', 0.00, NULL, '2025-01-01 00:00:00', '2025-12-31 23:59:59', (SELECT id FROM users WHERE role = 'admin' LIMIT 1)),
('550e8400-e29b-41d4-a716-446655440002', 'STAFF', 'Free registration for staff members', 'free', 0.00, NULL, '2025-01-01 00:00:00', '2025-12-31 23:59:59', (SELECT id FROM users WHERE role = 'admin' LIMIT 1)),
('550e8400-e29b-41d4-a716-446655440003', 'WELCOME10', '10% welcome discount', 'percentage', 10.00, 100, '2025-01-01 00:00:00', '2025-12-31 23:59:59', (SELECT id FROM users WHERE role = 'admin' LIMIT 1));

-- Update existing stages with sample pricing
UPDATE `stages` SET `price` = 25.00, `currency` = 'EUR' WHERE `name` LIKE '%10km%' OR `name` LIKE '%10K%';
UPDATE `stages` SET `price` = 35.00, `currency` = 'EUR' WHERE `name` LIKE '%21km%' OR `name` LIKE '%half%' OR `name` LIKE '%semi%';
UPDATE `stages` SET `price` = 45.00, `currency` = 'EUR' WHERE `name` LIKE '%42km%' OR `name` LIKE '%marathon%' OR `name` LIKE '%full%';
UPDATE `stages` SET `price` = 15.00, `currency` = 'EUR' WHERE `name` LIKE '%5km%' OR `name` LIKE '%5K%';
UPDATE `stages` SET `price` = 20.00, `currency` = 'EUR' WHERE `price` IS NULL; -- Default price for stages without specific pricing

-- Add constraints to ensure data integrity
ALTER TABLE `promo_codes` ADD CONSTRAINT `chk_discount_value_non_negative` CHECK (`discount_value` >= 0);
ALTER TABLE `promo_codes` ADD CONSTRAINT `chk_percentage_max_100` CHECK (`discount_type` != 'percentage' OR `discount_value` <= 100);
ALTER TABLE `promo_codes` ADD CONSTRAINT `chk_max_uses_positive` CHECK (`max_uses` IS NULL OR `max_uses` > 0);
ALTER TABLE `promo_codes` ADD CONSTRAINT `chk_uses_count_non_negative` CHECK (`uses_count` >= 0);
ALTER TABLE `promo_codes` ADD CONSTRAINT `chk_valid_date_range` CHECK (`valid_from` < `valid_until`);

ALTER TABLE `stages` ADD CONSTRAINT `chk_stage_price_positive` CHECK (`price` IS NULL OR `price` >= 0);

-- Create a simple view for active promo codes
CREATE VIEW `active_promo_codes` AS
SELECT 
    pc.*,
    (pc.max_uses IS NULL OR pc.uses_count < pc.max_uses) AS has_remaining_uses,
    (NOW() BETWEEN pc.valid_from AND pc.valid_until) AS is_date_valid
FROM `promo_codes` pc
WHERE pc.is_active = 1;

-- Migration completion marker
INSERT INTO `migration_log` (`migration_name`, `executed_at`) VALUES ('20251104_add_promo_code_system', NOW())
ON DUPLICATE KEY UPDATE `executed_at` = NOW();