-- Migration to add point redemption to crypto feature
-- Date: 2025-01-XX

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- Update referral_point_transactions type enum to include 'redeemed'
ALTER TABLE `referral_point_transactions` 
  MODIFY COLUMN `type` enum('earned','spent','redeemed') NOT NULL;

-- Create point_redemptions table to track crypto redemptions
CREATE TABLE `point_redemptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `buyer_id` int(11) NOT NULL,
  `points` decimal(10,2) NOT NULL,
  `crypto_currency` varchar(20) NOT NULL,
  `wallet_address` varchar(255) NOT NULL,
  `crypto_amount` decimal(20,8) DEFAULT NULL,
  `status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
  `transaction_hash` varchar(255) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `buyer_id` (`buyer_id`),
  KEY `status` (`status`),
  CONSTRAINT `point_redemptions_ibfk_1` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add redemption rate setting (points per 1 CHF worth of crypto)
-- This determines how many points are needed to redeem 1 CHF worth of crypto
INSERT INTO `referral_settings` (`setting_key`, `setting_value`, `description`) 
VALUES ('points_to_crypto_chf', '100', 'Number of referral points required to redeem 1 CHF worth of crypto')
ON DUPLICATE KEY UPDATE `description` = 'Number of referral points required to redeem 1 CHF worth of crypto';

-- Add minimum redemption amount setting
INSERT INTO `referral_settings` (`setting_key`, `setting_value`, `description`) 
VALUES ('min_redemption_points', '1000', 'Minimum number of points required for crypto redemption')
ON DUPLICATE KEY UPDATE `description` = 'Minimum number of points required for crypto redemption';

COMMIT;