-- =============================================
-- Dater App Database Schema - Updated
-- =============================================

-- Create database (run this separately if needed)
-- CREATE DATABASE dater CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE dater;

-- =============================================
-- Users Table
-- =============================================
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `gender` ENUM('male', 'female', 'other') DEFAULT NULL,
    `birth_date` DATE DEFAULT NULL,
    `bio` TEXT DEFAULT NULL,
    `profile_image` VARCHAR(500) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `remember_token` VARCHAR(100) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_email` (`email`),
    INDEX `idx_gender` (`gender`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- User Profile Details
-- =============================================
CREATE TABLE IF NOT EXISTS `user_profile_details` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL UNIQUE,
    `height` INT DEFAULT NULL COMMENT 'Height in cm',
    `body_type` ENUM('slim', 'athletic', 'average', 'curvy', 'muscular', 'plus_size') DEFAULT NULL,
    `hair_color` ENUM('black', 'brown', 'blonde', 'red', 'gray', 'bald', 'other') DEFAULT NULL,
    `eye_color` ENUM('brown', 'blue', 'green', 'hazel', 'gray', 'other') DEFAULT NULL,
    `occupation` VARCHAR(100) DEFAULT NULL,
    `education` VARCHAR(100) DEFAULT NULL,
    `location` VARCHAR(255) DEFAULT NULL,
    `relationship_status` ENUM('single', 'divorced', 'widowed', 'separated') DEFAULT NULL,
    `children` ENUM('none', 'yes_no_living_with', 'yes_not_living_with') DEFAULT NULL,
    `smoking` ENUM('never', 'occasionally', 'regularly') DEFAULT NULL,
    `drinking` ENUM('never', 'occasionally', 'regularly') DEFAULT NULL,
    `exercise` ENUM('never', 'occasionally', 'regularly', 'daily') DEFAULT NULL,
    `zodiac_sign` VARCHAR(50) DEFAULT NULL,
    `personality_type` VARCHAR(50) DEFAULT NULL,
    `spoken_languages` TEXT DEFAULT NULL COMMENT 'Comma separated languages',
    `about_me` TEXT DEFAULT NULL,
    `looking_for` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- User Preferences Table
-- =============================================
CREATE TABLE IF NOT EXISTS `user_preferences` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `show_men` TINYINT(1) DEFAULT 1,
    `show_women` TINYINT(1) DEFAULT 1,
    `show_other` TINYINT(1) DEFAULT 1,
    `min_age` INT DEFAULT 18,
    `max_age` INT DEFAULT 50,
    `min_height` INT DEFAULT NULL COMMENT 'Min height in cm',
    `max_height` INT DEFAULT NULL COMMENT 'Max height in cm',
    `max_distance` INT DEFAULT 50 COMMENT 'Distance in km',
    `latitude` DECIMAL(10, 8) DEFAULT NULL,
    `longitude` DECIMAL(11, 8) DEFAULT NULL,
    `travel_mode` TINYINT(1) DEFAULT 0 COMMENT 'Show people from anywhere',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- User Boosts
-- =============================================
CREATE TABLE IF NOT EXISTS `user_boosts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `boost_type` ENUM('super_like', 'fast_add', 'travel_mode', 'boost', 'see_likes') NOT NULL,
    `quantity` INT DEFAULT 1,
    `expires_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_boosts` (`user_id`, `boost_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Boost Packages (for donations)
-- =============================================
CREATE TABLE IF NOT EXISTS `boost_packages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `boost_type` ENUM('super_like', 'fast_add', 'travel_mode', 'boost', 'see_likes') NOT NULL,
    `quantity` INT NOT NULL,
    `price` DECIMAL(10, 2) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default boost packages
INSERT IGNORE INTO `boost_packages` (`name`, `description`, `boost_type`, `quantity`, `price`) VALUES
('Super Like Pack 5', '5 Super Likes to stand out', 'super_like', 5, 4.99),
('Super Like Pack 25', '25 Super Likes - Best Value!', 'super_like', 25, 14.99),
('Super Like Pack 60', '60 Super Likes', 'super_like', 60, 24.99),
('Fast Add 1 Hour', 'Be shown to new people for 1 hour', 'fast_add', 1, 6.99),
('Fast Add 24 Hours', 'Be shown to new people for 24 hours', 'fast_add', 24, 19.99),
('Travel Mode 1 Week', 'See people from anywhere for 1 week', 'travel_mode', 7, 9.99),
('Travel Mode 1 Month', 'See people from anywhere for 1 month', 'travel_mode', 30, 24.99),
('Boost 1 Time', 'Boost your profile once', 'boost', 1, 7.99),
('Boost 5 Times', 'Boost your profile 5 times', 'boost', 5, 29.99),
('See Who Likes You Pack', 'See who likes you - 100 views', 'see_likes', 100, 14.99),
('See Who Likes You Unlimited', 'See who likes you for 1 month', 'see_likes', 1000, 34.99);

-- =============================================
-- Swipes Table (Track user swipes)
-- =============================================
CREATE TABLE IF NOT EXISTS `swipes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `swiped_user_id` INT UNSIGNED NOT NULL,
    `type` ENUM('like', 'dislike', 'super_like') NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`swiped_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE `idx_swipe_unique` (`user_id`, `swiped_user_id`),
    INDEX `idx_swiped_user` (`swiped_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Matches Table
-- =============================================
CREATE TABLE IF NOT EXISTS `matches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `matched_user_id` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`matched_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE `idx_match_unique` (`user_id`, `matched_user_id`),
    INDEX `idx_users` (`user_id`, `matched_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Messages Table
-- =============================================
CREATE TABLE IF NOT EXISTS `messages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `match_id` INT UNSIGNED NOT NULL,
    `sender_id` INT UNSIGNED NOT NULL,
    `receiver_id` INT UNSIGNED NOT NULL,
    `message` TEXT NOT NULL,
    `is_read` TINYINT(1) DEFAULT 0,
    `read_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`match_id`) REFERENCES `matches`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`receiver_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_match_id` (`match_id`),
    INDEX `idx_sender` (`sender_id`),
    INDEX `idx_receiver` (`receiver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- User Photos Table
-- =============================================
CREATE TABLE IF NOT EXISTS `user_photos` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `photo_url` VARCHAR(500) NOT NULL,
    `is_primary` TINYINT(1) DEFAULT 0,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_photos` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Interests/Tags Table
-- =============================================
CREATE TABLE IF NOT EXISTS `interests` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `icon` VARCHAR(50) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_interests` (
    `user_id` INT UNSIGNED NOT NULL,
    `interest_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`, `interest_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`interest_id`) REFERENCES `interests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default interests
INSERT IGNORE INTO `interests` (`name`, `icon`) VALUES 
('Travel', '✈️'),
('Music', '🎵'),
('Movies', '🎬'),
('Food', '🍕'),
('Sports', '⚽'),
('Gaming', '🎮'),
('Reading', '📚'),
('Photography', '📷'),
('Art', '🎨'),
('Fitness', '💪'),
('Dancing', '💃'),
('Cooking', '👨‍🍳'),
('Hiking', '🏔️'),
('Coffee', '☕'),
('Wine', '🍷'),
('Yoga', '🧘'),
('Traveling', '🌍'),
('Netflix', '📺'),
('Dogs', '🐕'),
('Cats', '🐱'),
('Nature', '🌿'),
('Fashion', '👗'),
('Tech', '💻'),
('Movies', '🎥'),
('Theater', '🎭');

-- =============================================
-- Donations/Transactions Table
-- =============================================
CREATE TABLE IF NOT EXISTS `donations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `package_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `payment_method` VARCHAR(50) DEFAULT NULL,
    `payment_status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    `transaction_id` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`package_id`) REFERENCES `boost_packages`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
