-- 005_users_and_config.sql

-- ====================================
-- 10. USERS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    name VARCHAR(255),
    profile_image_url VARCHAR(500),
    user_type VARCHAR(50) DEFAULT 'user', -- 'user', 'vendor', 'admin'
    
    -- Referral Info
    referral_code VARCHAR(50) UNIQUE,
    referred_by VARCHAR(100),
    referral_count INT DEFAULT 0,
    referral_points INT DEFAULT 0,
    
    -- FCM Token for notifications
    fcm_token TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_email (email),
    INDEX idx_referral (referral_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 11. REFERRAL_EVENTS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS referral_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    referrer_uid VARCHAR(100) NOT NULL,
    referred_uid VARCHAR(100) NOT NULL,
    referred_email VARCHAR(255),
    status VARCHAR(50) DEFAULT 'EARNED',
    trigger_type VARCHAR(50) DEFAULT 'signup',
    points INT DEFAULT 0,
    amount DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 12. APP_CONFIG TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS app_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default app link settings
INSERT INTO app_config (config_key, config_value) VALUES
('appLinks', JSON_OBJECT(
    'dynamicLinkDomain', 'nandailseba.page.link',
    'continueUrl', 'https://nandailseba.firebaseapp.com/email-action'
)),
('referralProgram', JSON_OBJECT(
    'pointsPerReferral', 10,
    'pointsForOneCurrencyUnit', 1
))
ON DUPLICATE KEY UPDATE config_key = config_key;

-- ====================================
-- 13. COUPONS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS coupons (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    coupon_id VARCHAR(100) UNIQUE NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    type VARCHAR(50) DEFAULT 'percentage', -- 'percentage', 'fixed'
    value INT DEFAULT 0,
    min_order_value INT DEFAULT 0,
    max_discount INT,
    usage_limit INT DEFAULT 0,
    used_count INT DEFAULT 0,
    expiry_date DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 14. REFERRAL_REDEMPTIONS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS referral_redemptions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_uid VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'APPROVED',
    transaction_type VARCHAR(50) DEFAULT 'ORDER_PAYMENT',
    order_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 15. CAMPAIGN_ORDERS TABLE
-- ====================================
CREATE TABLE IF NOT EXISTS campaign_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id VARCHAR(100) NOT NULL,
    campaign_id VARCHAR(100) NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    vendor_id VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    qualifies_for_reward BOOLEAN DEFAULT TRUE,
    source VARCHAR(50) DEFAULT 'checkout',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample coupon
INSERT INTO coupons (coupon_id, code, type, value, min_order_value, is_active) VALUES
('coupon_001', 'WELCOME10', 'percentage', 10, 100, TRUE)
ON DUPLICATE KEY UPDATE code = code;