DROP TABLE operincid;
DROP TABLE imgincid;
DROP TABLE incidencias;
DROP TABLE estincid;
DROP TABLE priorincid;
DROP TABLE categincid;


CREATE TABLE estincid (
    est_codigo serial NOT NULL,
    est_nombre character varying(150) NOT NULL,
    est_estado integer DEFAULT 1
);


ALTER TABLE public.estincid OWNER TO sanjor;

ALTER TABLE public.estincid_est_codigo_seq OWNER TO sanjor;

ALTER TABLE ONLY estincid ALTER COLUMN est_codigo SET DEFAULT nextval('estincid_est_codigo_seq'::regclass);

ALTER TABLE ONLY estincid
    ADD CONSTRAINT estincid_pkey PRIMARY KEY (est_codigo);

REVOKE ALL ON TABLE estincid FROM PUBLIC;
REVOKE ALL ON TABLE estincid FROM sanjor;
GRANT ALL ON TABLE estincid TO sanjor;
GRANT ALL ON TABLE estincid TO nobody;

REVOKE ALL ON SEQUENCE estincid_est_codigo_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE estincid_est_codigo_seq FROM sanjor;
GRANT ALL ON SEQUENCE estincid_est_codigo_seq TO sanjor;
GRANT UPDATE ON SEQUENCE estincid_est_codigo_seq TO nobody;

CREATE TABLE priorincid (
    pri_codigo serial NOT NULL,
    pri_nombre character varying(150) NOT NULL,
    pri_estado integer DEFAULT 1
);


ALTER TABLE public.priorincid OWNER TO sanjor;

ALTER TABLE public.priorincid_pri_codigo_seq OWNER TO sanjor;

ALTER TABLE ONLY priorincid ALTER COLUMN pri_codigo SET DEFAULT nextval('priorincid_pri_codigo_seq'::regclass);

ALTER TABLE ONLY priorincid
    ADD CONSTRAINT priorincid_pkey PRIMARY KEY (pri_codigo);

REVOKE ALL ON TABLE priorincid FROM PUBLIC;
REVOKE ALL ON TABLE priorincid FROM sanjor;
GRANT ALL ON TABLE priorincid TO sanjor;
GRANT ALL ON TABLE priorincid TO nobody;

REVOKE ALL ON SEQUENCE priorincid_pri_codigo_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE priorincid_pri_codigo_seq FROM sanjor;
GRANT ALL ON SEQUENCE priorincid_pri_codigo_seq TO sanjor;
GRANT UPDATE ON SEQUENCE priorincid_pri_codigo_seq TO nobody;


CREATE TABLE categincid (
    cat_codigo serial NOT NULL,
    cat_nombre character varying(150) NOT NULL,
    cat_estado integer DEFAULT 1
);


ALTER TABLE public.categincid OWNER TO sanjor;

ALTER TABLE public.categincid_cat_codigo_seq OWNER TO sanjor;

ALTER TABLE ONLY categincid ALTER COLUMN cat_codigo SET DEFAULT nextval('categincid_cat_codigo_seq'::regclass);

ALTER TABLE ONLY categincid
    ADD CONSTRAINT categincid_pkey PRIMARY KEY (cat_codigo);

REVOKE ALL ON TABLE categincid FROM PUBLIC;
REVOKE ALL ON TABLE categincid FROM sanjor;
GRANT ALL ON TABLE categincid TO sanjor;
GRANT ALL ON TABLE categincid TO nobody;

REVOKE ALL ON SEQUENCE categincid_cat_codigo_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE categincid_cat_codigo_seq FROM sanjor;
GRANT ALL ON SEQUENCE categincid_cat_codigo_seq TO sanjor;
GRANT UPDATE ON SEQUENCE categincid_cat_codigo_seq TO nobody;


insert into estincid (est_nombre) values ('ABIERTO');
insert into estincid (est_nombre) values ('RESUELTO');
insert into estincid (est_nombre) values ('NO RESUELTO');
insert into estincid (est_nombre) values ('IGNORADO');


insert into priorincid (pri_nombre) values ('NULA');
insert into priorincid (pri_nombre) values ('BAJA');
insert into priorincid (pri_nombre) values ('MEDIA');
insert into priorincid (pri_nombre) values ('ALTA');
insert into priorincid (pri_nombre) values ('URGENTE');


insert into categincid (cat_nombre) values ('DESCONOCIDA');
insert into categincid (cat_nombre) values ('OPERATIVA');
insert into categincid (cat_nombre) values ('COBRANZAS');
insert into categincid (cat_nombre) values ('PAGO A PROVEEDORES');
insert into categincid (cat_nombre) values ('RECLAMOS');
insert into categincid (cat_nombre) values ('ADMINISTRACION');



CREATE TABLE incidencias (
    in_id serial NOT NULL,
    in_codemp integer NOT NULL,
    in_idptov integer NOT NULL,
    in_idcopr integer,
    in_codigo integer NOT NULL,
    in_anio integer NOT NULL,
    in_fechaest date,
    in_asunto text,
    in_visibilidad integer,
    in_categoria integer,
    in_estado integer DEFAULT 1
);


ALTER TABLE public.incidencias OWNER TO sanjor;

ALTER TABLE public.incidencias_in_id_seq OWNER TO sanjor;

ALTER TABLE ONLY incidencias ALTER COLUMN in_id SET DEFAULT nextval('incidencias_in_id_seq'::regclass);

ALTER TABLE ONLY incidencias
    ADD CONSTRAINT incidencias_pkey PRIMARY KEY (in_id);

ALTER TABLE ONLY incidencias ADD CONSTRAINT incidencias_in_codemp_fkey FOREIGN KEY (in_codemp) REFERENCES empresa(emp_codigo);
ALTER TABLE ONLY incidencias ADD CONSTRAINT incidencias_in_idptov_fkey FOREIGN KEY (in_idptov) REFERENCES ptovta(ptv_id);
ALTER TABLE ONLY incidencias ADD CONSTRAINT incidencias_in_idcopr_fkey FOREIGN KEY (in_idcopr) REFERENCES coprop(cop_id);
ALTER TABLE ONLY incidencias ADD CONSTRAINT incidencias_in_estado_fkey FOREIGN KEY (in_estado) REFERENCES estincid(est_codigo);
ALTER TABLE ONLY incidencias ADD CONSTRAINT incidencias_in_categoria_fkey FOREIGN KEY (in_categoria) REFERENCES categincid(cat_codigo);


REVOKE ALL ON TABLE incidencias FROM PUBLIC;
REVOKE ALL ON TABLE incidencias FROM sanjor;
GRANT ALL ON TABLE incidencias TO sanjor;
GRANT ALL ON TABLE incidencias TO nobody;

REVOKE ALL ON SEQUENCE incidencias_in_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE incidencias_in_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE incidencias_in_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE incidencias_in_id_seq TO nobody;


CREATE TABLE operincid (
    oi_id serial NOT NULL,
    oi_idinc integer NOT NULL,
    oi_operal integer NOT NULL,
    oi_operasign integer NOT NULL,
    oi_prioridad integer,
    oi_observ text,
    oi_fecha date default now(),
    oi_hora time without time zone default now(),
    oi_estado integer DEFAULT 1
);


ALTER TABLE public.operincid OWNER TO sanjor;

ALTER TABLE public.operincid_oi_id_seq OWNER TO sanjor;

ALTER TABLE ONLY operincid ALTER COLUMN oi_id SET DEFAULT nextval('operincid_oi_id_seq'::regclass);

ALTER TABLE ONLY operincid
    ADD CONSTRAINT operincid_pkey PRIMARY KEY (oi_id);

ALTER TABLE ONLY operincid ADD CONSTRAINT operincid_oi_idinc_fkey FOREIGN KEY (oi_idinc) REFERENCES incidencias(in_id);
ALTER TABLE ONLY operincid ADD CONSTRAINT operincid_oi_operal_fkey FOREIGN KEY (oi_operal) REFERENCES operadores(operad_id);
ALTER TABLE ONLY operincid ADD CONSTRAINT operincid_oi_operasign_fkey FOREIGN KEY (oi_operasign) REFERENCES operadores(operad_id);
ALTER TABLE ONLY operincid ADD CONSTRAINT operincid_oi_prioridad_fkey FOREIGN KEY (oi_prioridad) REFERENCES priorincid(pri_codigo);
ALTER TABLE ONLY operincid ADD CONSTRAINT operincid_oi_estado_fkey FOREIGN KEY (oi_estado) REFERENCES estincid(est_codigo);


REVOKE ALL ON TABLE operincid FROM PUBLIC;
REVOKE ALL ON TABLE operincid FROM sanjor;
GRANT ALL ON TABLE operincid TO sanjor;
GRANT ALL ON TABLE operincid TO nobody;

REVOKE ALL ON SEQUENCE operincid_oi_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE operincid_oi_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE operincid_oi_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE operincid_oi_id_seq TO nobody;



CREATE TABLE imgincid (
    img_codigo serial NOT NULL,
    img_idinc integer NOT NULL,
    img_file bytea
);


ALTER TABLE public.imgincid OWNER TO sanjor;

ALTER TABLE public.imgincid_img_codigo_seq OWNER TO sanjor;

ALTER TABLE ONLY imgincid ALTER COLUMN img_codigo SET DEFAULT nextval('imgincid_img_codigo_seq'::regclass);

ALTER TABLE ONLY imgincid
    ADD CONSTRAINT imgincid_pkey PRIMARY KEY (img_codigo);

ALTER TABLE ONLY imgincid ADD CONSTRAINT imgincid_img_idinc_fkey FOREIGN KEY (img_idinc) REFERENCES incidencias(in_id);

REVOKE ALL ON TABLE imgincid FROM PUBLIC;
REVOKE ALL ON TABLE imgincid FROM sanjor;
GRANT ALL ON TABLE imgincid TO sanjor;
GRANT ALL ON TABLE imgincid TO nobody;

REVOKE ALL ON SEQUENCE imgincid_img_codigo_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE imgincid_img_codigo_seq FROM sanjor;
GRANT ALL ON SEQUENCE imgincid_img_codigo_seq TO sanjor;
GRANT UPDATE ON SEQUENCE imgincid_img_codigo_seq TO nobody;


