-- Add WhatsApp link fields to appointments table
-- This enables tracking which WhatsApp conversation created an appointment
-- and allows sending messages back to customers about their appointments

-- Add whatsapp_session_id to link to the WhatsApp session
ALTER TABLE appointments ADD COLUMN IF NOT EXISTS whatsapp_session_id VARCHAR(255);

-- Add whatsapp_chat_id to link to specific WhatsApp conversation
ALTER TABLE appointments ADD COLUMN IF NOT EXISTS whatsapp_chat_id VARCHAR(255);

-- Add source_channel to track where appointment came from (widget, whatsapp, voice, flow)
ALTER TABLE appointments ADD COLUMN IF NOT EXISTS source_channel VARCHAR(50);

-- Create indexes for efficient lookups
CREATE INDEX IF NOT EXISTS idx_appointments_whatsapp_session ON appointments(whatsapp_session_id) WHERE whatsapp_session_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_appointments_whatsapp_chat ON appointments(whatsapp_chat_id) WHERE whatsapp_chat_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_appointments_source_channel ON appointments(source_channel) WHERE source_channel IS NOT NULL;

-- Add comments for documentation
COMMENT ON COLUMN appointments.whatsapp_session_id IS 'WhatsApp session ID that created this appointment';
COMMENT ON COLUMN appointments.whatsapp_chat_id IS 'WhatsApp chat ID where appointment was discussed';
COMMENT ON COLUMN appointments.source_channel IS 'Channel where appointment originated: widget, whatsapp, voice, or flow';