-- ============================================================================
-- EduAdvise CRM — Phase 2: Lead pipeline
-- lead-management masters, leads, assignments, follow-ups, notes
-- ============================================================================

-- ---------------------------------------------------------------------------
-- Lead-management masters (simple name + active; branch_id null = global)
-- ---------------------------------------------------------------------------
create table public.inquiries (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.exams (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.degrees (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.followup_types (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.lead_sources (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.interested_courses (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  is_active boolean not null default true,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Leads
-- ---------------------------------------------------------------------------
create table public.leads (
  id                  uuid primary key default gen_random_uuid(),
  branch_id           uuid not null references public.branches(id),
  full_name           text not null,
  email               text,
  phone               text,
  alternate_contact   text,
  country             text,
  city                text,
  gender              text,
  date_of_birth       date,
  age                 int,
  lead_status         text not null default 'New',  -- New | Contacted | Interested | Follow-up | Converted | Lost
  lead_source_id      uuid references public.lead_sources(id),
  inquiry_id          uuid references public.inquiries(id),
  interested_course_id uuid references public.interested_courses(id),
  preferred_country   text,
  other_service       text,
  source_of_reference text,
  office_use_only     text,
  comments            text,
  remarks             text,
  created_by          uuid references public.users(id),
  updated_by          uuid references public.users(id),
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);
create index leads_branch_idx on public.leads (branch_id);
create index leads_status_idx on public.leads (lead_status);
create index leads_created_idx on public.leads (created_at desc);

-- Assignment of a lead to one or more users (counselor, etc.)
create table public.lead_assignments (
  lead_id     uuid not null references public.leads(id) on delete cascade,
  user_id     uuid not null references public.users(id) on delete cascade,
  assigned_by uuid references public.users(id),
  assigned_at timestamptz not null default now(),
  primary key (lead_id, user_id)
);
create index lead_assignments_user_idx on public.lead_assignments (user_id);

-- Follow-ups
create table public.followups (
  id               uuid primary key default gen_random_uuid(),
  lead_id          uuid not null references public.leads(id) on delete cascade,
  branch_id        uuid not null references public.branches(id),
  next_date        date,
  from_time        time,
  to_time          time,
  followup_type_id uuid references public.followup_types(id),
  status           text not null default 'pending', -- pending | done | missed
  notes            text,
  outcome          text,
  created_by       uuid references public.users(id),
  created_at       timestamptz not null default now(),
  completed_at     timestamptz
);
create index followups_lead_idx on public.followups (lead_id);
create index followups_next_date_idx on public.followups (next_date);
create index followups_branch_idx on public.followups (branch_id);

-- Free-text notes (timeline)
create table public.lead_notes (
  id         uuid primary key default gen_random_uuid(),
  lead_id    uuid not null references public.leads(id) on delete cascade,
  body       text not null,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);
create index lead_notes_lead_idx on public.lead_notes (lead_id);

-- updated_at triggers
create trigger trg_inquiries_updated before update on public.inquiries
  for each row execute function public.set_updated_at();
create trigger trg_exams_updated before update on public.exams
  for each row execute function public.set_updated_at();
create trigger trg_degrees_updated before update on public.degrees
  for each row execute function public.set_updated_at();
create trigger trg_followup_types_updated before update on public.followup_types
  for each row execute function public.set_updated_at();
create trigger trg_lead_sources_updated before update on public.lead_sources
  for each row execute function public.set_updated_at();
create trigger trg_interested_courses_updated before update on public.interested_courses
  for each row execute function public.set_updated_at();
create trigger trg_leads_updated before update on public.leads
  for each row execute function public.set_updated_at();