-- Migration: Add external dating profile links to undatable_profiles table
-- Version: v3.1.0
-- Date: 2024-01-15

-- Add columns for external dating profile links
ALTER TABLE undatable_profiles 
ADD COLUMN external_dating_profiles JSON DEFAULT NULL COMMENT 'JSON object containing external dating site profile URLs',
ADD COLUMN match_com_profile VARCHAR(500) DEFAULT NULL COMMENT 'Match.com profile URL or username',
ADD COLUMN okcupid_profile VARCHAR(500) DEFAULT NULL COMMENT 'OkCupid profile URL or username', 
ADD COLUMN tinder_profile VARCHAR(500) DEFAULT NULL COMMENT 'Tinder profile information or URL',
ADD COLUMN coffee_meets_bagel_profile VARCHAR(500) DEFAULT NULL COMMENT 'Coffee Meets Bagel profile URL or username',
ADD COLUMN bumble_profile VARCHAR(500) DEFAULT NULL COMMENT 'Bumble profile information',
ADD COLUMN hinge_profile VARCHAR(500) DEFAULT NULL COMMENT 'Hinge profile information',
ADD COLUMN other_dating_profiles JSON DEFAULT NULL COMMENT 'Other dating site profiles as JSON array',
ADD COLUMN profile_verification_status ENUM('unverified', 'pending', 'verified', 'disputed') DEFAULT 'unverified' COMMENT 'Verification status of external profiles',
ADD COLUMN last_profile_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last time external profiles were updated';

-- Add indexes for better query performance
CREATE INDEX idx_match_profile ON undatable_profiles(match_com_profile);
CREATE INDEX idx_okcupid_profile ON undatable_profiles(okcupid_profile); 
CREATE INDEX idx_tinder_profile ON undatable_profiles(tinder_profile);
CREATE INDEX idx_coffee_meets_bagel_profile ON undatable_profiles(coffee_meets_bagel_profile);
CREATE INDEX idx_bumble_profile ON undatable_profiles(bumble_profile);
CREATE INDEX idx_hinge_profile ON undatable_profiles(hinge_profile);
CREATE INDEX idx_profile_verification ON undatable_profiles(profile_verification_status);
CREATE INDEX idx_profile_update ON undatable_profiles(last_profile_update);

-- Create audit table for tracking external profile changes
CREATE TABLE external_profile_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_id INT NOT NULL,
    changed_by INT NOT NULL,
    change_type ENUM('created', 'updated', 'deleted', 'verified', 'disputed') NOT NULL,
    field_name VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profile_id) REFERENCES undatable_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_profile_audit (profile_id),
    INDEX idx_changed_by (changed_by),
    INDEX idx_change_type (change_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create table for tracking external profile verification attempts
CREATE TABLE profile_verification_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_id INT NOT NULL,
    platform VARCHAR(50) NOT NULL,
    verification_method ENUM('url_check', 'screenshot', 'manual_review', 'user_report') NOT NULL,
    attempt_result ENUM('success', 'failed', 'inconclusive', 'invalid') NOT NULL,
    verification_data JSON,
    verified_by INT,
    verification_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profile_id) REFERENCES undatable_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_profile_platform (profile_id, platform),
    INDEX idx_verification_result (attempt_result),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update the profile_stats view to include external profile information
DROP VIEW IF EXISTS profile_stats;
CREATE VIEW profile_stats AS
SELECT 
    p.id,
    p.first_name,
    p.last_name,
    p.email,
    p.phone,
    p.location,
    p.borough,
    p.match_com_profile,
    p.okcupid_profile,
    p.tinder_profile,
    p.coffee_meets_bagel_profile,
    p.bumble_profile,
    p.hinge_profile,
    p.other_dating_profiles,
    p.profile_verification_status,
    COALESCE(ROUND(AVG(pr.rating), 1), 0) as avg_undatable_score,
    COUNT(pr.rating) as total_ratings,
    COUNT(CASE WHEN v.vote_type = 'up' THEN 1 END) as upvotes,
    COUNT(CASE WHEN v.vote_type = 'down' THEN 1 END) as downvotes,
    COALESCE(ROUND(AVG(CASE WHEN pr.category = 'ghosting' THEN pr.rating END), 1), 0) as ghosting_score,
    COALESCE(ROUND(AVG(CASE WHEN pr.category = 'catfishing' THEN pr.rating END), 1), 0) as catfishing_score,
    COALESCE(ROUND(AVG(CASE WHEN pr.category = 'inappropriate_behavior' THEN pr.rating END), 1), 0) as inappropriate_score,
    COALESCE(ROUND(AVG(CASE WHEN pr.category = 'cheating' THEN pr.rating END), 1), 0) as cheating_score,
    COALESCE(ROUND(AVG(CASE WHEN pr.category = 'abusive' THEN pr.rating END), 1), 0) as abusive_score,
    -- Count how many external profiles are linked
    (CASE WHEN p.match_com_profile IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN p.okcupid_profile IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN p.tinder_profile IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN p.coffee_meets_bagel_profile IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN p.bumble_profile IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN p.hinge_profile IS NOT NULL THEN 1 ELSE 0 END) as external_profile_count
FROM undatable_profiles p
LEFT JOIN profile_ratings pr ON p.id = pr.profile_id
LEFT JOIN votes v ON p.id = v.profile_id
GROUP BY p.id, p.first_name, p.last_name, p.email, p.phone, p.location, p.borough,
         p.match_com_profile, p.okcupid_profile, p.tinder_profile, 
         p.coffee_meets_bagel_profile, p.bumble_profile, p.hinge_profile,
         p.other_dating_profiles, p.profile_verification_status;

-- Add triggers for audit logging
DELIMITER $$

CREATE TRIGGER external_profile_update_audit 
AFTER UPDATE ON undatable_profiles
FOR EACH ROW
BEGIN
    -- Track changes to external dating profiles
    IF OLD.match_com_profile != NEW.match_com_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'match_com_profile', OLD.match_com_profile, NEW.match_com_profile);
    END IF;
    
    IF OLD.okcupid_profile != NEW.okcupid_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'okcupid_profile', OLD.okcupid_profile, NEW.okcupid_profile);
    END IF;
    
    IF OLD.tinder_profile != NEW.tinder_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'tinder_profile', OLD.tinder_profile, NEW.tinder_profile);
    END IF;
    
    IF OLD.coffee_meets_bagel_profile != NEW.coffee_meets_bagel_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'coffee_meets_bagel_profile', OLD.coffee_meets_bagel_profile, NEW.coffee_meets_bagel_profile);
    END IF;
    
    IF OLD.bumble_profile != NEW.bumble_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'bumble_profile', OLD.bumble_profile, NEW.bumble_profile);
    END IF;
    
    IF OLD.hinge_profile != NEW.hinge_profile THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'hinge_profile', OLD.hinge_profile, NEW.hinge_profile);
    END IF;
    
    IF OLD.profile_verification_status != NEW.profile_verification_status THEN
        INSERT INTO external_profile_audit (profile_id, changed_by, change_type, field_name, old_value, new_value)
        VALUES (NEW.id, @current_user_id, 'updated', 'profile_verification_status', OLD.profile_verification_status, NEW.profile_verification_status);
    END IF;
END$$

DELIMITER ;

-- Insert sample data for testing (optional)
-- UPDATE: Comment out for production
/*
UPDATE undatable_profiles SET 
    match_com_profile = 'https://match.com/profile/sample123',
    okcupid_profile = 'https://okcupid.com/profile/sampleuser',
    tinder_profile = 'Tinder: @sampleuser',
    coffee_meets_bagel_profile = 'https://coffeemeetsbagel.com/profile/sample',
    profile_verification_status = 'pending'
WHERE id = 1;
*/
