-- Performance tracking tables, ported from legacy state.reports/
-- penalties/qa/trustpilot (JavaScript.html — see chat log for the full
-- investigation: fetchCommslayerMetrics/fetchCommslayerData for reports,
-- openPenaltyEditor/openQaEditor/openTpEditor for the other three,
-- computeMonthlyTotals for the combined-score formula that reads all of
-- them). Legacy had NO real backend access control for any of this
-- (Code.js functions take no role argument; every "who can do X" rule
-- was client-side only and bypassable) — there was no faithful legacy
-- permission model to port, so the access design below was confirmed in
-- conversation rather than mirrored from source:
--   - New feature_key 'performance', separate from 'invoicing' and
--     'rate_and_schedule'.
--   - View is GATED (unlike schedule/rate_history's deliberate open
--     read) — has_permission('performance') required, not open to any
--     authenticated user. Performance data (QA scores, Trustpilot
--     complaint counts, penalties) is closer to a performance review
--     than an operational calendar.
--   - Agents can always see their OWN data (agent_id = current_agent_id()),
--     regardless of the 'performance' grant — same shape as
--     invoices_select_own. No agent has any write path to any of these
--     four tables under any circumstance.
--   - No permissions rows granted in this migration — admin-only
--     (is_admin bypass) until specific people are granted 'performance'
--     via /settings/users.
--
-- Confirmed side effect, not a bug: Berry's is_broad_reviewer clause
-- (0016, refined 0020) excludes 'invoicing'/'rate_and_schedule' only —
-- 'performance' is not excluded, so she automatically gets view-only
-- access to it the moment this feature_key exists. This is the intended
-- use of that mechanism (a genuinely separate, non-invoicing-adjacent
-- future feature), unlike the rate_and_schedule case in 0020.
--
-- Write-capability set per table matches what legacy's own editors
-- actually exposed, not a blanket uniform CRUD set:
--   - commslayer_reports: insert/update/delete (upsert-by-date+agent,
--     plus "clear a day" — legacy's daily view had a clear-report control)
--   - penalties: insert/delete only — legacy's UI only ever adds or
--     removes a penalty row (openPenaltyEditor pushes, the × button
--     splices), never edits one in place
--   - qa_scores / trustpilot_scores: insert/update/delete — legacy's
--     modals upsert a value per agent, and clearing a field back to
--     blank deletes that agent's entry for the month
--
-- Design deviations from legacy's literal shape, flagged in chat:
--   - commslayer_reports stores raw seconds (first_response_secs/
--     avg_response_secs/resolution_secs), not legacy's pre-formatted
--     display strings ("45m"/"2.3h") — avoids the lossy format-then-
--     reparse round trip legacy does via fmt()/parseTimeToMinutes().
--   - one_touch_tickets is stored as-is from the Commslayer API field
--     name; whether it's actually a raw count or a percentage is
--     unresolved (legacy's fetch code treats it as a count, legacy's
--     trend chart treats it as a percentage) — deferred to when the
--     'onetouch' trend view is actually ported.
--   - penalties.created_by is a new column, not in legacy's
--     {date,agentId,reason,points} shape, added for accountability
--     (matches rate_history.saved_by / invoices.created_by precedent).

-- ============================================================
-- 1. New feature_key
-- ============================================================

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('performance', 'Performance', 3)
on conflict (feature_key) do nothing;

-- ============================================================
-- 2. commslayer_reports
-- ============================================================

create table if not exists public.commslayer_reports (
  id                   uuid primary key default gen_random_uuid(),
  report_date          date not null,
  agent_id             text not null,
  commslayer_agent_id  text,
  closed_tickets       integer not null default 0,
  tickets_replied      integer not null default 0,
  first_response_secs  integer,
  avg_response_secs    integer,
  resolution_secs      integer,
  one_touch_tickets    integer not null default 0,
  fetched_at           timestamptz not null default now(),
  unique (report_date, agent_id)
);

alter table public.commslayer_reports enable row level security;

create policy "commslayer_reports_select_own"
on public.commslayer_reports
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "commslayer_reports_select_performance_view"
on public.commslayer_reports
for select
to authenticated
using (public.has_permission('performance'));

create policy "commslayer_reports_insert"
on public.commslayer_reports
for insert
to authenticated
with check (public.has_permission('performance', need_write => true));

create policy "commslayer_reports_update"
on public.commslayer_reports
for update
to authenticated
using (public.has_permission('performance', need_write => true))
with check (public.has_permission('performance', need_write => true));

create policy "commslayer_reports_delete"
on public.commslayer_reports
for delete
to authenticated
using (public.has_permission('performance', need_write => true));

-- ============================================================
-- 3. penalties
-- ============================================================

create table if not exists public.penalties (
  id            uuid primary key default gen_random_uuid(),
  agent_id      text not null,
  penalty_date  date not null,
  reason        text not null,
  points        numeric not null,
  created_by    text,
  created_at    timestamptz not null default now()
);

alter table public.penalties enable row level security;

create policy "penalties_select_own"
on public.penalties
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "penalties_select_performance_view"
on public.penalties
for select
to authenticated
using (public.has_permission('performance'));

create policy "penalties_insert"
on public.penalties
for insert
to authenticated
with check (public.has_permission('performance', need_write => true));

create policy "penalties_delete"
on public.penalties
for delete
to authenticated
using (public.has_permission('performance', need_write => true));

-- ============================================================
-- 4. qa_scores
-- ============================================================

create table if not exists public.qa_scores (
  id           uuid primary key default gen_random_uuid(),
  agent_id     text not null,
  month        text not null,
  score        integer not null check (score between 0 and 100),
  recorded_by  text,
  updated_at   timestamptz not null default now(),
  unique (agent_id, month)
);

alter table public.qa_scores enable row level security;

create policy "qa_scores_select_own"
on public.qa_scores
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "qa_scores_select_performance_view"
on public.qa_scores
for select
to authenticated
using (public.has_permission('performance'));

create policy "qa_scores_insert"
on public.qa_scores
for insert
to authenticated
with check (public.has_permission('performance', need_write => true));

create policy "qa_scores_update"
on public.qa_scores
for update
to authenticated
using (public.has_permission('performance', need_write => true))
with check (public.has_permission('performance', need_write => true));

create policy "qa_scores_delete"
on public.qa_scores
for delete
to authenticated
using (public.has_permission('performance', need_write => true));

-- ============================================================
-- 5. trustpilot_scores
-- ============================================================

create table if not exists public.trustpilot_scores (
  id           uuid primary key default gen_random_uuid(),
  agent_id     text not null,
  month        text not null,
  negatives    integer not null default 0,
  severe       integer not null default 0,
  recorded_by  text,
  updated_at   timestamptz not null default now(),
  unique (agent_id, month)
);

alter table public.trustpilot_scores enable row level security;

create policy "trustpilot_scores_select_own"
on public.trustpilot_scores
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "trustpilot_scores_select_performance_view"
on public.trustpilot_scores
for select
to authenticated
using (public.has_permission('performance'));

create policy "trustpilot_scores_insert"
on public.trustpilot_scores
for insert
to authenticated
with check (public.has_permission('performance', need_write => true));

create policy "trustpilot_scores_update"
on public.trustpilot_scores
for update
to authenticated
using (public.has_permission('performance', need_write => true))
with check (public.has_permission('performance', need_write => true));

create policy "trustpilot_scores_delete"
on public.trustpilot_scores
for delete
to authenticated
using (public.has_permission('performance', need_write => true));