-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.denuncias
(
    id_denuncia integer NOT NULL DEFAULT nextval('denuncias_id_denuncia_seq'::regclass),
    estatus character varying COLLATE pg_catalog."default",
    fecha_hora time without time zone,
    resumen_reporte character varying COLLATE pg_catalog."default",
    observacion character varying COLLATE pg_catalog."default",
    id_personas integer,
    id_usuario integer,
    id_reporte integer,
    id_estado integer,
    id_municipio integer,
    id_parroquia integer,
    id_linea_transp integer,
    id_procedencia integer,
    CONSTRAINT denuncias_pkey PRIMARY KEY (id_denuncia)
);

CREATE TABLE IF NOT EXISTS public.procedencias
(
    id_procedencia integer NOT NULL DEFAULT nextval('procedencias_id_procedencia_seq'::regclass),
    personal character varying[] COLLATE pg_catalog."default",
    correo character varying COLLATE pg_catalog."default",
    llamada character varying COLLATE pg_catalog."default",
    red_social character varying COLLATE pg_catalog."default",
    qr character varying COLLATE pg_catalog."default",
    CONSTRAINT procedencias_pkey PRIMARY KEY (id_procedencia)
);

CREATE TABLE IF NOT EXISTS public.userbackend
(
    id_usuario integer NOT NULL DEFAULT nextval('userbackend_id_usuario_seq'::regclass),
    cedula character varying COLLATE pg_catalog."default",
    username character varying COLLATE pg_catalog."default",
    auth_key character varying COLLATE pg_catalog."default",
    password_hash character varying COLLATE pg_catalog."default",
    password_reset_token character varying COLLATE pg_catalog."default",
    correo character varying COLLATE pg_catalog."default",
    status smallint,
    created_at integer,
    updated_at integer,
    nomper character varying COLLATE pg_catalog."default",
    apeper character varying COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
    role integer,
    CONSTRAINT userbackend_pkey PRIMARY KEY (id_usuario),
    CONSTRAINT userbackend_correo_key UNIQUE (correo),
    CONSTRAINT userbackend_password_reset_token_key UNIQUE (password_reset_token),
    CONSTRAINT userbackend_username_key UNIQUE (username)
);

CREATE TABLE IF NOT EXISTS public.personas
(
    nac character varying COLLATE pg_catalog."default",
    ci character varying COLLATE pg_catalog."default",
    nombre character varying COLLATE pg_catalog."default",
    apellido character varying COLLATE pg_catalog."default",
    rif character varying COLLATE pg_catalog."default",
    telefono character varying COLLATE pg_catalog."default",
    correo character varying COLLATE pg_catalog."default",
    fecha date,
    id_usuario integer,
    id_personas integer NOT NULL DEFAULT nextval('personas_id_personas_seq'::regclass),
    CONSTRAINT pk_id_personas PRIMARY KEY (id_personas)
        INCLUDE(id_personas)
);

CREATE TABLE IF NOT EXISTS public.lineas_transporte
(
    id_linea_transp integer NOT NULL DEFAULT nextval('lineas_transporte_id_linea_transp_seq'::regclass),
    desc_linea_transp character varying COLLATE pg_catalog."default",
    CONSTRAINT lineas_transporte_pkey PRIMARY KEY (id_linea_transp)
);

ALTER TABLE IF EXISTS public.denuncias
    ADD CONSTRAINT fk_id_procedencia FOREIGN KEY (id_procedencia)
    REFERENCES public.procedencias (id_procedencia) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.denuncias
    ADD CONSTRAINT fk_id_usuario FOREIGN KEY (id_usuario)
    REFERENCES public.userbackend (id_usuario) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.denuncias
    ADD CONSTRAINT fk_linea_transporte FOREIGN KEY (id_linea_transp)
    REFERENCES public.lineas_transporte (id_linea_transp) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.personas
    ADD CONSTRAINT fk_id_usuario FOREIGN KEY (id_usuario)
    REFERENCES public.userbackend (id_usuario) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;