schema.sql

CREATE SCHEMA vet;

CREATE FUNCTION vet.set_current_timestamp_updated_at()
    RETURNS TRIGGER AS $$
DECLARE
_new record;
BEGIN
  _new := NEW;
  _new."updated_at" = now();
RETURN _new;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE vet.person(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null
);

CREATE TRIGGER set_person_updated_at
    BEFORE UPDATE ON vet.person
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();
    
CREATE TABLE vet.pet_kind(
    value text not null primary key,
    comment text not null default ''
);

INSERT INTO vet.pet_kind(value, comment)
VALUES 
    ('dog', 'A Canine'),
    ('cat', 'A Feline'),
    ('bird', 'A 50 Year Commitment');
    
CREATE TABLE vet.pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null,
    kind text not null references vet.pet_kind(value)
            on update restrict
            on delete restrict
);

CREATE TRIGGER set_pet_updated_at
    BEFORE UPDATE ON vet.pet
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();
    
CREATE TABLE vet.person_pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    pet_id uuid not null references vet.pet(id)
                on update restrict
                on delete restrict
);

CREATE TRIGGER set_person_pet_updated_at
    BEFORE UPDATE ON vet.person_pet
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();

CREATE UNIQUE INDEX ON vet.person_pet(person_id, pet_id);

CREATE TABLE vet.adoption_approval_status(
    value text not null primary key
);

INSERT INTO vet.adoption_approval_status(value)
VALUES ('submitted'), ('in_review'), ('rejected'), ('approved');

CREATE TABLE vet.adoption_approval(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    status text not null references vet.adoption_approval_status(value)
                on update restrict
                on delete restrict,
    valid_at timestamptz not null,
    latest boolean default false
);

CREATE TRIGGER set_adoption_approval_updated_at
    BEFORE UPDATE ON vet.adoption_approval
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();


CREATE INDEX ON vet.adoption_approval(person_id, valid_at DESC);

-- Conditional unique index makes sure we only have one latest
CREATE UNIQUE INDEX ON vet.adoption_approval(person_id, latest)
WHERE latest = true;

-- Then a trigger to keep latest up to date
CREATE OR REPLACE FUNCTION vet.set_adoption_approval_latest()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE vet.adoption_approval
    SET latest = false
    WHERE latest = true and person_id = NEW.person_id;

    UPDATE vet.adoption_approval
    SET latest = true
    WHERE id = (
        SELECT id 
        FROM vet.adoption_approval 
        WHERE person_id = NEW.person_id
        ORDER BY valid_at DESC 
        LIMIT 1
    );

    RETURN null;
END;
$function$;

CREATE TRIGGER adoption_approval_insert_trigger
    AFTER INSERT ON vet.adoption_approval
    FOR EACH ROW
    EXECUTE FUNCTION vet.set_adoption_approval_latest();

CREATE TABLE vet.contact_info(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid references vet.person(id)
                on update restrict
                on delete restrict,
    mailing_address text not null,
    system_id text
);

CREATE TRIGGER set_contact_info_updated_at
    BEFORE UPDATE ON vet.contact_info
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();
    
CREATE UNIQUE INDEX ON vet.contact_info(system_id);

-- Not hard to imagine wanting to build functionality that
-- automatically contacts the CDC for cases of rabies or similar,
-- but maybe every other bit of contact_info in the system is
-- for more "normal" purposes
INSERT INTO vet.contact_info(system_id, mailing_address)
VALUES ('cdc', '4770 Buford Highway, NE');
    
CREATE TABLE vet.prescription(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    pet_id uuid not null references vet.pet(id)
             on update restrict
             on delete restrict,
    issued_at timestamptz not null,
    -- Instead of deleting a prescription,
    -- explicitly mark when it was revoked
    revoked_at timestamptz
);

CREATE TRIGGER set_prescription_updated_at
    BEFORE UPDATE ON vet.prescription
    FOR EACH ROW
    EXECUTE PROCEDURE vet.set_current_timestamp_updated_at();

CREATE INDEX ON vet.prescription(revoked_at);

-- There are pros and cons to having this view
CREATE VIEW vet.active_prescription AS
    SELECT
        vet.prescription.id,
        vet.prescription.created_at,
        vet.prescription.updated_at,
        vet.prescription.pet_id,
        vet.prescription.issued_at
    FROM
        vet.prescription
    WHERE 
        vet.prescription.revoked_at IS NULL;
        
-- person_id = 29168a93-cd14-478f-8c70-a2b7a782c714
-- pet_id_1 = 3e5557c0-c628-44ef-b4d1-86012c5f48bf
-- pet_id_2 = ed63ca7d-3368-4353-9747-6b6b2fa6657a

INSERT INTO vet.person(id, name)
VALUES ('29168a93-cd14-478f-8c70-a2b7a782c714', 'Jeff Computers');

INSERT INTO vet.pet(id, name, kind)
VALUES ('3e5557c0-c628-44ef-b4d1-86012c5f48bf', 'Rhodie', 'dog');

INSERT INTO vet.pet(id, name, kind)
VALUES ('ed63ca7d-3368-4353-9747-6b6b2fa6657a', 'Jenny', 'dog');

INSERT INTO vet.person_pet(person_id, pet_id)
VALUES 
  ('29168a93-cd14-478f-8c70-a2b7a782c714', '3e5557c0-c628-44ef-b4d1-86012c5f48bf'),
  ('29168a93-cd14-478f-8c70-a2b7a782c714', 'ed63ca7d-3368-4353-9747-6b6b2fa6657a');
  

INSERT INTO vet.prescription(pet_id, issued_at)
VALUES ('3e5557c0-c628-44ef-b4d1-86012c5f48bf', now());

INSERT INTO vet.contact_info(person_id, mailing_address)
VALUES ('29168a93-cd14-478f-8c70-a2b7a782c714', '123 Sesame St.');

SELECT jsonb_build_object(
  'id', vet.person.id,
  'name', vet.person.name,
  'pets', array(
    SELECT jsonb_build_object(
      'id', vet.pet.id,
      'name', vet.pet.name,
      'prescriptions', array(
        SELECT jsonb_build_object(
          'issued_at', vet.prescription.issued_at
        )
        FROM vet.prescription
        WHERE vet.prescription.pet_id = vet.pet.id
      )
    )
    FROM vet.person_pet
    LEFT JOIN vet.pet 
      ON vet.pet.id = vet.person_pet.pet_id
    WHERE vet.person_pet.person_id = vet.person.id
  ),
  'contact_infos', array(
    SELECT jsonb_build_object(
      'mailing_address', vet.contact_info.mailing_address
    )
    FROM vet.contact_info
    WHERE vet.contact_info.person_id = vet.person.id
  )
) 
FROM vet.person
WHERE id = '29168a93-cd14-478f-8c70-a2b7a782c714';