schema.sql

-- ============================================================================
--  sql-for-agents — the example schema (SQLite)
--  Synthetic data only. No customer data, no production anything.
--  9 tables · 8 foreign keys · 1 many-to-many junction · 1 view
-- ============================================================================

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------- roots ----
-- Three tables that reference nobody. Everything else hangs off these.

CREATE TABLE customers (
  id         INTEGER PRIMARY KEY,
  full_name  TEXT    NOT NULL,
  email      TEXT    NOT NULL UNIQUE,
  city       TEXT    NOT NULL,
  segment    TEXT    NOT NULL CHECK (segment IN ('consumer','business','enterprise')),
  joined_on  TEXT    NOT NULL
);

CREATE TABLE plans (
  id           INTEGER PRIMARY KEY,
  code         TEXT    NOT NULL UNIQUE,
  name         TEXT    NOT NULL,
  monthly_fee  REAL    NOT NULL CHECK (monthly_fee >= 0),
  data_gb      INTEGER NOT NULL,
  minutes      INTEGER NOT NULL,
  is_active    INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE addons (
  id           INTEGER PRIMARY KEY,
  name         TEXT    NOT NULL UNIQUE,
  monthly_fee  REAL    NOT NULL CHECK (monthly_fee >= 0)
);

-- ------------------------------------------------------------ the spine ----
-- A subscription is the join between a person and a price list.

CREATE TABLE subscriptions (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  plan_id      INTEGER NOT NULL REFERENCES plans(id)     ON DELETE RESTRICT,
  msisdn       TEXT    NOT NULL UNIQUE,
  started_on   TEXT    NOT NULL,
  ended_on     TEXT,
  status       TEXT    NOT NULL CHECK (status IN ('active','suspended','closed'))
);

CREATE TABLE tickets (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  opened_at    TEXT    NOT NULL,
  topic        TEXT    NOT NULL,
  severity     INTEGER NOT NULL CHECK (severity BETWEEN 1 AND 4),
  resolved_at  TEXT
);

-- ---------------------------------------------------------- the leaves -----

-- Many-to-many: one subscription can carry several add-ons, one add-on sits on
-- many subscriptions. Composite primary key, two foreign keys, no surrogate id.
CREATE TABLE subscription_addons (
  subscription_id  INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  addon_id         INTEGER NOT NULL REFERENCES addons(id)        ON DELETE CASCADE,
  added_on         TEXT    NOT NULL,
  PRIMARY KEY (subscription_id, addon_id)
);

CREATE TABLE devices (
  id                INTEGER PRIMARY KEY,
  subscription_id   INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  model             TEXT    NOT NULL,
  imei              TEXT    NOT NULL UNIQUE,
  instalments_left  INTEGER NOT NULL DEFAULT 0 CHECK (instalments_left >= 0)
);

CREATE TABLE usage_daily (
  id               INTEGER PRIMARY KEY,
  subscription_id  INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  day              TEXT    NOT NULL,
  data_mb          INTEGER NOT NULL CHECK (data_mb >= 0),
  minutes          INTEGER NOT NULL CHECK (minutes >= 0),
  sms              INTEGER NOT NULL CHECK (sms     >= 0),
  UNIQUE (subscription_id, day)
);

CREATE TABLE invoices (
  id               INTEGER PRIMARY KEY,
  subscription_id  INTEGER NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  period           TEXT    NOT NULL,                     -- 'YYYY-MM'
  amount           REAL    NOT NULL CHECK (amount >= 0),
  status           TEXT    NOT NULL CHECK (status IN ('paid','open','overdue')),
  paid_on          TEXT,
  UNIQUE (subscription_id, period)
);

-- ------------------------------------------------------------- indexes -----
-- SQLite indexes a PRIMARY KEY and a UNIQUE for you. It does NOT index the
-- child side of a foreign key — and that is the classic slow-join surprise.

CREATE INDEX idx_sub_customer   ON subscriptions (customer_id);
CREATE INDEX idx_sub_plan       ON subscriptions (plan_id);
CREATE INDEX idx_dev_sub        ON devices       (subscription_id);
CREATE INDEX idx_usage_sub_day  ON usage_daily   (subscription_id, day);
CREATE INDEX idx_inv_sub        ON invoices      (subscription_id);
CREATE INDEX idx_tickets_cust   ON tickets       (customer_id);
CREATE INDEX idx_subadd_addon   ON subscription_addons (addon_id);

-- ---------------------------------------------------------------- view -----
-- Hand an agent a VIEW, not the base tables: the join is already decided,
-- the columns are already chosen, and the shape cannot be got wrong.

CREATE VIEW v_monthly_bill AS
SELECT
  c.id                                 AS customer_id,
  c.full_name,
  c.city,
  s.msisdn,
  p.name                               AS plan,
  i.period,
  i.amount,
  i.status,
  COALESCE(a.addon_fees, 0)            AS addon_fees,
  ROUND(i.amount + COALESCE(a.addon_fees, 0), 2) AS total_due
FROM invoices i
JOIN subscriptions s ON s.id = i.subscription_id
JOIN customers     c ON c.id = s.customer_id
JOIN plans         p ON p.id = s.plan_id
LEFT JOIN (
  SELECT sa.subscription_id, SUM(ad.monthly_fee) AS addon_fees
  FROM subscription_addons sa
  JOIN addons ad ON ad.id = sa.addon_id
  GROUP BY sa.subscription_id
) a ON a.subscription_id = s.id;