delimiter $$
drop procedure if exists cancela_timbre;
create procedure cancela_timbre
(folio$ bigint, cancelacion$ text, tipo$ int)
begin
    declare id_$ bigint;
    declare estado_$ int;
    declare predio_$ int;
    if tipo$ = 21 then
        set id_$ := (select id from timbres where clave = folio$);
        update timbres as t
        left join recibos as r on t.id = r.timbre
        set t.estado = 1000, t.cancelacion = cancelacion$, r.timbre = 0
        where t.id = id_$;
    else
        set id_$ := (select id from recibos where folio = folio$);
        set estado_$ := (select estado from recibos where id = id_$);
        update recibos as r
        left join timbres as t on r.timbre = t.id
        set r.estado = 1000, t.estado = 1000, t.cancelacion = cancelacion$
        where r.id = id_$;
        if estado_$ <> 80 then
            delete from mantto where recibo = id_$;
            delete from servicio where recibo = id_$;
        else
            set predio_$ := (select (descifrar(p.predio)*-1) from recibos as r inner join predios as p on r.predio = p.id where r.id = id_$);
            update mantto set recibo = predio_$ where recibo = id_$;
        end if;
    end if;
    select 88 as estado, 'La cancelacion se realizo' as mssg;
end
$$

delimiter $$
drop procedure if exists carga_migracion;
/*create procedure carga_migracion
(datos$ longtext)
begin
    insert into proveedores(timbre, concepto, monto, receptor, rfc, codigo, regimen, uso, forma, metodo, nota)
    select jt.timbre, cifrar(jt.concepto), jt.monto, cifrar(jt.receptor), cifrar(jt.rfc), cifrar(jt.codigo), r.id, jt.uso, jt.forma, jt.metodo, jt.nota
    from regimenes as r
    inner join json_table(
        datos$, '$[*]' columns (
            timbre bigint path '$.timbre',
            concepto varchar(500) path '$.concepto',
            monto decimal(10,2) path '$.monto',
            receptor varchar(500) path '$.receptor',
            rfc varchar(13) path '$.rfc',
            codigo varchar(10) path '$.codigo',
            regimen varchar(5) path '$.regimen',
            uso int path '$.uso',
            forma int path '$.forma',
            metodo int path '$.metodo',
            nota text path '$.nota'
        )
    ) as jt on r.clave = jt.regimen COLLATE utf8mb4_unicode_ci;
end*/
$$



DELIMITER $$
DROP PROCEDURE IF EXISTS cTimbres;
CREATE PROCEDURE cTimbres
(inicio$ DATE, fin$ DATE)
BEGIN
    SELECT t.clave AS timbre, fechas(t.registro, 3) AS registro, CASE t.estado WHEN 1780 THEN CONCAT('Recibo: RCB',r.folio) else descifrar(p.concepto) END AS tipo, CASE WHEN t.cancelacion IS NULL THEN 'Activo' ELSE 'Cancelado' END AS estado, case when t.estado = 1780 then descifrar(r.referencia) else p.nota end as referencia, case when t.estado = 1780 then total_recibo(r.id, r.estado) else p.monto end as total, ExtractValue(t.cfdi, '//@UUID') AS uuid_factura
	FROM timbres AS t
	LEFT JOIN recibos AS r ON t.id = r.timbre
	LEFT JOIN proveedores AS p ON t.id = p.timbre
	WHERE CAST(t.registro AS DATE) BETWEEN inicio$ AND fin$ and not t.estado = 1000 
	ORDER BY clave;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS timbre_libre;
CREATE PROCEDURE timbre_libre
(concepto$ VARCHAR(100), monto$ DECIMAL(10,2), receptor$ VARCHAR(300), rfc$ VARCHAR(15), codigo$ VARCHAR(8), regimen$ VARCHAR(5), uso$ VARCHAR(5), forma$ VARCHAR(5), metodo$ VARCHAR(5), nota$ TEXT)
BEGIN
	DECLARE regimen_$ INT;
	DECLARE uso_$ INT;
	DECLARE forma_$ INT;
	DECLARE metodo_$ INT;
	DECLARE timbre_$ BIGINT DEFAULT(19);
	SET regimen_$ := (SELECT id FROM regimenes WHERE clave = regimen$);
	SET uso_$ := (SELECT id FROM usos WHERE clave = uso$);
	SET forma_$ := (SELECT id FROM formas WHERE clave = forma$);
	IF EXISTS (SELECT id FROM timbres) THEN
		SET timbre_$ := (SELECT (clave+1) FROM timbres ORDER BY clave DESC LIMIT 1);
	END IF;
	SET metodo_$ := (SELECT id FROM metodos WHERE clave = metodo$);
	INSERT INTO timbres(clave, cfdi, cancelacion, estado, registro)
	VALUES(timbre_$, '', null, 404, NOW());
	INSERT INTO proveedores(timbre, concepto, monto, receptor, rfc, codigo, regimen, uso, forma, metodo, nota)
	SELECT id, cifrar(UPPER(concepto$)), monto$, cifrar(UPPER(receptor$)), cifrar(UPPER(rfc$)), cifrar(UPPER(codigo$)), regimen_$, uso_$, forma_$, metodo_$, nota$ FROM timbres WHERE clave = timbre_$;
	IF (ROW_COUNT()) = 1 THEN
		SELECT 800 AS estado, clave AS folio, CONCAT(DATE_FORMAT(registro, '%Y-%m-%d'),'T',TIME(registro)) AS fecha FROM timbres WHERE clave = timbre_$;
	ELSE
		SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cTimbre;
CREATE PROCEDURE cTimbre
(folio$ BIGINT)
BEGIN
	DECLARE id_$ BIGINT;
	SET id_$ := (SELECT id FROM timbres WHERE clave = folio$);
	SELECT cfdi FROM timbres WHERE id = id_$;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS iTimbre;
CREATE PROCEDURE iTimbre
(folio$ BIGINT, xml$ TEXT, estado$ INT)
BEGIN
	DECLARE id_$ BIGINT;
	SET id_$ := (SELECT id FROM timbres WHERE clave = folio$);
	UPDATE timbres SET cfdi = xml$, estado = estado$, registro = NOW() WHERE id = id_$;
END
$$
