db/schema.sql

CREATE TABLE IF NOT EXISTS animals (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    species         TEXT NOT NULL CHECK (species IN ('dog', 'cat')),
    name            TEXT,
    description     TEXT,
    embedding       BLOB,
    first_seen      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sighting_count  INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS sightings (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    animal_id       INTEGER NOT NULL REFERENCES animals(id) ON DELETE CASCADE,
    photo_path      TEXT,
    latitude        REAL,
    longitude       REAL,
    notes           TEXT,
    is_help_event   INTEGER NOT NULL DEFAULT 0,
    help_type       TEXT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_sightings_animal ON sightings(animal_id);
CREATE INDEX IF NOT EXISTS idx_animals_last_seen ON animals(last_seen);
CREATE INDEX IF NOT EXISTS idx_animals_species ON animals(species);