`migrations/008_dog_friends.sql`

47 lines
-- Dog-friend graph: each registered owner_pets row can request / accept /
-- decline friendships with another. Powers the "make friends with other
-- dogs on the platform" feature.

BEGIN;

CREATE TABLE IF NOT EXISTS dog_friendships (
  id            BIGSERIAL PRIMARY KEY,
  pet_a_id      BIGINT NOT NULL REFERENCES owner_pets(id) ON DELETE CASCADE,
  pet_b_id      BIGINT NOT NULL REFERENCES owner_pets(id) ON DELETE CASCADE,
  status        TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','accepted','declined','blocked')),
  initiated_by  TEXT NOT NULL CHECK (initiated_by IN ('a','b','ai_match')),
  ai_score      NUMERIC(4,3),                     -- 0-1, how compatible per AI
  ai_reasons    TEXT[],
  message       TEXT,
  responded_at  TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (pet_a_id < pet_b_id)                     -- always store ordered to avoid dup pairs
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_friendships_pair ON dog_friendships(pet_a_id, pet_b_id);
CREATE INDEX IF NOT EXISTS idx_friendships_status ON dog_friendships(status);
CREATE INDEX IF NOT EXISTS idx_friendships_pet_a   ON dog_friendships(pet_a_id);
CREATE INDEX IF NOT EXISTS idx_friendships_pet_b   ON dog_friendships(pet_b_id);

CREATE OR REPLACE FUNCTION bump_friendships_updated() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_friendships_updated BEFORE UPDATE ON dog_friendships
  FOR EACH ROW EXECUTE FUNCTION bump_friendships_updated();

-- Add traits to owner_pets that the matcher needs (most are nullable so we
-- can ask about them progressively, not all at once).
ALTER TABLE owner_pets
  ADD COLUMN IF NOT EXISTS home_zip       TEXT,
  ADD COLUMN IF NOT EXISTS home_lat       NUMERIC(10,7),
  ADD COLUMN IF NOT EXISTS home_lng       NUMERIC(10,7),
  ADD COLUMN IF NOT EXISTS energy_level   INTEGER CHECK (energy_level BETWEEN 1 AND 5),
  ADD COLUMN IF NOT EXISTS size_class     TEXT CHECK (size_class IN ('toy','small','medium','large','giant')),
  ADD COLUMN IF NOT EXISTS sociability    INTEGER CHECK (sociability BETWEEN 1 AND 5),
  ADD COLUMN IF NOT EXISTS good_with_dogs INTEGER CHECK (good_with_dogs BETWEEN 1 AND 5),
  ADD COLUMN IF NOT EXISTS playstyle      TEXT CHECK (playstyle IN ('chase','wrestle','fetch','sniff','parallel','mixed','unknown')),
  ADD COLUMN IF NOT EXISTS open_to_friends BOOLEAN NOT NULL DEFAULT TRUE;

COMMIT;