Prime tabelle prima dei model Django.

CREATE TABLE helpdesk_institution (
  id BIGSERIAL PRIMARY KEY,
  public_entity_id BIGINT NOT NULL REFERENCES enti_publicentity(id),
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  official_email VARCHAR(160) NOT NULL,
  privacy_contact VARCHAR(180) NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  public_organization_visible BOOLEAN NOT NULL DEFAULT TRUE,
  public_function_visible BOOLEAN NOT NULL DEFAULT TRUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (public_entity_id)
);

CREATE UNIQUE INDEX helpdesk_institution_primary_uniq
ON helpdesk_institution(is_primary)
WHERE is_primary;

CREATE TABLE helpdesk_institution_setup (
  id BIGSERIAL PRIMARY KEY,
  institution_id BIGINT NOT NULL UNIQUE REFERENCES helpdesk_institution(id) ON DELETE CASCADE,
  privacy_text_version VARCHAR(80) NOT NULL,
  privacy_text TEXT NOT NULL,
  consent_text TEXT NOT NULL,
  receipt_template TEXT NOT NULL,
  public_access_days SMALLINT NOT NULL DEFAULT 30,
  take_charge_hours SMALLINT NOT NULL DEFAULT 32,
  first_response_hours SMALLINT NOT NULL DEFAULT 48,
  urgent_review_hours SMALLINT NOT NULL DEFAULT 24,
  new_ticket_backlog_days SMALLINT NOT NULL DEFAULT 4,
  user_integration_reminder_days SMALLINT NOT NULL DEFAULT 3,
  operator_active_ticket_limit SMALLINT NOT NULL DEFAULT 10,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (public_access_days > 0),
  CHECK (take_charge_hours > 0),
  CHECK (first_response_hours > 0),
  CHECK (urgent_review_hours > 0),
  CHECK (new_ticket_backlog_days > 0),
  CHECK (user_integration_reminder_days > 0),
  CHECK (operator_active_ticket_limit > 0)
);

CREATE TABLE enti_aoo_profile (
  id BIGSERIAL PRIMARY KEY,
  entity_id BIGINT NOT NULL REFERENCES enti_publicentity(id),
  organizational_unit_id BIGINT NOT NULL UNIQUE REFERENCES enti_publicentityorganizationalunit(id),
  ipa_unique_code VARCHAR(20) NOT NULL,
  cod_aoo VARCHAR(100) NOT NULL DEFAULT '',
  official_label VARCHAR(220) NOT NULL,
  protocol_label VARCHAR(180) NOT NULL DEFAULT '',
  pec_primary VARCHAR(180) NOT NULL DEFAULT '',
  protocol_uri VARCHAR(220) NOT NULL DEFAULT '',
  source_updated_on DATE,
  ipa_snapshot_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (entity_id, ipa_unique_code),
  CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE UNIQUE INDEX enti_aoo_profile_primary_uniq
ON enti_aoo_profile(entity_id)
WHERE is_primary AND is_active;

CREATE TABLE enti_unit_aoo_membership (
  id BIGSERIAL PRIMARY KEY,
  entity_id BIGINT NOT NULL REFERENCES enti_publicentity(id),
  aoo_profile_id BIGINT NOT NULL REFERENCES enti_aoo_profile(id),
  organizational_unit_id BIGINT NOT NULL REFERENCES enti_publicentityorganizationalunit(id),
  ipa_unit_code VARCHAR(20) NOT NULL DEFAULT '',
  relation_kind VARCHAR(40) NOT NULL DEFAULT 'protocol_scope',
  is_primary BOOLEAN NOT NULL DEFAULT TRUE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (aoo_profile_id, organizational_unit_id, relation_kind),
  CHECK (relation_kind IN ('protocol_scope', 'document_flow', 'reference')),
  CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE TABLE enti_public_entity_function (
  id BIGSERIAL PRIMARY KEY,
  entity_id BIGINT NOT NULL REFERENCES enti_publicentity(id),
  code VARCHAR(80) NOT NULL,
  public_label VARCHAR(180) NOT NULL,
  setup_label VARCHAR(220) NOT NULL DEFAULT '',
  function_scope VARCHAR(60) NOT NULL,
  public_visibility VARCHAR(32) NOT NULL DEFAULT 'public_synthetic',
  description TEXT NOT NULL DEFAULT '',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (entity_id, code),
  CHECK (function_scope IN ('student_admin', 'student_didactic', 'protocol', 'international', 'events', 'internal_admin', 'other')),
  CHECK (public_visibility IN ('public_synthetic', 'setup_only', 'hidden')),
  CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE TABLE enti_unit_function_assignment (
  id BIGSERIAL PRIMARY KEY,
  function_id BIGINT NOT NULL REFERENCES enti_public_entity_function(id),
  organizational_unit_id BIGINT NOT NULL REFERENCES enti_publicentityorganizationalunit(id),
  responsibility_role VARCHAR(40) NOT NULL DEFAULT 'responsible',
  public_summary TEXT NOT NULL DEFAULT '',
  setup_notes TEXT NOT NULL DEFAULT '',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (function_id, organizational_unit_id, responsibility_role),
  CHECK (responsibility_role IN ('responsible', 'instructor', 'support', 'approver', 'informed')),
  CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE TABLE helpdesk_queue (
  id BIGSERIAL PRIMARY KEY,
  institution_id BIGINT NOT NULL REFERENCES helpdesk_institution(id),
  organizational_unit_id BIGINT NOT NULL REFERENCES enti_publicentityorganizationalunit(id),
  primary_function_id BIGINT NOT NULL REFERENCES enti_public_entity_function(id),
  aoo_profile_id BIGINT REFERENCES enti_aoo_profile(id),
  code VARCHAR(60) NOT NULL,
  public_label VARCHAR(140) NOT NULL,
  unit_snapshot_label VARCHAR(180) NOT NULL DEFAULT '',
  function_snapshot_label VARCHAR(180) NOT NULL DEFAULT '',
  privacy_level VARCHAR(32) NOT NULL DEFAULT 'ordinary',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (institution_id, code),
  CHECK (privacy_level IN ('ordinary', 'reserved', 'restricted'))
);

CREATE TABLE helpdesk_queue_membership (
  id BIGSERIAL PRIMARY KEY,
  queue_id BIGINT NOT NULL REFERENCES helpdesk_queue(id),
  user_id BIGINT NOT NULL,
  role VARCHAR(32) NOT NULL,
  can_take_charge BOOLEAN NOT NULL DEFAULT TRUE,
  can_answer_public BOOLEAN NOT NULL DEFAULT TRUE,
  can_add_internal_note BOOLEAN NOT NULL DEFAULT TRUE,
  can_reassign_within_queue BOOLEAN NOT NULL DEFAULT FALSE,
  can_close_ordinary BOOLEAN NOT NULL DEFAULT TRUE,
  can_reopen_first BOOLEAN NOT NULL DEFAULT FALSE,
  is_primary_responsible BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (queue_id, user_id),
  CHECK (role IN ('operator', 'responsible', 'administrator')),
  CHECK (is_primary_responsible = FALSE OR role IN ('responsible', 'administrator'))
);

CREATE UNIQUE INDEX helpdesk_queue_one_primary_responsible
ON helpdesk_queue_membership(queue_id)
WHERE is_primary_responsible AND is_active;

CREATE TABLE helpdesk_requester (
  id BIGSERIAL PRIMARY KEY,
  requester_type VARCHAR(40) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(180) NOT NULL,
  phone VARCHAR(80) NOT NULL DEFAULT '',
  student_number VARCHAR(80) NOT NULL DEFAULT '',
  organization_name VARCHAR(180) NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (requester_type IN ('student', 'public', 'partner', 'supplier', 'staff', 'operator_on_behalf'))
);

CREATE TABLE helpdesk_category (
  id BIGSERIAL PRIMARY KEY,
  institution_id BIGINT NOT NULL REFERENCES helpdesk_institution(id),
  default_queue_id BIGINT REFERENCES helpdesk_queue(id),
  function_id BIGINT REFERENCES enti_public_entity_function(id),
  code VARCHAR(80) NOT NULL,
  public_label VARCHAR(160) NOT NULL,
  mode VARCHAR(32) NOT NULL DEFAULT 'ticket',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (institution_id, code),
  CHECK (mode IN ('ticket', 'orientation', 'guided'))
);

CREATE TABLE helpdesk_category_entry_rule (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL UNIQUE REFERENCES helpdesk_category(id),
  entry_mode VARCHAR(32) NOT NULL DEFAULT 'informative_first',
  allow_public_ticket BOOLEAN NOT NULL DEFAULT TRUE,
  require_sensitive_warning BOOLEAN NOT NULL DEFAULT FALSE,
  required_context_fields JSONB NOT NULL DEFAULT '[]'::jsonb,
  public_guidance TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (entry_mode IN ('informative_first', 'ticket', 'orientation', 'pre_triage')),
  CHECK (entry_mode <> 'orientation' OR allow_public_ticket = FALSE)
);

CREATE TABLE helpdesk_category_workflow_policy (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL REFERENCES helpdesk_category(id),
  queue_change_mode VARCHAR(32) NOT NULL DEFAULT 'supervisor',
  close_mode VARCHAR(32) NOT NULL DEFAULT 'operator',
  reopen_mode VARCHAR(32) NOT NULL DEFAULT 'supervisor',
  public_status_strategy VARCHAR(32) NOT NULL DEFAULT 'manual',
  requires_official_answer BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (category_id),
  CHECK (queue_change_mode IN ('free', 'supervisor', 'admin')),
  CHECK (close_mode IN ('operator', 'supervisor', 'admin')),
  CHECK (reopen_mode IN ('operator', 'supervisor', 'admin')),
  CHECK (public_status_strategy IN ('auto', 'manual', 'hidden'))
);

CREATE TABLE helpdesk_sla_policy (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT REFERENCES helpdesk_category(id),
  queue_id BIGINT REFERENCES helpdesk_queue(id),
  take_charge_hours INTEGER NOT NULL DEFAULT 32,
  first_response_hours INTEGER NOT NULL DEFAULT 48,
  new_backlog_days INTEGER NOT NULL DEFAULT 4,
  user_reminder_days INTEGER NOT NULL DEFAULT 3,
  urgent_validation_hours INTEGER NOT NULL DEFAULT 24,
  calendar_mode VARCHAR(32) NOT NULL DEFAULT 'institutional',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (category_id IS NOT NULL OR queue_id IS NOT NULL),
  CHECK (take_charge_hours > 0 AND first_response_hours > 0),
  CHECK (calendar_mode IN ('calendar', 'business', 'institutional'))
);

CREATE TABLE helpdesk_ticket (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(24) NOT NULL UNIQUE,
  institution_id BIGINT NOT NULL REFERENCES helpdesk_institution(id),
  requester_id BIGINT NOT NULL REFERENCES helpdesk_requester(id),
  category_id BIGINT NOT NULL REFERENCES helpdesk_category(id),
  queue_id BIGINT REFERENCES helpdesk_queue(id),
  public_status VARCHAR(40) NOT NULL DEFAULT 'received',
  internal_status VARCHAR(40) NOT NULL DEFAULT 'new',
  subject VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  sensitivity_level VARCHAR(32) NOT NULL DEFAULT 'ordinary',
  opened_by_user_id BIGINT,
  opened_for_requester BOOLEAN NOT NULL DEFAULT FALSE,
  first_taken_at TIMESTAMPTZ,
  first_public_response_at TIMESTAMPTZ,
  closed_at TIMESTAMPTZ,
  reopened_count INTEGER NOT NULL DEFAULT 0,
  public_access_until TIMESTAMPTZ,
  public_lookup_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  public_lookup_disabled_reason TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (public_status IN ('received', 'in_progress', 'waiting_user', 'answered', 'closed', 'reopened')),
  CHECK (internal_status IN ('new', 'to_assign', 'assigned', 'in_review', 'suspended', 'waiting_user', 'ready_to_answer', 'closed', 'archived')),
  CHECK (sensitivity_level IN ('ordinary', 'reserved', 'sensitive', 'protected_attachments', 'admin_only', 'secret'))
);

CREATE TABLE helpdesk_sensitive_content_marker (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  source VARCHAR(32) NOT NULL,
  classification VARCHAR(32) NOT NULL,
  action VARCHAR(32) NOT NULL DEFAULT 'review',
  status VARCHAR(32) NOT NULL DEFAULT 'open',
  reason TEXT NOT NULL DEFAULT '',
  created_by_user_id BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  resolved_at TIMESTAMPTZ,
  CHECK (source IN ('user', 'automatic', 'operator', 'admin')),
  CHECK (classification IN ('reserved', 'sensitive', 'protected_attachments', 'admin_only', 'secret')),
  CHECK (action IN ('warning', 'review', 'minimize', 'lock')),
  CHECK (status IN ('open', 'resolved', 'dismissed'))
);

CREATE TABLE helpdesk_ticket_routing_snapshot (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL UNIQUE REFERENCES helpdesk_ticket(id),
  queue_id BIGINT REFERENCES helpdesk_queue(id),
  organizational_unit_id BIGINT REFERENCES enti_publicentityorganizationalunit(id),
  function_id BIGINT REFERENCES enti_public_entity_function(id),
  aoo_profile_id BIGINT REFERENCES enti_aoo_profile(id),
  public_area_label VARCHAR(180) NOT NULL,
  unit_label VARCHAR(180) NOT NULL DEFAULT '',
  function_label VARCHAR(180) NOT NULL DEFAULT '',
  aoo_label VARCHAR(180) NOT NULL DEFAULT '',
  snapshot_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX helpdesk_ticket_routing_snapshot_payload_gin
ON helpdesk_ticket_routing_snapshot USING GIN (snapshot_payload);

CREATE TABLE helpdesk_ticket_assignment (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  queue_id BIGINT NOT NULL REFERENCES helpdesk_queue(id),
  assigned_to_user_id BIGINT NOT NULL,
  assigned_by_user_id BIGINT,
  previous_assignment_id BIGINT REFERENCES helpdesk_ticket_assignment(id) ON DELETE SET NULL,
  assignment_kind VARCHAR(40) NOT NULL DEFAULT 'self_take_charge',
  reason TEXT NOT NULL,
  started_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  ended_by_user_id BIGINT,
  end_reason TEXT NOT NULL DEFAULT '',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (assignment_kind IN (
    'self_take_charge',
    'responsible_assign',
    'auto_least_loaded',
    'auto_single_operator',
    'auto_responsible_overload',
    'release_to_responsible',
    'reassign_within_queue'
  )),
  CHECK (
    (is_active = TRUE AND ended_at IS NULL)
    OR (is_active = FALSE AND ended_at IS NOT NULL)
  )
);

CREATE UNIQUE INDEX helpdesk_ticket_one_active_assignment
ON helpdesk_ticket_assignment(ticket_id)
WHERE is_active;

CREATE TABLE helpdesk_public_message (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  author_kind VARCHAR(32) NOT NULL,
  author_user_id BIGINT,
  body TEXT NOT NULL,
  message_kind VARCHAR(32) NOT NULL DEFAULT 'reply',
  is_official_answer BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (author_kind IN ('requester', 'operator', 'system')),
  CHECK (message_kind IN ('question', 'reply', 'integration_request', 'official_answer', 'correction'))
);

CREATE TABLE helpdesk_reply_draft (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  author_user_id BIGINT NOT NULL,
  reviewer_user_id BIGINT,
  body TEXT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'draft',
  official_message_id BIGINT REFERENCES helpdesk_public_message(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (status IN ('draft', 'proposed', 'approved', 'rejected', 'sent'))
);

CREATE TABLE helpdesk_internal_note (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  author_user_id BIGINT NOT NULL,
  body TEXT NOT NULL,
  visibility_scope VARCHAR(32) NOT NULL DEFAULT 'queue',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (visibility_scope IN ('queue', 'supervisor', 'admin'))
);

CREATE TABLE helpdesk_attachment (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  uploaded_by_user_id BIGINT,
  file_name VARCHAR(220) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes BIGINT NOT NULL,
  storage_key VARCHAR(260) NOT NULL,
  visibility_scope VARCHAR(32) NOT NULL DEFAULT 'internal',
  security_status VARCHAR(32) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (size_bytes > 0 AND size_bytes <= 10485760),
  CHECK (visibility_scope IN ('public', 'internal', 'protected')),
  CHECK (security_status IN ('pending', 'safe', 'blocked'))
);

CREATE TABLE helpdesk_privacy_consent_snapshot (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL UNIQUE REFERENCES helpdesk_ticket(id),
  privacy_version VARCHAR(80) NOT NULL,
  privacy_text TEXT NOT NULL,
  accepted_privacy_at TIMESTAMPTZ NOT NULL,
  accepted_processing_at TIMESTAMPTZ NOT NULL,
  ip_truncated VARCHAR(64) NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE helpdesk_receipt_snapshot (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  template_version VARCHAR(80) NOT NULL,
  recipient_email VARCHAR(180) NOT NULL,
  subject VARCHAR(180) NOT NULL,
  body_text TEXT NOT NULL,
  delivery_status VARCHAR(32) NOT NULL DEFAULT 'generated',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (delivery_status IN ('generated', 'sent', 'deferred', 'failed'))
);

CREATE TABLE helpdesk_service_mailbox (
  id BIGSERIAL PRIMARY KEY,
  institution_id BIGINT NOT NULL REFERENCES helpdesk_institution(id),
  queue_id BIGINT REFERENCES helpdesk_queue(id),
  category_id BIGINT REFERENCES helpdesk_category(id),
  address VARCHAR(180) NOT NULL,
  display_label VARCHAR(160) NOT NULL DEFAULT '',
  mailbox_kind VARCHAR(32) NOT NULL DEFAULT 'service',
  is_publicly_shown BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (mailbox_kind IN ('general', 'service', 'error'))
);

CREATE TABLE helpdesk_audit_event (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  actor_user_id BIGINT,
  event_type VARCHAR(80) NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX helpdesk_audit_event_payload_gin
ON helpdesk_audit_event USING GIN (payload);

CREATE TABLE helpdesk_public_access_log (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  email_hash CHAR(64) NOT NULL,
  code_hash CHAR(64) NOT NULL,
  surname_hash CHAR(64) NOT NULL,
  ip_truncated VARCHAR(64) NOT NULL DEFAULT '',
  user_agent_reduced VARCHAR(180) NOT NULL DEFAULT '',
  public_view_mode VARCHAR(32) NOT NULL DEFAULT 'active',
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (public_view_mode IN ('active', 'expired_minimal'))
);

CREATE TABLE helpdesk_public_access_attempt (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT REFERENCES helpdesk_ticket(id),
  email_hash CHAR(64) NOT NULL,
  code_hash CHAR(64) NOT NULL,
  surname_hash CHAR(64) NOT NULL,
  ip_truncated VARCHAR(64) NOT NULL DEFAULT '',
  user_agent_reduced VARCHAR(180) NOT NULL DEFAULT '',
  attempt_result VARCHAR(32) NOT NULL,
  lock_level SMALLINT NOT NULL DEFAULT 0,
  locked_until TIMESTAMPTZ,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (attempt_result IN ('failed', 'expired', 'rate_limited', 'locked')),
  CHECK (lock_level >= 0 AND lock_level <= 3)
);

CREATE TABLE helpdesk_escalation_event (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES helpdesk_ticket(id),
  level VARCHAR(32) NOT NULL,
  reason VARCHAR(80) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'open',
  assigned_to_user_id BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  closed_at TIMESTAMPTZ,
  CHECK (level IN ('supervisor', 'helpdesk_admin', 'super_admin')),
  CHECK (status IN ('open', 'acknowledged', 'done'))
);