-- Coupon codes migration: run this on existing DBs to add coupon support
-- (schema.sql already includes coupon_codes for fresh installs)

CREATE TABLE IF NOT EXISTS `coupon_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `discount_type` enum('percentage','fixed') NOT NULL DEFAULT 'fixed',
  `discount_value` int(11) NOT NULL,
  `min_amount` int(11) DEFAULT 0,
  `max_uses` int(11) DEFAULT NULL,
  `used_count` int(11) NOT NULL DEFAULT 0,
  `valid_from` datetime DEFAULT NULL,
  `valid_to` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_coupon_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add coupon columns to wallet_transactions (run once; skip if columns exist)
ALTER TABLE `wallet_transactions` ADD COLUMN IF NOT EXISTS `coupon_id` int(11) DEFAULT NULL AFTER `ref_id`;
ALTER TABLE `wallet_transactions` ADD COLUMN IF NOT EXISTS `discount_amount` int(11) DEFAULT NULL AFTER `coupon_id`;

-- Sample coupons (ignore if already exist)
INSERT IGNORE INTO `coupon_codes` (`id`, `code`, `discount_type`, `discount_value`, `min_amount`, `max_uses`, `used_count`, `valid_from`, `valid_to`, `is_active`) VALUES
(1, 'WELCOME50', 'fixed', 50, 200, 100, 0, NULL, NULL, 1),
(2, 'FLAT100', 'fixed', 100, 500, NULL, 0, NULL, NULL, 1),
(3, 'SAVE10', 'percentage', 10, 300, 50, 0, NULL, NULL, 1);
