CREATE TABLE IF NOT EXISTS record (
    did        text    NOT NULL,
    collection text    NOT NULL,
    rkey       text    NOT NULL,
    rev        text    NOT NULL,
    cid        text    NOT NULL,
    live       boolean NOT NULL,
    data       jsonb   NOT NULL,

    PRIMARY KEY (did, collection, rkey)
);

CREATE INDEX ON record (collection);

CREATE TABLE IF NOT EXISTS deleted_record (
    LIKE record
    INCLUDING all
);

DO $$ BEGIN
    CREATE TYPE identity_status AS ENUM (
        'active',
        'takendown',
        'suspended',
        'deactivated',
        'deleted'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS identity (
    did    text            NOT NULL,
    handle text            NOT NULL,
    active boolean         NOT NULL,
    status identity_status NOT NULL,

    PRIMARY KEY (did)
);

CREATE INDEX ON identity (handle);

CREATE OR REPLACE VIEW record_by_collection AS
    SELECT collection,
            count(*) AS count
        FROM record
        GROUP BY collection
        ORDER BY (count(*)) DESC;