CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
CREATE TYPE keyalgorithm AS ENUM (
    'Ed25519'
);

CREATE FUNCTION permissions(uuid, uuid) RETURNS bigint
    LANGUAGE plpgsql
    AS $_$
DECLARE
  perm integer;BEGIN

  SELECT coalesce(bit_or(permissions.perm), 0) AS p FROM permissions WHERE
    (user_id = $1 OR user_id = '00000000-0000-0000-0000-000000000000') AND repo_id = $2 AND end_date IS NULL
  INTO perm;
  RETURN perm; END
$_$;


CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() NOT NULL primary key,
    login citext unique not null,
    password text not null,
    email citext unique not null,
    email_is_invalid boolean,
    creation_date timestamp with time zone not null DEFAULT now(),
    is_active boolean not null DEFAULT true,
    name text,
    creation_ip inet,
    last_identity_change timestamp with time zone,
    storage_used bigint DEFAULT 0,
    suspended boolean not null DEFAULT false
);

-- Dummy user for permissions
INSERT INTO users(id, login, password, email) VALUES('00000000-0000-0000-0000-000000000000', '', '', '');

CREATE TABLE repositories (
    id uuid DEFAULT gen_random_uuid() NOT NULL primary key,
    owner uuid not null references users(id) on delete cascade,
    name text not null,
    creation_ip text not null,
    fork_origin uuid,
    creation_date timestamp with time zone not null DEFAULT now(),
    next_discussion_number integer not null DEFAULT 0,
    default_channel text not null DEFAULT 'main'::text,
    description text,
    is_active boolean not null DEFAULT true,
    max_changes_size bigint not null DEFAULT '10737418240'::bigint,
    n_followers bigint not null DEFAULT 0,
    last_pushed timestamp with time zone not null DEFAULT now(),
    last_discussed timestamp with time zone not null DEFAULT now(),
    rank double precision not null default 0,
    UNIQUE(owner, name)
);


CREATE TABLE discussions (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    repository_id uuid not null references repositories(id) on delete cascade,
    title text not null,
    author uuid references users(id),
    creation_ip inet not null,
    creation_date timestamp with time zone not null default now(),
    number integer not null,
    pull_to_branch text,
    first_patch integer,
    n_changes integer not null default 0,
    changes integer not null DEFAULT 0,
    closed timestamp with time zone,
    uniq bigint not null
);

CREATE TABLE comments (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    discussion_id uuid not null references discussions(id) on delete cascade,
    author uuid references users(id),
    contents text not null,
    creation_ip inet not null,
    creation_date timestamp with time zone not null default now(),
    cached_html text not null,
    uniq bigint not null
);

CREATE TABLE discussion_changes (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    change text not null,
    discussion uuid not null references discussions(id) on delete cascade,
    pushed_by uuid references users(id),
    added timestamp with time zone not null DEFAULT now(),
    removed timestamp with time zone
);

CREATE TABLE tags (
    id uuid DEFAULT gen_random_uuid() primary key not null,
    repository_id uuid not null references repositories(id) on delete cascade,
    name text not null,
    creation_date timestamp with time zone not null default now(),
    deletion_date timestamp with time zone,
    color integer not null
);

CREATE TABLE discussion_tags (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    discussion uuid not null references discussions(id) on delete cascade,

    -- null: open/closed
    tag uuid references tags(id) on delete cascade,

    author uuid references users(id),
    date timestamp with time zone not null default now(),
    active bool not null default true,
    addition bool not null default true
);

CREATE TABLE discussion_subscriptions (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    user_id uuid not null references users(id) on delete cascade,
    discussion_id uuid not null references discussions(id) on delete cascade,
    start_date timestamp with time zone DEFAULT now(),
    end_date timestamp with time zone
);

CREATE TABLE signingkeys (
    public_key bytea NOT NULL primary key,
    user_id uuid not null references users(id) on delete cascade,
    algorithm keyalgorithm not null,
    signature bytea not null,
    expires timestamp with time zone,
    added timestamp with time zone not null DEFAULT now()
);

CREATE TABLE contributors (
    repo uuid NOT NULL references repositories(id) on delete cascade,
    key bytea NOT NULL,
    revision timestamp with time zone not null,
    primary key(repo, key)
);

CREATE TABLE email_log (
    "time" timestamp with time zone primary key not null default now(),
    discussion uuid not null references discussions(id) on delete cascade,
    email text not null
);

CREATE TABLE hooks (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    repository uuid not null references repositories(id) on delete cascade,
    author uuid not null references users(id) on delete cascade,
    creation_date timestamp with time zone not null,
    active boolean not null DEFAULT true,
    secret text not null,
    url text not null,
    action bigint
);

CREATE TABLE old_logins (
    login citext NOT NULL primary key,
    user_id uuid,
    retired timestamp with time zone DEFAULT now()
);

CREATE TABLE password_failures (
    date timestamp with time zone DEFAULT now() primary key not null,
    ip inet not null,
    login text not null
);

CREATE TABLE permissions (
    user_id uuid not null references users(id) ON DELETE CASCADE,
    repo_id uuid not null references repositories(id) ON DELETE CASCADE,
    perm bigint not null DEFAULT 0,
    start_date timestamp with time zone not null DEFAULT now(),
    end_date timestamp with time zone,
    primary key (user_id, repo_id)
);

CREATE TABLE profile_pics (
    user_id uuid NOT NULL references users(id) primary key,
    updated timestamp with time zone not null DEFAULT now(),
    format text not null,
    image bytea not null,
    small bytea not null
);

CREATE TABLE publickeys (
    id uuid DEFAULT gen_random_uuid() primary key NOT NULL,
    user_id uuid not null references users(id) on delete cascade,
    publickey text not null,
    bin bytea not null,
    UNIQUE(id, bin)
);

CREATE TABLE repository_followers (
    repository_id uuid not null references repositories(id) on delete cascade,
    user_id uuid not null references users(id) on delete cascade,
    start_date timestamp with time zone DEFAULT now(),
    end_date timestamp with time zone,
    primary key(repository_id, user_id)
);

CREATE TABLE tokens (
    token bytea NOT NULL primary key,
    user_id uuid not null references users(id) on delete cascade,
    date timestamp without time zone not null DEFAULT now()
);

GRANT ALL ON TABLE comments TO pijul;
GRANT ALL ON TABLE contributors TO pijul;
GRANT ALL ON TABLE discussion_changes TO pijul;
GRANT ALL ON TABLE discussion_subscriptions TO pijul;
GRANT ALL ON TABLE discussions TO pijul;
GRANT ALL ON TABLE tags TO pijul;
GRANT ALL ON TABLE discussion_tags TO pijul;
GRANT ALL ON TABLE hooks TO pijul;
GRANT ALL ON TABLE old_logins TO pijul;
GRANT ALL ON TABLE password_failures TO pijul;
GRANT ALL ON TABLE permissions TO pijul;
GRANT ALL ON TABLE profile_pics TO pijul;
GRANT ALL ON TABLE publickeys TO pijul;
GRANT ALL ON TABLE repositories TO pijul;
GRANT ALL ON TABLE repository_followers TO pijul;
GRANT ALL ON TABLE signingkeys TO pijul;
GRANT ALL ON TABLE tokens TO pijul;
GRANT ALL ON TABLE users TO pijul;