-- Cameleer SaaS schema baseline
-- Consolidated from V001-V015

-- Tenants
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(255) NOT NULL,
    slug            VARCHAR(100) NOT NULL,
    tier            VARCHAR(20) NOT NULL DEFAULT 'LOW',
    status          VARCHAR(20) NOT NULL DEFAULT 'PROVISIONING',
    logto_org_id    VARCHAR(255),
    stripe_customer_id     VARCHAR(255),
    stripe_subscription_id VARCHAR(255),
    settings        JSONB NOT NULL DEFAULT '{}',
    server_endpoint VARCHAR(512),
    provision_error TEXT,
    db_password     VARCHAR(255),
    ca_applied_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX tenants_slug_active_key ON tenants (slug) WHERE status != 'DELETED';
CREATE INDEX idx_tenants_status ON tenants (status);
CREATE INDEX idx_tenants_logto_org_id ON tenants (logto_org_id);

-- Licenses
CREATE TABLE licenses (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    tier        VARCHAR(20) NOT NULL,
    features    JSONB NOT NULL DEFAULT '{}',
    limits      JSONB NOT NULL DEFAULT '{}',
    issued_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ NOT NULL,
    revoked_at  TIMESTAMPTZ,
    token       TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_licenses_tenant_id ON licenses (tenant_id);
CREATE INDEX idx_licenses_expires_at ON licenses (expires_at);

-- Audit log
CREATE TABLE audit_log (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_id    UUID,
    actor_email VARCHAR(255),
    tenant_id   UUID,
    action      VARCHAR(100)  NOT NULL,
    resource    VARCHAR(500),
    environment VARCHAR(50),
    source_ip   VARCHAR(45),
    result      VARCHAR(20)   NOT NULL DEFAULT 'SUCCESS',
    metadata    JSONB,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE INDEX idx_audit_log_tenant ON audit_log (tenant_id, created_at DESC);
CREATE INDEX idx_audit_log_actor ON audit_log (actor_id, created_at DESC);
CREATE INDEX idx_audit_log_action ON audit_log (action, created_at DESC);

-- Platform TLS certificates
CREATE TABLE certificates (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status       VARCHAR(10) NOT NULL CHECK (status IN ('ACTIVE', 'STAGED', 'ARCHIVED')),
    subject      VARCHAR(500),
    issuer       VARCHAR(500),
    not_before   TIMESTAMPTZ,
    not_after    TIMESTAMPTZ,
    fingerprint  VARCHAR(128),
    has_ca       BOOLEAN NOT NULL DEFAULT FALSE,
    self_signed  BOOLEAN NOT NULL DEFAULT FALSE,
    uploaded_by  UUID,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    activated_at TIMESTAMPTZ,
    archived_at  TIMESTAMPTZ
);

-- Per-tenant CA certificates
CREATE TABLE tenant_ca_certs (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id  UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    status     VARCHAR(10) NOT NULL CHECK (status IN ('ACTIVE', 'STAGED')),
    label      VARCHAR(200),
    subject    VARCHAR(500),
    issuer     VARCHAR(500),
    fingerprint VARCHAR(128),
    not_before TIMESTAMPTZ,
    not_after  TIMESTAMPTZ,
    cert_pem   TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_tenant_ca_certs_tenant ON tenant_ca_certs(tenant_id);
