-- ============================================
-- Bone Appétit – Database Schema
-- Compatible with PostgreSQL / Supabase
-- ============================================

-- 1. species
create table species (
  id int8 generated always as identity primary key,
  common_name text not null,
  scientific_name text not null
);

-- 2. cohort
create table cohort (
  id int8 generated always as identity primary key,
  name text not null
);

-- 3. person
create table person (
  id int8 generated always as identity primary key,
  first_name text not null,
  last_name text not null,
  email text not null
);

-- 4. member_of (many-to-many: person <-> cohort)
--    Attributes from diagram: date_invited, date_left (dotted = nullable),
--    is_membership_accepted, is_membership_paused, administers (boolean)
create table member_of (
  id int8 generated always as identity primary key,
  person_id int8 not null references person(id),
  cohort_id int8 not null references cohort(id),
  date_invited timestamptz not null default now(),
  date_left timestamptz,
  is_membership_accepted bool not null default false,
  is_membership_paused bool not null default false,
  administers bool not null default false
);

-- 5. creature
--    date_of_passing is dotted = nullable
create table creature (
  id int8 generated always as identity primary key,
  name text not null,
  date_of_birth timestamptz,
  date_of_passing timestamptz,
  species_id int8 not null references species(id),
  cohort_id int8 not null references cohort(id)
);

-- 6. frequency
create table frequency (
  id int8 generated always as identity primary key,
  description text not null
);

-- 7. meal
create table meal (
  id int8 generated always as identity primary key,
  description text not null,
  quantity numeric not null,
  units text not null,
  is_active bool not null default true,
  creature_id int8 not null references creature(id)
);

-- 8. planned_feeding
create table planned_feeding (
  id int8 generated always as identity primary key,
  time_unit int8 not null,
  is_active bool not null default true,
  meal_id int8 not null references meal(id),
  frequency_id int8 not null references frequency(id)
);

-- 9. feeding_event
--    created_to_occur_at_time_and_date: when it should happen
--    actually_carried_out_at_time_and_date: when someone did it (nullable until completed)
create table feeding_event (
  id int8 generated always as identity primary key,
  created_to_occur_at_time_and_date timestamptz not null,
  actually_carried_out_at_time_and_date timestamptz,
  planned_feeding_id int8 not null references planned_feeding(id),
  completed_by_person_id int8 references person(id)
);

-- ============================================
-- Sample Data
-- ============================================

-- Species
insert into species (common_name, scientific_name) values
  ('Dog', 'Canis lupus familiaris'),
  ('Cat', 'Felis catus'),
  ('Snake', 'Serpentes');

-- Cohorts
insert into cohort (name) values
  ('Gordon Family'),
  ('Lakefield Animal Welfare Society');

-- Persons (10)
insert into person (first_name, last_name, email) values
  ('Russ', 'Gordon', 'russ@example.com'),
  ('Jen', 'Gordon', 'jen@example.com'),
  ('Claire', 'Gordon', 'claire@example.com'),
  ('Alice', 'Chen', 'alice.chen@example.com'),
  ('Bob', 'Patel', 'bob.patel@example.com'),
  ('Carlos', 'Rivera', 'carlos.rivera@example.com'),
  ('Diana', 'Thompson', 'diana.thompson@example.com'),
  ('Ethan', 'Nguyen', 'ethan.nguyen@example.com'),
  ('Fiona', 'Walsh', 'fiona.walsh@example.com'),
  ('George', 'Kim', 'george.kim@example.com');

-- Memberships
-- Gordon Family: Russ (admin), Jen, Claire
insert into member_of (person_id, cohort_id, date_invited, is_membership_accepted, administers) values
  (1, 1, '2025-01-01 00:00:00-05', true, true),
  (2, 1, '2025-01-01 00:00:00-05', true, false),
  (3, 1, '2025-01-01 00:00:00-05', true, false);

-- LAWS members
insert into member_of (person_id, cohort_id, date_invited, is_membership_accepted, administers) values
  (4, 2, '2025-02-01 00:00:00-05', true, true),
  (5, 2, '2025-02-01 00:00:00-05', true, false),
  (6, 2, '2025-02-15 00:00:00-05', true, false),
  (7, 2, '2025-03-01 00:00:00-05', true, false),
  (8, 2, '2025-03-01 00:00:00-05', true, false),
  (9, 2, '2025-03-10 00:00:00-05', true, false),
  (10, 2, '2025-04-01 00:00:00-05', false, false);

-- Creatures
-- Gordon Family: Piper (dog) and Shadow (cat)
insert into creature (name, date_of_birth, species_id, cohort_id) values
  ('Piper', '2022-04-15 00:00:00-04', 1, 1),
  ('Shadow', '2021-09-01 00:00:00-04', 2, 1);

-- LAWS creatures
insert into creature (name, date_of_birth, species_id, cohort_id) values
  ('Biscuit', '2023-06-10 00:00:00-04', 1, 2),
  ('Maple', '2020-11-20 00:00:00-05', 1, 2),
  ('Whiskers', '2022-01-05 00:00:00-05', 2, 2),
  ('Luna', '2023-03-14 00:00:00-04', 2, 2),
  ('Monty', '2021-07-22 00:00:00-04', 3, 2),
  ('Slinky', '2024-02-28 00:00:00-05', 3, 2);

-- Frequency (with explicit ids via overriding system value)
insert into frequency overriding system value (id, description) values
  (1, 'hourly'),
  (2, 'daily'),
  (3, 'biweekly'),
  (4, 'weekly'),
  (5, 'monthly'),
  (6, 'annually');

-- Reset the frequency identity sequence
select setval(pg_get_serial_sequence('frequency', 'id'), 6);

-- Meals
-- Piper: kibble and Puppucino
insert into meal (description, quantity, units, creature_id) values
  ('Kibble', 1.5, 'scoops', 1),
  ('Puppucino', 1, 'cup', 1);

-- Shadow
insert into meal (description, quantity, units, creature_id) values
  ('Wet food', 1, 'can', 2);

-- LAWS creatures
insert into meal (description, quantity, units, creature_id) values
  ('Kibble', 2, 'scoops', 3),
  ('Kibble', 2.5, 'scoops', 4),
  ('Wet food', 1, 'can', 5),
  ('Wet food', 1, 'can', 6),
  ('Frozen mouse', 1, 'piece', 7),
  ('Frozen mouse', 1, 'piece', 8);

-- Planned Feedings
-- Piper kibble: daily at 7, 11, 15, 19
insert into planned_feeding (time_unit, meal_id, frequency_id) values
  (7, 1, 2),
  (11, 1, 2),
  (15, 1, 2),
  (19, 1, 2);

-- Piper Puppucino: annually in August (month 8)
insert into planned_feeding (time_unit, meal_id, frequency_id) values
  (8, 2, 6);

-- Shadow wet food: daily at 8 and 18
insert into planned_feeding (time_unit, meal_id, frequency_id) values
  (8, 3, 2),
  (18, 3, 2);

-- Some LAWS planned feedings
insert into planned_feeding (time_unit, meal_id, frequency_id) values
  (8, 4, 2),
  (17, 4, 2),
  (8, 5, 2),
  (17, 5, 2),
  (7, 6, 2),
  (18, 6, 2),
  (7, 7, 2),
  (18, 7, 2),
  (10, 8, 4),
  (10, 9, 4);

-- Sample feeding events for Piper's 7 AM kibble (planned_feeding id 1)
insert into feeding_event (created_to_occur_at_time_and_date, actually_carried_out_at_time_and_date, planned_feeding_id, completed_by_person_id) values
  ('2026-04-10 07:00:00-04', '2026-04-10 07:12:00-04', 1, 1),
  ('2026-04-10 11:00:00-04', '2026-04-10 11:05:00-04', 2, 2),
  ('2026-04-10 15:00:00-04', '2026-04-10 15:20:00-04', 3, 3),
  ('2026-04-10 19:00:00-04', '2026-04-10 19:03:00-04', 4, 1),
  ('2026-04-11 07:00:00-04', null, 1, null),
  ('2026-04-11 11:00:00-04', null, 2, null),
  ('2026-04-11 15:00:00-04', null, 3, null),
  ('2026-04-11 19:00:00-04', null, 4, null);
