-- Migration to add user_audio_generations table
-- Usage: Store history of generated audio files for users

USE ashlya_academy;

CREATE TABLE IF NOT EXISTS `user_audio_generations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `text` text DEFAULT NULL,
  `voice` varchar(50) DEFAULT NULL,
  `language_code` varchar(20) DEFAULT 'en-US',
  `filename` varchar(255) NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `file_size` int(11) DEFAULT 0,
  `audio_format` varchar(10) DEFAULT 'mp3',
  `tokens_used` int(11) DEFAULT 0,
  `is_teacher_mode` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='History of AI audio generations by users';

-- Verify table creation
SELECT 'User Audio Generations Table Created' as status, COUNT(*) as table_exists 
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'user_audio_generations';
