-- =====================================================
-- AI Music & MIDI Generation Platform - Database Schema
-- MySQL 8.0+ Compatible
-- =====================================================

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

-- =====================================================
-- USERS TABLE
-- =====================================================
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(100) DEFAULT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `subscription_tier` ENUM('free', 'pro', 'producer_plus') DEFAULT 'free',
  `subscription_expires_at` DATETIME DEFAULT NULL,
  `email_verified` TINYINT(1) DEFAULT 0,
  `email_verified_at` DATETIME DEFAULT NULL,
  `two_factor_enabled` TINYINT(1) DEFAULT 0,
  `two_factor_secret` VARCHAR(255) DEFAULT NULL,
  `xp_points` INT UNSIGNED DEFAULT 0,
  `creativity_score` DECIMAL(5,2) DEFAULT 50.00,
  `total_generations` INT UNSIGNED DEFAULT 0,
  `total_projects` INT UNSIGNED DEFAULT 0,
  `wallet_balance` DECIMAL(10,2) DEFAULT 0.00,
  `preferred_currency` VARCHAR(3) DEFAULT 'ZMW',
  `last_login_at` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  UNIQUE KEY `idx_username` (`username`),
  KEY `idx_subscription_tier` (`subscription_tier`),
  KEY `idx_email_verified` (`email_verified`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SUBSCRIPTIONS TABLE
-- =====================================================
CREATE TABLE `subscriptions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `plan` ENUM('free', 'pro', 'producer_plus') NOT NULL,
  `status` ENUM('active', 'cancelled', 'expired', 'pending') DEFAULT 'pending',
  `pawapay_deposit_id` VARCHAR(255) DEFAULT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'ZMW',
  `interval` ENUM('monthly', 'yearly') DEFAULT 'monthly',
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `cancelled_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_pawapay_deposit_id` (`pawapay_deposit_id`),
  CONSTRAINT `fk_subscriptions_user` FOREIGN KEY (`user_id`) REFERENCES `users (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PROJECTS TABLE
-- =====================================================
CREATE TABLE `projects` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `bpm` INT UNSIGNED DEFAULT NULL,
  `key_signature` VARCHAR(10) DEFAULT NULL,
  `scale` VARCHAR(50) DEFAULT NULL,
  `genre` VARCHAR(100) DEFAULT NULL,
  `mood` VARCHAR(100) DEFAULT NULL,
  `cover_image` VARCHAR(255) DEFAULT NULL,
  `is_public` TINYINT(1) DEFAULT 0,
  `is_template` TINYINT(1) DEFAULT 0,
  `status` ENUM('draft', 'in_progress', 'completed', 'archived') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_genre` (`genre`),
  KEY `idx_status` (`status`),
  KEY `idx_is_public` (`is_public`),
  CONSTRAINT `fk_projects_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MIDI FILES TABLE
-- =====================================================
CREATE TABLE `midi_files` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `project_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `type` ENUM('melody', 'chord_progression', 'bassline', 'drum_pattern', 'arpeggiator', 'counter_melody', 'arrangement') NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `duration` DECIMAL(10,2) DEFAULT NULL,
  `bpm` INT UNSIGNED DEFAULT NULL,
  `key_signature` VARCHAR(10) DEFAULT NULL,
  `scale` VARCHAR(50) DEFAULT NULL,
  `genre` VARCHAR(100) DEFAULT NULL,
  `tags` JSON DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `is_public` TINYINT(1) DEFAULT 0,
  `downloads_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_type` (`type`),
  KEY `idx_genre` (`genre`),
  CONSTRAINT `fk_midi_files_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_midi_files_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- GENERATIONS TABLE
-- =====================================================
CREATE TABLE `generations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `project_id` INT UNSIGNED DEFAULT NULL,
  `generation_type` ENUM('melody', 'chord_progression', 'bassline', 'drum_pattern', 'arpeggiator', 'counter_melody', 'arrangement', 'genre_fusion', 'emotion_based', 'surprise_me', 'extreme_inspiration') NOT NULL,
  `parameters` JSON NOT NULL,
  `result_data` JSON NOT NULL,
  `midi_file_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
  `error_message` TEXT DEFAULT NULL,
  `processing_time_ms` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_generation_type` (`generation_type`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_generations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_generations_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_generations_midi_file` FOREIGN KEY (`midi_file_id`) REFERENCES `midi_files` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- GENERATION QUEUE TABLE
-- =====================================================
CREATE TABLE `generation_queue` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `generation_id` INT UNSIGNED NOT NULL,
  `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
  `status` ENUM('queued', 'processing', 'completed', 'failed') DEFAULT 'queued',
  `worker_id` VARCHAR(100) DEFAULT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `attempts` TINYINT UNSIGNED DEFAULT 0,
  `max_attempts` TINYINT UNSIGNED DEFAULT 3,
  `error_message` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_generation_id` (`generation_id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_generation_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_generation_queue_generation` FOREIGN KEY (`generation_id`) REFERENCES `generations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EXPORTS TABLE
-- =====================================================
CREATE TABLE `exports` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `project_id` INT UNSIGNED DEFAULT NULL,
  `midi_file_id` INT UNSIGNED DEFAULT NULL,
  `export_type` ENUM('midi', 'wav', 'mp3', 'zip_project') NOT NULL,
  `format` VARCHAR(50) DEFAULT NULL,
  `file_path` VARCHAR(500) DEFAULT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `download_url` VARCHAR(500) DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
  `error_message` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_midi_file_id` (`midi_file_id`),
  KEY `idx_export_type` (`export_type`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_exports_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_exports_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exports_midi_file` FOREIGN KEY (`midi_file_id`) REFERENCES `midi_files` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PRODUCER DNA PROFILES TABLE
-- =====================================================
CREATE TABLE `producer_dna_profiles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `favorite_genres` JSON DEFAULT NULL,
  `favorite_bpms` JSON DEFAULT NULL,
  `favorite_scales` JSON DEFAULT NULL,
  `favorite_keys` JSON DEFAULT NULL,
  `favorite_chords` JSON DEFAULT NULL,
  `favorite_arrangements` JSON DEFAULT NULL,
  `favorite_instruments` JSON DEFAULT NULL,
  `common_patterns` JSON DEFAULT NULL,
  `melodic_tendencies` JSON DEFAULT NULL,
  `harmonic_tendencies` JSON DEFAULT NULL,
  `rhythmic_tendencies` JSON DEFAULT NULL,
  `last_50_projects_analyzed` INT UNSIGNED DEFAULT 0,
  `last_100_generations_analyzed` INT UNSIGNED DEFAULT 0,
  `analysis_date` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_producer_dna_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CREATIVITY SCORES TABLE
-- =====================================================
CREATE TABLE `creativity_scores` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `overall_score` DECIMAL(5,2) NOT NULL,
  `harmonic_diversity` DECIMAL(5,2) DEFAULT NULL,
  `rhythmic_diversity` DECIMAL(5,2) DEFAULT NULL,
  `melodic_diversity` DECIMAL(5,2) DEFAULT NULL,
  `genre_diversity` DECIMAL(5,2) DEFAULT NULL,
  `scale_diversity` DECIMAL(5,2) DEFAULT NULL,
  `arrangement_diversity` DECIMAL(5,2) DEFAULT NULL,
  `growth_trend` ENUM('improving', 'stable', 'declining') DEFAULT 'stable',
  `improvement_suggestions` JSON DEFAULT NULL,
  `creative_block_risk` ENUM('low', 'medium', 'high') DEFAULT 'low',
  `calculated_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_overall_score` (`overall_score`),
  KEY `idx_calculated_at` (`calculated_at`),
  CONSTRAINT `fk_creativity_scores_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSPIRATION FEED TABLE
-- =====================================================
CREATE TABLE `inspiration_feed` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `type` ENUM('trending_genre', 'trending_bpm', 'trending_chord', 'viral_style', 'daily_inspiration', 'tip', 'news') NOT NULL,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `source_url` VARCHAR(500) DEFAULT NULL,
  `tags` JSON DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `display_order` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- IDEA VAULT TABLE
-- =====================================================
CREATE TABLE `idea_vault` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `idea_type` ENUM('melody', 'chord_progression', 'bassline', 'drum_pattern', 'arrangement', 'concept', 'full_idea') NOT NULL,
  `data` JSON NOT NULL,
  `tags` JSON DEFAULT NULL,
  `is_favorite` TINYINT(1) DEFAULT 0,
  `folder` VARCHAR(100) DEFAULT NULL,
  `color` VARCHAR(7) DEFAULT NULL,
  `rating` TINYINT UNSIGNED DEFAULT NULL,
  `usage_count` INT UNSIGNED DEFAULT 0,
  `last_used_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_idea_type` (`idea_type`),
  KEY `idx_is_favorite` (`is_favorite`),
  KEY `idx_folder` (`folder`),
  CONSTRAINT `fk_idea_vault_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CHALLENGES TABLE
-- =====================================================
CREATE TABLE `challenges` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `type` ENUM('daily', 'weekly', 'monthly') NOT NULL,
  `difficulty` ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',
  `requirements` JSON NOT NULL,
  `xp_reward` INT UNSIGNED NOT NULL,
  `badge_reward` VARCHAR(100) DEFAULT NULL,
  `creativity_points` INT UNSIGNED DEFAULT 0,
  `genre` VARCHAR(100) DEFAULT NULL,
  `bpm_constraint` JSON DEFAULT NULL,
  `scale_constraint` JSON DEFAULT NULL,
  `instrument_constraint` JSON DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `starts_at` DATETIME NOT NULL,
  `ends_at` DATETIME NOT NULL,
  `participants_count` INT UNSIGNED DEFAULT 0,
  `completions_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_starts_at` (`starts_at`),
  KEY `idx_ends_at` (`ends_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CHALLENGE PROGRESS TABLE
-- =====================================================
CREATE TABLE `challenge_progress` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `challenge_id` INT UNSIGNED NOT NULL,
  `status` ENUM('not_started', 'in_progress', 'completed', 'abandoned') DEFAULT 'not_started',
  `progress_data` JSON DEFAULT NULL,
  `submission_data` JSON DEFAULT NULL,
  `xp_earned` INT UNSIGNED DEFAULT 0,
  `badge_earned` VARCHAR(100) DEFAULT NULL,
  `creativity_points_earned` INT UNSIGNED DEFAULT 0,
  `completed_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_challenge` (`user_id`, `challenge_id`),
  KEY `idx_challenge_id` (`challenge_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_challenge_progress_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_challenge_progress_challenge` FOREIGN KEY (`challenge_id`) REFERENCES `challenges` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MARKETPLACE TABLE
-- =====================================================
CREATE TABLE `marketplace` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `seller_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `type` ENUM('midi_pack', 'chord_pack', 'drum_kit', 'preset_pack', 'template', 'sample_pack') NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'USD',
  `cover_image` VARCHAR(500) DEFAULT NULL,
  `preview_file` VARCHAR(500) DEFAULT NULL,
  `download_file` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `tags` JSON DEFAULT NULL,
  `genre` VARCHAR(100) DEFAULT NULL,
  `bpm` INT UNSIGNED DEFAULT NULL,
  `key_signature` VARCHAR(10) DEFAULT NULL,
  `is_approved` TINYINT(1) DEFAULT 0,
  `is_featured` TINYINT(1) DEFAULT 0,
  `views_count` INT UNSIGNED DEFAULT 0,
  `sales_count` INT UNSIGNED DEFAULT 0,
  `rating_average` DECIMAL(3,2) DEFAULT 0.00,
  `rating_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_approved` (`is_approved`),
  KEY `idx_is_featured` (`is_featured`),
  KEY `idx_genre` (`genre`),
  CONSTRAINT `fk_marketplace_seller` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PURCHASES TABLE
-- =====================================================
CREATE TABLE `purchases` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `buyer_id` INT UNSIGNED NOT NULL,
  `seller_id` INT UNSIGNED NOT NULL,
  `marketplace_item_id` INT UNSIGNED NOT NULL,
  `transaction_id` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'USD',
  `platform_fee` DECIMAL(10,2) DEFAULT NULL,
  `seller_earnings` DECIMAL(10,2) DEFAULT NULL,
  `status` ENUM('pending', 'completed', 'refunded', 'failed') DEFAULT 'pending',
  `refund_reason` TEXT DEFAULT NULL,
  `download_url` VARCHAR(500) DEFAULT NULL,
  `download_expires_at` DATETIME DEFAULT NULL,
  `download_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_buyer_id` (`buyer_id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_marketplace_item_id` (`marketplace_item_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_purchases_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_purchases_seller` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_purchases_marketplace` FOREIGN KEY (`marketplace_item_id`) REFERENCES `marketplace` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ANALYTICS TABLE
-- =====================================================
CREATE TABLE `analytics` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `event_type` VARCHAR(100) NOT NULL,
  `event_data` JSON DEFAULT NULL,
  `page_url` VARCHAR(500) DEFAULT NULL,
  `referrer_url` VARCHAR(500) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `session_id` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_analytics_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- NOTIFICATIONS TABLE
-- =====================================================
CREATE TABLE `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` ENUM('generation_complete', 'export_ready', 'challenge_update', 'purchase', 'collaboration_invite', 'system', 'marketing') NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `data` JSON DEFAULT NULL,
  `action_url` VARCHAR(500) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `read_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COLLABORATIONS TABLE
-- =====================================================
CREATE TABLE `collaborations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT UNSIGNED NOT NULL,
  `owner_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `invite_code` VARCHAR(50) DEFAULT NULL,
  `max_collaborators` INT UNSIGNED DEFAULT 5,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_owner_id` (`owner_id`),
  KEY `idx_invite_code` (`invite_code`),
  CONSTRAINT `fk_collaborations_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_collaborations_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COMMENTS TABLE
-- =====================================================
CREATE TABLE `comments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `collaboration_id` INT UNSIGNED NOT NULL,
  `content` TEXT NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `timestamp_position` DECIMAL(15,3) DEFAULT NULL,
  `is_resolved` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_collaboration_id` (`collaboration_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_comments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comments_collaboration` FOREIGN KEY (`collaboration_id`) REFERENCES `collaborations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comments_parent` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CURRENCIES TABLE
-- =====================================================
CREATE TABLE `currencies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(3) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `symbol` VARCHAR(10) NOT NULL,
  `exchange_rate` DECIMAL(10,6) DEFAULT 1.000000,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default currencies
INSERT INTO `currencies` (`code`, `name`, `symbol`, `exchange_rate`) VALUES
('ZMW', 'Zambian Kwacha', 'ZK', 1.000000),
('USD', 'US Dollar', '$', 0.052000),
('EUR', 'Euro', '€', 0.048000),
('GBP', 'British Pound', '£', 0.041000),
('XOF', 'West African CFA Franc', 'CFA', 32.000000),
('XAF', 'Central African CFA Franc', 'CFA', 32.000000),
('NGN', 'Nigerian Naira', '₦', 38.000000),
('GHS', 'Ghanaian Cedi', 'GH₵', 0.320000),
('KES', 'Kenyan Shilling', 'KSh', 6.800000),
('UGX', 'Ugandan Shilling', 'USh', 195.000000),
('TZS', 'Tanzanian Shilling', 'TSh', 135.000000),
('RWF', 'Rwandan Franc', 'RF', 55.000000),
('BIF', 'Burundian Franc', 'FBu', 110.000000),
('CDF', 'Congolese Franc', 'FC', 140.000000),
('AOA', 'Angolan Kwanza', 'Kz', 44.000000),
('MWK', 'Malawian Kwacha', 'MK', 45.000000),
('ZAR', 'South African Rand', 'R', 0.950000),
('BWP', 'Botswanan Pula', 'P', 0.700000),
('NAD', 'Namibian Dollar', 'N$', 0.950000),
('SZL', 'Swazi Lilangeni', 'L', 0.950000),
('LSL', 'Lesotho Loti', 'L', 0.950000),
('MZN', 'Mozambican Metical', 'MT', 3.200000),
('BRL', 'Brazilian Real', 'R$', 0.260000),
('MXN', 'Mexican Peso', '$', 0.950000),
('INR', 'Indian Rupee', '₹', 4.350000),
('PHP', 'Philippine Peso', '₱', 2.900000),
('IDR', 'Indonesian Rupiah', 'Rp', 800.000000),
('MYR', 'Malaysian Ringgit', 'RM', 0.240000),
('SGD', 'Singapore Dollar', 'S$', 0.070000),
('HKD', 'Hong Kong Dollar', 'HK$', 0.410000),
('CNY', 'Chinese Yuan', '¥', 0.380000),
('JPY', 'Japanese Yen', '¥', 7.800000),
('KRW', 'South Korean Won', '₩', 70.000000),
('THB', 'Thai Baht', '฿', 1.850000),
('VND', 'Vietnamese Dong', '₫', 1250.000000),
('AUD', 'Australian Dollar', 'A$', 0.080000),
('NZD', 'New Zealand Dollar', 'NZ$', 0.089000),
('CAD', 'Canadian Dollar', 'C$', 0.071000),
('CHF', 'Swiss Franc', 'CHF', 0.046000),
('SEK', 'Swedish Krona', 'kr', 0.570000),
('NOK', 'Norwegian Krone', 'kr', 0.570000),
('DKK', 'Danish Krone', 'kr', 0.360000),
('PLN', 'Polish Zloty', 'zł', 0.210000),
('CZK', 'Czech Koruna', 'Kč', 1.200000),
('HUF', 'Hungarian Forint', 'Ft', 18.500000),
('RON', 'Romanian Leu', 'lei', 0.240000),
('BGN', 'Bulgarian Lev', 'лв', 0.094000),
('TRY', 'Turkish Lira', '₺', 1.700000),
('RUB', 'Russian Ruble', '₽', 4.900000),
('UAH', 'Ukrainian Hryvnia', '₴', 2.000000),
('ILS', 'Israeli New Shekel', '₪', 0.190000),
('SAR', 'Saudi Riyal', '﷼', 0.200000),
('AED', 'United Arab Emirates Dirham', 'د.إ', 0.200000),
('QAR', 'Qatari Riyal', '﷼', 0.190000),
('KWD', 'Kuwaiti Dinar', 'د.ك', 0.016000),
('BHD', 'Bahraini Dinar', 'BD', 0.020000),
('OMR', 'Omani Rial', '﷼', 0.020000),
('EGP', 'Egyptian Pound', 'E£', 1.600000),
('MAD', 'Moroccan Dirham', 'DH', 0.520000),
('TND', 'Tunisian Dinar', 'DT', 0.160000),
('DZD', 'Algerian Dinar', 'DA', 7.000000),
('LYD', 'Libyan Dinar', 'LD', 0.027000),
('SDG', 'Sudanese Pound', 'ج.س', 31.000000),
('ETB', 'Ethiopian Birr', 'Br', 2.900000),
('KES', 'Kenyan Shilling', 'KSh', 6.800000),
('UGX', 'Ugandan Shilling', 'USh', 195.000000),
('TZS', 'Tanzanian Shilling', 'TSh', 135.000000),
('RWF', 'Rwandan Franc', 'RF', 55.000000),
('BIF', 'Burundian Franc', 'FBu', 110.000000),
('CDF', 'Congolese Franc', 'FC', 140.000000),
('GMD', 'Gambian Dalasi', 'D', 3.500000),
('LRD', 'Liberian Dollar', 'L$', 0.009000),
('SLL', 'Sierra Leonean Leone', 'Le', 550.000000),
('SOS', 'Somali Shilling', 'Sh', 30.000000),
('DJF', 'Djiboutian Franc', 'Fdj', 9.300000),
('ERN', 'Eritrean Nakfa', 'Nfk', 0.780000),
('SSP', 'South Sudanese Pound', '£', 0.009000),
('GNF', 'Guinean Franc', 'FG', 460.000000),
('CVE', 'Cape Verdean Escudo', '$', 5.300000),
('STN', 'São Tomé and Príncipe Dobra', 'Db', 0.240000),
('XAF', 'Central African CFA Franc', 'FCFA', 32.000000),
('XOF', 'West African CFA Franc', 'CFA', 32.000000),
('XAF', 'Central African CFA Franc', 'FCFA', 32.000000),
('CDF', 'Congolese Franc', 'FC', 140.000000),
('BGN', 'Bulgarian Lev', 'лв', 0.094000),
('HRK', 'Croatian Kuna', 'kn', 0.360000);

-- =====================================================
-- DEPOSITS TABLE
-- =====================================================
CREATE TABLE `deposits` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `deposit_id` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) NOT NULL,
  `converted_amount` DECIMAL(10,2) DEFAULT NULL,
  `msisdn` VARCHAR(20) NOT NULL,
  `country` VARCHAR(3) NOT NULL,
  `status` ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_deposit_id` (`deposit_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_deposits_user` FOREIGN KEY (`user_id`) REFERENCES `users (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PAYOUTS TABLE
-- =====================================================
CREATE TABLE `payouts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `payout_id` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) NOT NULL,
  `msisdn` VARCHAR(20) NOT NULL,
  `country` VARCHAR(3) NOT NULL,
  `correspondent` VARCHAR(100) NOT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
  `failure_reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_payout_id` (`payout_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_payouts_user` FOREIGN KEY (`user_id`) REFERENCES `users (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TRANSACTIONS TABLE
-- =====================================================
CREATE TABLE `transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` ENUM('deposit', 'withdrawal', 'subscription', 'marketplace_purchase', 'marketplace_sale', 'refund') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'ZMW',
  `description` TEXT NOT NULL,
  `reference_id` VARCHAR(255) DEFAULT NULL,
  `balance_after` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_reference_id` (`reference_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INDEXES FOR PERFORMANCE
-- =====================================================

-- Composite indexes for common queries
CREATE INDEX `idx_generations_user_type_status` ON `generations` (`user_id`, `generation_type`, `status`);
CREATE INDEX `idx_projects_user_status` ON `projects` (`user_id`, `status`);
CREATE INDEX `idx_midi_files_user_type` ON `midi_files` (`user_id`, `type`);
CREATE INDEX `idx_idea_vault_user_favorite` ON `idea_vault` (`user_id`, `is_favorite`);
CREATE INDEX `idx_notifications_user_read` ON `notifications` (`user_id`, `is_read`);
CREATE INDEX `idx_analytics_user_created` ON `analytics` (`user_id`, `created_at`);

COMMIT;