-- metering: squashed from 1600_metering.sql, 1601_metering_reservation_nonce.sql.

CREATE TABLE usage_events (
  idempotency_key TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  quantity REAL NOT NULL,
  repo_id TEXT,
  attributes TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX usage_events_account ON usage_events (account_id, period, meter);

CREATE INDEX usage_events_created ON usage_events (created_at);

CREATE TRIGGER usage_events_rollup AFTER INSERT ON usage_events
BEGIN
  INSERT INTO usage_periods (account_id, meter, period, quantity, updated_at)
    VALUES (NEW.account_id, NEW.meter, NEW.period, NEW.quantity, NEW.created_at)
    ON CONFLICT (account_id, meter, period) DO UPDATE SET quantity = quantity + excluded.quantity,
      updated_at = excluded.updated_at;
  INSERT INTO usage_days (account_id, meter, day, quantity)
    VALUES (NEW.account_id, NEW.meter, strftime('%Y-%m-%d', NEW.created_at / 1000, 'unixepoch'), NEW.quantity)
    ON CONFLICT (account_id, meter, day) DO UPDATE SET quantity = quantity + excluded.quantity;
END;

CREATE TRIGGER usage_events_repo_rollup AFTER INSERT ON usage_events WHEN NEW.repo_id IS NOT NULL
BEGIN
  INSERT INTO usage_repo_periods (account_id, repo_id, meter, period, quantity)
    VALUES (NEW.account_id, NEW.repo_id, NEW.meter, NEW.period, NEW.quantity)
    ON CONFLICT (account_id, repo_id, meter, period) DO UPDATE SET quantity = quantity + excluded.quantity;
END;

-- Counter meters: running totals per period. reserved holds in-flight hard-cap reservations.
CREATE TABLE usage_periods (
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  quantity REAL NOT NULL DEFAULT 0,
  reserved REAL NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, meter, period)
);

CREATE TABLE usage_repo_periods (
  account_id TEXT NOT NULL,
  repo_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  quantity REAL NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, repo_id, meter, period)
);

-- Daily series for the "over time" chart.
CREATE TABLE usage_days (
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  day TEXT NOT NULL,
  quantity REAL NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, meter, day)
);

-- Hard-cap reservations. Settled (commit or release) exactly once; expired ones are released by cron.
CREATE TABLE usage_reservations (
  id TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  quantity REAL NOT NULL,
  expires_at INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  nonce TEXT
);

CREATE INDEX usage_reservations_expiry ON usage_reservations (expires_at);

-- Gauge meters (storage): the current level, plus the peak seen in the period for billing.
CREATE TABLE usage_gauges (
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  current REAL NOT NULL DEFAULT 0,
  peak REAL NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (account_id, meter, period)
);

CREATE TABLE account_billing (
  account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  plan TEXT NOT NULL DEFAULT 'free',
  spending_limit_cents INTEGER,
  updated_at INTEGER NOT NULL
);

-- One row per alert ever sent, so each threshold fires once per account per period.
CREATE TABLE usage_alerts (
  account_id TEXT NOT NULL,
  period TEXT NOT NULL,
  subject TEXT NOT NULL,
  threshold INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (account_id, period, subject, threshold)
);

-- Anonymous (scratch) usage is held under a synthetic account id until claimed.
CREATE TABLE usage_claims (
  scratch_id TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  claimed_at INTEGER NOT NULL
);

-- Artifacts exposes no per-repo size, so repository storage is estimated from received pack bytes
-- (an upper bound: git gc on the Artifacts side only shrinks it). Forks start at 0 because Artifacts
-- forks share the parent's objects, so the same bytes are never billed to two accounts.
CREATE TABLE repo_storage_estimates (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  pack_bytes INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL
);