-- After the invoice migration, "applied" / "transferred" status is
-- driven by time_entries.invoice_id (the canonical lock). The three
-- entries-related RPCs (entries_filter_totals, entries_filter_options,
-- project_summary) still encode the old predicate `transferred_at IS
-- NOT NULL`. Update them to be invoice-aware. transferred_at is kept
-- in the OR for safety (any row that somehow escaped the backfill).
--
-- Eligibility columns trimmed too: converted_duration_seconds /
-- converted_user are Toggl-export plumbing. We're dropping the Toggl
-- export, so they no longer block applying to an invoice. Pending /
-- blocked predicates check the columns that the invoice summary
-- actually depends on (team_member_id, client, project, description,
-- end_at, source_user_email).

create or replace function public.entries_filter_totals(
  p_org_id uuid,
  p_start timestamp default null,
  p_end timestamp default null,
  p_status text default 'all',
  p_batch uuid default null,
  p_q text default null,
  p_project text default null,
  p_user text default null,
  p_source_email text default null,
  p_client text default null,
  p_operator text default null,
  p_imported_by uuid default null
)
returns table (
  row_count bigint,
  source_seconds bigint,
  billout_seconds bigint,
  cost_usd numeric,
  billout_amount_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  with filtered as (
    select te.*
    from public.time_entries te
    where te.org_id = p_org_id
      and (p_start is null or te.start_at >= p_start)
      and (p_end is null or te.start_at < p_end)
      and (p_batch is null or te.import_id = p_batch)
      and (
        p_status = 'all'
        or (p_status = 'pending'
            and te.invoice_id is null
            and te.transferred_at is null
            and te.team_member_id is not null
            and te.operator is not null
            and te.client is not null
            and te.project is not null
            and te.description is not null
            and te.end_at is not null
            and te.source_user_email is not null)
        or ((p_status = 'transferred' or p_status = 'applied')
            and (te.invoice_id is not null or te.transferred_at is not null))
        or (p_status = 'blocked'
            and te.invoice_id is null
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.operator is null
                 or te.client is null
                 or te.project is null
                 or te.description is null
                 or te.end_at is null
                 or te.source_user_email is null))
      )
      and (
        p_q is null
        or te.project ilike '%' || p_q || '%'
        or te.description ilike '%' || p_q || '%'
        or te.source_user_email ilike '%' || p_q || '%'
        or te.client ilike '%' || p_q || '%'
      )
      and (p_project is null or te.project ilike '%' || p_project || '%')
      and (p_user is null or te.converted_user ilike '%' || p_user || '%')
      and (
        p_source_email is null
        or te.source_user_email ilike '%' || p_source_email || '%'
      )
      and (p_client is null or te.client ilike '%' || p_client || '%')
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      and (
        p_imported_by is null
        or exists (
          select 1 from public.clockify_imports ci
          where ci.id = te.import_id and ci.imported_by = p_imported_by
        )
      )
  )
  select
    count(*)::bigint as row_count,
    coalesce(sum(duration_seconds), 0)::bigint as source_seconds,
    -- After the proportion-elimination migration, "billout hours" = source hours.
    coalesce(sum(duration_seconds), 0)::bigint as billout_seconds,
    coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd,
    coalesce(sum(billout_amount_usd), 0)::numeric as billout_amount_usd
  from filtered
$$;

create or replace function public.entries_filter_options(
  p_org_id uuid,
  p_start timestamp default null,
  p_end timestamp default null,
  p_status text default 'all',
  p_batch uuid default null,
  p_q text default null,
  p_project text default null,
  p_user text default null,
  p_source_email text default null,
  p_client text default null,
  p_operator text default null,
  p_imported_by uuid default null
)
returns table (
  operators text[],
  clients text[],
  projects text[],
  users text[],
  source_emails text[]
)
language sql
stable
security invoker
set search_path = public
as $$
  with filtered as (
    select te.*
    from public.time_entries te
    where te.org_id = p_org_id
      and (p_start is null or te.start_at >= p_start)
      and (p_end is null or te.start_at < p_end)
      and (p_batch is null or te.import_id = p_batch)
      and (
        p_status = 'all'
        or (p_status = 'pending'
            and te.invoice_id is null
            and te.transferred_at is null
            and te.team_member_id is not null
            and te.operator is not null
            and te.client is not null
            and te.project is not null
            and te.description is not null
            and te.end_at is not null
            and te.source_user_email is not null)
        or ((p_status = 'transferred' or p_status = 'applied')
            and (te.invoice_id is not null or te.transferred_at is not null))
        or (p_status = 'blocked'
            and te.invoice_id is null
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.operator is null
                 or te.client is null
                 or te.project is null
                 or te.description is null
                 or te.end_at is null
                 or te.source_user_email is null))
      )
      and (
        p_q is null
        or te.project ilike '%' || p_q || '%'
        or te.description ilike '%' || p_q || '%'
        or te.source_user_email ilike '%' || p_q || '%'
        or te.client ilike '%' || p_q || '%'
      )
      and (p_project is null or te.project ilike '%' || p_project || '%')
      and (p_user is null or te.converted_user ilike '%' || p_user || '%')
      and (
        p_source_email is null
        or te.source_user_email ilike '%' || p_source_email || '%'
      )
      and (p_client is null or te.client ilike '%' || p_client || '%')
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      and (
        p_imported_by is null
        or exists (
          select 1 from public.clockify_imports ci
          where ci.id = te.import_id and ci.imported_by = p_imported_by
        )
      )
  )
  select
    (select array_agg(distinct operator order by operator)
       from filtered where operator is not null) as operators,
    (select array_agg(distinct client order by client)
       from filtered where client is not null) as clients,
    (select array_agg(distinct project order by project)
       from filtered where project is not null) as projects,
    (select array_agg(distinct converted_user order by converted_user)
       from filtered where converted_user is not null) as users,
    (select array_agg(distinct source_user_email order by source_user_email)
       from filtered where source_user_email is not null) as source_emails
$$;

create or replace function public.project_summary(
  p_org_id uuid,
  p_start timestamp default null,
  p_end timestamp default null,
  p_status text default 'all',
  p_batch uuid default null,
  p_q text default null,
  p_project text default null,
  p_user text default null,
  p_source_email text default null,
  p_client text default null,
  p_operator text default null,
  p_imported_by uuid default null,
  p_missing_info boolean default false
)
returns table (
  operator text,
  client text,
  project text,
  project_entity_id uuid,
  row_count bigint,
  billout_seconds bigint,
  cost_usd numeric,
  billout_amount_usd numeric,
  billout_adjusted_usd numeric,
  billout_adjustment_pct numeric,
  billout_adjustment_amount numeric,
  income_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  with filtered as (
    select te.*
    from public.time_entries te
    where te.org_id = p_org_id
      and (p_start is null or te.start_at >= p_start)
      and (p_end is null or te.start_at < p_end)
      and (p_batch is null or te.import_id = p_batch)
      and (
        p_status = 'all'
        or (p_status = 'pending'
            and te.invoice_id is null
            and te.transferred_at is null
            and te.team_member_id is not null
            and te.operator is not null
            and te.client is not null
            and te.project is not null
            and te.description is not null
            and te.end_at is not null
            and te.source_user_email is not null)
        or ((p_status = 'transferred' or p_status = 'applied')
            and (te.invoice_id is not null or te.transferred_at is not null))
        or (p_status = 'blocked'
            and te.invoice_id is null
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.operator is null
                 or te.client is null
                 or te.project is null
                 or te.description is null
                 or te.end_at is null
                 or te.source_user_email is null))
      )
      and (
        p_q is null
        or te.project ilike '%' || p_q || '%'
        or te.description ilike '%' || p_q || '%'
        or te.source_user_email ilike '%' || p_q || '%'
        or te.client ilike '%' || p_q || '%'
      )
      and (p_project is null or te.project ilike '%' || p_project || '%')
      and (p_user is null or te.converted_user ilike '%' || p_user || '%')
      and (
        p_source_email is null
        or te.source_user_email ilike '%' || p_source_email || '%'
      )
      and (p_client is null or te.client ilike '%' || p_client || '%')
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      and (
        p_imported_by is null
        or exists (
          select 1 from public.clockify_imports ci
          where ci.id = te.import_id and ci.imported_by = p_imported_by
        )
      )
      and (
        coalesce(p_missing_info, false) = false
        or (te.operator is null or te.client is null or te.project is null)
      )
  ),
  agg as (
    select
      operator,
      client,
      project,
      count(*)::bigint as row_count,
      coalesce(sum(duration_seconds), 0)::bigint as billout_seconds,
      coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd,
      coalesce(sum(billout_amount_usd), 0)::numeric as billout_amount_usd
    from filtered
    group by 1, 2, 3
  )
  select
    agg.operator,
    agg.client,
    agg.project,
    p.id as project_entity_id,
    agg.row_count,
    agg.billout_seconds,
    agg.cost_usd,
    agg.billout_amount_usd,
    (
      agg.billout_amount_usd
        * (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
      + coalesce(p.billout_adjustment_amount, 0)
    )::numeric as billout_adjusted_usd,
    p.billout_adjustment_pct,
    p.billout_adjustment_amount,
    p.income_usd
  from agg
  left join public.operators o
    on o.org_id = p_org_id and lower(o.name) = lower(agg.operator)
  left join public.clients c
    on c.operator_id = o.id and lower(c.name) = lower(agg.client)
  left join public.projects p
    on p.client_id = c.id and lower(p.name) = lower(agg.project)
$$;
