--
-- PostgreSQL database dump
--

-- Dumped from database version 13.3 (Debian 13.3-1.pgdg100+1)
-- Dumped by pg_dump version 13.3 (Debian 13.3-1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_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: audit; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA audit;


ALTER SCHEMA audit OWNER TO postgres;

--
-- Name: event_type; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.event_type AS ENUM (
    'redirect',
    'ban',
    'kick',
    'quiet'
);


ALTER TYPE public.event_type OWNER TO postgres;

--
-- Name: disallow_updates(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.disallow_updates() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
RAISE EXCEPTION 'Updates on this table are disabled';
END;
$$;


ALTER FUNCTION public.disallow_updates() OWNER TO postgres;

--
-- Name: identities_seen_count_up_only(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.identities_seen_count_up_only() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

  IF NEW.seen_count < OLD.seen_count THEN
	RAISE EXCEPTION 'Cannot lower seen_count on identity [%] from % to %', OLD.id, OLD.seen_count, NEW.seen_count;
  END IF;
END;
$$;


ALTER FUNCTION public.identities_seen_count_up_only() OWNER TO postgres;

--
-- Name: identities_set_last_seen(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.identities_set_last_seen() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

NEW.last_seen = CURRENT_TIMESTAMP;

END;
$$;


ALTER FUNCTION public.identities_set_last_seen() OWNER TO postgres;

--
-- Name: no_deletes(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.no_deletes() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

RAISE EXCEPTION 'No deletions allowed';

END;
$$;


ALTER FUNCTION public.no_deletes() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: acl_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.acl_rules (
    acl_id bigint NOT NULL,
    location_id bigint NOT NULL,
    account text NOT NULL,
    time_before integer NOT NULL,
    time_after integer NOT NULL,
    lines_before integer NOT NULL,
    lines_after integer NOT NULL,
    deleted boolean,
    created_on timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_on timestamp with time zone
);


ALTER TABLE public.acl_rules OWNER TO postgres;

--
-- Name: TABLE acl_rules; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.acl_rules IS 'This describes what an authorized user is allowed to render, see and search when they are using the website.  You can only delete and create new ACL rules, but never change them.  This behavior will be hidden by the system from the user perspective.  This is so that we can audit access later.';


--
-- Name: acl_rules_acl_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.acl_rules_acl_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.acl_rules_acl_id_seq OWNER TO postgres;

--
-- Name: acl_rules_acl_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.acl_rules_acl_id_seq OWNED BY public.acl_rules.acl_id;


--
-- Name: event; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.event (
    event_id bigint NOT NULL,
    server_id bigint NOT NULL,
    location_id bigint NOT NULL,
    happened_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    affected_ids bigint[],
    deleted boolean DEFAULT false NOT NULL,
    operator_id bigint NOT NULL,
    operation_type public.event_type NOT NULL,
    removed_at timestamp with time zone,
    expires_on timestamp with time zone,
    redirected_to text,
    reason text,
    topic_change text,
    removed_id bigint,
    removes_id bigint,
    log_id bigint NOT NULL
);


ALTER TABLE public.event OWNER TO postgres;

--
-- Name: TABLE event; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.event IS 'Event table is a descriptor of an operator event in a location.  It''s purpose is to be used to help power the eventual search and identify points of interest.

+b, +q, and other events in the channel done by an op are all supposed to be identified and recorded here.';


--
-- Name: COLUMN event.server_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.server_id IS 'With the changes I''ve made this is probably redundant and not needed.  location_id should already point this in the right place';


--
-- Name: COLUMN event.location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.location_id IS 'An id that descibes where this event happened.  Almost certainly a channel on the irc server.';


--
-- Name: COLUMN event.happened_at; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.happened_at IS 'Recorded timestamp of this event.';


--
-- Name: COLUMN event.affected_ids; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.affected_ids IS 'Identities that were online and in the location that the action took place and are beleived to have been affected by the event.  This is used to help power search.';


--
-- Name: COLUMN event.deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.deleted IS 'This event has been deleted from the database, it will not be shown in search.  This should get paired with a deleted_by and deleted_reason when actually implementing this.';


--
-- Name: COLUMN event.operator_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.operator_id IS 'The identity of the person who actually performed the action.';


--
-- Name: COLUMN event.operation_type; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.operation_type IS 'Description of what they did.  Changing channel topic, banning a user, unbanning a user, etc.';


--
-- Name: COLUMN event.removed_at; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.removed_at IS 'Timestamp of when the event was reversed by an operator.  See the removed_id and removes_id to find the event that removed this event';


--
-- Name: COLUMN event.expires_on; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.expires_on IS 'What time the bot believes the ban should be removed at.  This gets used to eventually enforce temporary bans.';


--
-- Name: COLUMN event.redirected_to; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.redirected_to IS 'If there''s a forwarding ban setup, where that ban was set to go.';


--
-- Name: COLUMN event.reason; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.reason IS 'The reason we believe this happened for.  The bot should parse this from the kick reason, or in the case of a ban, the text of the operator immediately before and after the action.  May not be fully accurate, the context of the event should be inspected to fully determine this.';


--
-- Name: COLUMN event.topic_change; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.topic_change IS 'The new topic that was set.';


--
-- Name: COLUMN event.log_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.event.log_id IS 'The log of the actual event when it happened.  Used to find the rest of the context when searching and rendering.';


--
-- Name: event_event_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.event_event_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.event_event_id_seq OWNER TO postgres;

--
-- Name: event_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.event_event_id_seq OWNED BY public.event.event_id;


--
-- Name: event_renderings; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.event_renderings (
    render_id bigint NOT NULL,
    event_id bigint NOT NULL,
    log_ids bigint[] NOT NULL,
    requester_id bigint NOT NULL,
    rendered_on timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    acl_at_render text NOT NULL,
    path_key text NOT NULL,
    rendered_lines json[] NOT NULL,
    deleted boolean
);


ALTER TABLE public.event_renderings OWNER TO postgres;

--
-- Name: TABLE event_renderings; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.event_renderings IS 'This is a write only rendered version of an operator event.  It''s made to be served on the webpage and they are only created by access of an authorized user.  Once rendered they are never removed or altered.';


--
-- Name: event_renderings_render_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.event_renderings_render_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.event_renderings_render_id_seq OWNER TO postgres;

--
-- Name: event_renderings_render_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.event_renderings_render_id_seq OWNED BY public.event_renderings.render_id;


--
-- Name: identities; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.identities (
    id bigint NOT NULL,
    account text,
    nickname text,
    username text,
    realname text,
    host text,
    first_seen timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_seen timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    seen_count bigint DEFAULT 0 NOT NULL,
    deleted boolean DEFAULT false NOT NULL
);


ALTER TABLE public.identities OWNER TO postgres;

--
-- Name: TABLE identities; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.identities IS 'This is used for tracking who is who.  This way user changing their nickname is able to be accurately identified and correctly recorded for operator events.';


--
-- Name: identities_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.identities_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.identities_id_seq OWNER TO postgres;

--
-- Name: identities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.identities_id_seq OWNED BY public.identities.id;


--
-- Name: location; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.location (
    location_id bigint NOT NULL,
    server_id bigint NOT NULL,
    location text NOT NULL,
    encoding text
);


ALTER TABLE public.location OWNER TO postgres;

--
-- Name: TABLE location; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.location IS 'Description of channels and other locations that are logged in this database.';


--
-- Name: location_location_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.location_location_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.location_location_id_seq OWNER TO postgres;

--
-- Name: location_location_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.location_location_id_seq OWNED BY public.location.location_id;


--
-- Name: logs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.logs (
    log_id bigint NOT NULL,
    server_id bigint NOT NULL,
    "time" timestamp with time zone NOT NULL,
    location text NOT NULL,
    type text,
    raw bytea NOT NULL,
    message text,
    identity_id bigint NOT NULL,
    deleted boolean DEFAULT false NOT NULL
);


ALTER TABLE public.logs OWNER TO postgres;

--
-- Name: TABLE logs; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.logs IS 'This is a write only log of all IRC lines sent to the bot.  locations and actors are parsed out and used for rendering events in the future.';


--
-- Name: COLUMN logs.type; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.logs.type IS 'What type of irc line we parsed this as.';


--
-- Name: COLUMN logs.raw; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.logs.raw IS 'These are the raw bytes that were sent to us by the irc server.  They are encoded in whatever encoding the location is set to, or are encoded according to the IRC protocol';


--
-- Name: COLUMN logs.message; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.logs.message IS 'This is the text of the "message" that was sent.  i.e. the content of the PRIVMSG, NOTICE, CTCP,  etc. that was recorded.  This is used to help search later as such it is the only part that is actually mutable in this table.';


--
-- Name: COLUMN logs.identity_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.logs.identity_id IS 'This is the id of the user who originated the message';


--
-- Name: servers; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.servers (
    server_id bigint NOT NULL,
    host text NOT NULL,
    name text NOT NULL,
    description text,
    deleted boolean DEFAULT false NOT NULL
);


ALTER TABLE public.servers OWNER TO postgres;

--
-- Name: TABLE servers; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.servers IS 'Description of all servers that are recorded in this database.';


--
-- Name: servers_server_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.servers_server_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.servers_server_id_seq OWNER TO postgres;

--
-- Name: servers_server_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.servers_server_id_seq OWNED BY public.servers.server_id;


--
-- Name: acl_rules acl_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.acl_rules ALTER COLUMN acl_id SET DEFAULT nextval('public.acl_rules_acl_id_seq'::regclass);


--
-- Name: event event_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.event ALTER COLUMN event_id SET DEFAULT nextval('public.event_event_id_seq'::regclass);


--
-- Name: event_renderings render_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.event_renderings ALTER COLUMN render_id SET DEFAULT nextval('public.event_renderings_render_id_seq'::regclass);


--
-- Name: identities id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.identities ALTER COLUMN id SET DEFAULT nextval('public.identities_id_seq'::regclass);


--
-- Name: location location_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.location ALTER COLUMN location_id SET DEFAULT nextval('public.location_location_id_seq'::regclass);


--
-- Name: servers server_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.servers ALTER COLUMN server_id SET DEFAULT nextval('public.servers_server_id_seq'::regclass);


--
-- Name: acl_rules acl_rules_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.acl_rules
    ADD CONSTRAINT acl_rules_pkey PRIMARY KEY (acl_id);


--
-- Name: event event_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.event
    ADD CONSTRAINT event_pkey PRIMARY KEY (event_id);


--
-- Name: event_renderings event_renderings_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.event_renderings
    ADD CONSTRAINT event_renderings_pkey PRIMARY KEY (render_id);


--
-- Name: identities identities_one_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE public.identities
    ADD CONSTRAINT identities_one_not_null CHECK (((account IS NOT NULL) OR (nickname IS NOT NULL) OR (realname IS NOT NULL) OR (host IS NOT NULL))) NOT VALID;


--
-- Name: identities identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.identities
    ADD CONSTRAINT identities_pkey PRIMARY KEY (id);


--
-- Name: location location_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.location
    ADD CONSTRAINT location_pkey PRIMARY KEY (location_id);


--
-- Name: logs logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.logs
    ADD CONSTRAINT logs_pkey PRIMARY KEY (log_id);


--
-- Name: servers servers_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_pkey PRIMARY KEY (server_id);


--
-- Name: acl_rules disallow_most_updates; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER disallow_most_updates BEFORE UPDATE OF acl_id, location_id, account, time_before, time_after, lines_before, lines_after, created_on, deleted_on ON public.acl_rules FOR EACH ROW EXECUTE FUNCTION public.disallow_updates();


--
-- Name: event_renderings disallow_most_updates; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER disallow_most_updates BEFORE UPDATE OF render_id, event_id, log_ids, requester_id, rendered_on, acl_at_render, path_key, rendered_lines ON public.event_renderings FOR EACH ROW EXECUTE FUNCTION public.disallow_updates();


--
-- Name: logs disallow_most_updates; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER disallow_most_updates BEFORE UPDATE OF log_id, server_id, "time", raw ON public.logs FOR EACH ROW EXECUTE FUNCTION public.disallow_updates();


--
-- Name: acl_rules disallow_undelete; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER disallow_undelete BEFORE UPDATE OF deleted ON public.acl_rules FOR EACH ROW WHEN (old.deleted) EXECUTE FUNCTION public.disallow_updates();


--
-- Name: identities identities_update_inc_only_seen_count; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER identities_update_inc_only_seen_count BEFORE UPDATE OF seen_count ON public.identities FOR EACH ROW EXECUTE FUNCTION public.identities_seen_count_up_only();


--
-- Name: identities identities_update_last_seen; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER identities_update_last_seen BEFORE UPDATE ON public.identities FOR EACH ROW EXECUTE FUNCTION public.identities_set_last_seen();


--
-- Name: acl_rules no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.acl_rules FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- Name: event no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.event FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- Name: event_renderings no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.event_renderings FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- Name: identities no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.identities FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- Name: logs no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.logs FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- Name: servers no_deletes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER no_deletes BEFORE DELETE OR TRUNCATE ON public.servers FOR EACH STATEMENT EXECUTE FUNCTION public.no_deletes();


--
-- PostgreSQL database dump complete
--