-- Timezone Awareness and Presence Tracking Migration
-- Add timezone and presence tracking columns to users table

ALTER TABLE users 
ADD COLUMN timezone VARCHAR(100) DEFAULT 'UTC' COMMENT 'User timezone (e.g., America/New_York)',
ADD COLUMN work_start_time TIME DEFAULT '09:00:00' COMMENT 'User preferred work start time',
ADD COLUMN work_end_time TIME DEFAULT '17:00:00' COMMENT 'User preferred work end time',
ADD COLUMN last_slack_activity TIMESTAMP NULL COMMENT 'Last time user was active on Slack',
ADD COLUMN slack_status VARCHAR(50) DEFAULT 'offline' COMMENT 'Current Slack status',
ADD COLUMN morning_greeting_enabled BOOLEAN DEFAULT TRUE COMMENT 'Whether user wants morning greetings',
ADD COLUMN last_morning_greeting DATE NULL COMMENT 'Last date morning greeting was sent',
ADD COLUMN preferred_morning_time TIME DEFAULT '09:00:00' COMMENT 'Preferred time for morning greeting';

-- Create user daily schedules table for detailed time management
CREATE TABLE user_daily_schedules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    day_of_week ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday') NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    is_working_day BOOLEAN DEFAULT TRUE,
    break_times JSON NULL COMMENT 'Array of break time objects {start, end}',
    focus_hours JSON NULL COMMENT 'Array of deep focus time slots',
    meeting_preference ENUM('morning', 'afternoon', 'anytime', 'never') DEFAULT 'anytime',
    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 KEY unique_user_day (user_id, day_of_week)
);

-- Create timezone activity log for tracking patterns
CREATE TABLE user_activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type ENUM('slack_online', 'slack_offline', 'task_completed', 'login', 'logout') NOT NULL,
    activity_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_local_time TIME NOT NULL COMMENT 'Activity time in user timezone',
    user_timezone VARCHAR(100) NOT NULL,
    metadata JSON NULL COMMENT 'Additional activity context',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_activity (user_id, activity_timestamp),
    INDEX idx_activity_type (activity_type)
);

-- Create morning greeting templates
CREATE TABLE morning_greeting_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    greeting_text TEXT NOT NULL,
    conditions JSON NULL COMMENT 'Conditions for when to use this template',
    is_active BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 0 COMMENT 'Higher priority templates used first',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default morning greeting templates
INSERT INTO morning_greeting_templates (name, greeting_text, conditions, priority) VALUES
('Default Morning', 'Good morning! 🌅 Ready to tackle today''s challenges?', '{"days": ["monday", "tuesday", "wednesday", "thursday", "friday"]}', 1),
('Monday Motivation', 'Happy Monday! 💪 Let''s start this week strong! Here''s what I have planned for you:', '{"days": ["monday"]}', 10),
('Friday Energy', 'Happy Friday! 🎉 Let''s finish this week on a high note!', '{"days": ["friday"]}', 10),
('Heavy Workload', 'Good morning! I see you have a busy day ahead. Let''s prioritize your most important tasks:', '{"conditions": ["heavy_workload"]}', 8),
('Light Day', 'Good morning! 😊 You have a lighter schedule today - perfect time for some strategic work!', '{"conditions": ["light_workload"]}', 7),
('Catch Up', 'Good morning! I noticed you''ve been away. Let me help you catch up with what''s important:', '{"conditions": ["returning_user"]}', 9);

-- Create intelligent task scheduling table
CREATE TABLE intelligent_task_schedule (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    task_id INT NOT NULL,
    scheduled_date DATE NOT NULL,
    scheduled_start_time TIME NOT NULL,
    scheduled_end_time TIME NOT NULL,
    estimated_duration_minutes INT NOT NULL,
    priority_score DECIMAL(5,2) DEFAULT 0.00,
    energy_level_required ENUM('high', 'medium', 'low') DEFAULT 'medium',
    task_type ENUM('creative', 'analytical', 'administrative', 'collaborative') DEFAULT 'administrative',
    auto_scheduled BOOLEAN DEFAULT FALSE,
    user_confirmed BOOLEAN DEFAULT FALSE,
    completed BOOLEAN DEFAULT FALSE,
    actual_start_time TIMESTAMP NULL,
    actual_end_time TIMESTAMP 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,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_task_date (user_id, task_id, scheduled_date),
    INDEX idx_user_schedule (user_id, scheduled_date),
    INDEX idx_task_schedule (task_id, scheduled_date)
);

-- Create team timezone coordination table
CREATE TABLE team_timezone_coordination (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_name VARCHAR(100) NOT NULL,
    optimal_meeting_start TIME NOT NULL COMMENT 'Best time for team meetings in UTC',
    optimal_meeting_end TIME NOT NULL COMMENT 'Latest acceptable meeting time in UTC',
    overlap_hours_start TIME NOT NULL COMMENT 'When most team members are online',
    overlap_hours_end TIME NOT NULL COMMENT 'When overlap period ends',
    timezone_count INT DEFAULT 0 COMMENT 'Number of different timezones in team',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create table to track team member timezone overlaps
CREATE TABLE team_member_overlaps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    overlap_start_utc TIME NOT NULL,
    overlap_end_utc TIME NOT NULL,
    overlap_duration_hours DECIMAL(4,2) NOT NULL,
    last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_pair (user1_id, user2_id),
    INDEX idx_overlap_duration (overlap_duration_hours)
);