quetzal.sql

-- Dropping existing views
DROP VIEW IF EXISTS view_people_addresses;
DROP VIEW IF EXISTS view_people_phone_numbers;
DROP VIEW IF EXISTS view_people_emails;

-- Dropping existing tables
DROP TABLE IF EXISTS people_email_mapping CASCADE;
DROP TABLE IF EXISTS people_phone_number_mapping CASCADE;
DROP TABLE IF EXISTS people_address_mapping CASCADE;
DROP TABLE IF EXISTS people_household_mapping CASCADE;
DROP TABLE IF EXISTS days_of_week CASCADE;
DROP TABLE IF EXISTS account_types CASCADE;
DROP TABLE IF EXISTS accounts CASCADE;
DROP TABLE IF EXISTS banks CASCADE;
DROP TABLE IF EXISTS people CASCADE;
DROP TABLE IF EXISTS bills CASCADE;
DROP TABLE IF EXISTS my_bills CASCADE;
DROP TABLE IF EXISTS vendors CASCADE;
DROP TABLE IF EXISTS vendor_categories CASCADE;
DROP TABLE IF EXISTS bill_instance_states CASCADE;
DROP TABLE IF EXISTS frequency_patterns CASCADE;
DROP TABLE IF EXISTS events CASCADE;
DROP TABLE IF EXISTS my_events CASCADE;
DROP TABLE IF EXISTS event_states CASCADE;
DROP TABLE IF EXISTS event_types CASCADE;
DROP TABLE IF EXISTS event_domains CASCADE;
DROP TABLE IF EXISTS emails CASCADE;
DROP TABLE IF EXISTS phone_numbers CASCADE;
DROP TABLE IF EXISTS labels CASCADE;
DROP TABLE IF EXISTS addresses CASCADE;
DROP TABLE IF EXISTS people CASCADE;
DROP TABLE IF EXISTS household CASCADE;
DROP TABLE IF EXISTS bank_transaction_log CASCADE;

-- Revoke all privileges in this database from quetzal_user
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM quetzal_user;

-- Revoke all privileges on all tables in the public schema (or any other schemas they have access to)
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM quetzal_user;

DROP USER quetzal_user;

CREATE USER 'quetzal_user' WITH PASSWORD 'RfZSQ$St{c=Ujko]';
CREATE USER 'adge' WITH PASSWORD 'VanFiry!78ox08'

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    user_name TEXT NOT NULL UNIQUE,
    name_prefix VARCHAR(255),
    first_name TEXT NOT NULL,
    middle_name VARCHAR(255),
    last_name TEXT NOT NULL,
    name_suffix VARCHAR(255),
    dob TIMESTAMPTZ,
    user_password VARCHAR(255),
    on_create_action VARCHAR(255),
    on_update_action VARCHAR(255),
    on_delete_action VARCHAR(255),
    on_complete_action VARCHAR(255),
    on_cancel_action VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    address VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    zip VARCHAR(255),
    county VARCHAR(255),
    country VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE household (
    id SERIAL PRIMARY KEY,
    household_name VARCHAR(255),
    address_id INTEGER REFERENCES addresses(id),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE people_household_mapping (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES people(id),
    household_id INTEGER REFERENCES household(id),
    hoh INTEGER DEFAULT 0,
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE labels (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE,
    description VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE phone_numbers (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE emails (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE event_domains (
    id SERIAL PRIMARY KEY,
    domain_name VARCHAR(255) UNIQUE,
    domain_description VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE event_types (
    id SERIAL PRIMARY KEY,
    event_domain_id INTEGER REFERENCES event_domains(id),
    type_name VARCHAR(255) UNIQUE,
    type_description VARCHAR(255),
    data_table_name VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE event_states (
    id SERIAL PRIMARY KEY,
    event_type_id INTEGER REFERENCES event_types(id),
    state_name VARCHAR(255),
    state_description VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE days_of_week (
    id SERIAL PRIMARY KEY,
    day_name VARCHAR(255),
    day_number INTEGER,
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE frequency_patterns (
    id SERIAL PRIMARY KEY,
    pattern_name VARCHAR(255),
    pattern_description VARCHAR(255),
    pattern_type VARCHAR(255),
    pattern_value VARCHAR(255),
    pattern_start_date TIMESTAMPTZ,
    pattern_end_date TIMESTAMPTZ,
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE my_events (
    id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(id),
    notes VARCHAR(255),
    tags VARCHAR(255),
    my_on_create_action VARCHAR(255),
    my_on_complete_action VARCHAR(255),
    my_on_cancel_action VARCHAR(255),
    my_on_update_action VARCHAR(255),
    my_on_delete_action VARCHAR(255),
    opt_config JSON
);

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    description VARCHAR(255),
    creator_id INTEGER REFERENCES people(id),
    owner_id INTEGER REFERENCES people(id),
    event_type_id INTEGER REFERENCES event_types(id),
    event_domain_id INTEGER REFERENCES event_domains(id),
    event_state_id INTEGER REFERENCES event_states(id),
    all_day INTEGER CHECK (all_day IN (0, 1)),
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,
    frequency_pattern_id INTEGER REFERENCES frequency_patterns(id),
    frequency_value INTEGER CHECK (frequency_value >= 0),
    day_of_week_id INTEGER,
    week_of_month INTEGER,
    on_create_action VARCHAR(255),
    on_update_action VARCHAR(255),
    on_delete_action VARCHAR(255),
    on_complete_action VARCHAR(255),
    on_cancel_action VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE bill_instance_states (
    id SERIAL PRIMARY KEY,
    state_name VARCHAR(255),
    state_description VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE vendor_categories (
    id SERIAL PRIMARY KEY,
    category_name VARCHAR(255),
    category_description VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE vendors (
    id SERIAL PRIMARY KEY,
    vendor_name VARCHAR(255),
    vendor_category_id INTEGER REFERENCES vendor_categories(id),
    address VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    zip VARCHAR(255),
    vendor_phone_number VARCHAR(255),
    vendor_website VARCHAR(255),
    vendor_notes VARCHAR(255),
    services_provided VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE account_types (
    account_type_id int PRIMARY KEY,
    account_type varchar(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE banks (
    id serial primary key,
    bank varchar(255),
    routing_num varchar(25),
    address varchar(255),
    address2 varchar(255),
    city varchar(255),
    state varchar(2),
    zip varchar(5),
    phone varchar(15),
    url varchar(255),
    sms_notifications varchar(100),
    sms_inquiry varchar(100),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE accounts (
    id int PRIMARY KEY,
    person_id int REFERENCES people(id),
    bank_id int,
    account_number varchar(255),
    account_type_id int,
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE my_bills (
    id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(id),
    vendor_id INTEGER REFERENCES vendors(id),
    amount NUMERIC(10,2),
    bill_instance_state_id INTEGER REFERENCES bill_instance_states(id),
    auto_pay INTEGER DEFAULT 0,
    auto_pay_account_id INTEGER REFERENCES accounts(id),
    payment_account_used_id INTEGER REFERENCES accounts(id),
    payment_amount NUMERIC(10,2),
    user_action VARCHAR(255),
    on_create_action VARCHAR(255),
    on_complete_action VARCHAR(255),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

--CREATE VIEW view_my_bill AS
-- SELECT 
--     mb.id,
--     mb.event_id,
--     mb.vendor_id,
--     v.vendor_name,
--     e.name,
--     e.description,
--     mb.amount_owed,
--     mb.amount_paid,
--     e.on_complete_action,
--     e.on_cancel_action,
--     e.on_update_action,
--     e.on_delete_action,
--     e.on_create_action,
-- FROM my_bills mb
-- INNER JOIN events e ON my_bills.event_id = events.id
-- INNER JOIN vendors v ON my_bills.vendor_id = vendors.id
-- INNER JOIN bill_instance_states bis ON my_bills.bill_instance_state_id = bill_instance_states.id
-- INNER JOIN accounts acct_auto ON my_bills.auto_pay_account_id = accounts.id
-- INNER JOIN accounts acct_manual ON my_bills.payment_account_used_id = accounts.id
-- ;
--CREATE VIEW view_my_event AS;
-- name VARCHAR(255),
--     description VARCHAR(255),
--     creator_id INTEGER REFERENCES people(id),
--     owner_id INTEGER REFERENCES people(id),
--     event_type_id INTEGER REFERENCES event_types(id),
--     event_domain_id INTEGER REFERENCES event_domains(id),
--     event_state_id INTEGER REFERENCES event_states(id),
--     all_day INTEGER CHECK (all_day IN (0, 1)),
--     start_time TIMESTAMPTZ,
--     end_time TIMESTAMPTZ,
--     frequency_pattern_id INTEGER REFERENCES frequency_patterns(id),
--     frequency_value INTEGER CHECK (frequency_value >= 0),
--     day_of_week_id INTEGER,
--     week_of_month INTEGER,
--     on_create_action VARCHAR(255),
--     on_update_action VARCHAR(255),
--     on_delete_action VARCHAR(255),
--     on_complete_action VARCHAR(255),
--     on_cancel_action VARCHAR(255),


CREATE TABLE bills (
    id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(id),
    vendor_id INTEGER REFERENCES vendors(id),
    amount NUMERIC(10,2),
    bill_instance_state_id INTEGER REFERENCES bill_instance_states(id),
    auto_pay INTEGER DEFAULT 0,
    auto_pay_account_id INTEGER REFERENCES accounts(id),
    payment_account_used_id INTEGER REFERENCES accounts(id),
    payment_amount NUMERIC(10,2),
    user_action VARCHAR(255),
    on_create_action VARCHAR(255),
    on_complete_action VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

-- CREATE TRIGGER bills_on_state_change BEFORE UPDATE ON bills FOR EACH ROW EXECUTE PROCEDURE bills_on_state_change();
-- CREATE PROCEDURE bills_on_state_change() LANGUAGE plpgsql AS $$



CREATE TABLE bank_transaction_log (
    id SERIAL PRIMARY KEY,
    date TIMESTAMPTZ,
    transaction_type VARCHAR(255),
    memo VARCHAR(255),
    amount NUMERIC(10,2),
    bank_id INTEGER REFERENCES banks(id),
    other_data VARCHAR(255),
    bill_flag INTEGER DEFAULT 0,
    bill_id INTEGER REFERENCES bills(id),
    bill_payment_confirmed INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE people_address_mapping (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES people(id),
    address_id INTEGER REFERENCES addresses(id),
    label_id INTEGER REFERENCES labels(id),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE people_phone_number_mapping (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES people(id),
    phone_number_id INTEGER REFERENCES phone_numbers(id),
    label_id INTEGER REFERENCES labels(id),
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    additional_attributes JSON
);

CREATE TABLE people_email_mapping (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES people(id),
    email_id INTEGER REFERENCES emails(id),
    label_id INTEGER REFERENCES labels(id),
    primary_email INTEGER DEFAULT 0,
    active INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-----------------
-- Fact Tables --
-----------------
-- Inserting data into 'people' table
INSERT INTO people (user_name, name_prefix, first_name, middle_name, last_name, dob) VALUES
  ('Troy', 'Mr.', 'Troy', 'Harold', 'Middleton', '1977-05-25T08:45:22-0500'),
  ('Saray', 'Mrs.', 'Sarah', 'Kathleen', 'Middleton', '1978-04-11T14:02:33-0400'),
  ('Charlie', 'Mr.', 'Charles', 'Joseph', 'Middleton', '2010-09-08T14:39:11-0400'),
  ('Linda', 'Ms.', 'Linda', 'Maria', 'Ryan', '2000-01-29T11:28:13-0500')
;


------------------------
-- Dimensional Tables --
------------------------

-- Inserting data into 'addresses' table
INSERT INTO addresses (address, city, state, zip, county, country) VALUES
  ('46 Tutor Road', 'Oxville', 'WY', '54744', 'Winchester', 'United States'),
  ('12 W Center Street', 'Norville', 'WY', '54748', 'Winchester', 'United States');

-- Inserting data into 'phone_numbers' table
INSERT INTO phone_numbers (phone_number) VALUES
  (''),
  ('233-987-8407'),
  ('233-966-0012'),
  ('233-987-6458'),
  ('233-966-6521');

-- Inserting data into 'emails' table
INSERT INTO emails (email) VALUES
  (''),
  ('troy.middleton@gmail.com'),
  ('sarah.middleton@gmail.com'),
  ('charlie.middleton@gmail.com'),
  ('lryan2352@gmail.com')
;

INSERT INTO household (household_name, address_id) VALUES
    (''),
    ('Middleton Household', 1),
    ('Ryan Household', 2);

INSERT INTO people_household_mapping (person_id, household_id, hoh) VALUES
    (1, 2, 1),
    (2, 2, 0),
    (3, 2, 0),
    (4, 3, 1);

INSERT INTO labels (name, description) VALUES
  ('none','none'),
  ('Home', 'Home Information'),
  ('Mobile', 'Mobile Information'),
  ('Work', 'Work Information'),
  ('School', 'School Information'),
  ('Office', 'Other Information'),
  ('Main', 'Main Information'),
  ('Other', 'Other Information');

-- Inserting data into 'event_domains' table
INSERT INTO event_domains (domain_name, domain_description) VALUES
    ('none','none'),
    ('Home', 'Home-related events'),
    ('Health', 'Health-related events'),
    ('Financial', 'Financial events'),          -- bills, transactions, etc.
    ('Social', 'Social events'),
    ('School', 'School-related events'),
    ('Sports', 'Sports-related events'),
    ('Travel', 'Travel-related events'),
    ('Technology', 'Technology-related events'),
    ('Work', 'Work-related events');

-- Inserting data into 'event_types' table
INSERT INTO event_types (type_name, data_table_name) VALUES
    ('event', 'events'),
    ('recurring bill', 'bills'),
    ('transaction', 'transactions'),
    ('appointment', 'appointments'),
    ('chore', 'chores'),
    ('meeting', 'meetings'),
    ('reminder', 'reminders'),
    ('task', 'tasks'),
    ('workout', 'workouts');

INSERT INTO event_states (event_type_id, state_name, state_description) VALUES
    (1, 'created', 'The newest instance of the bill has been created'),
    (1, 'paid-pending', 'Bill is marked as paid, but not yet recognized as paid in the bank statements - but not late yet'),
    (1, 'paid', 'Bill is marked as paid and recognized as paid in the bank statements, both on time'),
    (1, 'unpaid', 'Bill is marked as unpaid, and not yet recognized as paid in the bank statements'),
    (1, 'late-unpaid', 'Bill is marked as unpaid and has not shown up in the bank statements'),
    (1, 'late-paid-pending', 'Bill is marked as paid, but not yet recognized as paid in the bank statements'),
    (1, 'late-paid', 'Bill is marked as paid and has been recognized as paid in the bank statements'),
    (1, 'override', 'A manual override has been applied to the bill instance'),
    (1, 'finished', 'The event has passed');

INSERT INTO days_of_week (day_name, day_number) VALUES
    ('Sunday', 0),
    ('Monday', 1),
    ('Tuesday', 2),
    ('Wednesday', 3),
    ('Thursday', 4),
    ('Friday', 5),
    ('Saturday', 6);
INSERT INTO frequency_patterns (pattern_name, pattern_description) VALUES
    ('day', 'n per day'),
    ('days', 'every n days'),
    ('week', 'n per week'),
    ('weeks', 'every n weeks'),
    ('month', 'n per month'),
    ('months', 'every n months'),
    ('year', 'n per year'),
    ('years', 'every n years')
;

INSERT INTO my_events (name, description, creator_id, owner_id, event_type_id, event_domain_id, all_day, start_time, end_time, frequency_pattern_id, frequency_value, day_of_week_id, week_of_month, on_create_action, on_complete_action) VALUES
    ('NYSEG', 'Electricity Bill', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('AT&T', 'Mobile Phone Bill', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Netflix', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Amazon Prime', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Hulu', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Disney+', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('HBO Max', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('YouTube TV', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2021-09-01T00:00:00-0400', 2, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Apple TV+', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2022-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Discovery+', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2022-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Paramount+', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2022-09-01T00:00:00-0400', 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Peacock', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', '2022-09-01T00:00:00-0400', 6, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Spotify', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 7, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Pandora', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('SiriusXM', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Apple Music', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Tidal', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Amazon Music', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('YouTube Music', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Audible', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Kindle Unlimited', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 7, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Google Play Music', 'Streaming Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 6, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Google One', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 7, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('iCloud', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 8, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Dropbox', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 5, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('OneDrive', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('Google Drive', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 1, 1, 0, 0, 'create_bill', 'mark_bill_paid'),
    ('iCloud Drive', 'Storage Service', 1, 1, 1, 3, 0, '2021-09-01T00:00:00-0400', NULL, 2, 1, 0, 0, 'create_bill', 'mark_bill_paid');

-- INSERT INTO events (name, description, creator_id, owner_id, event_type_id, event_domain_id, all_day, start_time, end_time, frequency_pattern_id, frequency_value, day_of_week_id, week_of_month, on_create_action, on_complete_action) VALUES

-- Inserting data into 'bill_instance_states' table
INSERT INTO bill_instance_states (state_name, state_description) VALUES
    ('scheduled', 'The newest instance of the bill has been scheduled'),
    ('paid-pending', 'Bill is marked as paid, but not yet recognized as paid in the bank statements - but not late yet'),
    ('paid', 'Bill is marked as paid and recognized as paid in the bank statements, both on time'),
    ('unpaid', 'Bill is marked as unpaid, and not yet recognized as paid in the bank statements'),
    ('late-unpaid', 'Bill is marked as unpaid and has not shown up in the bank statements'),
    ('late-paid-pending', 'Bill is marked as paid, but not yet recognized as paid in the bank statements'),
    ('late-paid', 'Bill is marked as paid and has been recognized as paid in the bank statements'),
    ('override', 'A manual override has been applied to the bill instance')
;

-- Inserting into vendor_categories table
INSERT INTO vendor_categories (category_name, category_description) VALUES
    ('Utility', ''),
    ('Entertainment', ''),
    ('Food', ''),
    ('Shopping', ''),
    ('Education', ''),
    ('Health', ''),
    ('Transportation', ''),
    ('Insurance', ''),
    ('Banking', ''),
    ('Auto', ''),
    ('Taxes', ''),
    ('Other', '');

-- Inserting into vendors table
INSERT INTO vendors (vendor_name, vendor_category_id, address, city, state, zip, vendor_phone_number, vendor_website, vendor_notes, services_provided) VALUES
    ('NYSEG', 1, '18 Eaton Avenue', 'Norwich', 'NY', '13815', '800-572-1111', 'https://www.nyseg.com/', 'NYSEG is a utility company that provides electricity and natural gas to the area', 'Electricity'),
    ('AT&T', 1, '18 Eaton Avenue', 'Norwich', 'NY', '13815', '800-331-0500', 'https://www.att.com/', 'AT&T is a utility company that provides telephone and internet services to the area', 'Mobile Phones'),
    ('Netflix', 2, '100 Winchester Circle', 'Los Gatos', 'CA', '95032', '888-638-3549', 'https://www.netflix.com/', 'Netflix is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Amazon Prime', 2, '410 Terry Avenue North', 'Seattle', 'WA', '98109', '888-280-4331', 'https://www.amazon.com/', 'Amazon Prime is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Hulu', 2, '2500 Broadway', 'Santa Monica', 'CA', '90404', '888-265-6650', 'https://www.hulu.com/', 'Hulu is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Disney+', 2, '500 South Buena Vista Street', 'Burbank', 'CA', '91521', '888-905-7888', 'https://www.disneyplus.com/', 'Disney+ is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('HBO Max', 2, '30 Hudson Yards', 'New York', 'NY', '10001', '888-512-8660', 'https://www.hbomax.com/', 'HBO Max is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('YouTube TV', 2, '901 Cherry Avenue', 'San Bruno', 'CA', '94066', '888-600-6076', 'https://tv.youtube.com/', 'YouTube TV is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Apple TV+', 2, '1 Apple Park Way', 'Cupertino', 'CA', '95014', '888-600-6076', 'https://www.apple.com/apple-tv-plus/', 'Apple TV+ is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Discovery+', 2, '230 Park Avenue', 'New York', 'NY', '10169', '888-600-6076', 'https://www.discoveryplus.com/', 'Discovery+ is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Paramount+', 2, '1515 Broadway', 'New York', 'NY', '10036', '888-600-6076', 'https://www.paramountplus.com/', 'Paramount+ is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Peacock', 2, '30 Rockefeller Plaza', 'New York', 'NY', '10112', '888-600-6076', 'https://www.peacocktv.com/', 'Peacock is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Spotify', 2, '45 West 18th Street', 'New York', 'NY', '10011', '888-600-6076', 'https://www.spotify.com/', 'Spotify is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Pandora', 2, '2101 Webster Street', 'Oakland', 'CA', '94612', '888-600-6076', 'https://www.pandora.com/', 'Pandora is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('SiriusXM', 2, '1221 Avenue of the Americas', 'New York', 'NY', '10020', '888-600-6076', 'https://www.siriusxm.com/', 'SiriusXM is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Apple Music', 2, '1 Apple Park Way', 'Cupertino', 'CA', '95014', '888-600-6076', 'https://www.apple.com/apple-music/', 'Apple Music is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Tidal', 2, '529 Broadway', 'New York', 'NY', '10012', '888-600-6076', 'https://www.tidal.com/', 'Tidal is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Amazon Music', 2, '410 Terry Avenue North', 'Seattle', 'WA', '98109', '888-600-6076', 'https://www.amazon.com/music/unlimited', 'Amazon Music is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('YouTube Music', 2, '901 Cherry Avenue', 'San Bruno', 'CA', '94066', '888-600-6076', 'https://music.youtube.com/', 'YouTube Music is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Audible', 2, '1 Washington Place', 'Newark', 'NJ', '07102', '888-600-6076', 'https://www.audible.com/', 'Audible is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Kindle Unlimited', 2, '410 Terry Avenue North', 'Seattle', 'WA', '98109', '888-600-6076', 'https://www.amazon.com/kindle-dbs/hz/signup', 'Kindle Unlimited is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Xbox Game Pass', 2, '1 Microsoft Way', 'Redmond', 'WA', '98052', '888-600-6076', 'https://www.xbox.com/en-US/xbox-game-pass', 'Xbox Game Pass is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('PlayStation Now', 2, '2207 Bridgepointe Parkway', 'San Mateo', 'CA', '94404', '888-600-6076', 'https://www.playstation.com/en-us/ps-now/', 'PlayStation Now is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Nintendo Switch Online', 2, '4600 150th Avenue Northeast', 'Redmond', 'WA', '98052', '888-600-6076', 'https://www.nintendo.com/switch/online-service/', 'Nintendo Switch Online is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('Nintendo Switch Online Individual', 2, '4600 150th Avenue Northeast', 'Redmond', 'WA', '98052', '888-600-6076', 'https://www.nintendo.com/switch/online-service/', 'Nintendo Switch Online Individual is a streaming service that provides entertainment to the area', 'Streaming Services'),
    ('BlueOx Energy', 1, 'PO Box 100', 'Oxford', 'NY', '13830', '607-843-2341', 'https://www.blueoxcorp.com/', 'BlueOx Energy is a utility company that provides propane to the area', 'Propane'),
    ('ChatGPT', 1, '','','','','', 'https://www.chatgpt.com/', 'ChatGPT is a utility company that provides chatbot services to the area', 'Chatbot Services'),
    ('Oxford Academy and Central School District', 1, '50 South Washington Avenue', 'Oxford', 'NY', '13830', '607-843-2025', 'https://www.oxac.org/', 'Oxford Academy and Central School District is a school district that provides education to the area', 'School Taxes'),
    ('Chenango County', 1, '5 Court Street', 'Norwich', 'NY', '13815', '607-337-1400', 'https://www.co.chenango.ny.us/', 'Chenango County is a county government that provides services to the area', 'Property Taxes'),
    ('Chenango County Clerk', 1, '5 Court Street', 'Norwich', 'NY', '13815', '607-337-1450', 'https://www.co.chenango.ny.us/clerk/', 'Chenango County Clerk is a county government that provides services to the area', 'Government Services'),
    ('Star Link', 1, '','','','','',  'https://www.starlink.com/', 'Star Link is a satellite internet service provider', 'Internet'),
    ('Davis Trucking', 1,  '','','','','', '', 'Davis Trucking is a trucking company that provides services to the area', 'Garbage Removal')
;


-- INSERT INTO my_bills (event_id, vendor_id, amount, bill_instance_state_id, auto_pay, auto_pay_account_id, payment_account_used_id, payment_amount, user_action, on_create_action, on_complete_action) VALUES


-- INSERT INTO bills (event_id, vendor_id, amount, bill_instance_state_id, auto_pay, auto_pay_account_id, payment_account_used_id, payment_amount, user_action, on_create_action, on_complete_action) VALUES

-- Inserting data into 'frequency_patterns' table


INSERT INTO account_types (account_type_id, account_type) VALUES
(1, 'Checking'),
(2, 'Savings'),
(3, '401k'),
(4, 'TSP'),
(5, 'Roth IRA'),
(6, 'Traditional IRA'),
(7, 'Cash'),
(8, 'Mortgage'),
(9, 'Home Equity Line of Credit'),
(10, 'Car Loan'),
(11, 'Personal Loan');

INSERT INTO banks (bank, routing_num, phone, url) VALUES
    ('USAA', '314074269', '800-832-3724', 'https://www.usaa.com/my/logon?logoffjump=true&wa_ref=pub_global_log_on'),
    ('Sunmark Federal Credit Union', '221379824', '866-786-6275', 'https://sunmarkfcu.org'),
    ('Sidney Federal Credit Union', '221379905', '877-642-7328', 'https://sfcuonline.org'),
    ('Advantage Financial Credit Union', '254075470', '800-822-6875', 'https://advfcu.org'),
    ('NBT Bank', '021303618', '800-628-2265', 'https://nbtbank.com'),
    ('SEFCU', '221373383', '800-727-3328', 'https://www.sefcu.com');

INSERT INTO accounts (id, person_id, bank_id, account_number, account_type_id, active) VALUES
    (1, 1, 1, '19290810', 1, 1),
    (2, 1, 1, '284821616', 2, 1),
    (3, 1, 2, '12489001395123-0900', 1, 1),
    (4, 1, 2, '12489001395123-0000', 2, 1),
    (5, 1, 2, '12489001395123-0020', 10, 1),
    (6, 1, 3, '438815-0900', 1, 1),
    (7, 1, 3, '438815-0000', 2, 1),
    (8, 1, 4, '1900000053890', 1, 1),
    (9, 1, 4, '1900000053890', 2, 1),
    (10, 1, 5, '1047612', 1, 1),
    (11, 1,  5, '1047612', 2, 1),
    (12, 1,  6, '420076', 9, 1);

-- Inserting data into 'people_address_mapping' table
INSERT INTO people_address_mapping (person_id, address_id, label_id) VALUES
  (1, 1, 6),
  (1, 1, 3),
  (2, 1, 6),
  (3, 1, 6),
  (4, 2, 6);

-- Inserting data into 'people_phone_number_mapping' table
INSERT INTO people_phone_number_mapping (person_id, phone_number_id, label_id) VALUES
  (1, 1, 1),
  (1, 2, 7),
  (2, 2, 1),
  (2, 1, 7),
  (3, 3, 3),
  (4, 4, 2);

-- Inserting data into 'people_email_mapping' table
INSERT INTO people_email_mapping (person_id, email_id, label_id, primary_email) VALUES
  (1, 1, 1, 1),
  (1, 2, 7, 0),
  (2, 2, 1, 1),
  (2, 1, 7, 0),
  (3, 3, 3, 1),
  (4, 4, 2, 1);

-- create views

CREATE VIEW view_people_addresses AS
SELECT
    p.id as person_id,
    p.user_name,
    p.first_name,
    p.last_name,
    a.id as address_id,
    a.address,
    a.city,
    a.state,
    a.zip,
    a.county,
    a.country,
    l.name AS label_name,
    l.description AS label_description
FROM
    people p
JOIN
    people_address_mapping pam ON p.id = pam.person_id
JOIN
    addresses a ON pam.address_id = a.id
LEFT JOIN
    labels l ON pam.label_id = l.id
WHERE p.active = 1
ORDER BY
    p.user_name, a.city, a.state;


CREATE VIEW view_people_phone_numbers AS
SELECT
    p.id as person_id,
    p.user_name,
    p.first_name,
    p.last_name,
    pn.id as phone_number_id,
    pn.phone_number,
    l.name AS label_name,
    l.description AS label_description
FROM
    people p
JOIN
    people_phone_number_mapping ppnm ON p.id = ppnm.person_id
JOIN
    phone_numbers pn ON ppnm.phone_number_id = pn.id
LEFT JOIN
    labels l ON ppnm.label_id = l.id
WHERE p.active = 1
ORDER BY
    p.user_name, pn.phone_number;


CREATE VIEW view_people_emails AS
SELECT
    p.id as person_id,
    p.user_name,
    p.first_name,
    p.last_name,
    e.id as email_id,
    e.email,
    l.name AS label_name,
    l.description AS label_description,
    CASE
        WHEN pem.primary_email = 1 THEN 'Yes'
        ELSE 'No'
    END AS is_primary_email
FROM
    people p
JOIN
    people_email_mapping pem ON p.id = pem.person_id
JOIN
    emails e ON pem.email_id = e.id
LEFT JOIN
    labels l ON pem.label_id = l.id
WHERE p.active = 1
ORDER BY
    p.user_name, e.email;

-- Create view for bank accounts
CREATE VIEW user_bank_accounts AS
SELECT
    p.id AS person_id,
    p.user_name,
    a.id as account_id,
    a.account_number,
    at.account_type,
    b.bank,
    b.routing_num,
    b.address,
    b.address2,
    b.city,
    b.state,
    b.zip,
    b.phone,
    b.url,
    b.sms_notifications,
    b.sms_inquiry
FROM
    people p
JOIN
    accounts a ON p.id = a.person_id
JOIN
    banks b ON a.bank_id = b.id
LEFT JOIN
    account_types at ON a.account_type_id = at.account_type_id
WHERE
    a.active = 1 AND p.active = 1;


-- CREATE VIEW view_people_medication_groups AS

-- CREATE VIEW view_people_medication_group_members AS

-- CREATE VIEW upcoming_events AS

-- CREATE VIEW upcoming_bills AS