-- Create plan_description table for plan definitions
CREATE TABLE IF NOT EXISTS `plan_description` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `plan_name` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT NOT NULL,
    `price_monthly` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `price_yearly` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `stripe_price_id_monthly` VARCHAR(255) NULL,
    `stripe_price_id_yearly` VARCHAR(255) NULL,
    `stripe_product_id` VARCHAR(255) NULL,
    `stripe_checkout_url_monthly` TEXT NULL,
    `stripe_checkout_url_yearly` TEXT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `sort_order` INT DEFAULT 0,
    `limitations` JSON NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_plan_name` (`plan_name`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_plan table to track current user plans
CREATE TABLE IF NOT EXISTS `user_plan` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `plan_id` INT NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_plan_id` (`plan_id`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_expires_at` (`expires_at`),
    UNIQUE KEY `unique_active_user_plan` (`user_id`, `is_active`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`plan_id`) REFERENCES `plan_description`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default plans
INSERT INTO `plan_description` (`plan_name`, `description`, `price_monthly`, `price_yearly`, `limitations`, `sort_order`) VALUES
('Free', 'Basic plan with limited features', 0.00, 0.00, '{
    "data_file_limit": 5,
    "data_file_size_limit": 10485760,
    "total_data_limit": 52428800,
    "monthly_processing_time_limit": 300,
    "api_calls_limit": 100,
    "api_client_call_limit": 10,
    "allow_api_access": false,
    "max_link_names": 3,
    "max_concurrent_uploads": 1,
    "support_level": "community"
}', 0),
('Starter', 'Perfect for small projects and personal use', 9.99, 99.99, '{
    "data_file_limit": 50,
    "data_file_size_limit": 104857600,
    "total_data_limit": 1073741824,
    "monthly_processing_time_limit": 1800,
    "api_calls_limit": 1000,
    "api_client_call_limit": 100,
    "allow_api_access": true,
    "max_link_names": 25,
    "max_concurrent_uploads": 3,
    "support_level": "email"
}', 1),
('Professional', 'For businesses and advanced users', 29.99, 299.99, '{
    "data_file_limit": 200,
    "data_file_size_limit": 1073741824,
    "total_data_limit": 10737418240,
    "monthly_processing_time_limit": 7200,
    "api_calls_limit": 10000,
    "api_client_call_limit": 1000,
    "allow_api_access": true,
    "max_link_names": 100,
    "max_concurrent_uploads": 10,
    "support_level": "priority"
}', 2),
('Enterprise', 'Unlimited access for large organizations', 99.99, 999.99, '{
    "data_file_limit": -1,
    "data_file_size_limit": -1,
    "total_data_limit": -1,
    "monthly_processing_time_limit": -1,
    "api_calls_limit": -1,
    "api_client_call_limit": -1,
    "allow_api_access": true,
    "max_link_names": -1,
    "max_concurrent_uploads": -1,
    "support_level": "dedicated"
}', 3)
ON DUPLICATE KEY UPDATE 
    `description` = VALUES(`description`),
    `limitations` = VALUES(`limitations`),
    `updated_at` = CURRENT_TIMESTAMP;

-- Assign all existing users to the Free plan
INSERT INTO `user_plan` (`user_id`, `plan_id`, `is_active`)
SELECT u.id, p.id, TRUE
FROM `users` u
CROSS JOIN `plan_description` p
WHERE p.plan_name = 'Free'
AND NOT EXISTS (
    SELECT 1 FROM `user_plan` up 
    WHERE up.user_id = u.id AND up.is_active = TRUE
);
