-- ============================================================================
-- EduAdvise CRM — Phase 3: Course catalogue
-- countries, states, institutes, campuses, course masters, courses, shortlist
-- ============================================================================

create extension if not exists pg_trgm;

-- ---- Simple name+active masters -------------------------------------------
do $$
declare t text;
begin
  foreach t in array array[
    'program_levels','qualifications','streams','requirements','course_tags','intakes'
  ]
  loop
    execute format($f$
      create table public.%I (
        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()
      )$f$, t);
  end loop;
end $$;

-- ---- Geography ------------------------------------------------------------
create table public.countries (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  code text,
  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.states (
  id uuid primary key default gen_random_uuid(),
  country_id uuid references public.countries(id) on delete cascade,
  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 index states_country_idx on public.states (country_id);

-- ---- Institutes & campuses ------------------------------------------------
create table public.institutes (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  country_id uuid references public.countries(id),
  state_id uuid references public.states(id),
  city text,
  logo_url text,
  is_direct boolean not null default false,  -- "Direct Institute" in EduCa
  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 index institutes_country_idx on public.institutes (country_id);

create table public.campuses (
  id uuid primary key default gen_random_uuid(),
  institute_id uuid references public.institutes(id) on delete cascade,
  name text not null,
  city text,
  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 index campuses_institute_idx on public.campuses (institute_id);

-- ---- Courses (the catalog) ------------------------------------------------
create table public.courses (
  id                 uuid primary key default gen_random_uuid(),
  title              text not null,
  institute_id       uuid references public.institutes(id) on delete cascade,
  campus_id          uuid references public.campuses(id) on delete set null,
  country            text,              -- denormalized for fast filtering/import
  state              text,
  program_level_id   uuid references public.program_levels(id),
  qualification_id   uuid references public.qualifications(id),
  stream_id          uuid references public.streams(id),
  level              text,              -- denormalized label (Bachelor/Master/...)
  application_fee    numeric,
  yearly_tuition_fee numeric,
  currency           text default 'GBP',
  duration_months    int,
  duration_label     text,
  intake_months      text,              -- e.g. "Sep, Jan"
  intake_year        int,
  requirements       text,
  tags               text[] not null default '{}',
  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 index courses_institute_idx on public.courses (institute_id);
create index courses_country_idx on public.courses (country);
create index courses_level_idx on public.courses (level);
create index courses_title_trgm_idx on public.courses using gin (title gin_trgm_ops);

-- ---- Apply Now: shortlist a course against a lead -------------------------
create table public.course_shortlist (
  id         uuid primary key default gen_random_uuid(),
  lead_id    uuid not null references public.leads(id) on delete cascade,
  course_id  uuid not null references public.courses(id) on delete cascade,
  status     text not null default 'shortlisted',
  added_by   uuid references public.users(id),
  created_at timestamptz not null default now(),
  unique (lead_id, course_id)
);
create index course_shortlist_lead_idx on public.course_shortlist (lead_id);

-- ---- updated_at triggers --------------------------------------------------
do $$
declare t text;
begin
  foreach t in array array[
    'program_levels','qualifications','streams','requirements','course_tags',
    'intakes','countries','states','institutes','campuses','courses'
  ]
  loop
    execute format('create trigger trg_%1$s_updated before update on public.%1$I
      for each row execute function public.set_updated_at()', t);
  end loop;
end $$;