deploy/db/createDB.sql

-- CREATE Contacts table

CREATE TABLE public.tbl_contact (
    id bigint NOT NULL,
    contactdbuser_id character varying(255) NOT NULL
);

ALTER TABLE public.tbl_contact OWNER TO spa_service;

CREATE SEQUENCE public.tbl_contact_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.tbl_contact_id_seq OWNER TO spa_service;
ALTER SEQUENCE public.tbl_contact_id_seq OWNED BY public.tbl_contact.id;
ALTER TABLE ONLY public.tbl_contact ALTER COLUMN id SET DEFAULT nextval('public.tbl_contact_id_seq'::regclass);
ALTER TABLE ONLY public.tbl_contact ADD CONSTRAINT tbl_contact_pkey PRIMARY KEY (id);


-- CREATE StatementType table

CREATE TABLE public.tbl_statementtype (
    id bigint NOT NULL,
    name character varying(255) NOT NULL
);
ALTER TABLE public.tbl_statementtype OWNER TO spa_service;
CREATE SEQUENCE public.tbl_statementtype_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.tbl_statementtype_id_seq OWNER TO spa_service;
ALTER SEQUENCE public.tbl_statementtype_id_seq OWNED BY public.tbl_statementtype.id;
ALTER TABLE ONLY public.tbl_statementtype ALTER COLUMN id SET DEFAULT nextval('public.tbl_statementtype_id_seq'::regclass);
ALTER TABLE ONLY public.tbl_statementtype ADD CONSTRAINT tbl_statementtype_pkey PRIMARY KEY (id);

INSERT INTO public.tbl_statementtype (
name) VALUES (
'Bauleitplanung'::character varying(255))
 returning id;
 INSERT INTO public.tbl_statementtype (
name) VALUES (
'Mitverlegungsanfrage'::character varying(255))
 returning id;
 INSERT INTO public.tbl_statementtype (
name) VALUES (
'Bauvoranfrage'::character varying(255))
 returning id;
 INSERT INTO public.tbl_statementtype (
name) VALUES (
'Planfeststellungsverfahren'::character varying(255))
 returning id;
 INSERT INTO public.tbl_statementtype (
name) VALUES (
'Formlose Anfrage'::character varying(255))
 returning id;

-- CREATE Statements table

CREATE TABLE public.tbl_statement (
    id bigint NOT NULL,
    due_date date NOT NULL,
    receipt_date date NOT NULL,
    finished boolean NOT NULL,
    task_id uuid,
    title character varying(255) NOT NULL,
    city character varying(255) NOT NULL,
    district character varying(255) NOT NULL,
    contact_id bigint,
    statementtype_id bigint NOT NULL
);
ALTER TABLE public.tbl_statement OWNER TO spa_service;
CREATE SEQUENCE public.tbl_statement_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.tbl_statement_id_seq OWNER TO spa_service;
ALTER SEQUENCE public.tbl_statement_id_seq OWNED BY public.tbl_statement.id;
ALTER TABLE ONLY public.tbl_statement ALTER COLUMN id SET DEFAULT nextval('public.tbl_statement_id_seq'::regclass);
ALTER TABLE ONLY public.tbl_statement ADD CONSTRAINT tbl_statement_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.tbl_statement ADD CONSTRAINT fkphbtui6ekoc8gyq9sfd80yfc FOREIGN KEY (contact_id) REFERENCES public.tbl_contact(id);
ALTER TABLE ONLY public.tbl_statement ADD CONSTRAINT fkphbtui6ekoc8gyq9sfd80yfd FOREIGN KEY (statementtype_id) REFERENCES public.tbl_statementtype(id);

-- CREATE Attachments table

CREATE TABLE public.tbl_attachment (
    id bigint NOT NULL,
    data bytea NOT NULL,
    file_name character varying(255) NOT NULL,
    file_type character varying(255) NOT NULL,
    statement_id bigint NOT NULL
);
ALTER TABLE public.tbl_attachment OWNER TO spa_service;
CREATE SEQUENCE public.tbl_attachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.tbl_attachment_id_seq OWNER TO spa_service;
ALTER SEQUENCE public.tbl_attachment_id_seq OWNED BY public.tbl_attachment.id;
ALTER TABLE ONLY public.tbl_attachment ALTER COLUMN id SET DEFAULT nextval('public.tbl_attachment_id_seq'::regclass);
ALTER TABLE ONLY public.tbl_attachment ADD CONSTRAINT tbl_attachment_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.tbl_attachment ADD CONSTRAINT fkhvwav3ujkwgls0b2nx9r5egm6 FOREIGN KEY (statement_id) REFERENCES public.tbl_statement(id);