-- PM-76: durable LDraw resource projection, verification, and opaque routes.
-- All dependent cleanup is performed by application transactions; SQLite foreign-key
-- enforcement is intentionally not required by this schema.

CREATE TABLE ldraw_resources (
    resource_uri TEXT PRIMARY KEY,
    owner_did TEXT NOT NULL,
    project_uri TEXT NOT NULL,
    record_cid TEXT NOT NULL,
    rkey TEXT NOT NULL,
    root TEXT NOT NULL,
    canonical_path TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    byte_length INTEGER NOT NULL CHECK (byte_length >= 0),
    sha256 BLOB NOT NULL CHECK (length(sha256) = 32),
    ordered_blob_cids TEXT NOT NULL,
    provenance TEXT NOT NULL,
    licence TEXT NOT NULL,
    notices_json TEXT NOT NULL,
    snapshot_id TEXT,
    legacy INTEGER NOT NULL DEFAULT 0 CHECK (legacy IN (0, 1)),
    revoked_at INTEGER,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    UNIQUE (owner_did, project_uri, root, canonical_path),
    UNIQUE (resource_uri, sha256, byte_length, ordered_blob_cids)
);
CREATE INDEX idx_ldraw_resources_owner ON ldraw_resources(owner_did);
CREATE INDEX idx_ldraw_resources_project_path ON ldraw_resources(project_uri, root, canonical_path);
CREATE INDEX idx_ldraw_resources_source ON ldraw_resources(resource_uri, sha256, byte_length);

CREATE TABLE ldraw_manifest_files (
    resource_uri TEXT NOT NULL,
    root TEXT NOT NULL,
    canonical_path TEXT NOT NULL,
    target_resource_uri TEXT NOT NULL,
    target_cid TEXT NOT NULL,
    byte_length INTEGER NOT NULL CHECK (byte_length >= 0),
    sha256 BLOB NOT NULL CHECK (length(sha256) = 32),
    mime_type TEXT NOT NULL,
    ordinal INTEGER NOT NULL CHECK (ordinal >= 0),
    PRIMARY KEY (resource_uri, root, canonical_path),
    UNIQUE (resource_uri, ordinal)
);
CREATE INDEX idx_ldraw_manifest_target ON ldraw_manifest_files(target_resource_uri, target_cid);

CREATE TABLE ldraw_verification (
    resource_uri TEXT PRIMARY KEY,
    source_identity TEXT NOT NULL,
    state TEXT NOT NULL CHECK (state IN ('unverified', 'verifying', 'verified', 'rejected')),
    attempt_token TEXT UNIQUE,
    lease_expires_at INTEGER,
    expired_attempts INTEGER NOT NULL DEFAULT 0 CHECK (expired_attempts >= 0 AND expired_attempts <= 3),
    diagnostic TEXT,
    revoked_at INTEGER,
    updated_at INTEGER NOT NULL
);
CREATE INDEX idx_ldraw_verification_state ON ldraw_verification(state, lease_expires_at);
CREATE INDEX idx_ldraw_verification_source ON ldraw_verification(source_identity);

CREATE TABLE ldraw_routes (
    token_digest BLOB PRIMARY KEY CHECK (length(token_digest) = 32),
    principal_did TEXT,
    project_uri TEXT NOT NULL,
    resource_uri TEXT NOT NULL,
    source_identity TEXT NOT NULL,
    root TEXT NOT NULL,
    canonical_path TEXT NOT NULL,
    target_identity TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    revoked_at INTEGER,
    created_at INTEGER NOT NULL,
    UNIQUE (principal_did, project_uri, resource_uri, source_identity, root, canonical_path, target_identity)
);
CREATE INDEX idx_ldraw_routes_auth ON ldraw_routes(principal_did, project_uri, expires_at, revoked_at);
CREATE INDEX idx_ldraw_routes_resource ON ldraw_routes(resource_uri, source_identity);

CREATE TABLE ldraw_official_snapshots (
    snapshot_id TEXT PRIMARY KEY,
    digest BLOB NOT NULL CHECK (length(digest) = 32),
    licence TEXT NOT NULL,
    notices_json TEXT NOT NULL,
    loaded_at INTEGER NOT NULL,
    UNIQUE (snapshot_id, digest)
);

-- Legacy rows are represented explicitly and remain unreadable until verified.
CREATE TABLE ldraw_legacy_resources (
    resource_uri TEXT PRIMARY KEY,
    owner_did TEXT NOT NULL,
    source_record_cid TEXT,
    reason TEXT NOT NULL,
    classified_at INTEGER NOT NULL
);
CREATE INDEX idx_ldraw_legacy_owner ON ldraw_legacy_resources(owner_did);