-- PM-80 G28: project/path is membership metadata, not source identity.
-- Rebuild the table created by migration 009 without its obsolete compound
-- uniqueness constraint. resource_uri remains the sole source key; all
-- project/path visibility is represented by ldraw_resource_memberships.
ALTER TABLE ldraw_resources RENAME TO ldraw_resources_pm80_old;
DROP INDEX idx_ldraw_resources_owner;
DROP INDEX idx_ldraw_resources_project_path;
DROP INDEX idx_ldraw_resources_source;

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)),
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

INSERT INTO ldraw_resources (
    resource_uri, owner_did, project_uri, record_cid, rkey, root,
    canonical_path, mime_type, byte_length, sha256, ordered_blob_cids,
    provenance, licence, notices_json, snapshot_id, legacy, created_at, updated_at
)
SELECT
    resource_uri, owner_did, project_uri, record_cid, rkey, root,
    canonical_path, mime_type, byte_length, sha256, ordered_blob_cids,
    provenance, licence, notices_json, snapshot_id, legacy, created_at, updated_at
FROM ldraw_resources_pm80_old;

DROP TABLE ldraw_resources_pm80_old;

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);