CREATE TYPE arch AS ENUM('x32','x64');

CREATE TABLE system_op
(
  id serial NOT NULL,
  nazwa character varying(150) NOT NULL,
  architektura arch NOT NULL,
  CONSTRAINT system_op_pkey PRIMARY KEY (id)
);

  
  CREATE TABLE statusy
(
  id serial NOT NULL,
  opis character varying(15) NOT NULL,
  CONSTRAINT statusy_pkey PRIMARY KEY (id)
);

CREATE TABLE kategoria
(
  id serial NOT NULL,
  opis character varying(50) NOT NULL,
  CONSTRAINT Kategoria_pkey PRIMARY KEY (id)
);

  
CREATE TABLE sl_org
(
  id serial NOT NULL,
  nazwa character varying(150) NOT NULL,
  CONSTRAINT sl_org_pkey PRIMARY KEY (id)
);

  
  CREATE TABLE komputery
(
  id serial NOT NULL,
  nazwa character varying(150) NOT NULL,
  lokalizacja integer NOT NULL,
  system_op integer NOT NULL,
  system_op2 integer,
  nr_inw character varying(30) NOT NULL,
  CONSTRAINT komputery_pkey PRIMARY KEY (id),
  CONSTRAINT fk_komputery_system_op FOREIGN KEY (system_op)
      REFERENCES system_op (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_komputery_system_op2 FOREIGN KEY (system_op2)
      REFERENCES system_op (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_komputery_sl_org FOREIGN KEY (lokalizacja)
      REFERENCES sl_org (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE users
(
  id serial NOT NULL,
  login character varying(50) NOT NULL,
  password character varying(30) NOT NULL,
  name character varying(250) NOT NULL,
  komorka integer NOT NULL,
  funkcja character varying(150),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT fk_users_sl_org FOREIGN KEY (komorka)
      REFERENCES sl_org (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


CREATE TABLE uprawnienia
(
  id serial NOT NULL,
  uzytkownik integer NOT NULL,
  administracja character varying(1) NOT NULL,
  kon_kier character varying(1) NOT NULL,
  kier_help character varying(1) NOT NULL,
  raporty character varying(1) NOT NULL,
  CONSTRAINT uprawnienia_pkey PRIMARY KEY (id),
  CONSTRAINT fk_uprawnienia_users FOREIGN KEY (uzytkownik)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);



CREATE TABLE upr_zgl
(
  user_id integer NOT NULL,
  kategoria_id integer NOT NULL,
  CONSTRAINT upr_zgl_pkey PRIMARY KEY (user_id, kategoria_id),
  CONSTRAINT fk_upr_zgl_kategoria FOREIGN KEY (kategoria_id)
      REFERENCES kategoria (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_upr_zgl_users FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE zgloszenie
(
  id serial NOT NULL,
  zglaszajacy integer NOT NULL,
  kategoria integer NOT NULL,
  komputer integer,
  status integer,
  odpowiedzialny integer,
  data_zgloszenia timestamp(2) without time zone,
  termin_rozwiazania timestamp(2) without time zone,
  data_rozwiazania timestamp(2) without time zone,
  opis character varying(250) NOT NULL,
  wyjasnienie character varying(250),
  CONSTRAINT zgloszenie_pkey PRIMARY KEY (id),
  CONSTRAINT fk_zgloszenie_kategoria FOREIGN KEY (kategoria)
      REFERENCES kategoria (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_zgloszenie_komputery FOREIGN KEY (komputer)
      REFERENCES komputery (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_zgloszenie_statusy FOREIGN KEY (status)
      REFERENCES statusy (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_zgloszenie_user_02 FOREIGN KEY (odpowiedzialny)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_zgloszenie_users_01 FOREIGN KEY (zglaszajacy)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);