schema.sql

-- Run this first to clear tables and enums
DROP TABLE IF EXISTS roommate, chore, bill, bill_list, chore_assignment CASCADE;

DROP TYPE IF EXISTS frequency_enum, status_enum, bill_type_enum, payment_status_enum CASCADE;


-- Frequency enum used for chore creation. These are the only options, database will throw errors if other inputs are given
CREATE TYPE frequency_enum AS ENUM ('daily', 'weekly', 'biweekly', 'monthly', 'bimonthly', 'yearly', 'no-repeat');

create table
  public.chore (
    id integer generated by default as identity not null,
    created_at timestamptz DEFAULT now(),
    name text null,
    location_in_house text null,
    frequency public.frequency_enum null,
    duration_mins integer null default '0'::integer,
    priority integer null,
    due_date date null,
    constraint chore_pkey primary key (id),
    constraint chore_priority_check check (
      (
        (priority >= 1)
        and (priority <= 5)
      )
    )
  ) tablespace pg_default;

-- Insert pre-populated data into the chore table
INSERT INTO public.chore (name, location_in_house, frequency, duration_mins, priority, due_date)
VALUES 
    ('Mop floors', 'Living Room', 'weekly', 30, 3, '2024-11-01'),
    ('Wash dishes', 'Kitchen', 'daily', 10, 2, '2024-10-28'), 
    ('Vacuum', 'Living Room', 'weekly', 10, 4, '2024-10-5'),
    ('Clean Kitchen', 'Kitchen', 'monthly', 30, 3, '2024-10-12');
ON CONFLICT DO NOTHING;  -- Avoid duplicates if re-run

create table
  public.roommate (
    id integer generated by default as identity not null,
    created_at timestamptz DEFAULT now(),
    first_name text not null,
    last_name text not null,
    email text null,
    constraint roommate_pkey primary key (id)
  ) tablespace pg_default;
  
-- Insert pre-populated data into the roommate table
INSERT INTO public.roommate (id,first_name, last_name, email)
VALUES 
    (100, 'Sarah', 'LeGoat', 'sarah@gmail.com'),
    (101, 'Billy', 'LeGoat', 'billy@gmail.com'),
    (102, 'Jake', 'LeGoat', 'jake@gmail.com');
ON CONFLICT DO NOTHING;  -- Avoid duplicates if re-run

-- status enum used in chore assignment
CREATE TYPE status_enum AS ENUM ('pending', 'in_progress', 'completed');

create table
  public.chore_assignment (
    id integer generated by default as identity not null,
    created_at timestamptz DEFAULT now(),
    chore_id integer null,
    roommate_id integer null,
    status public.status_enum null,
    constraint chore_assignment_pkey primary key (id),
    constraint chore_assignment_chore_id_fkey foreign key (chore_id) references chore (id) ON DELETE SET NULL,
    constraint chore_assignment_roommate_id_fkey foreign key (roommate_id) references roommate (id) ON DELETE SET NULL
  ) tablespace pg_default;

CREATE TYPE bill_type_enum AS ENUM ('electricity', 'water', 'internet', 'rent','gas','trash','groceries');

create table
  public.bill (
    id integer generated by default as identity not null,
    created_at timestamptz DEFAULT now(),
    cost real null,
    due_date date null,
    bill_type public.bill_type_enum null,
    message text null,
    constraint bill_pkey primary key (id)
  ) tablespace pg_default;

-- Pre-populate the bill table with initial data
INSERT INTO bill (cost, due_date, bill_type, message) 
VALUES
  (100, '2024-11-06', 'electricity', 'pay up'),
  (1000, '2024-12-01', 'rent', 'pay by updated due date'),
  (300, '2024-11-05', 'internet', 'to pay')
ON CONFLICT DO NOTHING;  -- Avoid duplicates if re-run

create TYPE payment_status_enum AS ENUM('unpaid', 'paid', 'overdue');

create table
  public.bill_list (
    id integer generated by default as identity not null,
    created_at timestamptz DEFAULT now(),
    bill_id integer null,
    roommate_id integer null,
    status public.payment_status_enum null,
    amount real not null default '0'::real,
    constraint bill_list_pkey primary key (id),
    constraint bill_list_bill_id_fkey foreign key (bill_id) references bill (id) ON DELETE SET NULL,
    constraint roommate_id_fkey foreign key (roommate_id) references roommate (id) ON DELETE SET NULL
  ) tablespace pg_default;