-- Migration: Align Leads, Viewing Appointments, and Lead Activities with Dynamic CRM Pipeline
-- Date: 2026-09-04

-- 1. LEADS TABLE
ALTER TABLE public.leads ADD COLUMN IF NOT EXISTS client_name VARCHAR(255);
ALTER TABLE public.leads ADD COLUMN IF NOT EXISTS client_email VARCHAR(255);
ALTER TABLE public.leads ADD COLUMN IF NOT EXISTS client_phone VARCHAR(50);
ALTER TABLE public.leads ADD COLUMN IF NOT EXISTS inquiry_type VARCHAR(50) DEFAULT 'GENERAL_INQUIRY';
ALTER TABLE public.leads ADD COLUMN IF NOT EXISTS preferred_viewing_date TIMESTAMPTZ;
ALTER TABLE public.leads ALTER COLUMN phone_number DROP NOT NULL;


-- Backfill existing leads
UPDATE public.leads SET client_name = full_name WHERE client_name IS NULL AND full_name IS NOT NULL;
UPDATE public.leads SET client_email = email WHERE client_email IS NULL AND email IS NOT NULL;
UPDATE public.leads SET client_phone = phone_number WHERE client_phone IS NULL AND phone_number IS NOT NULL;

-- Trigger for bidirectional sync between client_* and full_name/email/phone_number
CREATE OR REPLACE FUNCTION sync_lead_client_fields()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.client_name IS NOT NULL AND (NEW.full_name IS NULL OR NEW.full_name = '') THEN
    NEW.full_name := NEW.client_name;
  ELSIF NEW.full_name IS NOT NULL AND (NEW.client_name IS NULL OR NEW.client_name = '') THEN
    NEW.client_name := NEW.full_name;
  END IF;

  IF NEW.client_email IS NOT NULL AND (NEW.email IS NULL OR NEW.email = '') THEN
    NEW.email := NEW.client_email;
  ELSIF NEW.email IS NOT NULL AND (NEW.client_email IS NULL OR NEW.client_email = '') THEN
    NEW.client_email := NEW.email;
  END IF;

  IF NEW.client_phone IS NOT NULL AND (NEW.phone_number IS NULL OR NEW.phone_number = '') THEN
    NEW.phone_number := NEW.client_phone;
  ELSIF NEW.phone_number IS NOT NULL AND (NEW.client_phone IS NULL OR NEW.client_phone = '') THEN
    NEW.client_phone := NEW.phone_number;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_lead_client_fields ON public.leads;
CREATE TRIGGER trg_sync_lead_client_fields
BEFORE INSERT OR UPDATE ON public.leads
FOR EACH ROW EXECUTE FUNCTION sync_lead_client_fields();

-- 2. VIEWING APPOINTMENTS TABLE
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS scheduled_at TIMESTAMPTZ;
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS duration_minutes INTEGER DEFAULT 60;
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS meeting_type VARCHAR(50) DEFAULT 'PHYSICAL';
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS meeting_location TEXT;
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS client_notes TEXT;
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS agent_notes TEXT;
ALTER TABLE public.viewing_appointments ADD COLUMN IF NOT EXISTS cancellation_reason TEXT;

-- Backfill scheduled_at from scheduled_for if scheduled_at is null
UPDATE public.viewing_appointments SET scheduled_at = scheduled_for WHERE scheduled_at IS NULL AND scheduled_for IS NOT NULL;
UPDATE public.viewing_appointments SET scheduled_for = scheduled_at WHERE scheduled_for IS NULL AND scheduled_at IS NOT NULL;

CREATE OR REPLACE FUNCTION sync_viewing_appointment_dates()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.scheduled_at IS NOT NULL AND NEW.scheduled_for IS NULL THEN
    NEW.scheduled_for := NEW.scheduled_at;
  ELSIF NEW.scheduled_for IS NOT NULL AND NEW.scheduled_at IS NULL THEN
    NEW.scheduled_at := NEW.scheduled_for;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_viewing_appointment_dates ON public.viewing_appointments;
CREATE TRIGGER trg_sync_viewing_appointment_dates
BEFORE INSERT OR UPDATE ON public.viewing_appointments
FOR EACH ROW EXECUTE FUNCTION sync_viewing_appointment_dates();

-- 3. LEAD ACTIVITIES TABLE
ALTER TABLE public.lead_activities ADD COLUMN IF NOT EXISTS created_by_user_id UUID REFERENCES public.users(id) ON DELETE SET NULL;
ALTER TABLE public.lead_activities ADD COLUMN IF NOT EXISTS note TEXT;

UPDATE public.lead_activities SET created_by_user_id = author_user_id WHERE created_by_user_id IS NULL AND author_user_id IS NOT NULL;
UPDATE public.lead_activities SET author_user_id = created_by_user_id WHERE author_user_id IS NULL AND created_by_user_id IS NOT NULL;
UPDATE public.lead_activities SET note = content WHERE note IS NULL AND content IS NOT NULL;
UPDATE public.lead_activities SET content = note WHERE content IS NULL AND note IS NOT NULL;

CREATE OR REPLACE FUNCTION sync_lead_activity_fields()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.created_by_user_id IS NOT NULL AND NEW.author_user_id IS NULL THEN
    NEW.author_user_id := NEW.created_by_user_id;
  ELSIF NEW.author_user_id IS NOT NULL AND NEW.created_by_user_id IS NULL THEN
    NEW.created_by_user_id := NEW.author_user_id;
  END IF;

  IF NEW.note IS NOT NULL AND NEW.content IS NULL THEN
    NEW.content := NEW.note;
  ELSIF NEW.content IS NOT NULL AND NEW.note IS NULL THEN
    NEW.note := NEW.content;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_lead_activity_fields ON public.lead_activities;
CREATE TRIGGER trg_sync_lead_activity_fields
BEFORE INSERT OR UPDATE ON public.lead_activities
FOR EACH ROW EXECUTE FUNCTION sync_lead_activity_fields();

-- 4. RLS POLICIES FOR INQUIRIES & VIEWINGS
DROP POLICY IF EXISTS "Public can submit leads" ON public.leads;
CREATE POLICY "Public can submit leads" ON public.leads FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Agents and owners can update their leads" ON public.leads;
CREATE POLICY "Agents and owners can update their leads" ON public.leads FOR UPDATE USING (true) WITH CHECK (true);

ALTER TABLE public.viewing_appointments ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Public can request viewing appointments" ON public.viewing_appointments;
CREATE POLICY "Public can request viewing appointments" ON public.viewing_appointments FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Users can view own appointments" ON public.viewing_appointments;
CREATE POLICY "Users can view own appointments" ON public.viewing_appointments FOR SELECT USING (true);

DROP POLICY IF EXISTS "Agents and owners can update appointments" ON public.viewing_appointments;
CREATE POLICY "Agents and owners can update appointments" ON public.viewing_appointments FOR UPDATE USING (true) WITH CHECK (true);

-- Notify PostgREST schema cache to reload
NOTIFY pgrst, 'reload schema';