-- Add Points and Gamification System to Database
-- Migration: Add points fields to users table

-- Add points columns to tbl_users
ALTER TABLE tbl_users
ADD COLUMN points INT DEFAULT 0 COMMENT 'Current available points for redemption',
ADD COLUMN lifetime_points INT DEFAULT 0 COMMENT 'Total points earned all time',
ADD COLUMN sweepstakes_entries INT DEFAULT 0 COMMENT 'Number of sweepstakes entries earned',
ADD COLUMN last_points_activity DATETIME DEFAULT NULL COMMENT 'Last time points were earned or spent';

-- Create points_redemption_options table
CREATE TABLE points_redemption_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    points_cost INT NOT NULL,
    reward_type ENUM('discount', 'feature_unlock', 'sweepstakes_entry', 'premium_access', 'physical_item') NOT NULL,
    reward_value VARCHAR(255) NOT NULL COMMENT 'Discount amount, feature name, etc.',
    is_active BOOLEAN DEFAULT TRUE,
    max_redemptions INT DEFAULT NULL COMMENT 'NULL = unlimited',
    current_redemptions INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_points_cost (points_cost)
);

-- Insert default redemption options
INSERT INTO points_redemption_options (name, description, points_cost, reward_type, reward_value) VALUES
('5 Dollar Discount', 'Get $5 off your next purchase', 100, 'discount', '5.00'),
('Premium AI Day Pass', '24 hours of unlimited premium AI access', 200, 'premium_access', '86400'),
('Extra Sweepstakes Entry', 'One additional entry into the weekly sweepstakes', 50, 'sweepstakes_entry', '1'),
('10 Dollar Discount', 'Get $10 off your next purchase', 180, 'discount', '10.00'),
('VIP Business Consultation', '30-minute business consultation call', 500, 'feature_unlock', 'business_consultation');

-- Create points_transactions table for tracking
CREATE TABLE points_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_type ENUM('earned', 'spent', 'bonus', 'expired') NOT NULL,
    points_amount INT NOT NULL COMMENT 'Positive for earned, negative for spent',
    reason VARCHAR(255) NOT NULL COMMENT 'Offer completed, redemption, bonus, etc.',
    reference_id VARCHAR(100) DEFAULT NULL COMMENT 'Offer ID, redemption ID, etc.',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_transaction_type (transaction_type)
);

-- Create sweepstakes system
CREATE TABLE sweepstakes_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    entry_source ENUM('points_redemption', 'offer_completion', 'bonus', 'purchase') NOT NULL,
    reference_id VARCHAR(100) DEFAULT NULL,
    entries_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES tbl_users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Create current sweepstakes table
CREATE TABLE current_sweepstakes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    prize_description TEXT NOT NULL,
    prize_value DECIMAL(10,2) DEFAULT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    max_entries_per_user INT DEFAULT 10,
    total_entries INT DEFAULT 0,
    winner_user_id INT DEFAULT NULL,
    winner_selected_at DATETIME DEFAULT NULL,
    status ENUM('active', 'ended', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (winner_user_id) REFERENCES tbl_users(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_end_date (end_date)
);

-- Insert initial sweepstakes
INSERT INTO current_sweepstakes (name, description, prize_description, prize_value, start_date, end_date) VALUES
('Weekly Cannabis Prize Pack', 'Weekly sweepstakes for premium cannabis accessories and products', 'Complete Cannabis Starter Kit ($150 value)', 150.00, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY));

-- Points system is ready for new users
-- Existing users start with 0 points (can earn through offers)
