CRM_ENVR/Web_Calendar/database/011_calendar_events.sql

CREATE TABLE IF NOT EXISTS scheduled_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  participant_id UUID REFERENCES users(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  event_type VARCHAR(20) NOT NULL DEFAULT 'meeting',
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ,
  duration_minutes INT,
  status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_event_type CHECK (event_type IN ('meeting','call','chat','follow_up','demo','other')),
  CONSTRAINT chk_event_status CHECK (status IN ('scheduled','completed','cancelled','rescheduled'))
);

CREATE INDEX IF NOT EXISTS idx_scheduled_events_user ON scheduled_events(user_id, start_time);
CREATE INDEX IF NOT EXISTS idx_scheduled_events_participant ON scheduled_events(participant_id);
CREATE INDEX IF NOT EXISTS idx_scheduled_events_lead ON scheduled_events(lead_id);
CREATE INDEX IF NOT EXISTS idx_scheduled_events_date ON scheduled_events(start_time);
CREATE INDEX IF NOT EXISTS idx_scheduled_events_status ON scheduled_events(user_id, status);