-- abono's tables. Timestamps are DATETIME, not TEXT: modernc.org/sqlite
-- decodes a stored timestamp into a time.Time only when the column's
-- declared type is DATE, DATETIME or TIMESTAMP.
--
-- No foreign key reaches an app's identity table: `subject` is the
-- rastrillo session subject, and apps own their identity schema.

-- The plans an app sells. `plan` is the app's own name for it.
CREATE TABLE abono_offers (
    plan        TEXT     NOT NULL PRIMARY KEY,
    name        TEXT     NOT NULL DEFAULT '',
    created_at  DATETIME NOT NULL,
    archived_at DATETIME
);

-- The Stripe prices that belong to a plan. `sold` marks a price the
-- app offers to new buyers; a price no longer sold stays, so that
-- existing subscriptions on it still resolve to their plan. A price
-- bills every `interval_count` `interval`s, as Stripe last said (0 until
-- read); v0.1 sells only every one month or one year.
CREATE TABLE abono_prices (
    stripe_price_id TEXT     NOT NULL PRIMARY KEY,
    plan            TEXT     NOT NULL REFERENCES abono_offers(plan),
    sold            INTEGER  NOT NULL DEFAULT 0,
    interval        TEXT     NOT NULL DEFAULT '',
    interval_count  INTEGER  NOT NULL DEFAULT 0,
    amount          INTEGER  NOT NULL DEFAULT 0,
    currency        TEXT     NOT NULL DEFAULT '',
    refreshed_at    DATETIME
);
CREATE INDEX abono_prices_plan ON abono_prices(plan);

-- Which Stripe customer is which subject.
CREATE TABLE abono_customers (
    stripe_customer_id TEXT NOT NULL PRIMARY KEY,
    subject            TEXT NOT NULL
);
CREATE INDEX abono_customers_subject ON abono_customers(subject);

-- Stripe's subscriptions, as last reported. plan = '' means the price
-- was not recognised. subject = '' with `email` set means unclaimed:
-- paid for, not yet tied to a signed-in person. unpaid_since is the
-- first unpaid day abono saw (the unpaid period's start), kept while the
-- subscription stays past_due or unpaid and cleared when it is paid: the
-- 30 days of past_due count from it, never from a later period's start.
-- subject_pinned is set when the subject was decided explicitly (an
-- owner's Adopt for a subject, a reader's Claim): from then on a hint
-- from Checkout or Stripe's metadata never changes it.
CREATE TABLE abono_subscriptions (
    stripe_subscription_id TEXT     NOT NULL PRIMARY KEY,
    stripe_customer_id     TEXT     NOT NULL,
    subject                TEXT     NOT NULL DEFAULT '',
    subject_pinned         INTEGER  NOT NULL DEFAULT 0,
    plan                   TEXT     NOT NULL DEFAULT '',
    stripe_price_id        TEXT     NOT NULL DEFAULT '',
    status                 TEXT     NOT NULL,
    current_period_start   DATETIME,
    current_period_end     DATETIME,
    cancel_at_period_end   INTEGER  NOT NULL DEFAULT 0,
    unpaid_since           DATETIME,
    email                  TEXT     NOT NULL DEFAULT '',
    created_at             DATETIME NOT NULL,
    updated_at             DATETIME NOT NULL
);
CREATE INDEX abono_subscriptions_subject  ON abono_subscriptions(subject);
CREATE INDEX abono_subscriptions_customer ON abono_subscriptions(stripe_customer_id);

-- Plans an operator gives a subject without payment. A comp, not a
-- grant, so it is never confused with "grants access".
CREATE TABLE abono_comps (
    id         INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    subject    TEXT     NOT NULL,
    plan       TEXT     NOT NULL,
    status     TEXT     NOT NULL CHECK (status IN ('active', 'canceled')),
    ends_at    DATETIME,
    note       TEXT     NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);
CREATE INDEX abono_comps_subject ON abono_comps(subject);

-- Stripe webhook events already handled, so a redelivery is a no-op.
CREATE TABLE abono_events (
    id          TEXT     NOT NULL PRIMARY KEY,
    type        TEXT     NOT NULL,
    received_at DATETIME NOT NULL
);