CREATE TABLE IF NOT EXISTS photos_galleries (
  id              text PRIMARY KEY,
  slug            text NOT NULL UNIQUE,
  title           text NOT NULL,
  title_es        text,
  description     text,
  description_es  text,
  event_id        text,
  visibility      text NOT NULL DEFAULT 'private'
                  CHECK (visibility IN ('public','private','link','ticket')),
  share_token     text NOT NULL UNIQUE,
  cover_photo_id  text,
  created_by      text,
  created_at      text NOT NULL,
  updated_at      text NOT NULL
);

CREATE INDEX IF NOT EXISTS photos_galleries_event_idx ON photos_galleries (event_id);

CREATE INDEX IF NOT EXISTS photos_galleries_visibility_idx ON photos_galleries (visibility);

CREATE TABLE IF NOT EXISTS photos_photos (
  id                text PRIMARY KEY,
  gallery_id        text NOT NULL REFERENCES photos_galleries(id) ON DELETE CASCADE,
  position          integer NOT NULL DEFAULT 0,
  original_key      text NOT NULL,
  original_filename text,
  content_type      text NOT NULL,
  size_bytes        integer NOT NULL,
  width             integer,
  height            integer,
  thumb_key         text,
  preview_key       text,
  taken_at          text,
  status            text NOT NULL DEFAULT 'queued'
                    CHECK (status IN ('queued','processing','ready','failed')),
  attempts          integer NOT NULL DEFAULT 0,
  next_attempt_at   text NOT NULL,
  last_error        text,
  created_at        text NOT NULL,
  updated_at        text NOT NULL
);

CREATE INDEX IF NOT EXISTS photos_photos_gallery_pos_idx ON photos_photos (gallery_id, position);

CREATE INDEX IF NOT EXISTS photos_photos_queue_idx ON photos_photos (status, next_attempt_at) WHERE status IN ('queued','failed');