-- Square onboarding — database schema.
--
-- ALL Square state now lives in the database (no JSON files), persisted through
-- the host application's procedural db_* layer (../include/database.php) — the
-- same data layer the saving/ API uses. This folder's db_config.php is NO LONGER
-- used; the real credentials live in the host app.
--
-- Run once against the hotlunch database. Safe to re-run: ADD COLUMN IF NOT
-- EXISTS / CREATE TABLE IF NOT EXISTS are guarded (MySQL 8 / MariaDB 10.5+).

-- ─────────────────────────────────────────────────────────────────────────────
-- 1) Per-school Square connection + metadata, all on the existing `schools` row.
--    Previously the tokens were here but the metadata (merchant_key, location,
--    currency, readiness, …) lived only in data/square_merchants.json. It is now
--    fully columnar so nothing is stored outside the database.
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE schools
    ADD COLUMN IF NOT EXISTS square_merchant_id            VARCHAR(64)  NULL,
    ADD COLUMN IF NOT EXISTS square_access_token           TEXT         NULL,  -- AES-256-GCM ciphertext (enc:v1:...)
    ADD COLUMN IF NOT EXISTS square_refresh_token          TEXT         NULL,  -- AES-256-GCM ciphertext (enc:v1:...)
    ADD COLUMN IF NOT EXISTS square_token_expires_at       DATETIME     NULL,  -- access token expiry (ISO 8601 from Square)
    ADD COLUMN IF NOT EXISTS square_connected_at           DATETIME     NULL,
    ADD COLUMN IF NOT EXISTS square_merchant_key           VARCHAR(64)  NULL,  -- public embed key (sqpk_…)
    ADD COLUMN IF NOT EXISTS square_location_id            VARCHAR(64)  NULL,  -- active location for Web Payments SDK
    ADD COLUMN IF NOT EXISTS square_currency               VARCHAR(3)   NULL,
    ADD COLUMN IF NOT EXISTS square_country                VARCHAR(2)   NULL,
    ADD COLUMN IF NOT EXISTS square_merchant_name          VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS square_ready                  TINYINT(1)   NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS square_status_issues          TEXT         NULL,  -- readiness issues, one per line
    ADD COLUMN IF NOT EXISTS square_allowed_origins        TEXT         NULL,  -- embed origin allowlist, one per line
    ADD COLUMN IF NOT EXISTS square_status_checked_at      BIGINT       NULL,  -- unix time of last readiness check
    ADD COLUMN IF NOT EXISTS square_refreshed_at           BIGINT       NULL,  -- unix time of last token refresh
    ADD COLUMN IF NOT EXISTS square_refresh_failed_at      BIGINT       NULL,
    ADD COLUMN IF NOT EXISTS square_refresh_error          TEXT         NULL,
    ADD COLUMN IF NOT EXISTS square_authorization_revoked  TINYINT(1)   NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS square_revoked_at             BIGINT       NULL,
    ADD COLUMN IF NOT EXISTS square_seeded_for_testing     TINYINT(1)   NOT NULL DEFAULT 0;

-- Speed up lookups by merchant id (webhooks) and by public embed key (checkout).
CREATE INDEX idx_schools_square_merchant ON schools (square_merchant_id);
CREATE INDEX idx_schools_square_key      ON schools (square_merchant_key);

-- ─────────────────────────────────────────────────────────────────────────────
-- 2) Payment records (replaces data/square_payments.json). Keyed by the Square
--    payment id so a retried idempotent charge updates its row instead of
--    duplicating it. The school is referenced by its numeric id schools.record_num,
--    NOT by name — indexed for fast per-school lookups. (The column is named
--    `school_num` for historical reasons but stores schools.record_num.)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS square_payments (
    record_num        INT          NOT NULL AUTO_INCREMENT,
    payment_id        VARCHAR(64)  NOT NULL,
    school_num        BIGINT       NULL,                   -- stores schools.record_num
    merchant_id       VARCHAR(64)  NULL,
    amount_cents      INT          NOT NULL DEFAULT 0,
    currency          VARCHAR(3)   NOT NULL DEFAULT 'CAD',
    app_fee_cents     INT          NOT NULL DEFAULT 0,
    status            VARCHAR(32)  NULL,
    source_id_suffix  VARCHAR(8)   NULL,
    recorded_at       BIGINT       NOT NULL DEFAULT 0,
    PRIMARY KEY (record_num),
    UNIQUE KEY uq_square_payment_id (payment_id),
    KEY idx_square_payments_school_num (school_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────────────────────────────────────
-- 3) Pending OAuth onboarding attempts (replaces data/pending_onboarding.json).
--    Keyed by the anti-CSRF `state` token echoed back by Square on the callback.
--    The school is referenced by its UNIQUE numeric id schools.record_num (the
--    schools table's primary key — there is no `school_num` column on it), NOT by
--    name: school names are not unique across clients, so the callback must reattach
--    the connection to the exact school that began onboarding. The column below is
--    named `school_num` for historical reasons but stores schools.record_num.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS square_pending_onboarding (
    state          VARCHAR(64)  NOT NULL,
    school_num     BIGINT       NOT NULL,   -- stores schools.record_num
    status         VARCHAR(32)  NOT NULL DEFAULT 'pending',  -- pending|connected|declined|token_exchange_failed
    merchant_id    VARCHAR(64)  NULL,
    created_at     BIGINT       NOT NULL DEFAULT 0,
    connected_at   BIGINT       NULL,
    last_callback  BIGINT       NULL,
    PRIMARY KEY (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;