-- New /projects page (project-level summary table). Mirrors the
-- /entries filter contract but groups by (operator, client, project).
--
-- Also adds a `p_missing_info` flag to support the "missing info" quick
-- filter: rows where operator, client, OR project is NULL.

-- ============================================================
-- 1. project_summary: one row per (operator, client, project)
-- ============================================================
drop function if exists public.project_summary(
  uuid, timestamp, timestamp, text, uuid, text, text, text, text, text, text, uuid, boolean
);

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,
  row_count bigint,
  billout_seconds bigint,
  cost_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.transferred_at is null
            and te.team_member_id is not null
            and te.converted_duration_seconds 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
            and te.converted_user is not null)
        or (p_status = 'transferred' and te.transferred_at is not null)
        or (p_status = 'blocked'
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.converted_duration_seconds 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
                 or te.converted_user 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)
      )
  )
  select
    operator,
    client,
    project,
    count(*)::bigint as row_count,
    coalesce(sum(converted_duration_seconds), 0)::bigint as billout_seconds,
    coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd
  from filtered
  group by 1, 2, 3
$$;

-- ============================================================
-- 2. project_entries_by_description: for expanding a single project,
--    aggregate its entries by description. Same filter contract so
--    the expansion respects whatever filter is active on the page.
-- ============================================================
drop function if exists public.project_entries_by_description(
  uuid, text, text, text, timestamp, timestamp, text, uuid, text, text, text, text, text, text, uuid, boolean
);

create or replace function public.project_entries_by_description(
  p_org_id uuid,
  p_operator text,
  p_client text,
  p_project text,
  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_q text default null,
  p_user text default null,
  p_source_email text default null,
  p_client_q text default null,
  p_operator_q text default null,
  p_imported_by uuid default null,
  p_missing_info boolean default false
)
returns table (
  description text,
  row_count bigint,
  billout_seconds bigint,
  cost_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
      -- Pin to the specific project group (NULL-safe comparison).
      and te.operator is not distinct from p_operator
      and te.client is not distinct from p_client
      and te.project is not distinct from p_project
      -- All the standard filter args, same shape as project_summary.
      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.transferred_at is null
            and te.team_member_id is not null
            and te.converted_duration_seconds 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
            and te.converted_user is not null)
        or (p_status = 'transferred' and te.transferred_at is not null)
        or (p_status = 'blocked'
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.converted_duration_seconds 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
                 or te.converted_user 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_q is null or te.project ilike '%' || p_project_q || '%')
      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_q is null or te.client ilike '%' || p_client_q || '%')
      and (p_operator_q is null or te.operator ilike '%' || p_operator_q || '%')
      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)
      )
  )
  select
    description,
    count(*)::bigint as row_count,
    coalesce(sum(converted_duration_seconds), 0)::bigint as billout_seconds,
    coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd
  from filtered
  group by 1
  order by billout_seconds desc nulls last
$$;
