-- SPDX-License-Identifier: Apache-2.0
--
-- 0004 — tenants_index.db registry for the per-tenant DB layout.
--
-- See docs/dev-log/0090-v0.8.0-implementation-plan.md §2 Priority 1.
-- ADR-0004 (added in v0.8.0 P7) supersedes ADR-0003's single-DB assumption
-- with the per-tenant invariants this schema underpins.
--
-- IMPORTANT — this migration ONLY applies to `<data_dir>/tenants_index.db`.
-- The per-tenant DBs at `<data_dir>/tenants/<id>.db` continue to use the
-- existing migrations 0001 through 0003 (and any future per-tenant
-- migrations) unchanged. `migration::run_migrations_for_connection` lets
-- callers target either schema chain.
--
-- Pattern conventions mirror 0001_initial.sql:
--   * INTEGER PRIMARY KEY AUTOINCREMENT for audit ids (we want stable,
--     non-reused ids for compliance trails)
--   * CHECK constraints for enum-like fields
--   * INTEGER epoch-ms for timestamps
--
-- A note on the parallel migration tracker:
--   `tenants_index.db` does NOT share `schema_migrations` with the
--   per-tenant DBs — it has its own `schema_migrations_tenants_index`
--   table created by the migration runner (NOT by this file, to match the
--   convention 0001_initial.sql uses) so the version sequences can
--   evolve independently. If a future release adds a per-tenant audit
--   retention column, that's a per-tenant migration (e.g., 0005) that
--   doesn't touch the tenants_index counter, and vice-versa.

-- --------------------------------------------------------------------------
-- tenants — one row per tenant
-- --------------------------------------------------------------------------

CREATE TABLE tenants (
    tenant_id      TEXT PRIMARY KEY NOT NULL,        -- TenantId; validated by solo-core
    db_filename    TEXT NOT NULL UNIQUE,             -- relative to <data_dir>/tenants/
    display_name   TEXT,                             -- optional human-readable label
    created_at_ms  INTEGER NOT NULL,
    status         TEXT NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active','pending_migration','pending_delete'))
);

CREATE INDEX idx_tenants_status ON tenants(status);

-- --------------------------------------------------------------------------
-- audit_events_admin — admin-tier operations not bound to one tenant
-- --------------------------------------------------------------------------
--
-- Per-tenant operational audit lives inside each per-tenant DB (added in
-- v0.8.0 P4). This table only captures admin operations that touch the
-- registry itself or that need to outlive a deleted tenant (notably the
-- hard-delete event — see P6 sequencing: audit row is committed BEFORE
-- fs::remove_file).

CREATE TABLE audit_events_admin (
    audit_id              INTEGER PRIMARY KEY AUTOINCREMENT,
    ts_ms                 INTEGER NOT NULL,
    principal_subject     TEXT,                                -- nullable for system-initiated
    operation             TEXT NOT NULL,                       -- e.g. "tenant.create"
    target_tenant_id      TEXT,                                -- nullable for cross-cutting ops
    result                TEXT NOT NULL CHECK (result IN ('ok','error','forbidden')),
    details_json          TEXT                                 -- free-form per-op context
);

CREATE INDEX idx_audit_admin_ts ON audit_events_admin(ts_ms);
CREATE INDEX idx_audit_admin_target ON audit_events_admin(target_tenant_id);

-- (schema_migrations_tenants_index is created by the migration runner
-- via CREATE TABLE IF NOT EXISTS prior to applying this file — see
-- `migration::run_migrations_for_connection`. We don't redeclare it
-- here, matching how 0001_initial.sql leaves `schema_migrations`
-- creation to the runner.)
