-- Fix Timezone Issues for Events
-- Run this in Supabase SQL Editor

-- Step 1: Drop the existing trigger that's causing issues
DROP TRIGGER IF EXISTS update_event_status_trigger ON events;

-- Step 2: Create a new function that properly handles IST timezone
CREATE OR REPLACE FUNCTION update_event_status()
RETURNS TRIGGER AS $$
DECLARE
    event_start TIMESTAMPTZ;
    event_end TIMESTAMPTZ;
    duration_minutes INTEGER;
    current_time_ist TIMESTAMPTZ;
BEGIN
    -- Parse duration to minutes
    IF NEW.duration LIKE '%hour%' THEN
        duration_minutes := (regexp_replace(NEW.duration, '[^0-9]', '', 'g')::INTEGER) * 60;
    ELSIF NEW.duration LIKE '%min%' THEN
        duration_minutes := regexp_replace(NEW.duration, '[^0-9]', '', 'g')::INTEGER;
    ELSE
        duration_minutes := 60; -- Default 1 hour
    END IF;
    
    -- Convert event date and time to IST timestamp
    -- This assumes the event_date and event_time are in IST
    event_start := (NEW.event_date + NEW.event_time)::TIMESTAMP AT TIME ZONE 'Asia/Kolkata';
    event_end := event_start + (duration_minutes || ' minutes')::INTERVAL;
    
    -- Get current time in IST
    current_time_ist := NOW() AT TIME ZONE 'Asia/Kolkata';
    
    -- Update status based on IST time
    IF current_time_ist < event_start THEN
        NEW.status := 'upcoming';
    ELSIF current_time_ist >= event_start AND current_time_ist < event_end THEN
        NEW.status := 'started';
    ELSE
        NEW.status := 'ended';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 3: Recreate the trigger
CREATE TRIGGER update_event_status_trigger
    BEFORE INSERT OR UPDATE ON events
    FOR EACH ROW EXECUTE FUNCTION update_event_status();

-- Step 4: Update existing events to have correct status based on IST time
UPDATE events 
SET status = CASE 
    WHEN (event_date + event_time)::TIMESTAMP AT TIME ZONE 'Asia/Kolkata' > NOW() AT TIME ZONE 'Asia/Kolkata' THEN 'upcoming'
    WHEN (event_date + event_time)::TIMESTAMP AT TIME ZONE 'Asia/Kolkata' + 
         CASE 
             WHEN duration LIKE '%hour%' THEN (regexp_replace(duration, '[^0-9]', '', 'g')::INTEGER * 60)::INTERVAL
             WHEN duration LIKE '%min%' THEN (regexp_replace(duration, '[^0-9]', '', 'g')::INTEGER)::INTERVAL
             ELSE '1 hour'::INTERVAL
         END > NOW() AT TIME ZONE 'Asia/Kolkata' THEN 'started'
    ELSE 'ended'
END,
updated_at = NOW()
WHERE status != 'cancelled';

-- Step 5: Create a function to manually update event status (for testing)
CREATE OR REPLACE FUNCTION force_update_event_status(event_uuid UUID)
RETURNS TEXT AS $$
DECLARE
    event_record RECORD;
    event_start TIMESTAMPTZ;
    event_end TIMESTAMPTZ;
    duration_minutes INTEGER;
    current_time_ist TIMESTAMPTZ;
    new_status TEXT;
BEGIN
    -- Get event details
    SELECT * INTO event_record FROM events WHERE id = event_uuid;
    
    IF NOT FOUND THEN
        RETURN 'Event not found';
    END IF;
    
    -- Parse duration to minutes
    IF event_record.duration LIKE '%hour%' THEN
        duration_minutes := (regexp_replace(event_record.duration, '[^0-9]', '', 'g')::INTEGER) * 60;
    ELSIF event_record.duration LIKE '%min%' THEN
        duration_minutes := regexp_replace(event_record.duration, '[^0-9]', '', 'g')::INTEGER;
    ELSE
        duration_minutes := 60;
    END IF;
    
    -- Calculate times in IST
    event_start := (event_record.event_date + event_record.event_time)::TIMESTAMP AT TIME ZONE 'Asia/Kolkata';
    event_end := event_start + (duration_minutes || ' minutes')::INTERVAL;
    current_time_ist := NOW() AT TIME ZONE 'Asia/Kolkata';
    
    -- Determine status
    IF current_time_ist < event_start THEN
        new_status := 'upcoming';
    ELSIF current_time_ist >= event_start AND current_time_ist < event_end THEN
        new_status := 'started';
    ELSE
        new_status := 'ended';
    END IF;
    
    -- Update the event
    UPDATE events 
    SET status = new_status, updated_at = NOW()
    WHERE id = event_uuid;
    
    RETURN 'Event status updated to: ' || new_status || 
           ' (Event start: ' || event_start || 
           ', Current IST: ' || current_time_ist || 
           ', Event end: ' || event_end || ')';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Step 6: Create a function to get current time in different timezones (for debugging)
CREATE OR REPLACE FUNCTION get_timezone_info()
RETURNS TABLE(timezone_name TEXT, current_time TIMESTAMPTZ) AS $$
BEGIN
    RETURN QUERY
    SELECT 'UTC'::TEXT, NOW() AT TIME ZONE 'UTC'
    UNION ALL
    SELECT 'IST'::TEXT, NOW() AT TIME ZONE 'Asia/Kolkata'
    UNION ALL
    SELECT 'Local'::TEXT, NOW();
END;
$$ LANGUAGE plpgsql;

-- Step 7: Show current timezone information
SELECT * FROM get_timezone_info();

-- Step 8: Show updated events with their status
SELECT 
    id,
    title,
    event_date,
    event_time,
    duration,
    status,
    (event_date + event_time)::TIMESTAMP AT TIME ZONE 'Asia/Kolkata' as event_start_ist,
    NOW() AT TIME ZONE 'Asia/Kolkata' as current_time_ist
FROM events 
ORDER BY created_at DESC;