-- Task 2 (MVP): assets/imports schema

CREATE EXTENSION IF NOT EXISTS pgcrypto;

DO $$ BEGIN
  CREATE TYPE media_type AS ENUM ('image', 'video');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE asset_status AS ENUM ('new', 'processing', 'ready', 'failed');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE date_confidence AS ENUM ('camera', 'container', 'object_mtime', 'import_time');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE import_type AS ENUM ('upload', 'minio_scan', 'normalize_copy');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS imports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  type import_type NOT NULL,
  status text NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now(),
  total_count int,
  processed_count int,
  failed_count int
);

CREATE TABLE IF NOT EXISTS assets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  bucket text NOT NULL,
  media_type media_type NOT NULL,
  mime_type text NOT NULL,

  source_key text NOT NULL,
  active_key text NOT NULL,
  canonical_key text,

  capture_ts_utc timestamptz,
  capture_offset_minutes int,
  date_confidence date_confidence,

  width int,
  height int,
  rotation int,
  duration_seconds double precision,

  thumb_small_key text,
  thumb_med_key text,
  poster_key text,

  status asset_status NOT NULL DEFAULT 'new',
  error_message text,
  raw_tags_json jsonb,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS assets_source_key_idx ON assets (source_key);
CREATE INDEX IF NOT EXISTS assets_capture_ts_idx ON assets (capture_ts_utc);
CREATE INDEX IF NOT EXISTS assets_status_idx ON assets (status);
CREATE INDEX IF NOT EXISTS assets_media_type_idx ON assets (media_type);

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$ BEGIN
  CREATE TRIGGER assets_set_updated_at
  BEFORE UPDATE ON assets
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;