--
-- PostgreSQL database dump
--

-- Dumped from database version 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 17.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: acl; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA acl;


--
-- Name: activity; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA activity;


--
-- Name: data; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA data;


--
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
--

-- *not* creating schema, since initdb creates it


--
-- Name: system; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA system;


--
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;


--
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


--
-- Name: add_file_to_tag_recursive(uuid, uuid); Type: FUNCTION; Schema: data; Owner: -
--

CREATE FUNCTION data.add_file_to_tag_recursive(f_id uuid, t_id uuid) RETURNS SETOF uuid
    LANGUAGE plpgsql
    AS $$
DECLARE
	tmp uuid;
	tt_id uuid;
	ttt_id uuid;
BEGIN
	INSERT INTO data.file_tag VALUES (f_id, t_id) ON CONFLICT DO NOTHING RETURNING tag_id INTO tmp;
	IF tmp IS NULL THEN
		RETURN;
	END IF;
	RETURN NEXT t_id;
	FOR tt_id IN
		SELECT a.add_tag_id FROM data.autotags a WHERE a.trigger_tag_id=t_id AND a.is_active
	LOOP
		FOR ttt_id IN SELECT data.add_file_to_tag_recursive(f_id, tt_id)
		LOOP
			RETURN NEXT ttt_id;
		END LOOP;
	END LOOP;
END;
$$;


--
-- Name: uuid_extract_timestamp(uuid); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.uuid_extract_timestamp(uuid_val uuid) RETURNS timestamp with time zone
    LANGUAGE sql IMMUTABLE
    AS $$
    SELECT to_timestamp(
        ('x' || LEFT(REPLACE(uuid_val::TEXT, '-', ''), 12))::BIT(48)::BIGINT
        / 1000.0
    );
$$;


--
-- Name: uuid_v7(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.uuid_v7(cts timestamp with time zone DEFAULT clock_timestamp()) RETURNS uuid
    LANGUAGE plpgsql
    AS $$
DECLARE
  state text = current_setting('uuidv7.old_tp',true);
  old_tp text = split_part(state, ':',1);
  base int = coalesce(nullif(split_part(state,':',4),'')::int,(random()*16777215/2-1)::int);
  tp text;
  entropy text;
  seq text=base;
  seqn int=split_part(state,':',2);
  ver text = coalesce(split_part(state,':',3),to_hex(8+(random()*3)::int));
BEGIN
  base = (random()*16777215/2-1)::int;
  tp = lpad(to_hex(floor(extract(epoch from cts)*1000)::int8),12,'0')||'7';
  if tp is distinct from old_tp then
    old_tp = tp;
    ver = to_hex(8+(random()*3)::int);
    base = (random()*16777215/2-1)::int;
    seqn = base;
  else
    seqn = seqn+(random()*1000)::int;
  end if;
  perform set_config('uuidv7.old_tp',old_tp||':'||seqn||':'||ver||':'||base, false);
  entropy = md5(gen_random_uuid()::text);
  seq = lpad(to_hex(seqn),6,'0');
  return (tp || substring(seq from 1 for 3) || ver || substring(seq from 4 for 3) ||
                substring(entropy from 1 for 12))::uuid;
END
$$;


SET default_table_access_method = heap;

--
-- Name: categories; Type: TABLE; Schema: acl; Owner: -
--

CREATE TABLE acl.categories (
    user_id smallint NOT NULL,
    category_id uuid NOT NULL,
    view boolean NOT NULL,
    edit boolean NOT NULL
);


--
-- Name: files; Type: TABLE; Schema: acl; Owner: -
--

CREATE TABLE acl.files (
    user_id smallint NOT NULL,
    file_id uuid NOT NULL,
    view boolean NOT NULL,
    edit boolean NOT NULL
);


--
-- Name: pools; Type: TABLE; Schema: acl; Owner: -
--

CREATE TABLE acl.pools (
    user_id smallint NOT NULL,
    pool_id uuid NOT NULL,
    view boolean NOT NULL,
    edit boolean NOT NULL
);


--
-- Name: tags; Type: TABLE; Schema: acl; Owner: -
--

CREATE TABLE acl.tags (
    user_id smallint NOT NULL,
    tag_id uuid NOT NULL,
    view boolean NOT NULL,
    edit boolean NOT NULL
);


--
-- Name: file_views; Type: TABLE; Schema: activity; Owner: -
--

CREATE TABLE activity.file_views (
    file_id uuid NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    user_id smallint NOT NULL
);


--
-- Name: pool_views; Type: TABLE; Schema: activity; Owner: -
--

CREATE TABLE activity.pool_views (
    pool_id uuid NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    user_id smallint NOT NULL
);


--
-- Name: sessions; Type: TABLE; Schema: activity; Owner: -
--

CREATE TABLE activity.sessions (
    id integer NOT NULL,
    token text NOT NULL,
    user_id smallint NOT NULL,
    user_agent character varying(256) NOT NULL,
    started_at timestamp with time zone DEFAULT statement_timestamp() NOT NULL,
    expires_at timestamp with time zone,
    last_activity timestamp with time zone DEFAULT statement_timestamp() NOT NULL
);


--
-- Name: sessions_id_seq; Type: SEQUENCE; Schema: activity; Owner: -
--

CREATE SEQUENCE activity.sessions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sessions_id_seq; Type: SEQUENCE OWNED BY; Schema: activity; Owner: -
--

ALTER SEQUENCE activity.sessions_id_seq OWNED BY activity.sessions.id;


--
-- Name: tag_uses; Type: TABLE; Schema: activity; Owner: -
--

CREATE TABLE activity.tag_uses (
    tag_id uuid NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    user_id smallint NOT NULL,
    included boolean NOT NULL
);


--
-- Name: autotags; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.autotags (
    trigger_tag_id uuid NOT NULL,
    add_tag_id uuid NOT NULL,
    is_active boolean DEFAULT true NOT NULL
);


--
-- Name: categories; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.categories (
    id uuid DEFAULT public.uuid_v7() NOT NULL,
    name character varying(256) NOT NULL,
    notes text DEFAULT ''::text NOT NULL,
    color character(6),
    creator_id smallint NOT NULL
);


--
-- Name: file_pool; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.file_pool (
    file_id uuid NOT NULL,
    pool_id uuid NOT NULL,
    number smallint NOT NULL
);


--
-- Name: file_tag; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.file_tag (
    file_id uuid NOT NULL,
    tag_id uuid NOT NULL
);


--
-- Name: files; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.files (
    id uuid DEFAULT public.uuid_v7() NOT NULL,
    name character varying(256),
    mime_id smallint NOT NULL,
    datetime timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
    notes text,
    metadata jsonb NOT NULL,
    creator_id smallint NOT NULL,
    is_deleted boolean DEFAULT false NOT NULL
);


--
-- Name: pools; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.pools (
    id uuid DEFAULT public.uuid_v7() NOT NULL,
    name character varying(256) NOT NULL,
    notes text,
    creator_id smallint NOT NULL
);


--
-- Name: tags; Type: TABLE; Schema: data; Owner: -
--

CREATE TABLE data.tags (
    id uuid DEFAULT public.uuid_v7() NOT NULL,
    name character varying(256) NOT NULL,
    notes text,
    color character(6),
    category_id uuid,
    creator_id smallint NOT NULL
);


--
-- Name: mime; Type: TABLE; Schema: system; Owner: -
--

CREATE TABLE system.mime (
    id smallint NOT NULL,
    name character varying(127) NOT NULL,
    extension character varying(16) NOT NULL
);


--
-- Name: mime_id_seq; Type: SEQUENCE; Schema: system; Owner: -
--

CREATE SEQUENCE system.mime_id_seq
    AS smallint
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mime_id_seq; Type: SEQUENCE OWNED BY; Schema: system; Owner: -
--

ALTER SEQUENCE system.mime_id_seq OWNED BY system.mime.id;


--
-- Name: users; Type: TABLE; Schema: system; Owner: -
--

CREATE TABLE system.users (
    id smallint NOT NULL,
    name character varying(32) NOT NULL,
    password text NOT NULL,
    is_admin boolean DEFAULT false NOT NULL,
    can_create boolean DEFAULT false NOT NULL
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: system; Owner: -
--

CREATE SEQUENCE system.users_id_seq
    AS smallint
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: system; Owner: -
--

ALTER SEQUENCE system.users_id_seq OWNED BY system.users.id;


--
-- Name: sessions id; Type: DEFAULT; Schema: activity; Owner: -
--

ALTER TABLE ONLY activity.sessions ALTER COLUMN id SET DEFAULT nextval('activity.sessions_id_seq'::regclass);


--
-- Name: mime id; Type: DEFAULT; Schema: system; Owner: -
--

ALTER TABLE ONLY system.mime ALTER COLUMN id SET DEFAULT nextval('system.mime_id_seq'::regclass);


--
-- Name: users id; Type: DEFAULT; Schema: system; Owner: -
--

ALTER TABLE ONLY system.users ALTER COLUMN id SET DEFAULT nextval('system.users_id_seq'::regclass);


--
-- Name: categories categories_pkey; Type: CONSTRAINT; Schema: acl; Owner: -
--

ALTER TABLE ONLY acl.categories
    ADD CONSTRAINT categories_pkey PRIMARY KEY (user_id, category_id);


--
-- Name: files files_pkey; Type: CONSTRAINT; Schema: acl; Owner: -
--

ALTER TABLE ONLY acl.files
    ADD CONSTRAINT files_pkey PRIMARY KEY (user_id, file_id);


--
-- Name: pools pools_pkey; Type: CONSTRAINT; Schema: acl; Owner: -
--

ALTER TABLE ONLY acl.pools
    ADD CONSTRAINT pools_pkey PRIMARY KEY (user_id, pool_id);


--
-- Name: tags tags_pkey; Type: CONSTRAINT; Schema: acl; Owner: -
--

ALTER TABLE ONLY acl.tags
    ADD CONSTRAINT tags_pkey PRIMARY KEY (user_id, tag_id);


--
-- Name: file_views file_views_pkey; Type: CONSTRAINT; Schema: activity; Owner: -
--

ALTER TABLE ONLY activity.file_views
    ADD CONSTRAINT file_views_pkey PRIMARY KEY (file_id, "timestamp", user_id);


--
-- Name: pool_views pool_views_pkey; Type: CONSTRAINT; Schema: activity; Owner: -
--

ALTER TABLE ONLY activity.pool_views
    ADD CONSTRAINT pool_views_pkey PRIMARY KEY (pool_id, "timestamp", user_id);


--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: activity; Owner: -
--

ALTER TABLE ONLY activity.sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);


--
-- Name: tag_uses tag_uses_pkey; Type: CONSTRAINT; Schema: activity; Owner: -
--

ALTER TABLE ONLY activity.tag_uses
    ADD CONSTRAINT tag_uses_pkey PRIMARY KEY (tag_id, "timestamp", user_id);


--
-- Name: autotags autotags_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.autotags
    ADD CONSTRAINT autotags_pkey PRIMARY KEY (trigger_tag_id, add_tag_id);


--
-- Name: categories categories_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.categories
    ADD CONSTRAINT categories_pkey PRIMARY KEY (id);


--
-- Name: file_pool file_pool_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.file_pool
    ADD CONSTRAINT file_pool_pkey PRIMARY KEY (file_id, pool_id, number);


--
-- Name: file_tag file_tag_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.file_tag
    ADD CONSTRAINT file_tag_pkey PRIMARY KEY (file_id, tag_id);


--
-- Name: files files_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.files
    ADD CONSTRAINT files_pkey PRIMARY KEY (id);


--
-- Name: pools pools_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.pools
    ADD CONSTRAINT pools_pkey PRIMARY KEY (id);


--
-- Name: tags tags_pkey; Type: CONSTRAINT; Schema: data; Owner: -
--

ALTER TABLE ONLY data.tags
    ADD CONSTRAINT tags_pkey PRIMARY KEY (id);


--
-- Name: mime mime_pkey; Type: CONSTRAINT; Schema: system; Owner: -
--

ALTER TABLE ONLY system.mime
    ADD CONSTRAINT mime_pkey PRIMARY KEY (id);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: system; Owner: -
--

ALTER TABLE ONLY system.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--