CREATE SCHEMA IF NOT EXISTS kittybox;

CREATE TABLE kittybox.users (
    user_domain TEXT NOT NULL PRIMARY KEY,
    site_name   JSONB NOT NULL DEFAULT '"Kittybox"'::jsonb,
    webring     JSONB NOT NULL DEFAULT 'false'::jsonb
);

CREATE TABLE kittybox.mf2_json (
    uid TEXT NOT NULL PRIMARY KEY,
    mf2 JSONB NOT NULL,
    owner TEXT NOT NULL -- REFERENCES kittybox.users(user_domain)
);

CREATE INDEX mf2props ON kittybox.mf2_json USING GIN (mf2);
CREATE INDEX published_date ON kittybox.mf2_json ((mf2 #>> '{properties,published,0}'));

CREATE TABLE kittybox.children (
    parent TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
    child  TEXT NOT NULL REFERENCES kittybox.mf2_json(uid) ON DELETE CASCADE,
    UNIQUE(parent, child)
);

CREATE INDEX fulltext ON kittybox.mf2_json USING GIN (
    to_tsvector('english', mf2['properties']['content'])
);

CREATE FUNCTION kittybox.set_setting(user_domain text, setting text, val anyelement) RETURNS void AS $$
BEGIN
EXECUTE format('INSERT INTO kittybox.users (user_domain, %I) VALUES ($1, $2) ON CONFLICT (user_domain) DO UPDATE SET %I = $2', setting, setting)
    USING user_domain, val;
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION kittybox.get_setting(user_domain text, setting text) RETURNS jsonb AS $$
DECLARE
  val jsonb;
BEGIN
EXECUTE format('SELECT %I FROM kittybox.users WHERE user_domain = $1', setting) USING user_domain INTO val;

RETURN val;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION kittybox.hydrate_author(mf2 jsonb) RETURNS jsonb AS $$
DECLARE
  author jsonb;
  author_uid text;
BEGIN

author_uid := mf2 #>> '{properties,author,0}';
IF NOT (author_uid IS NULL) THEN
   SELECT mf2_json.mf2 INTO author FROM kittybox.mf2_json WHERE uid = author_uid;
END IF;
IF NOT FOUND THEN
    RETURN mf2;
ELSE
    RETURN jsonb_set(mf2, '{properties,author,0}', author);
END IF;
END;
$$ LANGUAGE plpgsql;