`migrations/006_owner_pets.sql`

69 lines
-- Owner-uploaded pet profiles + photos — Steve's Bowie, Humphrey, Madison
-- (and any future user uploads).
--
-- Distinct from `breed_images` (which is the public PD/CC library) and
-- `shelter_animals` (Petfinder-style adoption feeds). These are personal
-- pets attached to an `app_users` account, with full attribution to that
-- user. They render on the user's profile and (with the user's consent)
-- can appear in the breed page as community-uploaded examples.

BEGIN;

CREATE TABLE IF NOT EXISTS owner_pets (
  id              BIGSERIAL PRIMARY KEY,
  app_user_id     BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
  name            TEXT NOT NULL,
  species_id      BIGINT REFERENCES species(id),
  breed_id        BIGINT REFERENCES breeds(id),
  breed_text      TEXT,                              -- free-text if no breed match
  sex             TEXT CHECK (sex IN ('m','f','unknown')),
  birth_year      INTEGER,
  death_year      INTEGER,
  bio             TEXT,
  hero_image_url  TEXT,
  share_publicly  BOOLEAN NOT NULL DEFAULT FALSE,    -- if TRUE, can show in breed gallery
  source          TEXT NOT NULL DEFAULT 'owner_upload', -- 'owner_upload' | 'gdrive_import' | 'manual'
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_owner_pets_user   ON owner_pets(app_user_id);
CREATE INDEX IF NOT EXISTS idx_owner_pets_breed  ON owner_pets(breed_id);

CREATE TABLE IF NOT EXISTS owner_pet_photos (
  id              BIGSERIAL PRIMARY KEY,
  pet_id          BIGINT NOT NULL REFERENCES owner_pets(id) ON DELETE CASCADE,
  image_url       TEXT NOT NULL,            -- where the file lives now (local path or CDN)
  thumb_url       TEXT,
  source          TEXT NOT NULL,            -- 'gdrive' | 'photos.app' | 'upload' | 'icloud'
  source_ref      TEXT,                     -- the file ID at the source (e.g., gdrive file id)
  filename        TEXT,
  caption         TEXT,
  taken_at        TIMESTAMPTZ,
  width           INTEGER,
  height          INTEGER,
  is_hero         BOOLEAN NOT NULL DEFAULT FALSE,
  share_publicly  BOOLEAN NOT NULL DEFAULT FALSE,
  ingested_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (pet_id, source_ref)
);
CREATE INDEX IF NOT EXISTS idx_owner_pet_photos_pet ON owner_pet_photos(pet_id);

-- Ensure Steve's known pets have stub rows even before any photo arrives,
-- so the import script just writes photos against existing IDs.
-- (Linked to his admin app_user once it exists. Idempotent.)
INSERT INTO owner_pets (name, species_id, breed_text, source, share_publicly)
  SELECT 'Bowie',    (SELECT id FROM species WHERE name='dog'), NULL, 'manual', FALSE
   WHERE NOT EXISTS (SELECT 1 FROM owner_pets WHERE name='Bowie' AND species_id=(SELECT id FROM species WHERE name='dog'));
INSERT INTO owner_pets (name, species_id, breed_text, source, share_publicly)
  SELECT 'Humphrey', (SELECT id FROM species WHERE name='dog'), NULL, 'manual', FALSE
   WHERE NOT EXISTS (SELECT 1 FROM owner_pets WHERE name='Humphrey' AND species_id=(SELECT id FROM species WHERE name='dog'));
INSERT INTO owner_pets (name, species_id, breed_text, source, share_publicly)
  SELECT 'Madison',  (SELECT id FROM species WHERE name='dog'), NULL, 'manual', FALSE
   WHERE NOT EXISTS (SELECT 1 FROM owner_pets WHERE name='Madison' AND species_id=(SELECT id FROM species WHERE name='dog'));

-- Attach to Steve's admin account if it exists
UPDATE owner_pets SET app_user_id = (SELECT id FROM app_users WHERE email = 'steve@designerwallcoverings.com' LIMIT 1)
  WHERE name IN ('Bowie','Humphrey','Madison') AND app_user_id IS NULL;

COMMIT;