-- Table HIST_BLOQUEIO_SEMANAL

-- CREATE TABLE

 CREATE TABLE HIST_BLOQUEIO_SABADO (
  DATA_SABADO    DATE          NOT NULL,
  TIPO_BLOQUEIO  VARCHAR2(30)  NOT NULL,
  CODFILIAL      VARCHAR2(10)  NOT NULL,
  CODPROD        NUMBER        NOT NULL,
  DESCRICAO      VARCHAR2(200),
  MARCA          VARCHAR2(80),
  QTESTGER       NUMBER(18,4),
  QT             NUMBER(18,4),
  CUSTOFIN       NUMBER(18,6),
  VALOR          NUMBER(18,2),
  DATA_TIPO      DATE,                 -- (DADOS.DATA)
  CODMOTIVO      NUMBER,
  MOTIVO         VARCHAR2(200),
  CODFUNC        NUMBER,
  FUNCIONARIO    VARCHAR2(120),
  HISTORICO      VARCHAR2(4000),
  FAIXA_PRIM     VARCHAR2(30),
  DT_CARGA       TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT PK_HIST_BLOQ_SAB PRIMARY KEY
    (DATA_SABADO, CODFILIAL, CODPROD, TIPO_BLOQUEIO)
);
CREATE INDEX IX_HIST_BLOQ_SAB_01
  ON HIST_BLOQUEIO_SABADO (CODFILIAL, CODPROD, DATA_SABADO);
CREATE INDEX IX_HIST_BLOQ_SAB_02
  ON HIST_BLOQUEIO_SABADO (DATA_SABADO, TIPO_BLOQUEIO, FAIXA_PRIM);



-- CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE PRC_SNAPSHOT_BLOQUEIO_SABADO (
  P_DATA_BASE IN DATE DEFAULT SYSDATE
) AS
  V_SABADO DATE;
BEGIN
  V_SABADO := TRUNC(P_DATA_BASE,'IW') + 5; -- sábado
  DELETE FROM HIST_BLOQUEIO_SABADO
   WHERE DATA_SABADO = V_SABADO;
  INSERT INTO HIST_BLOQUEIO_SABADO (
    DATA_SABADO, TIPO_BLOQUEIO, CODFILIAL, CODPROD,
    DESCRICAO, MARCA, QTESTGER,
    QT, CUSTOFIN, VALOR,
    DATA_TIPO, CODMOTIVO, MOTIVO, CODFUNC, FUNCIONARIO, HISTORICO,
    FAIXA_PRIM, DT_CARGA
  )
  WITH
  /* ---------------------------------------------------------
      BASE ESTOQUE (fonte de quantidades da 9871)
     --------------------------------------------------------- */
  BASE_EST AS (
    SELECT
      E.CODFILIAL,
      E.CODPROD,
      NVL(E.QTBLOQUEADA,0)  AS QT_BLOQ_TOTAL,
      NVL(E.QTINDENIZ,0)    AS QT_AVARIA,
      NVL(E.QTEXPOSICAO,0)  AS QT_EXPOSICAO,
      E.QTESTGER,
      NVL(E.CUSTOFIN,0)     AS CUSTOFIN
    FROM PCEST E
  ),
  /* ---------------------------------------------------------
      ÚLTIMO MOV 53 (só para DATA/CODMOTIVO/CODFUNC/HIST)
     --------------------------------------------------------- */
  MOV53 AS (
    SELECT
      CODFILIAL, CODPROD,
      TRUNC(DTINCLUSAO) AS DATA_EXPO,
      CODMOTIVO,
      CODFUNC,
      HISTORICO
    FROM (
      SELECT
        M.*,
        ROW_NUMBER() OVER (
          PARTITION BY M.CODFILIAL, M.CODPROD
          ORDER BY M.DTINCLUSAO DESC, M.ROWID DESC
        ) RN
      FROM ESTMOVBLOQESTOQUE M
      WHERE M.CODMOTIVO IN (53)
    )
    WHERE RN = 1
  ),
  /* ---------------------------------------------------------
      ÚLTIMO EA (AVARIA) para DATA/CODMOTIVO/CODFUNC/HIST
     --------------------------------------------------------- */
  EA AS (
    SELECT
      CODFILIAL, CODPROD,
      TRUNC(DATA) AS DATA_EA,
      CODMOTIVO,
      CODFUNC,
      HISTORICO
    FROM (
      SELECT
        M.*,
        ROW_NUMBER() OVER (
          PARTITION BY M.CODFILIAL, M.CODPROD
          ORDER BY TRUNC(M.DATA) DESC, M.ROWID DESC
        ) RN
      FROM ESTMOVBLOQESTOQUE M
      WHERE M.TIPO = 'EA'
    )
    WHERE RN = 1
  ),
  /* ---------------------------------------------------------
      Data ENTRADA (último B no log, exceto 9871) igual 8189
     --------------------------------------------------------- */
  DT_ENTRADA AS (
    SELECT CODFILIAL, CODPROD, MAX(TRUNC(DATA)) AS DATA_ENT
    FROM ESTLOGBLOQESTOQUE
    WHERE PROGRAMA NOT LIKE '%9871%'
      AND TIPOBLOQUEIO = 'B'
    GROUP BY CODFILIAL, CODPROD
  ),
  /* ---------------------------------------------------------
      EXPOSIÇÃO (lógica 9871)
     --------------------------------------------------------- */
  EXPOSICAO AS (
    SELECT
      'BLOQUEIO EXPOSICAO' AS TIPO_BLOQUEIO,
      B.QT_EXPOSICAO       AS QUANTIDADE,
      M53.DATA_EXPO        AS DATA,
      B.CODPROD,
      B.CODFILIAL,
      M53.CODMOTIVO,
      M53.CODFUNC,
      M53.HISTORICO
    FROM BASE_EST B
    LEFT JOIN MOV53 M53
      ON M53.CODFILIAL = B.CODFILIAL
     AND M53.CODPROD   = B.CODPROD
    WHERE B.QT_EXPOSICAO > 0
  ),
  /* ---------------------------------------------------------
      MANUAL (lógica 9871) = QTBLOQMANUAL - QTEXPOSICAO
     --------------------------------------------------------- */
  MANUAL AS (
    SELECT
      'BLOQUEIO MANUAL' AS TIPO_BLOQUEIO,
      GREATEST(NVL(EBP.QTBLOQMANUAL,0) - B.QT_EXPOSICAO, 0) AS QUANTIDADE,
      TRUNC(EBP.DTPRIMBLOQUEIO) AS DATA,
      B.CODPROD,
      B.CODFILIAL,
      CAST(NULL AS NUMBER) AS CODMOTIVO,
      CAST(NULL AS NUMBER) AS CODFUNC,
      CAST(NULL AS VARCHAR2(4000)) AS HISTORICO
    FROM BASE_EST B
    LEFT JOIN ESTBLOQUEIOPRODUTO EBP
      ON EBP.CODFILIAL = B.CODFILIAL
     AND EBP.CODPROD   = B.CODPROD
    WHERE GREATEST(NVL(EBP.QTBLOQMANUAL,0) - B.QT_EXPOSICAO, 0) > 0
  ),
  /* ---------------------------------------------------------
      ENTRADA (lógica 9871 = 8189)
     Entrada = QTBLOQUEADA - QTINDENIZ - QTBLOQMANUAL
     --------------------------------------------------------- */
  ENTRADA AS (
    SELECT
      'BLOQUEIO ENTRADA' AS TIPO_BLOQUEIO,
      GREATEST(B.QT_BLOQ_TOTAL - B.QT_AVARIA - NVL(EBP.QTBLOQMANUAL,0), 0) AS QUANTIDADE,
      X.DATA_ENT AS DATA,
      B.CODPROD,
      B.CODFILIAL,
      CAST(NULL AS NUMBER) AS CODMOTIVO,
      CAST(NULL AS NUMBER) AS CODFUNC,
      CAST(NULL AS VARCHAR2(4000)) AS HISTORICO
    FROM BASE_EST B
    LEFT JOIN ESTBLOQUEIOPRODUTO EBP
      ON EBP.CODFILIAL = B.CODFILIAL
     AND EBP.CODPROD   = B.CODPROD
    INNER JOIN DT_ENTRADA X
      ON X.CODFILIAL = B.CODFILIAL
     AND X.CODPROD   = B.CODPROD
    WHERE GREATEST(B.QT_BLOQ_TOTAL - B.QT_AVARIA - NVL(EBP.QTBLOQMANUAL,0), 0) > 0
  ),
  /* ---------------------------------------------------------
      AVARIA (quantidade vem do PCEST.QTINDENIZ)
     --------------------------------------------------------- */
  AVARIA AS (
    SELECT
      'BLOQUEIO AVARIA' AS TIPO_BLOQUEIO,
      B.QT_AVARIA AS QUANTIDADE,
      EA.DATA_EA  AS DATA,
      B.CODPROD,
      B.CODFILIAL,
      EA.CODMOTIVO,
      EA.CODFUNC,
      EA.HISTORICO
    FROM BASE_EST B
    LEFT JOIN EA
      ON EA.CODFILIAL = B.CODFILIAL
     AND EA.CODPROD   = B.CODPROD
    WHERE B.QT_AVARIA > 0
  ),
  DADOS AS (
    SELECT * FROM EXPOSICAO
    UNION ALL
    SELECT * FROM MANUAL
    UNION ALL
    SELECT * FROM ENTRADA
    UNION ALL
    SELECT * FROM AVARIA
  )
  SELECT
    V_SABADO                            AS DATA_SABADO,
    DADOS.TIPO_BLOQUEIO,
    TO_CHAR(DADOS.CODFILIAL)            AS CODFILIAL,
    DADOS.CODPROD,
    P.DESCRICAO,
    M.MARCA,
    E.QTESTGER,
    DADOS.QUANTIDADE                    AS QT,
    ROUND(E.CUSTOFIN, 2)                AS CUSTOFIN,
    ROUND(DADOS.QUANTIDADE * E.CUSTOFIN, 2) AS VALOR,
    DADOS.DATA                          AS DATA_TIPO,
    DADOS.CODMOTIVO,
    TD.MOTIVO,
    DADOS.CODFUNC,
    F.NOME AS FUNCIONARIO,
    DADOS.HISTORICO,
    CASE
      WHEN DADOS.DATA IS NULL THEN '?'
      WHEN V_SABADO - TRUNC(DADOS.DATA) <=  3 THEN '1 - 0 A 3 DIAS'
      WHEN V_SABADO - TRUNC(DADOS.DATA) <=  7 THEN '2 - 4 A 7 DIAS'
      WHEN V_SABADO - TRUNC(DADOS.DATA) <= 14 THEN '3 - 8 A 14 DIAS'
      WHEN V_SABADO - TRUNC(DADOS.DATA) <= 30 THEN '4 - 15 A 30 DIAS'
      WHEN V_SABADO - TRUNC(DADOS.DATA) <= 60 THEN '5 - 30 A 60 DIAS'
      ELSE '6 - 60 + DIAS'
    END AS FAIXA_PRIM,
    SYSTIMESTAMP AS DT_CARGA
  FROM DADOS
  JOIN PCPRODUT P
    ON P.CODPROD = DADOS.CODPROD
  JOIN PCEST E
    ON E.CODPROD   = DADOS.CODPROD
   AND E.CODFILIAL = DADOS.CODFILIAL
  LEFT JOIN PCMARCA M
    ON M.CODMARCA = P.CODMARCA
  LEFT JOIN PCTABDEV TD
    ON TD.CODDEVOL = DADOS.CODMOTIVO
  LEFT JOIN PCEMPR F
    ON F.MATRICULA = DADOS.CODFUNC;

  COMMIT;
END;
/


-- job (todo sábado)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_SNAPSHOT_BLOQUEIO_SABADO',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PRC_SNAPSHOT_BLOQUEIO_SABADO',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=23;BYMINUTE=55;BYSECOND=0',
    enabled         => TRUE
  );
END;
/