update case_file
set status = 'CLOSED'
where id = :case_id;

create table case_state_transition_rule (
  from_status text not null,
  to_status text not null,
  required_role text not null,
  requires_reason boolean not null default true,
  requires_assignment boolean not null default false,
  requires_open_task_completion boolean not null default false,
  is_active boolean not null default true,
  primary key (from_status, to_status)
);

create table case_file (
  tenant_id uuid not null,
  case_id uuid not null,
  case_number text not null,
  title text not null,
  description text,
  status text not null,
  priority text not null,
  sensitivity_level text not null default 'NORMAL',
  source_type text not null,
  source_reference text,
  assigned_team_id uuid,
  assigned_user_id uuid,
  opened_at timestamptz not null default now(),
  closed_at timestamptz,
  due_at timestamptz,
  version bigint not null default 1,
  created_at timestamptz not null default now(),
  created_by uuid not null,
  updated_at timestamptz not null default now(),
  updated_by uuid not null,
  primary key (tenant_id, case_id),
  unique (tenant_id, case_number),
  check (priority in ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
  check (sensitivity_level in ('NORMAL', 'CONFIDENTIAL', 'RESTRICTED')),
  check (
    (status <> 'CLOSED' and closed_at is null)
    or
    (status = 'CLOSED' and closed_at is not null)
  )
);

create index idx_case_inbox_by_assignee
on case_file (tenant_id, assigned_user_id, status, due_at, priority, case_id)
where status not in ('CLOSED', 'ARCHIVED');

create index idx_case_by_team
on case_file (tenant_id, assigned_team_id, status, due_at, case_id)
where status not in ('CLOSED', 'ARCHIVED');

create index idx_case_opened_at
on case_file (tenant_id, opened_at desc, case_id);

create table case_transition (
  tenant_id uuid not null,
  transition_id uuid not null,
  case_id uuid not null,
  from_status text not null,
  to_status text not null,
  reason text not null,
  actor_user_id uuid not null,
  actor_role text not null,
  occurred_at timestamptz not null default now(),
  request_id text,
  metadata jsonb not null default '{}'::jsonb,
  primary key (tenant_id, transition_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  unique (tenant_id, case_id, request_id)
);

-- Pseudocode transaction
begin;

select status, version
from case_file
where tenant_id = :tenant_id
  and case_id = :case_id
for update;

-- Validate allowed transition from current status to requested status.
-- Validate actor role, assignment, open tasks, evidence requirements.

insert into case_transition (
  tenant_id,
  transition_id,
  case_id,
  from_status,
  to_status,
  reason,
  actor_user_id,
  actor_role,
  request_id
) values (
  :tenant_id,
  :transition_id,
  :case_id,
  :from_status,
  :to_status,
  :reason,
  :actor_user_id,
  :actor_role,
  :request_id
);

update case_file
set status = :to_status,
    closed_at = case when :to_status = 'CLOSED' then now() else closed_at end,
    version = version + 1,
    updated_at = now(),
    updated_by = :actor_user_id
where tenant_id = :tenant_id
  and case_id = :case_id;

insert into outbox_event (...);
insert into case_audit_event (...);

commit;

create table intake_report (
  tenant_id uuid not null,
  report_id uuid not null,
  source_channel text not null,
  reporter_type text not null,
  reporter_contact_id uuid,
  received_at timestamptz not null default now(),
  raw_payload jsonb not null,
  triage_status text not null default 'RECEIVED',
  created_case_id uuid,
  primary key (tenant_id, report_id),
  foreign key (tenant_id, created_case_id)
    references case_file (tenant_id, case_id)
);

create table intake_report_case_link (
  tenant_id uuid not null,
  report_id uuid not null,
  case_id uuid not null,
  link_type text not null,
  linked_at timestamptz not null default now(),
  linked_by uuid not null,
  primary key (tenant_id, report_id, case_id),
  foreign key (tenant_id, report_id)
    references intake_report (tenant_id, report_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id)
);

create table party (
  tenant_id uuid not null,
  party_id uuid not null,
  party_type text not null,
  display_name text not null,
  external_reference text,
  normalized_identifier text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (tenant_id, party_id),
  check (party_type in ('PERSON', 'ORGANIZATION', 'AGENCY', 'UNKNOWN'))
);

create table case_party (
  tenant_id uuid not null,
  case_id uuid not null,
  party_id uuid not null,
  role_type text not null,
  is_primary boolean not null default false,
  effective_from timestamptz not null default now(),
  effective_to timestamptz,
  notes text,
  primary key (tenant_id, case_id, party_id, role_type, effective_from),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, party_id)
    references party (tenant_id, party_id),
  check (effective_to is null or effective_to > effective_from)
);

create table regulatory_rule_version (
  tenant_id uuid not null,
  rule_version_id uuid not null,
  rule_code text not null,
  rule_title text not null,
  version_label text not null,
  effective_from date not null,
  effective_to date,
  rule_text text not null,
  primary key (tenant_id, rule_version_id),
  unique (tenant_id, rule_code, version_label),
  check (effective_to is null or effective_to > effective_from)
);

create table case_allegation (
  tenant_id uuid not null,
  allegation_id uuid not null,
  case_id uuid not null,
  respondent_party_id uuid,
  rule_version_id uuid not null,
  allegation_summary text not null,
  status text not null default 'OPEN',
  created_at timestamptz not null default now(),
  created_by uuid not null,
  primary key (tenant_id, allegation_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, respondent_party_id)
    references party (tenant_id, party_id),
  foreign key (tenant_id, rule_version_id)
    references regulatory_rule_version (tenant_id, rule_version_id),
  check (status in ('OPEN', 'SUBSTANTIATED', 'NOT_SUBSTANTIATED', 'WITHDRAWN'))
);

create table case_evidence (
  tenant_id uuid not null,
  evidence_id uuid not null,
  case_id uuid not null,
  evidence_type text not null,
  title text not null,
  description text,
  storage_uri text not null,
  content_hash text not null,
  hash_algorithm text not null default 'SHA-256',
  source_type text not null,
  acquired_at timestamptz not null,
  acquired_by uuid not null,
  classification text not null default 'CONFIDENTIAL',
  legal_basis text,
  is_redacted boolean not null default false,
  supersedes_evidence_id uuid,
  created_at timestamptz not null default now(),
  primary key (tenant_id, evidence_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, supersedes_evidence_id)
    references case_evidence (tenant_id, evidence_id),
  unique (tenant_id, case_id, content_hash)
);

create table evidence_allegation_link (
  tenant_id uuid not null,
  evidence_id uuid not null,
  allegation_id uuid not null,
  support_type text not null,
  relevance_note text,
  linked_at timestamptz not null default now(),
  linked_by uuid not null,
  primary key (tenant_id, evidence_id, allegation_id),
  foreign key (tenant_id, evidence_id)
    references case_evidence (tenant_id, evidence_id),
  foreign key (tenant_id, allegation_id)
    references case_allegation (tenant_id, allegation_id),
  check (support_type in ('SUPPORTS', 'CONTRADICTS', 'CONTEXT', 'UNKNOWN'))
);

create table evidence_custody_event (
  tenant_id uuid not null,
  custody_event_id uuid not null,
  evidence_id uuid not null,
  event_type text not null,
  actor_user_id uuid not null,
  occurred_at timestamptz not null default now(),
  location text,
  note text,
  primary key (tenant_id, custody_event_id),
  foreign key (tenant_id, evidence_id)
    references case_evidence (tenant_id, evidence_id)
);

create table case_task (
  tenant_id uuid not null,
  task_id uuid not null,
  case_id uuid not null,
  task_type text not null,
  title text not null,
  status text not null default 'OPEN',
  assigned_team_id uuid,
  assigned_user_id uuid,
  priority text not null default 'MEDIUM',
  due_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  created_by uuid not null,
  primary key (tenant_id, task_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  check (status in ('OPEN', 'IN_PROGRESS', 'BLOCKED', 'COMPLETED', 'CANCELLED')),
  check (
    (status = 'COMPLETED' and completed_at is not null)
    or
    (status <> 'COMPLETED' and completed_at is null)
  )
);

create table task_assignment_event (
  tenant_id uuid not null,
  assignment_event_id uuid not null,
  task_id uuid not null,
  from_user_id uuid,
  to_user_id uuid,
  from_team_id uuid,
  to_team_id uuid,
  reason text not null,
  assigned_by uuid not null,
  occurred_at timestamptz not null default now(),
  primary key (tenant_id, assignment_event_id),
  foreign key (tenant_id, task_id)
    references case_task (tenant_id, task_id)
);

create table case_sla_timer (
  tenant_id uuid not null,
  sla_timer_id uuid not null,
  case_id uuid not null,
  timer_type text not null,
  started_at timestamptz not null,
  paused_at timestamptz,
  due_at timestamptz not null,
  completed_at timestamptz,
  status text not null default 'RUNNING',
  primary key (tenant_id, sla_timer_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  check (status in ('RUNNING', 'PAUSED', 'COMPLETED', 'BREACHED', 'CANCELLED'))
);

create table case_decision (
  tenant_id uuid not null,
  decision_id uuid not null,
  case_id uuid not null,
  decision_type text not null,
  decision_status text not null default 'DRAFT',
  decision_summary text not null,
  rationale text not null,
  decided_by uuid,
  decided_at timestamptz,
  authority_role text,
  rule_version_id uuid,
  supersedes_decision_id uuid,
  created_at timestamptz not null default now(),
  created_by uuid not null,
  primary key (tenant_id, decision_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, rule_version_id)
    references regulatory_rule_version (tenant_id, rule_version_id),
  foreign key (tenant_id, supersedes_decision_id)
    references case_decision (tenant_id, decision_id),
  check (decision_status in ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'FINAL', 'SUPERSEDED'))
);

create table decision_evidence_link (
  tenant_id uuid not null,
  decision_id uuid not null,
  evidence_id uuid not null,
  usage_type text not null,
  note text,
  primary key (tenant_id, decision_id, evidence_id),
  foreign key (tenant_id, decision_id)
    references case_decision (tenant_id, decision_id),
  foreign key (tenant_id, evidence_id)
    references case_evidence (tenant_id, evidence_id),
  check (usage_type in ('PRIMARY_BASIS', 'SUPPORTING', 'CONTRADICTORY_REVIEWED'))
);

create table enforcement_action (
  tenant_id uuid not null,
  action_id uuid not null,
  case_id uuid not null,
  decision_id uuid not null,
  action_type text not null,
  status text not null default 'PENDING',
  effective_from date,
  effective_to date,
  amount_minor bigint,
  currency_code char(3),
  issued_at timestamptz,
  issued_by uuid,
  created_at timestamptz not null default now(),
  primary key (tenant_id, action_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, decision_id)
    references case_decision (tenant_id, decision_id),
  check (status in ('PENDING', 'ISSUED', 'ACTIVE', 'SUSPENDED', 'COMPLETED', 'CANCELLED')),
  check (effective_to is null or effective_from is null or effective_to > effective_from)
);

create table case_relationship (
  tenant_id uuid not null,
  from_case_id uuid not null,
  to_case_id uuid not null,
  relationship_type text not null,
  reason text not null,
  created_at timestamptz not null default now(),
  created_by uuid not null,
  primary key (tenant_id, from_case_id, to_case_id, relationship_type),
  foreign key (tenant_id, from_case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, to_case_id)
    references case_file (tenant_id, case_id),
  check (from_case_id <> to_case_id),
  check (relationship_type in ('DUPLICATE_OF', 'RELATED_TO', 'SPLIT_FROM', 'MERGED_INTO', 'APPEAL_OF'))
);

create table case_audit_event (
  tenant_id uuid not null,
  audit_event_id uuid not null,
  case_id uuid,
  entity_type text not null,
  entity_id uuid not null,
  action_type text not null,
  actor_user_id uuid,
  actor_type text not null,
  reason text,
  occurred_at timestamptz not null default now(),
  request_id text,
  correlation_id text,
  before_data jsonb,
  after_data jsonb,
  metadata jsonb not null default '{}'::jsonb,
  primary key (tenant_id, audit_event_id)
);

create or replace function prevent_audit_mutation()
returns trigger
language plpgsql
as $$
begin
  raise exception 'audit events are append-only';
end;
$$;

create trigger trg_prevent_audit_update
before update or delete on case_audit_event
for each row execute function prevent_audit_mutation();

create table case_access_grant (
  tenant_id uuid not null,
  case_id uuid not null,
  principal_type text not null,
  principal_id uuid not null,
  access_level text not null,
  grant_reason text not null,
  granted_at timestamptz not null default now(),
  granted_by uuid,
  expires_at timestamptz,
  primary key (tenant_id, case_id, principal_type, principal_id, access_level),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  check (principal_type in ('USER', 'TEAM', 'ROLE', 'EXTERNAL_AGENCY')),
  check (access_level in ('READ', 'COMMENT', 'EDIT', 'DECIDE', 'ADMIN'))
);

alter table case_file enable row level security;

create policy case_file_read_policy
on case_file
for select
using (
  tenant_id = current_setting('app.tenant_id')::uuid
  and exists (
    select 1
    from case_access_grant g
    where g.tenant_id = case_file.tenant_id
      and g.case_id = case_file.case_id
      and g.principal_type = 'USER'
      and g.principal_id = current_setting('app.user_id')::uuid
      and g.access_level in ('READ', 'COMMENT', 'EDIT', 'DECIDE', 'ADMIN')
      and (g.expires_at is null or g.expires_at > now())
  )
);

create table outbox_event (
  tenant_id uuid not null,
  outbox_event_id uuid not null,
  aggregate_type text not null,
  aggregate_id uuid not null,
  event_type text not null,
  event_version int not null,
  payload jsonb not null,
  occurred_at timestamptz not null default now(),
  published_at timestamptz,
  publish_attempts int not null default 0,
  last_error text,
  primary key (tenant_id, outbox_event_id)
);

select *
from outbox_event
where published_at is null
order by occurred_at, outbox_event_id
limit 100
for update skip locked;

create table inbox_event_dedup (
  consumer_name text not null,
  source_event_id uuid not null,
  processed_at timestamptz not null default now(),
  primary key (consumer_name, source_event_id)
);

create table case_search_document (
  tenant_id uuid not null,
  case_id uuid not null,
  case_number text not null,
  title text not null,
  status text not null,
  priority text not null,
  assigned_user_id uuid,
  assigned_team_id uuid,
  party_names text,
  allegation_codes text,
  due_at timestamptz,
  updated_at timestamptz not null,
  source_version bigint not null,
  primary key (tenant_id, case_id)
);

create table case_lifecycle_fact_daily (
  tenant_id uuid not null,
  report_date date not null,
  case_status text not null,
  priority text not null,
  assigned_team_id uuid,
  opened_count int not null default 0,
  closed_count int not null default 0,
  breached_sla_count int not null default 0,
  avg_days_to_close numeric(12,2),
  generated_at timestamptz not null default now(),
  primary key (tenant_id, report_date, case_status, priority, assigned_team_id)
);

create table report_run (
  tenant_id uuid not null,
  report_run_id uuid not null,
  report_name text not null,
  report_version text not null,
  parameters jsonb not null,
  as_of_time timestamptz not null,
  generated_at timestamptz not null default now(),
  generated_by uuid not null,
  output_uri text,
  checksum text,
  primary key (tenant_id, report_run_id)
);

create table case_legal_hold (
  tenant_id uuid not null,
  legal_hold_id uuid not null,
  case_id uuid not null,
  hold_reason text not null,
  placed_at timestamptz not null default now(),
  placed_by uuid not null,
  released_at timestamptz,
  released_by uuid,
  primary key (tenant_id, legal_hold_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id)
);

select cf.tenant_id, cf.case_id
from case_file cf
where cf.status = 'ARCHIVED'
  and cf.closed_at < now() - interval '7 years'
  and not exists (
    select 1
    from case_legal_hold h
    where h.tenant_id = cf.tenant_id
      and h.case_id = cf.case_id
      and h.released_at is null
  );

select case_id, case_number, title, priority, status, due_at
from case_file
where tenant_id = :tenant_id
  and assigned_user_id = :user_id
  and status not in ('CLOSED', 'ARCHIVED')
order by due_at asc nulls last, priority desc, case_id
limit 50;

create index idx_case_investigator_inbox
on case_file (tenant_id, assigned_user_id, due_at, priority, case_id)
where status not in ('CLOSED', 'ARCHIVED');

select case_id, case_number, title, priority, due_at
from case_file
where tenant_id = :tenant_id
  and assigned_team_id = :team_id
  and status in ('OPEN', 'ASSIGNED', 'INVESTIGATING')
order by priority desc, due_at asc, case_id
limit 100;

create table case_timeline_event (
  tenant_id uuid not null,
  case_id uuid not null,
  timeline_event_id uuid not null,
  event_type text not null,
  event_time timestamptz not null,
  actor_user_id uuid,
  summary text not null,
  source_entity_type text not null,
  source_entity_id uuid not null,
  primary key (tenant_id, case_id, timeline_event_id)
);

create index idx_case_timeline_order
on case_timeline_event (tenant_id, case_id, event_time desc, timeline_event_id desc);

create table case_appeal (
  tenant_id uuid not null,
  appeal_id uuid not null,
  case_id uuid not null,
  decision_id uuid not null,
  appeal_reason text not null,
  status text not null default 'OPEN',
  filed_at timestamptz not null default now(),
  filed_by uuid not null,
  resolved_at timestamptz,
  primary key (tenant_id, appeal_id),
  foreign key (tenant_id, case_id)
    references case_file (tenant_id, case_id),
  foreign key (tenant_id, decision_id)
    references case_decision (tenant_id, decision_id)
);