-- Server: Rings managed by this server
CREATE TABLE IF NOT EXISTS "managed_rings" (
    "id" integer NOT NULL UNIQUE,
    "name" varchar NOT NULL,
    "url" varchar NOT NULL UNIQUE,
    "description" varchar NOT NULL,
    "private_signing_key" varchar NOT NULL,
    "public_signing_key" varchar NOT NULL ,
    "private_encryption_key" varchar NOT NULL,
    "public_encryption_key" varchar NOT NULL ,
    "default_ring" boolean DEFAULT false,
    PRIMARY KEY("id" AUTOINCREMENT)
);
-- Client: Other Rings joined by this user
CREATE TABLE IF NOT EXISTS "joined_rings" (
    "id" integer NOT NULL UNIQUE,
    "name" varchar NOT NULL,
    "url" varchar NOT NULL,
    "description" varchar NOT NULL,
    "public_signing_key" varchar NOT NULL,
    "public_encryption_key" varchar NOT NULL,
    "default_ring" boolean DEFAULT false,
    PRIMARY KEY("id" AUTOINCREMENT)
);
-- Server: Members of Managed Rings
CREATE TABLE IF NOT EXISTS "members" (
    "id" integer NOT NULL UNIQUE,
    "name" varchar NOT NULL,
    "url" varchar NOT NULL,
    "description" varchar NOT NULL,
    "public_signing_key" varchar NOT NULL,
    "enrolled" boolean default true,
    "ring_id" integer NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);
-- Client: Sites managed by users (represnted as members on the ring server)
CREATE TABLE IF NOT EXISTS "sites" (
    "id" integer NOT NULL UNIQUE,
    "name" varchar NOT NULL,
    "url" varchar NOT NULL UNIQUE,
    "description" varchar NOT NULL,
    "public_signing_key" varchar NOT NULL,
    "private_signing_key" varchar NOT NULL,
    "ring_id" integer,
    "default_site" boolean DEFAULT false,
    PRIMARY KEY("id" AUTOINCREMENT)
);
-- Server: Votes from Members
CREATE TABLE IF NOT EXISTS "member_votes" (
    "id" integer NOT NULL UNIQUE,
    "ring_id" integer NOT NULL,
    "member_id" integer NOT NULL,
    "vote_subject_url" varchar NOT NULL,
    "standing" varchar NOT NULL,
    "description" varchar NOT NULL,
    "timestamp" integer NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
 );
-- Client: Votes on other Members
CREATE TABLE IF NOT EXISTS "site_votes" (
    "id" integer NOT NULL UNIQUE,
    "ring_id" integer NOT NULL,
    "site_id" integer NOT NULL,
    "vote_subject_url" varchar NOT NULL,
    "standing" varchar NOT NULL,
    "description" varchar NOT NULL,
    "timestamp" integer NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
 );
-- Server: Invites for new Members
CREATE TABLE IF NOT EXISTS "invites" (
    "id" integer NOT NULL UNIQUE,
    "url" varchar NOT NULL,
    "invite_code" uuid_text NOT NULL,
    "expiration" INTEGER NOT NULL,
    "redeemed" boolean default false,
    "ring_id" integer NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);
-- Client: Join Table for all sites/rings
CREATE TABLE IF NOT EXISTS "memberships" (
    "id" integer NOT NULL UNIQUE,
    "ring_id" integer NOT NULL,
    "site_id" integer NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);
