-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';
-- public.dignidad definition

-- Drop table

-- DROP TABLE public.dignidad;

CREATE TABLE public.dignidad (
	id numeric NOT NULL, -- Unique identifier
	nombre varchar(50) NOT NULL, -- Name of a dignity
	ambio varchar(1) NULL, -- Level of jurisdiction of dignity (national, province...etc)
	estado int2 NULL, -- Status of dignity
	orden int4 NULL, -- Orden of dignity in the processing
	CONSTRAINT dignidad_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.dignidad IS 'table that stores information about the privincies (highest level of the political division)';

-- Column comments

COMMENT ON COLUMN public.dignidad.id IS 'Unique identifier';
COMMENT ON COLUMN public.dignidad.nombre IS 'Name of a dignity';
COMMENT ON COLUMN public.dignidad.ambio IS 'Level of jurisdiction of dignity (national, province...etc)';
COMMENT ON COLUMN public.dignidad.estado IS 'Status of dignity';
COMMENT ON COLUMN public.dignidad.orden IS 'Orden of dignity in the processing';

-- Permissions

ALTER TABLE public.dignidad OWNER TO postgres;
GRANT ALL ON TABLE public.dignidad TO postgres;


-- public.imagensegmento definition

-- Drop table

-- DROP TABLE public.imagensegmento;

CREATE TABLE public.imagensegmento (
	id_junta int4 NULL, -- Id junta
	id_dignidad varchar NULL,
	id_candidato varchar NULL,
	imagen oid NULL, -- image
	nombre varchar NULL, -- Image name
	hash varchar NULL, -- Image hash code
	pathipfs varchar NULL, -- Image path on the IPFS network
	fecha date NULL -- Cecord creation date
);

-- Column comments

COMMENT ON COLUMN public.imagensegmento.id_junta IS 'Id junta';
COMMENT ON COLUMN public.imagensegmento.imagen IS 'image';
COMMENT ON COLUMN public.imagensegmento.nombre IS 'Image name';
COMMENT ON COLUMN public.imagensegmento.hash IS 'Image hash code';
COMMENT ON COLUMN public.imagensegmento.pathipfs IS 'Image path on the IPFS network';
COMMENT ON COLUMN public.imagensegmento.fecha IS 'Cecord creation date';

-- Permissions

ALTER TABLE public.imagensegmento OWNER TO postgres;
GRANT ALL ON TABLE public.imagensegmento TO postgres;


-- public.modulo definition

-- Drop table

-- DROP TABLE public.modulo;

CREATE TABLE public.modulo (
	id int4 NOT NULL, -- Modulo id
	nombre varchar NULL, -- Name of module
	codigo varchar NULL, -- Module code
	url varchar NULL, -- Address to access the module
	icono varchar NULL, -- Image that identifies a module
	estado varchar NULL, -- Module status
	fechacreacion date NULL, -- Module creation date
	fechamodificacion date NULL, -- Module modification date
	CONSTRAINT modulo_unique UNIQUE (id)
);
COMMENT ON TABLE public.modulo IS 'Table that stores information about the modules of which the system is composed';

-- Column comments

COMMENT ON COLUMN public.modulo.id IS 'Modulo id';
COMMENT ON COLUMN public.modulo.nombre IS 'Name of module';
COMMENT ON COLUMN public.modulo.codigo IS 'Module code';
COMMENT ON COLUMN public.modulo.url IS 'Address to access the module';
COMMENT ON COLUMN public.modulo.icono IS 'Image that identifies a module';
COMMENT ON COLUMN public.modulo.estado IS 'Module status';
COMMENT ON COLUMN public.modulo.fechacreacion IS 'Module creation date';
COMMENT ON COLUMN public.modulo.fechamodificacion IS 'Module modification date';

-- Permissions

ALTER TABLE public.modulo OWNER TO postgres;
GRANT ALL ON TABLE public.modulo TO postgres;


-- public.partido definition

-- Drop table

-- DROP TABLE public.partido;

CREATE TABLE public.partido (
	id numeric NOT NULL, -- Unique identifier
	nombre varchar(1000) NOT NULL, -- Name of a political parties
	siglas varchar(150) NULL, -- Acronym or short name of a political party
	lista varchar(50) NULL, -- List number assigned to a political party, does not refer to the order but to the identity of a party
	orden int4 NULL, -- Order assigned to a political party
	imagen int2 NULL, -- Image of political party image/logo
	CONSTRAINT partido_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.partido IS 'Table that stores information about political parties';

-- Column comments

COMMENT ON COLUMN public.partido.id IS 'Unique identifier';
COMMENT ON COLUMN public.partido.nombre IS 'Name of a political parties';
COMMENT ON COLUMN public.partido.siglas IS 'Acronym or short name of a political party';
COMMENT ON COLUMN public.partido.lista IS 'List number assigned to a political party, does not refer to the order but to the identity of a party';
COMMENT ON COLUMN public.partido.orden IS 'Order assigned to a political party';
COMMENT ON COLUMN public.partido.imagen IS 'Image of political party image/logo';

-- Permissions

ALTER TABLE public.partido OWNER TO postgres;
GRANT ALL ON TABLE public.partido TO postgres;


-- public.provincia definition

-- Drop table

-- DROP TABLE public.provincia;

CREATE TABLE public.provincia (
	id int4 NOT NULL, -- Unique identifier
	nombre varchar(50) NOT NULL, -- Name of provincia
	CONSTRAINT provincia_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.provincia IS 'Table that stores information about the privincies (highest level of the political division)';

-- Column comments

COMMENT ON COLUMN public.provincia.id IS 'Unique identifier';
COMMENT ON COLUMN public.provincia.nombre IS 'Name of provincia';

-- Permissions

ALTER TABLE public.provincia OWNER TO postgres;
GRANT ALL ON TABLE public.provincia TO postgres;


-- public.candidato definition

-- Drop table

-- DROP TABLE public.candidato;

CREATE TABLE public.candidato (
	id numeric NOT NULL, -- Unique identifier
	cedula int2 NULL, -- DNI of candidate
	nombre varchar(50) NOT NULL, -- Name of a candidate
	id_partido numeric NULL,
	CONSTRAINT candidato_pk PRIMARY KEY (id),
	CONSTRAINT partido_fk FOREIGN KEY (id_partido) REFERENCES public.partido(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE
);
COMMENT ON TABLE public.candidato IS 'table that stores information about the privincies (highest level of the political division)';

-- Column comments

COMMENT ON COLUMN public.candidato.id IS 'Unique identifier';
COMMENT ON COLUMN public.candidato.cedula IS 'DNI of candidate';
COMMENT ON COLUMN public.candidato.nombre IS 'Name of a candidate';

-- Permissions

ALTER TABLE public.candidato OWNER TO postgres;
GRANT ALL ON TABLE public.candidato TO postgres;


-- public.canton definition

-- Drop table

-- DROP TABLE public.canton;

CREATE TABLE public.canton (
	id numeric NOT NULL, -- Unique identifier
	nombre varchar(50) NOT NULL, -- name of canton
	id_provincia int4 NULL, -- id of provincia
	CONSTRAINT canton_pk PRIMARY KEY (id),
	CONSTRAINT canton_provincia_fk FOREIGN KEY (id_provincia) REFERENCES public.provincia(id)
);
COMMENT ON TABLE public.canton IS 'table that stores information about the privincies (highest level, second level of the political division)
Unique identifier';

-- Column comments

COMMENT ON COLUMN public.canton.id IS 'Unique identifier';
COMMENT ON COLUMN public.canton.nombre IS 'name of canton';
COMMENT ON COLUMN public.canton.id_provincia IS 'id of provincia';

-- Permissions

ALTER TABLE public.canton OWNER TO postgres;
GRANT ALL ON TABLE public.canton TO postgres;


-- public.menu definition

-- Drop table

-- DROP TABLE public.menu;

CREATE TABLE public.menu (
	id int4 NULL,
	id_modulo int4 NULL,
	nombre varchar NULL,
	icono varchar NULL,
	orden int4 NULL,
	estado bool NULL,
	CONSTRAINT menu_modulo_fk FOREIGN KEY (id_modulo) REFERENCES public.modulo(id)
);

-- Permissions

ALTER TABLE public.menu OWNER TO postgres;
GRANT ALL ON TABLE public.menu TO postgres;


-- public.parroquia definition

-- Drop table

-- DROP TABLE public.parroquia;

CREATE TABLE public.parroquia (
	id numeric NOT NULL, -- Unique identifier
	id_canton numeric NULL,
	nombre varchar(50) NOT NULL, -- Name of parishes
	CONSTRAINT parroquia_pk PRIMARY KEY (id),
	CONSTRAINT canton_fk FOREIGN KEY (id_canton) REFERENCES public.canton(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE
);
COMMENT ON TABLE public.parroquia IS 'table that stores the information of the parishes (third level of the political division)';

-- Column comments

COMMENT ON COLUMN public.parroquia.id IS 'Unique identifier';
COMMENT ON COLUMN public.parroquia.nombre IS 'Name of parishes';

-- Permissions

ALTER TABLE public.parroquia OWNER TO postgres;
GRANT ALL ON TABLE public.parroquia TO postgres;


-- public.rol definition

-- Drop table

-- DROP TABLE public.rol;

CREATE TABLE public.rol (
	id int4 NOT NULL, -- Id
	nombre varchar NULL, -- Name of rol
	estado int4 NULL, -- Status of rol
	tipo bpchar(1) NULL, -- Type of role, depending on the place from which you want to access (desktop, web)
	fechacreacion date NULL, -- Creation date
	fechamodificacion date NULL, -- Modification date
	id_modulo int4 NULL, -- Module to which the role belongs
	CONSTRAINT rol_pk PRIMARY KEY (id),
	CONSTRAINT rol_modulo_fk FOREIGN KEY (id_modulo) REFERENCES public.modulo(id)
);
COMMENT ON TABLE public.rol IS 'table with the role (group of users according to their responsibility profile)';

-- Column comments

COMMENT ON COLUMN public.rol.id IS 'Id';
COMMENT ON COLUMN public.rol.nombre IS 'Name of rol';
COMMENT ON COLUMN public.rol.estado IS 'Status of rol';
COMMENT ON COLUMN public.rol.tipo IS 'Type of role, depending on the place from which you want to access (desktop, web)';
COMMENT ON COLUMN public.rol.fechacreacion IS 'Creation date';
COMMENT ON COLUMN public.rol.fechamodificacion IS 'Modification date';
COMMENT ON COLUMN public.rol.id_modulo IS 'Module to which the role belongs';

-- Permissions

ALTER TABLE public.rol OWNER TO postgres;
GRANT ALL ON TABLE public.rol TO postgres;


-- public.usuario definition

-- Drop table

-- DROP TABLE public.usuario;

CREATE TABLE public.usuario (
	id int4 NULL, -- Unique identifier of the user in the system
	cedula varchar NULL, -- dni of user
	nombre varchar NULL, -- Name of user
	emial varchar NULL, -- Email of user
	estado varchar NULL, -- Status of user
	clave varchar NULL, -- password (encrypt)
	id_provincia int4 NULL, -- Province to which the user belongs
	id_rol int4 NULL, -- User role, to know what system options they can access
	ultimoacceso date NULL, -- Date of last access to the system
	CONSTRAINT usuario_unique UNIQUE (id),
	CONSTRAINT usuario_provincia_fk FOREIGN KEY (id_provincia) REFERENCES public.provincia(id),
	CONSTRAINT usuario_rol_fk FOREIGN KEY (id_rol) REFERENCES public.rol(id)
);
COMMENT ON TABLE public.usuario IS 'Table containing the information of the system users';

-- Column comments

COMMENT ON COLUMN public.usuario.id IS 'Unique identifier of the user in the system';
COMMENT ON COLUMN public.usuario.cedula IS 'dni of user';
COMMENT ON COLUMN public.usuario.nombre IS 'Name of user';
COMMENT ON COLUMN public.usuario.emial IS 'Email of user';
COMMENT ON COLUMN public.usuario.estado IS 'Status of user';
COMMENT ON COLUMN public.usuario.clave IS 'password (encrypt)';
COMMENT ON COLUMN public.usuario.id_provincia IS 'Province to which the user belongs';
COMMENT ON COLUMN public.usuario.id_rol IS 'User role, to know what system options they can access';
COMMENT ON COLUMN public.usuario.ultimoacceso IS 'Date of last access to the system';

-- Permissions

ALTER TABLE public.usuario OWNER TO postgres;
GRANT ALL ON TABLE public.usuario TO postgres;


-- public.zona definition

-- Drop table

-- DROP TABLE public.zona;

CREATE TABLE public.zona (
	id numeric NOT NULL, -- Unique identifier
	nombre varchar(50) NULL, -- Unique identifier
	id_parroquia numeric NULL,
	CONSTRAINT zona_pk PRIMARY KEY (id),
	CONSTRAINT parroquia_fk FOREIGN KEY (id_parroquia) REFERENCES public.parroquia(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE
);
COMMENT ON TABLE public.zona IS 'table that stores the information of the parishes (fourth level of the political division)
Unique identifier';

-- Column comments

COMMENT ON COLUMN public.zona.id IS 'Unique identifier';
COMMENT ON COLUMN public.zona.nombre IS 'Unique identifier';

-- Permissions

ALTER TABLE public.zona OWNER TO postgres;
GRANT ALL ON TABLE public.zona TO postgres;


-- public.junta definition

-- Drop table

-- DROP TABLE public.junta;

CREATE TABLE public.junta (
	id numeric NOT NULL, -- Unique identifier
	id_zona numeric NULL,
	junta int4 NULL, -- Number assigned to a meeting
	sexo varchar(1) NULL, -- Sex to which a junta belongs
	electores int4 NULL, -- Number of voters assigned to a "junta"
	estado int4 NULL, -- Status of a "junta" in the process
	CONSTRAINT junta_pk PRIMARY KEY (id),
	CONSTRAINT zona_fk FOREIGN KEY (id_zona) REFERENCES public.zona(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE
);
COMMENT ON TABLE public.junta IS 'table that stores the information of the parishes (fourth level of the political division)
Unique identifier';

-- Column comments

COMMENT ON COLUMN public.junta.id IS 'Unique identifier';
COMMENT ON COLUMN public.junta.junta IS 'Number assigned to a meeting';
COMMENT ON COLUMN public.junta.sexo IS 'Sex to which a junta belongs';
COMMENT ON COLUMN public.junta.electores IS 'Number of voters assigned to a "junta"';
COMMENT ON COLUMN public.junta.estado IS 'Status of a "junta" in the process';

-- Permissions

ALTER TABLE public.junta OWNER TO postgres;
GRANT ALL ON TABLE public.junta TO postgres;


-- public.rolusuario definition

-- Drop table

-- DROP TABLE public.rolusuario;

CREATE TABLE public.rolusuario (
	id int4 NULL,
	id_rol int4 NULL,
	id_usuario int4 NULL,
	estado bool NULL,
	fechacreacion date NULL,
	fechamodificacion date NULL,
	CONSTRAINT rolusuario_rol_fk FOREIGN KEY (id_rol) REFERENCES public.rol(id),
	CONSTRAINT rolusuario_usuario_fk FOREIGN KEY (id_usuario) REFERENCES public.usuario(id)
);

-- Permissions

ALTER TABLE public.rolusuario OWNER TO postgres;
GRANT ALL ON TABLE public.rolusuario TO postgres;


-- public.acta definition

-- Drop table

-- DROP TABLE public.acta;

CREATE TABLE public.acta (
	id numeric NOT NULL, -- Unique identifier
	id_dignidad numeric NULL,
	id_junta numeric NULL,
	seguridad int4 NULL, -- Minute security code to avoid falsification of a minute
	estado int4 NULL, -- Status of a record in the process, in what phase of the process it is
	usuarioescaneo int4 NULL, -- User code that scanned the vote record
	fechaescaneo date NULL, -- Date on which a record was scanned
	usuariodigitacion int4 NULL, -- user code that entered the votes
	fechadigitacion date NULL, -- Date on which the votes of the candidates in the minutes were entered
	usuariocontrol int4 NULL, -- User code that performed quality control of the votes of a minute
	fechacotrol date NULL, -- Date on which quality control was carried out on votes that do not match between intelligent recognition and entry
	peticion int4 NULL, -- know if the minutes were taken by a user to enter votes or quality control, this is used to prevent a minute from being taken by several users at the same time
	sufragantesicr int4 NULL, -- Number of voters of the act, this number was obtained in the intelligent recognition /IA
	sufragantesdigitacion int4 NULL, -- Number of voters of the act, this number was obtained by typing
	sufragantescontrol int4 NULL, -- Number of voters of the minutes, this number was obtained by typing in the quality control
	sufragantes int4 NULL, -- Number of voters of the minutes, corresponds to the number of people who voted
	blacosicr int4 NULL, -- Number of voters who voted blank, value obtained from automatic recognition / AI
	blancosdigitacion int4 NULL, -- Number of voters who voted blank, value obtained from the entry of votes
	blancoscontrol int4 NULL, -- Number of voters who voted blank, values ​​obtained from quality control
	blancos int4 NULL, -- Number of voters who voted blank
	nulosicr int4 NULL, -- Number of voters who annulled the vote, this is taken from automatic recognition
	nulosdigitacion int4 NULL, -- Number of voters who annulled the vote, this is taken from the manual entry of votes
	nulos int4 NULL, -- Number of voters who annulled the vote
	CONSTRAINT acta_pk PRIMARY KEY (id),
	CONSTRAINT dignidad_fk FOREIGN KEY (id_dignidad) REFERENCES public.dignidad(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE,
	CONSTRAINT junta_fk FOREIGN KEY (id_junta) REFERENCES public.junta(id) MATCH FULL ON DELETE SET NULL ON UPDATE CASCADE
);
COMMENT ON TABLE public.acta IS 'Table that stores information from the minutes of a meeting; An act is a minute where the votes that the candidates obtained in that meeting are written.';

-- Column comments

COMMENT ON COLUMN public.acta.id IS 'Unique identifier';
COMMENT ON COLUMN public.acta.seguridad IS 'Minute security code to avoid falsification of a minute';
COMMENT ON COLUMN public.acta.estado IS 'Status of a record in the process, in what phase of the process it is';
COMMENT ON COLUMN public.acta.usuarioescaneo IS 'User code that scanned the vote record';
COMMENT ON COLUMN public.acta.fechaescaneo IS 'Date on which a record was scanned';
COMMENT ON COLUMN public.acta.usuariodigitacion IS 'user code that entered the votes';
COMMENT ON COLUMN public.acta.fechadigitacion IS 'Date on which the votes of the candidates in the minutes were entered';
COMMENT ON COLUMN public.acta.usuariocontrol IS 'User code that performed quality control of the votes of a minute';
COMMENT ON COLUMN public.acta.fechacotrol IS 'Date on which quality control was carried out on votes that do not match between intelligent recognition and entry';
COMMENT ON COLUMN public.acta.peticion IS 'know if the minutes were taken by a user to enter votes or quality control, this is used to prevent a minute from being taken by several users at the same time';
COMMENT ON COLUMN public.acta.sufragantesicr IS 'Number of voters of the act, this number was obtained in the intelligent recognition /IA';
COMMENT ON COLUMN public.acta.sufragantesdigitacion IS 'Number of voters of the act, this number was obtained by typing';
COMMENT ON COLUMN public.acta.sufragantescontrol IS 'Number of voters of the minutes, this number was obtained by typing in the quality control';
COMMENT ON COLUMN public.acta.sufragantes IS 'Number of voters of the minutes, corresponds to the number of people who voted';
COMMENT ON COLUMN public.acta.blacosicr IS 'Number of voters who voted blank, value obtained from automatic recognition / AI';
COMMENT ON COLUMN public.acta.blancosdigitacion IS 'Number of voters who voted blank, value obtained from the entry of votes';
COMMENT ON COLUMN public.acta.blancoscontrol IS 'Number of voters who voted blank, values ​​obtained from quality control';
COMMENT ON COLUMN public.acta.blancos IS 'Number of voters who voted blank';
COMMENT ON COLUMN public.acta.nulosicr IS 'Number of voters who annulled the vote, this is taken from automatic recognition';
COMMENT ON COLUMN public.acta.nulosdigitacion IS 'Number of voters who annulled the vote, this is taken from the manual entry of votes';
COMMENT ON COLUMN public.acta.nulos IS 'Number of voters who annulled the vote';

-- Permissions

ALTER TABLE public.acta OWNER TO postgres;
GRANT ALL ON TABLE public.acta TO postgres;


-- public.imagenacta definition

-- Drop table

-- DROP TABLE public.imagenacta;

CREATE TABLE public.imagenacta (
	id_acta int4 NULL, -- id of "junta! to which the image belongs
	image oid NULL, -- image of the "acta de escrutino" sheet
	nombre varchar NULL, -- Name of image
	pagina int2 NULL, -- Page number
	hash varchar NULL, -- image data hash
	pathipfs varchar NULL, -- Address of image in IPFS
	fecha date NULL, -- record creation date
	CONSTRAINT imageacta_junta_fk FOREIGN KEY (id_acta) REFERENCES public.junta(id)
);
COMMENT ON TABLE public.imagenacta IS 'Table that stores the image of the sheets of a ballot report "acta de escrutinio", also stores the meta data of the images.';

-- Column comments

COMMENT ON COLUMN public.imagenacta.id_acta IS 'id of "junta! to which the image belongs';
COMMENT ON COLUMN public.imagenacta.image IS 'image of the "acta de escrutino" sheet';
COMMENT ON COLUMN public.imagenacta.nombre IS 'Name of image';
COMMENT ON COLUMN public.imagenacta.pagina IS 'Page number';
COMMENT ON COLUMN public.imagenacta.hash IS 'image data hash';
COMMENT ON COLUMN public.imagenacta.pathipfs IS 'Address of image in IPFS';
COMMENT ON COLUMN public.imagenacta.fecha IS 'record creation date';

-- Permissions

ALTER TABLE public.imagenacta OWNER TO postgres;
GRANT ALL ON TABLE public.imagenacta TO postgres;


-- public.votos definition

-- Drop table

-- DROP TABLE public.votos;

CREATE TABLE public.votos (
	id_acta int4 NULL,
	id_candidato int4 NULL,
	votosicr int4 NULL, -- Number of votes that the candidate obtained through automatic recognition
	votosdigitacion int4 NULL, -- Number of votes that the candidate obtained by manual typing
	votoscontrol int4 NULL, -- Number of votes the candidate obtained in the quality control
	votos int4 NULL, -- Number of votes the candidate obtained
	CONSTRAINT votos_acta_fk FOREIGN KEY (id_acta) REFERENCES public.acta(id),
	CONSTRAINT votos_candidato_fk FOREIGN KEY (id_candidato) REFERENCES public.candidato(id)
);
COMMENT ON TABLE public.votos IS 'This table stores the votes that the candidates received in a "junta"';

-- Column comments

COMMENT ON COLUMN public.votos.votosicr IS 'Number of votes that the candidate obtained through automatic recognition';
COMMENT ON COLUMN public.votos.votosdigitacion IS 'Number of votes that the candidate obtained by manual typing';
COMMENT ON COLUMN public.votos.votoscontrol IS 'Number of votes the candidate obtained in the quality control';
COMMENT ON COLUMN public.votos.votos IS 'Number of votes the candidate obtained';

-- Permissions

ALTER TABLE public.votos OWNER TO postgres;
GRANT ALL ON TABLE public.votos TO postgres;




-- Permissions

GRANT ALL ON SCHEMA public TO pg_database_owner;
GRANT USAGE ON SCHEMA public TO public;