-- Rotinas compartilhadas pelo integrador Delphi e pela API PHP.
DELIMITER $$
DROP FUNCTION IF EXISTS t2_sync_estoque_coluna$$
CREATE FUNCTION t2_sync_estoque_coluna() RETURNS VARCHAR(64)
READS SQL DATA
BEGIN
 DECLARE c VARCHAR(64);
 SET c=CONCAT('qtd',(SELECT empresa FROM t2_sync_config WHERE id=1));
 IF EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='estoqueprodutos' AND column_name=c) THEN RETURN c; END IF;
 RETURN 'qtd';
END$$
DROP FUNCTION IF EXISTS t2_sync_ref$$
CREATE FUNCTION t2_sync_ref(p_t VARCHAR(64),p_id VARCHAR(191)) RETURNS JSON
READS SQL DATA
BEGIN
 DECLARE v_o VARCHAR(64); DECLARE v_k VARCHAR(191);
 IF p_id IS NULL OR p_id='' OR p_id='0' THEN RETURN NULL; END IF;
 IF p_t='movimentacao' AND (SELECT lado FROM t2_sync_config WHERE id=1)='PDV' AND
   EXISTS(SELECT 1 FROM movimentacao WHERE documento=p_id AND doc_interno>0) THEN
  RETURN JSON_OBJECT('origem',(SELECT par FROM t2_sync_config WHERE id=1),'tabela',p_t,
   'chave',CAST((SELECT doc_interno FROM movimentacao WHERE documento=p_id) AS CHAR));
 END IF;
 SELECT origem,chave INTO v_o,v_k FROM t2_sync_mapa WHERE tabela=p_t AND local_id=p_id LIMIT 1;
 IF v_o IS NULL THEN SET v_o=(SELECT origem FROM t2_sync_config WHERE id=1); SET v_k=p_id; END IF;
 RETURN JSON_OBJECT('origem',v_o,'tabela',p_t,'chave',v_k);
END$$
DROP PROCEDURE IF EXISTS t2_sync_resolver$$
CREATE PROCEDURE t2_sync_resolver(IN p_o VARCHAR(64),IN p_t VARCHAR(64),IN p_k VARCHAR(191),OUT p_local VARCHAR(191))
BEGIN
 DECLARE v_pk VARCHAR(64);
 SET p_local=NULL;
 IF p_k IS NULL OR p_k='' OR p_k='0' THEN SET p_local=p_k;
 ELSE
  IF p_o=(SELECT origem FROM t2_sync_config WHERE id=1) THEN
   SET v_pk=(SELECT pk FROM t2_sync_tabelas WHERE nome=p_t);
   IF v_pk IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_TABELA_INVALIDA'; END IF;
   SET @sync_k=p_k; SET @sync_count=0;
   SET @sync_sql=CONCAT('SELECT COUNT(*) INTO @sync_count FROM `',p_t,'` WHERE `',v_pk,'`=?');
   PREPARE sync_stmt FROM @sync_sql; EXECUTE sync_stmt USING @sync_k; DEALLOCATE PREPARE sync_stmt;
   IF @sync_count=1 THEN SET p_local=p_k; END IF;
  ELSE
   SELECT local_id INTO p_local FROM t2_sync_mapa WHERE origem=p_o AND tabela=p_t AND chave=p_k LIMIT 1;
  END IF;
  IF p_local IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_DEPENDENCIA_SEM_VINCULO'; END IF;
 END IF;
END$$
DROP PROCEDURE IF EXISTS t2_sync_snapshot$$
CREATE PROCEDURE t2_sync_snapshot(IN p_e VARCHAR(64),IN p_k VARCHAR(191),OUT p_doc LONGTEXT)
BEGIN
 DECLARE v_t,v_pk,v_root,v_rootkey,v_empcol VARCHAR(64);
 DECLARE v_members,v_cols,v_refs,v_rows,v_all JSON;
 DECLARE v_expr,v_refexpr,v_where LONGTEXT;
 DECLARE v_i INT DEFAULT 0; DECLARE v_empresa INT; DECLARE v_origem VARCHAR(64);
 SET v_all=JSON_ARRAY();
 SELECT tabela_raiz,chave_raiz,tabelas INTO v_root,v_rootkey,v_members FROM t2_sync_entidades WHERE nome=p_e;
 SELECT origem,empresa INTO v_origem,v_empresa FROM t2_sync_config WHERE id=1;
 IF v_root IS NULL OR v_origem IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_CONFIGURACAO_AUSENTE'; END IF;
 SET v_empcol=(SELECT campo_empresa FROM t2_sync_tabelas WHERE nome=v_root);
 SET @sync_k=p_k; SET @sync_emp=v_empresa; SET @sync_count=0;
 SET @sync_sql=CONCAT('SELECT COUNT(*) INTO @sync_count FROM `',v_root,'` WHERE `',v_rootkey,'`=?',
  IF(v_empcol IS NULL,'',CONCAT(' AND `',v_empcol,'`=',v_empresa)));
 PREPARE sync_stmt FROM @sync_sql; EXECUTE sync_stmt USING @sync_k; DEALLOCATE PREPARE sync_stmt;
 IF @sync_count<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_EXCLUSAO_FISICA_OU_ESCOPO_INVALIDO'; END IF;
 IF p_e='venda' THEN
  IF EXISTS(SELECT 1 FROM movimentacao WHERE documento=p_k AND doc_interno>0) THEN
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_VENDA_IMPORTADA_ERP_REQUER_CONCILIACAO'; END IF;
  IF NOT EXISTS(SELECT 1 FROM movimentacao WHERE documento=p_k AND finalizado='S' AND empresa=v_empresa) THEN
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_VENDA_NAO_FINALIZADA'; END IF;
 END IF;
 IF p_e='nfce' THEN
  IF NOT EXISTS(SELECT 1 FROM nfe WHERE id=p_k AND modelo='65' AND status IN('100','150','101') AND LENGTH(xml)>0) THEN
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_AGUARDANDO_XML_AUTORIZADO'; END IF;
 END IF;
 SET SESSION group_concat_max_len=16777216;
 WHILE v_i<JSON_LENGTH(v_members) DO
  SET v_t=JSON_UNQUOTE(JSON_EXTRACT(v_members,CONCAT('$[',v_i,'].table')));
  SELECT pk,colunas,referencias INTO v_pk,v_cols,v_refs FROM t2_sync_tabelas WHERE nome=v_t;
  SELECT GROUP_CONCAT(CONCAT(QUOTE(j.c),',CONVERT(t.`',IF(v_t='estoqueprodutos' AND j.c='qtd',t2_sync_estoque_coluna(),j.c),'` USING utf8mb4)') ORDER BY j.n SEPARATOR ',')
   INTO v_expr FROM JSON_TABLE(v_cols,'$[*]' COLUMNS(n FOR ORDINALITY,c VARCHAR(64) PATH '$')) j;
  SELECT GROUP_CONCAT(CONCAT(QUOTE(j.c),',t2_sync_ref(',QUOTE(JSON_UNQUOTE(JSON_EXTRACT(v_refs,CONCAT('$.',j.c)))),
   ',CAST(t.`',j.c,'` AS CHAR))') SEPARATOR ',') INTO v_refexpr
   FROM JSON_TABLE(JSON_KEYS(v_refs),'$[*]' COLUMNS(c VARCHAR(64) PATH '$')) j
   WHERE JSON_UNQUOTE(JSON_EXTRACT(v_refs,CONCAT('$.',j.c)))<>'@empresa';
  SET v_where=CONCAT('t.`',JSON_UNQUOTE(JSON_EXTRACT(v_members,CONCAT('$[',v_i,'].field'))),'`=?');
  SET v_empcol=(SELECT campo_empresa FROM t2_sync_tabelas WHERE nome=v_t);
  IF v_empcol IS NOT NULL THEN SET v_where=CONCAT(v_where,' AND t.`',v_empcol,'`=',v_empresa); END IF;
  SET @sync_rows=NULL;
  SET @sync_sql=CONCAT('SELECT CONCAT(''['',GROUP_CONCAT(JSON_OBJECT(''tabela'',',QUOTE(v_t),
   ',''origem'',COALESCE(mp.origem,',QUOTE(v_origem),'),''chave'',COALESCE(mp.chave,CAST(t.`',v_pk,'` AS CHAR)),',
   '''base_hash'',(SELECT mm.hash_recebido FROM t2_sync_membro mm WHERE mm.tabela=',QUOTE(v_t),' AND mm.local_id=CAST(t.`',v_pk,'` AS CHAR) AND mm.ultima_origem=(SELECT par FROM t2_sync_config WHERE id=1) ORDER BY mm.atualizado DESC LIMIT 1),''dados'',JSON_OBJECT(',v_expr,'),''refs'',JSON_OBJECT(',COALESCE(v_refexpr,''),')) ORDER BY t.`',v_pk,'` SEPARATOR '',''),'']'') INTO @sync_rows FROM `',v_t,
   '` t LEFT JOIN t2_sync_mapa mp ON mp.tabela=',QUOTE(v_t),' AND mp.local_id=CAST(t.`',v_pk,'` AS CHAR) WHERE ',v_where);
  PREPARE sync_stmt FROM @sync_sql; EXECUTE sync_stmt USING @sync_k; DEALLOCATE PREPARE sync_stmt;
  SET v_rows=COALESCE(CAST(@sync_rows AS JSON),JSON_ARRAY());
  SET v_all=JSON_MERGE_PRESERVE(v_all,v_rows); SET v_i=v_i+1;
 END WHILE;
 SET p_doc=CAST(JSON_OBJECT('versao',1,'origem',v_origem,'empresa',v_empresa,'entidade',p_e,'chave',p_k,'linhas',v_all) AS CHAR CHARACTER SET utf8mb4);
 IF OCTET_LENGTH(p_doc)>8388608 OR JSON_LENGTH(v_all)>10000 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_LOTE_EXCESSIVO'; END IF;
END$$
DROP PROCEDURE IF EXISTS t2_sync_preparar$$
CREATE PROCEDURE t2_sync_preparar(IN p_e VARCHAR(64),IN p_k VARCHAR(191))
BEGIN
 DECLARE v_op CHAR(36); DECLARE v_doc LONGTEXT; DECLARE v_hash CHAR(64); DECLARE v_ver BIGINT;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
 START TRANSACTION;
 SELECT operacao,payload,hash_payload,versao INTO v_op,v_doc,v_hash,v_ver FROM t2_sync_fila
  WHERE entidade=p_e AND chave=p_k AND estado='pendente' FOR UPDATE;
 IF v_ver IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_PENDENCIA_INEXISTENTE'; END IF;
 IF v_op IS NULL THEN
  CALL t2_sync_snapshot(p_e,p_k,v_doc);
  SET v_doc=CAST(JSON_SET(CAST(v_doc AS JSON),'$.revisao',v_ver) AS CHAR CHARACTER SET utf8mb4); SET v_op=UUID(); SET v_hash=SHA2(v_doc,256);
  UPDATE t2_sync_fila SET operacao=v_op,payload=v_doc,hash_payload=v_hash,versao_envio=v_ver WHERE entidade=p_e AND chave=p_k;
 END IF;
 COMMIT;
 SELECT v_op AS operacao,v_doc AS payload,v_hash AS hash_payload;
END$$
DROP PROCEDURE IF EXISTS t2_sync_confirmar$$
CREATE PROCEDURE t2_sync_confirmar(IN p_op CHAR(36),IN p_hash CHAR(64))
BEGIN
 UPDATE t2_sync_fila SET estado=IF(versao=versao_envio,'concluido','pendente'),
  operacao=NULL,payload=NULL,hash_payload=NULL,versao_envio=NULL,tentativas=0,
  proxima_tentativa=UTC_TIMESTAMP(3),erro_codigo=NULL
 WHERE operacao=p_op AND hash_payload=p_hash AND estado='pendente';
 IF ROW_COUNT()<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='SYNC_CONFIRMACAO_INVALIDA'; END IF;
END$$
DROP PROCEDURE IF EXISTS t2_sync_falhar$$
CREATE PROCEDURE t2_sync_falhar(IN p_e VARCHAR(64),IN p_k VARCHAR(191),IN p_codigo VARCHAR(120),IN p_permanente BOOLEAN)
BEGIN
 UPDATE t2_sync_fila SET tentativas=tentativas+1,erro_codigo=p_codigo,
  estado=IF(p_permanente,'conflito','pendente'),
  proxima_tentativa=TIMESTAMPADD(SECOND,LEAST(300,POW(2,LEAST(tentativas+1,8))),UTC_TIMESTAMP(3))
 WHERE entidade=p_e AND chave=p_k;
 -- Uma rejeicao explicita por dependencia/estoque pendente significa rollback no destino.
 -- So nesses casos e seguro recapturar referencias/saldo; falha de rede mantem UUID e payload.
 IF p_codigo IN('SYNC_DEPENDENCIA_SEM_VINCULO','SYNC_ESTOQUE_COM_VENDAS_LOCAIS_PENDENTES') THEN
  UPDATE t2_sync_fila SET conflito_payload=COALESCE(payload,conflito_payload),versao=versao+1,
   operacao=NULL,payload=NULL,hash_payload=NULL,versao_envio=NULL WHERE entidade=p_e AND chave=p_k;
 END IF;
 INSERT INTO t2_sync_log(entidade,codigo) VALUES(p_e,p_codigo);
END$$
DELIMITER ;