-- ============================================
-- Dating App API v2 Migration
-- Run this against dating_app_db
-- ============================================

-- Add missing columns to users table
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `firebase_uid` varchar(128) DEFAULT NULL AFTER `phone`,
  ADD COLUMN IF NOT EXISTS `status` enum('active','banned','suspended','deactivated') DEFAULT 'active' AFTER `is_active`;

-- Add missing columns to matches table
ALTER TABLE `matches`
  ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `last_message_at`,
  ADD COLUMN IF NOT EXISTS `unmatched_by` int(11) DEFAULT NULL AFTER `is_active`;

-- Add missing columns to likes table  
ALTER TABLE `likes`
  ADD COLUMN IF NOT EXISTS `is_notified` tinyint(1) DEFAULT 0 AFTER `action`;

-- Swipe limits tracking
CREATE TABLE IF NOT EXISTS `daily_limits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `limit_type` enum('swipe','superlike','rewind','boost') NOT NULL,
  `count` int(11) DEFAULT 0,
  `limit_date` date NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_limit_date` (`user_id`,`limit_type`,`limit_date`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `daily_limits_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Profile boosts
CREATE TABLE IF NOT EXISTS `boosts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `started_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `boosts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Profile verification requests
CREATE TABLE IF NOT EXISTS `verification_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `selfie_url` varchar(500) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `rejection_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `verification_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_likes_mutual ON `likes` (`target_user_id`, `user_id`, `action`);
CREATE INDEX IF NOT EXISTS idx_messages_unread ON `messages` (`match_id`, `sender_id`, `is_read`);
CREATE INDEX IF NOT EXISTS idx_users_active ON `users` (`is_active`, `is_verified`);
CREATE INDEX IF NOT EXISTS idx_profiles_discovery ON `user_profiles` (`gender`, `age`, `pref_gender`);