-- ========================================
-- FIX SUPPORT TICKETS BADGE QUERY
-- Corrects table name from support_tickets to crm_support_tickets
-- ========================================

-- Support Tickets - Show open tickets (CORRECTED TABLE NAME)
-- Warning (yellow) when ANY open tickets, becomes critical at 10+
UPDATE menu_items 
SET badge_query = 'SELECT COUNT(*) as count FROM crm_support_tickets WHERE status NOT IN (''closed'', ''resolved'')',
    urgent_threshold = 0,
    urgent_color = 'warning',
    badge_color = 'warning'
WHERE url = 'crm/support' OR url = 'helpdesk/tickets' OR label = 'Tickets' OR label = 'Support';

-- Help Desk Dashboard - Show open tickets (CORRECTED TABLE NAME)
UPDATE menu_items 
SET badge_query = 'SELECT COUNT(*) as count FROM crm_support_tickets WHERE status NOT IN (''closed'', ''resolved'')'
WHERE url = 'helpdesk' OR url = 'helpdesk/dashboard' OR label = 'Help Desk';

-- Help Desk Tickets page specifically
UPDATE menu_items 
SET badge_query = 'SELECT COUNT(*) as count FROM crm_support_tickets WHERE status NOT IN (''closed'', ''resolved'')',
    urgent_threshold = 0,
    urgent_color = 'warning',
    badge_color = 'warning'
WHERE url = 'helpdesk/tickets';

-- My Tickets (Help Desk) - Show user's open tickets (CORRECTED TABLE NAME)
UPDATE menu_items 
SET badge_query = 'SELECT COUNT(*) as count FROM crm_support_tickets WHERE created_by = {user_id} AND status NOT IN (''closed'', ''resolved'')',
    urgent_threshold = 0,
    urgent_color = 'danger',
    badge_color = 'danger'
WHERE url = 'helpdesk/my-tickets' OR label = 'My Tickets';