-- Esquema propuesto para PostgreSQL. Ejecutar únicamente en un entorno de desarrollo.
BEGIN;
CREATE TABLE team_user (
  id uuid PRIMARY KEY,
  identity_subject text UNIQUE NOT NULL,
  role text NOT NULL CHECK (role IN ('owner', 'editor', 'registrar', 'issuer', 'reporter')),
  active boolean NOT NULL DEFAULT true
);
CREATE TABLE event (
  id uuid PRIMARY KEY,
  slug text UNIQUE NOT NULL,
  title text NOT NULL,
  category text NOT NULL,
  timezone text NOT NULL DEFAULT 'America/Mexico_City',
  begins_at timestamptz NOT NULL,
  ends_at timestamptz NOT NULL CHECK (ends_at >= begins_at),
  venue text,
  fee_minor integer CHECK (fee_minor >= 0),
  currency char(3) NOT NULL DEFAULT 'MXN',
  state text NOT NULL CHECK (state IN ('draft', 'published', 'closed', 'cancelled'))
);
CREATE TABLE participant (
  id uuid PRIMARY KEY,
  email text UNIQUE NOT NULL CHECK (email = lower(btrim(email))),
  full_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE privacy_notice (
  version text PRIMARY KEY,
  published_at timestamptz NOT NULL,
  url text NOT NULL
);
CREATE TABLE registration (
  id uuid PRIMARY KEY,
  event_id uuid NOT NULL REFERENCES event(id),
  participant_id uuid NOT NULL REFERENCES participant(id),
  notice_version text NOT NULL REFERENCES privacy_notice(version),
  notice_seen_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('received', 'under_review', 'confirmed', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (event_id, participant_id)
);
CREATE INDEX registration_by_event ON registration(event_id, status);
CREATE TABLE payment_evidence (
  id uuid PRIMARY KEY,
  registration_id uuid NOT NULL REFERENCES registration(id),
  object_key text UNIQUE NOT NULL,
  sha256 text NOT NULL,
  status text NOT NULL CHECK (status IN ('quarantined', 'available', 'rejected')),
  received_at timestamptz NOT NULL DEFAULT now(),
  retain_until timestamptz NOT NULL
);
CREATE TABLE certificate (
  id uuid PRIMARY KEY,
  registration_id uuid UNIQUE NOT NULL REFERENCES registration(id),
  folio text UNIQUE NOT NULL,
  object_key text UNIQUE NOT NULL,
  issued_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz
);
CREATE TABLE audit_entry (
  id uuid PRIMARY KEY,
  actor_id uuid REFERENCES team_user(id),
  action text NOT NULL,
  entity_id uuid NOT NULL,
  happened_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE notification_outbox (
  id uuid PRIMARY KEY,
  registration_id uuid NOT NULL REFERENCES registration(id),
  notification_kind text NOT NULL,
  deduplication_key text UNIQUE NOT NULL,
  attempts integer NOT NULL DEFAULT 0,
  send_after timestamptz NOT NULL DEFAULT now(),
  sent_at timestamptz
);
COMMIT;
