CREATE TABLE IF NOT EXISTS `ad_watch_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `ad_type` ENUM('banner', 'interstitial', 'rewarded') NOT NULL,
    `coins_earned` INT DEFAULT 0,
    `watched_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_ad_type` (`ad_type`),
    INDEX `idx_watched_at` (`watched_at`)
);

CREATE TABLE IF NOT EXISTS `user_coins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `total_coins` INT DEFAULT 0,
    `available_coins` INT DEFAULT 0,
    `redeemed_coins` INT DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_user` (`user_id`)
);

CREATE TABLE IF NOT EXISTS `coin_exchanges` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `exchange_type` ENUM('service_extension', 'new_account') NOT NULL,
    `coins_used` INT NOT NULL,
    `details` JSON,
    `status` ENUM('pending', 'completed', 'rejected') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_exchange_type` (`exchange_type`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
);

CREATE TABLE IF NOT EXISTS `coin_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_name` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO `coin_settings` (`setting_name`, `setting_value`, `description`) VALUES
('coins_per_month', '100', 'Coins required to extend service by 1 month'),
('coins_per_account', '200', 'Coins required to create a new user account')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);