-- ============================================================================
-- EduAdvise CRM — Phase 4: Applications, Coaching, Loan, Visa, Documents
-- ============================================================================

-- ---- Simple masters (name + active) ---------------------------------------
do $$
declare t text;
begin
  foreach t in array array[
    'application_types','application_statuses','education_loan_statuses',
    'visa_statuses','coaching_levels','coaching_subjects',
    'coaching_requirements','register_for'
  ]
  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 $$;

-- Coaching faculty (richer than a plain master)
create table public.coaching_faculty (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  name text not null,
  email text,
  phone text,
  subject 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()
);

-- ---- Student applications --------------------------------------------------
create table public.student_applications (
  id                  uuid primary key default gen_random_uuid(),
  branch_id           uuid not null references public.branches(id),
  lead_id             uuid references public.leads(id) on delete set null,
  applicant_name      text not null,
  email               text,
  phone               text,
  country             text,
  institute_id        uuid references public.institutes(id),
  campus_id           uuid references public.campuses(id),
  course_id           uuid references public.courses(id),
  intake_month        text,
  intake_year         int,
  application_type_id uuid references public.application_types(id),
  status              text not null default 'Applied',
  counselor_id        uuid references public.users(id),
  offer_received      boolean not null default false,
  offer_date          date,
  tuition_fee         numeric,
  currency            text default 'GBP',
  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 student_applications_branch_idx on public.student_applications (branch_id);
create index student_applications_status_idx on public.student_applications (status);
create index student_applications_lead_idx on public.student_applications (lead_id);

-- Documents attached to a student application (files in Supabase Storage)
create table public.application_documents (
  id          uuid primary key default gen_random_uuid(),
  student_application_id uuid not null references public.student_applications(id) on delete cascade,
  name        text not null,
  doc_type    text,
  file_path   text not null,           -- path within the 'documents' storage bucket
  status      text not null default 'pending',  -- pending | verified | rejected
  uploaded_by uuid references public.users(id),
  verified_by uuid references public.users(id),
  created_at  timestamptz not null default now()
);
create index application_documents_app_idx on public.application_documents (student_application_id);

-- ---- Coaching applications + attendance -----------------------------------
create table public.coaching_applications (
  id              uuid primary key default gen_random_uuid(),
  branch_id       uuid not null references public.branches(id),
  lead_id         uuid references public.leads(id) on delete set null,
  student_name    text not null,
  email           text,
  phone           text,
  subject_id      uuid references public.coaching_subjects(id),
  level_id        uuid references public.coaching_levels(id),
  faculty_id      uuid references public.coaching_faculty(id),
  register_for_id uuid references public.register_for(id),
  status          text not null default 'Active',
  fees            numeric,
  start_date      date,
  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 coaching_applications_branch_idx on public.coaching_applications (branch_id);

create table public.coaching_attendance (
  id            uuid primary key default gen_random_uuid(),
  coaching_application_id uuid not null references public.coaching_applications(id) on delete cascade,
  date          date not null,
  status        text not null default 'present',  -- present | absent
  marked_by     uuid references public.users(id),
  created_at    timestamptz not null default now(),
  unique (coaching_application_id, date)
);
create index coaching_attendance_app_idx on public.coaching_attendance (coaching_application_id);

-- ---- Education loan inquiries ----------------------------------------------
create table public.education_loan_inquiries (
  id              uuid primary key default gen_random_uuid(),
  branch_id       uuid not null references public.branches(id),
  lead_id         uuid references public.leads(id) on delete set null,
  applicant_name  text not null,
  bank_name       text,
  amount          numeric,
  currency        text default 'INR',
  status_id       uuid references public.education_loan_statuses(id),
  sanctioned_date date,
  notes           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 education_loan_inquiries_branch_idx on public.education_loan_inquiries (branch_id);

-- ---- Visa tracking (one per student application) --------------------------
create table public.visa_tracking (
  id                     uuid primary key default gen_random_uuid(),
  student_application_id uuid not null references public.student_applications(id) on delete cascade,
  status_id              uuid references public.visa_statuses(id),
  applied_date           date,
  interview_date         date,
  decision               text,        -- Approved | Rejected | Pending
  decision_date          date,
  notes                  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 visa_tracking_app_idx on public.visa_tracking (student_application_id);

-- ---- updated_at triggers ---------------------------------------------------
do $$
declare t text;
begin
  foreach t in array array[
    'application_types','application_statuses','education_loan_statuses',
    'visa_statuses','coaching_levels','coaching_subjects','coaching_requirements',
    'register_for','coaching_faculty','student_applications','coaching_applications',
    'education_loan_inquiries','visa_tracking'
  ]
  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 $$;