#!/bin/bash
# Fix event_venues table - ensure events are linked to published templates
#
# Problem: event_venues table can become empty or point to wrong templates
# Solution: Link each event to its venue's published template

source "$(dirname "$0")/_db-helper.sh"

echo "=== Fixing event_venues links ==="

# Get events with venues that have published templates
run_mysql_query "
-- Show current state
SELECT 'Current event_venues state:' as info;
SELECT ev.*, vt.status as template_status
FROM event_venues ev
LEFT JOIN venue_templates vt ON ev.venue_template_id = vt.id;

-- Find events that need linking (have venue_id but no event_venues entry)
SELECT 'Events needing links:' as info;
SELECT e.id as event_id, e.name, e.venue_id,
       (SELECT id FROM venue_templates WHERE venue_id = e.venue_id AND status = 'published' LIMIT 1) as published_template_id
FROM events e
WHERE e.venue_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM event_venues ev WHERE ev.event_id = e.id);
"

read -p "Fix missing links? (y/n): " confirm
if [[ "$confirm" != "y" ]]; then
    echo "Aborted."
    exit 0
fi

# Insert missing links
run_mysql_query "
INSERT INTO event_venues (event_id, venue_template_id, is_active, created_at, updated_at)
SELECT
    e.id as event_id,
    vt.id as venue_template_id,
    1 as is_active,
    NOW() as created_at,
    NOW() as updated_at
FROM events e
JOIN venue_templates vt ON vt.venue_id = e.venue_id AND vt.status = 'published'
WHERE e.venue_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM event_venues ev WHERE ev.event_id = e.id);

SELECT 'Updated event_venues:' as info;
SELECT * FROM event_venues;
"

echo "=== Done ==="