/*IMPORTANTE: TODO ESTO DEBE SER EJECUTADO CON EL USUARIO POSTGRES O UN USUARIO QUE SEA SUPERUSER*/
--creamos la bd
--DROP DATABASE pruebas;
CREATE DATABASE pruebas
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       template template0
       CONNECTION LIMIT = -1;


-- con este usuario se accederá hacia la bd, como se puede ver es un usuario normal
--DROP ROLE usuariochimbo;

CREATE ROLE usuariochimbo LOGIN
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

-- Importante: DE AQUI EN ADELANTE DEBES EJECUTAR ESTO CON LA BD PRUEBAS

/*Por estetica y por facilidad de administración de permisos, no se recomienda public para almacenar functions*/

--este schema servirá para almacenar las functions y darle permiso a cada usuario sobre que tablas puede acceder
create schema accesobd;

--esta extensión te permitirá leer/guardar el registro eliminado en el caso de borrado fisico con copia

-- DROP EXTENSION hstore;
CREATE EXTENSION hstore;

/*borrados lógicos*/
drop TABLE clientesborradofechas;

CREATE TABLE clientesborradofechas
(
  idcliente bigserial NOT NULL,
  codcliente character varying(20) NOT NULL,
  nomcliente character varying(120) NOT NULL,
  edad integer,
  sepso character varying(1),
  fechanac date NOT NULL,
  fecha_inicial date not null,
  fecha_final date,
  --verificar con unique check
  CONSTRAINT pk_clientes PRIMARY KEY (idcliente ),
  CONSTRAINT chk_sepso CHECK (sepso::text = 'F'::text OR sepso::text = 'M'::text OR sepso IS NULL)
);

drop TABLE clientesborradoestado;

CREATE TABLE clientesborradoestado
(
  idcliente bigserial NOT NULL,
  codcliente character varying(20) NOT NULL,
  nomcliente character varying(120) NOT NULL,
  edad integer,
  sepso character varying(1),
  fechanac date NOT NULL,
  estado varchar(1) not null default 'I',
  fecha_novedad date,
  CONSTRAINT pk_clientes2 PRIMARY KEY (idcliente ),
  --verificar con unique check
  CONSTRAINT chk_sepso CHECK (sepso::text = 'F'::text OR sepso::text = 'M'::text OR sepso IS NULL),
  CONSTRAINT chk_estado CHECK (estado::text = 'I'::text OR estado::text = 'M'::text OR estado::text = 'B'::text )
);

/* borrado físico*/

--creamos un schema que permita demostrar que funciona con cualquier schema la recuperacion
create schema test;

drop TABLE test.clientesborradocopia;

CREATE TABLE test.clientesborradocopia
(
  idcliente bigserial NOT NULL,
  codcliente character varying(20) NOT NULL,
  nomcliente character varying(120) NOT NULL,
  edad integer,
  sepso character varying(1),
  fechanac date NOT NULL,
  foto bytea,
  --verificar con unique check
  CONSTRAINT pk_clientes3 PRIMARY KEY (idcliente ),
  CONSTRAINT chk_sepso CHECK (sepso::text = 'F'::text OR sepso::text = 'M'::text OR sepso IS NULL)
);

drop TABLE borradoscopia;

CREATE TABLE borradoscopia
(
  usuario character varying(100) not null,	
  maquina inet not null,
  esquema character varying(80) NOT NULL,
  tabla character varying(80) NOT NULL,
  registro hstore
);
comment on column borradoscopia.registro is 'Registro que contiene los datos y campos almacenados en un hstore';

CREATE INDEX ind_reg ON borradoscopia USING BTREE (registro);

/*crear las funciones que accederán a las tablas*/



/*y asi para todos las tablas o funciones de la BD que no desee que tengan acceso*/

/*borrado fisico*/
/* esta funcion va asociada a todos los triggers de cada tabla donde se deseé el borrado fisico con copia*/
create or replace function public.fn_borradocopia () returns trigger
as
$$
declare 
	reg hstore; 
	r record;
begin	
	IF TG_WHEN != 'BEFORE' OR TG_OP != 'DELETE' OR (TG_LEVEL = 'STATEMENT' AND TG_OP <> 'TRUNCATE') THEN
		RAISE EXCEPTION 'Esta función solo aplica cuando el trigger es BEFORE DELETE ... FOR EACH ROW';
	END IF;
	reg:=hstore(OLD);
	insert into borradoscopia values (current_user,inet_client_addr(),TG_TABLE_SCHEMA, TG_TABLE_NAME,reg);
	return OLD;
end;
$$
LANGUAGE plpgsql VOLATILE
  COST 100;

--aca van cada trigger para cada tabla
drop TRIGGER trg_borradocopia on test.clientesborradocopia;
CREATE TRIGGER trg_borradocopia BEFORE DELETE ON test.clientesborradocopia
    FOR EACH ROW EXECUTE PROCEDURE  public.fn_borradocopia () ;  

--esta funcion es tan importante como la anterior, sirve para recuperar el registro. 
--Esta funcion lo ideal es que solo un usuario autorizado pueda acceder a ella, no cualquiera, en este caso solo postgres puede acceder
create or replace function public.fn_RecuperarReg (p_schema varchar, p_tabla varchar, p_campo varchar, p_valor varchar, p_maquina inet, p_usuario varchar) returns varchar
as
$$
declare
    biRegRec bigint := 0;
    biRegNoRec bigint := 0;
    regrec hstore;
begin   
    FOR regrec IN SELECT registro from borradoscopia 
		where registro -> p_campo like p_valor
		and tabla = p_tabla 
		and esquema = p_schema
		and coalesce(maquina,'0.0.0.0') = coalesce(p_maquina,coalesce(maquina,'0.0.0.0')) --campo opcional
		and coalesce(usuario,'') like coalesce('%'||p_usuario||'%','%%') --campo opcional
		and coalesce(usuario,'') like coalesce('%'||p_usuario||'%','%%') --campo opcional
    LOOP
	begin
		execute 'insert into ' || (quote_ident(p_schema)||'.'||quote_ident(p_tabla))::regclass 
		|| ' SELECT * FROM populate_record(null::'|| (quote_ident(p_schema)||'.'||quote_ident(p_tabla))::regclass ||', $1)' using regrec;
		--debe borrar cada registro que no haya fallado su copia
		delete from borradoscopia where registro = regrec;
		biRegRec:=biRegRec+1;
	exception
	when others then		
		raise notice '%',SQLERRM;
		biRegNoRec:=biRegNoRec+1;
	end;	
    END LOOP;
    if (biRegNoRec=0) and (biRegRec >0) then
	return biRegRec || ' Registro(s) recuperado(s) con éxito. 100% registros recuperados';
    elseif biRegNoRec>0 then 
	return biRegNoRec || ' no se pudieron recuperar'|| biRegRec || ' Registro(s) recuperado(s) con éxito, ' ;
    else 		
 	return 'No hay registros por recuperar.';
    end if;
    
end;
$$
LANGUAGE plpgsql VOLATILE
  COST 100;

/*pruebas borrado fisico*/

INSERT INTO test.clientesborradocopia(idcliente, codcliente, nomcliente, edad, sepso, fechanac) 
VALUES ('1'::bigint, 'sdsdfsd'::text, 'jajaja'::text, '34'::integer, 'M'::text, '15-04-1980'::date);

INSERT INTO test.clientesborradocopia(idcliente, codcliente, nomcliente, edad, sepso, fechanac,foto) 
VALUES ('2'::bigint, 'ssssdddd'::text, 'cuacuacua'::text, '15'::integer, 'F'::text, '16-05-1980'::date,'hola ù21○☼'::bytea );

select * from test.clientesborradocopia where idcliente = 2;

DELETE from test.clientesborradocopia where idcliente = 2;

select * from test.clientesborradocopia where idcliente = 2;

select public.fn_RecuperarReg('test','clientesborradocopia','idcliente','2',null,null);

select * from test.clientesborradocopia where idcliente = 2;

select * from borradoscopia 