-- ============================================================================
-- EduAdvise CRM — Phase 5: Finance & accountant data
-- ============================================================================

-- Simple masters
do $$
declare t text;
begin
  foreach t in array array['account_statuses','expense_types']
  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 $$;

-- Banking details
create table public.banking_details (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  bank_name text not null,
  account_name text,
  account_number text,
  ifsc text,
  branch_name 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()
);

-- Currency rates (1 from_currency = rate * to_currency, e.g. GBP->INR)
create table public.currency_rates (
  id uuid primary key default gen_random_uuid(),
  from_currency text not null,
  to_currency text not null,
  rate numeric not null,
  effective_date date,
  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()
);

-- Plans (main + sub)
create table public.plans_main (
  id uuid primary key default gen_random_uuid(),
  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.plans_sub (
  id uuid primary key default gen_random_uuid(),
  plan_id uuid references public.plans_main(id) on delete cascade,
  name text not null,
  amount numeric,
  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 plans_sub_plan_idx on public.plans_sub (plan_id);

-- Fee payments (against a student application)
create table public.fee_payments (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid not null references public.branches(id),
  student_application_id uuid references public.student_applications(id) on delete set null,
  amount numeric not null,
  currency text default 'INR',
  payment_date date not null default current_date,
  mode text,                      -- Cash | Bank | Online | Card
  reference text,
  plan_sub_id uuid references public.plans_sub(id),
  notes text,
  received_by uuid references public.users(id),
  created_by uuid references public.users(id),
  created_at timestamptz not null default now()
);
create index fee_payments_branch_idx on public.fee_payments (branch_id);
create index fee_payments_app_idx on public.fee_payments (student_application_id);
create index fee_payments_date_idx on public.fee_payments (payment_date);

-- University commissions
create table public.university_commissions (
  id uuid primary key default gen_random_uuid(),
  branch_id uuid references public.branches(id),
  student_application_id uuid references public.student_applications(id) on delete set null,
  institute_id uuid references public.institutes(id),
  amount numeric not null,
  currency text default 'GBP',
  status text not null default 'pending',  -- pending | invoiced | received
  received_date date,
  notes text,
  created_by uuid references public.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index university_commissions_branch_idx on public.university_commissions (branch_id);

-- updated_at triggers
do $$
declare t text;
begin
  foreach t in array array[
    'account_statuses','expense_types','banking_details','currency_rates',
    'plans_main','plans_sub','university_commissions'
  ]
  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 $$;