-- Project 2 database schema: p2_wallet.sqlite, build c14c2737ec6d
-- ACCTG 6155, Fall 2026. Silicone Motors and its records are fictional.
--
-- The CREATE statements below are read from the database's own sqlite_master
-- when the Project 2 site is built. Money is in integer cents. Dates are
-- YYYY-MM-DD text. A blank value is unknown or not applicable, never zero.
-- The data dictionary says what each column holds.
--
-- Joins declared as foreign keys (column -> the key it references):
--   app_login_days.driver_id -> drivers.driver_id
--   charging_sessions.driver_id -> drivers.driver_id
--   company_notices.driver_id -> drivers.driver_id
--   due_diligence_letters.driver_id -> drivers.driver_id
--   reservation_events.reservation_id -> reservations.reservation_id
--   reservations.driver_id -> drivers.driver_id
--   wallet_ledger.reverses_txn_id -> wallet_ledger.txn_id
--   wallet_ledger.session_id -> charging_sessions.session_id
--   wallet_ledger.driver_id -> drivers.driver_id
--
-- A join the schema does not declare:
--   charging_sessions.txn_id -> wallet_ledger.txn_id  (the values match; it is not declared as a foreign key)
--
-- gl_wallet_control and run_params stand alone: no key joins them to the other tables.
-- _meta and _data_dictionary describe the build and the columns.
--
-- To make an empty copy with the same structure:  sqlite3 empty.sqlite < schema.sql

CREATE TABLE drivers (
  driver_id        INTEGER PRIMARY KEY,
  opened_on        TEXT NOT NULL CHECK (opened_on GLOB '????-??-??'),
  closed_on        TEXT,
  address_line1    TEXT,
  city             TEXT,
  state_raw        TEXT,
  postal_raw       TEXT,
  country_raw      TEXT,
  mail_returned_on TEXT
);

CREATE TABLE app_login_days (
  driver_id  INTEGER NOT NULL REFERENCES drivers(driver_id),
  login_date TEXT NOT NULL CHECK (login_date GLOB '????-??-??'),
  logins     INTEGER NOT NULL CHECK (logins > 0),
  platform   TEXT NOT NULL,
  PRIMARY KEY (driver_id, login_date)
);

CREATE TABLE charging_sessions (
  session_id    INTEGER PRIMARY KEY,
  driver_id     INTEGER NOT NULL REFERENCES drivers(driver_id),
  station_id    TEXT NOT NULL,
  station_state TEXT NOT NULL,
  session_date  TEXT NOT NULL CHECK (session_date GLOB '????-??-??'),
  kwh           REAL NOT NULL CHECK (kwh >= 0),
  txn_id        INTEGER
);

CREATE TABLE company_notices (
  notice_id   INTEGER PRIMARY KEY,
  driver_id   INTEGER NOT NULL REFERENCES drivers(driver_id),
  sent_on     TEXT NOT NULL,
  channel     TEXT NOT NULL CHECK (channel IN ('email','postal','push')),
  notice_type TEXT NOT NULL CHECK (notice_type IN
                ('balance_statement','promo_email','service_alert')),
  bounced     INTEGER NOT NULL DEFAULT 0 CHECK (bounced IN (0,1))
);

CREATE TABLE due_diligence_letters (
  letter_id          INTEGER PRIMARY KEY,
  driver_id          INTEGER NOT NULL REFERENCES drivers(driver_id),
  cycle_year         INTEGER NOT NULL,
  mailed_on          TEXT NOT NULL,
  method             TEXT NOT NULL CHECK (method IN ('first_class','certified')),
  returned_on        TEXT,
  owner_responded_on TEXT
);

CREATE TABLE gl_wallet_control (
  period_end       TEXT NOT NULL,
  gl_account       TEXT NOT NULL,
  gl_balance_cents INTEGER NOT NULL,
  PRIMARY KEY (period_end, gl_account)
);

CREATE TABLE reservation_events (
  event_id       INTEGER PRIMARY KEY,
  reservation_id INTEGER NOT NULL REFERENCES reservations(reservation_id),
  activity_on    TEXT NOT NULL,
  posted_on      TEXT NOT NULL,
  event_type     TEXT NOT NULL CHECK (event_type IN
                   ('deposit','refund','application')),
  amount_cents   INTEGER NOT NULL,
  status_after   TEXT NOT NULL CHECK (status_after IN
                   ('open','converted','cancelled_refunded')),
  source_channel TEXT NOT NULL
);

CREATE TABLE reservations (
  reservation_id INTEGER PRIMARY KEY,
  driver_id      INTEGER NOT NULL REFERENCES drivers(driver_id),
  placed_on      TEXT NOT NULL,
  model          TEXT NOT NULL
);

CREATE TABLE run_params (
  cutoff_date            TEXT NOT NULL,
  observed_through_date  TEXT NOT NULL,
  report_due_date        TEXT NOT NULL,
  diligence_window_open  TEXT NOT NULL,
  diligence_window_close TEXT NOT NULL
);

CREATE TABLE wallet_ledger (
  txn_id          INTEGER PRIMARY KEY,
  driver_id       INTEGER NOT NULL REFERENCES drivers(driver_id),
  session_id      INTEGER REFERENCES charging_sessions(session_id),
  activity_on     TEXT NOT NULL CHECK (activity_on GLOB '????-??-??'),
  posted_on       TEXT NOT NULL CHECK (posted_on GLOB '????-??-??'),
  txn_type        TEXT NOT NULL CHECK (txn_type IN
                    ('load','charge','refund','fee','adjustment','reversal',
                     'promo_grant','promo_redeem','promo_expire')),
  amount_cents    INTEGER NOT NULL,
  source_channel  TEXT NOT NULL CHECK (source_channel IN
                    ('mobile_app','web_portal','kiosk','ivr',
                     'csr_console','batch_job','partner_api')),
  entered_by      TEXT,
  reverses_txn_id INTEGER REFERENCES wallet_ledger(txn_id)
);

-- Build and documentation tables

CREATE TABLE _data_dictionary (
  table_name  TEXT NOT NULL,
  column_name TEXT NOT NULL,
  description TEXT NOT NULL,
  PRIMARY KEY (table_name, column_name)
);

CREATE TABLE _meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);

-- Indexes

CREATE INDEX ix_ses_txn   ON charging_sessions(txn_id);
CREATE INDEX ix_ddl_drv   ON due_diligence_letters(driver_id, cycle_year);
CREATE INDEX ix_resev_res ON reservation_events(reservation_id, activity_on);
CREATE INDEX ix_led_drv   ON wallet_ledger(driver_id, activity_on);
CREATE INDEX ix_led_sess  ON wallet_ledger(session_id);