/*
Not the best of my code, but something I can share
*/

--filling data for testing, "time" column is filled with present data minus 2 days:
BEGIN
INSERT INTO "security"."COMPANY_OTP" ("sessionId", "password", "time")
SELECT '523' AS "sessionId", 'wer' AS "password", SYSDATE - 2 AS "time" FROM dual;
COMMIT;
END;

--display all records that are older than 10 min (deleting them as well)
SELECT t."SESSIONID", t."TIME", t."TRIES", t."PASSWORD", t."LIFETIME_MINUTES", t."RESULT"
FROM TABLE("security"."pkg_company_otp".fn_tshow_10min) t

--ensure that old record is already absent:
SELECT t.* FROM "security"."COMPANY_OTP" t

/*
  b. Number "3" - if record age is more than 10 min (after creating - "time" column) for sessionId and password pair, then the record is deleted
*/

--filling data for testing, "time" column is filled with present data minus 2 days:
BEGIN
INSERT INTO "security"."COMPANY_OTP" ("sessionId", "password", "time")
SELECT '523' AS "sessionId", 'wer' AS "password", SYSDATE - 2 AS "time" FROM dual;
COMMIT;
END;

--display the record just entered, column RESULT should return number 3, record is deleted
SELECT t."SESSIONID", t."TIME", t."TRIES", t."PASSWORD", t."LIFETIME_MINUTES", t."RESULT", t."ACOMMENT"
FROM TABLE("security"."pkg_company_otp".fn_tshow(
                                               parSESSIONID => '523',
                                               parPASSWORD  => 'khv'
                                             )
          ) t

--insert test data ("time" is autofilled with present date and time)
BEGIN
INSERT INTO "security"."COMPANY_OTP" ("sessionId", "password")
SELECT '423' AS "sessionId", 'ver' AS "password" FROM dual;
COMMIT;
END;


---------------------------------------------------------------

--create result table
--------------------------------------------------------
--  DDL for Table COMPANY_OTP
--------------------------------------------------------

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE security.COMPANY_OTP';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

  CREATE TABLE "security"."COMPANY_OTP"
   (
  "ID"              VARCHAR2(36 BYTE)                      DEFAULT SYS_GUID(),
  "sessionId"       VARCHAR2(100 BYTE)                     NOT NULL,
  "tries"           NUMBER(1,0)                            DEFAULT 0,
  "password"        VARCHAR2(99 BYTE)                      NOT NULL,
  "time"            DATE                                   DEFAULT SYSDATE,
  "auser"           VARCHAR2(99 BYTE)                      DEFAULT USER
   );

   CREATE UNIQUE INDEX "security"."COMPANY_OTP_ID2" ON "security"."COMPANY_OTP" ("sessionId");
   CREATE INDEX "security"."COMPANY_OTP_ID1" ON "security"."COMPANY_OTP" ("sessionId", "tries", "password", "time");

   COMMENT ON TABLE "security"."COMPANY_OTP" IS 'check entered temporary password OTP';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."sessionId" IS 'record ID';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."sessionId" IS 'session ID';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."tries" IS 'tries';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."password" IS 'password';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."time" IS 'Record date and time';
   COMMENT ON COLUMN "security"."COMPANY_OTP"."auser" IS 'Oracle user that entered record';


--------------------------------------------------------
--  DDL for Package pkg_company_otp
--------------------------------------------------------

CREATE OR REPLACE PACKAGE "security"."pkg_company_otp" IS

TYPE tshow_type IS RECORD
(
  "ID"                      VARCHAR2(36 BYTE)                    ,
  "sessionId"               VARCHAR2(100 BYTE)                   ,
  "tries"                   NUMBER  (1,0    )                    ,
  "password"                VARCHAR2(99 BYTE)                    ,
  "time"                    VARCHAR2(99 BYTE)                    ,
  "lifetime_minutes"        NUMBER                               ,
  "result"                  NUMBER  (1,0    )                    ,
  "acomment"                VARCHAR2(99 BYTE)                    ,
  "auser"                   VARCHAR2(99 BYTE)
);

TYPE tshow IS TABLE OF tshow_type;

FUNCTION fn_tshow_10min RETURN tshow pipelined;

FUNCTION fn_tshow(parSESSIONID VARCHAR2, parPASSWORD VARCHAR2) RETURN tshow pipelined;

PROCEDURE pr_del_2b(parSESSIONID VARCHAR2);

PROCEDURE pr_del_2c(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2);

PROCEDURE pr_del_2d(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2);

PROCEDURE pr_upd_2e(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2);

END;

/




--------------------------------------------------------
--  DDL for Package Body pkg_company_otp
--------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY "security"."pkg_company_otp" IS

FUNCTION fn_tshow_10min RETURN tshow pipelined IS
v_obj tshow_type;

BEGIN

FOR e IN (

    SELECT
           t."ID",
           t."sessionId",
           t."tries",
           t."password",
           TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS asysdate,
           TO_CHAR(t."time", 'DD.MM.YYYY HH24:MI:SS') AS "time",
           ROUND((SYSDATE - t."time")*24*60,1) AS "lifetime_minutes",
           CASE WHEN SYSDATE - t."time" >= 10/(24*60)
                THEN 3
                ELSE 1
           END AS "result",
           'all found values (previously inserted)' AS "acomment",
           t."auser"
    FROM "security"."COMPANY_OTP" t
    WHERE SYSDATE - t."time" >= 10/(24*60)

         )
LOOP

  v_obj."ID"                              := e."ID";
  v_obj."sessionId"                       := e."sessionId";
  v_obj."time"                            := e."time";
  v_obj."tries"                           := e."tries";
  v_obj."password"                        := e."password";
  v_obj."lifetime_minutes"                := e."lifetime_minutes";
  v_obj."result"                          := e."result";
  v_obj."acomment"                        := e."acomment";
  v_obj."auser"                           := e."auser";


PIPE ROW (v_obj);
IF e."result" = 3 THEN pr_del_2b(
                                 parSESSIONID => e."sessionId"
                                );
END IF;
END LOOP;

RETURN;

END;

FUNCTION fn_tshow(parSESSIONID VARCHAR2, parPASSWORD VARCHAR2) RETURN tshow pipelined IS

v_obj tshow_type;

BEGIN
    BEGIN
    pr_upd_2e(
               parSESSIONIDpr => parSESSIONID,
               parPASSWORDpr  => parPASSWORD
             );
    END;
FOR e IN (

    SELECT
           t."ID",
           t."sessionId",
           t."tries",
           t."password",
           TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS asysdate,
           TO_CHAR(t."time", 'DD.MM.YYYY HH24:MI:SS') AS "time",
           ROUND((SYSDATE - t."time")*24*60,1) AS "lifetime_minutes",
           CASE WHEN SYSDATE - t."time" >= 10/(24*60)
                THEN 3
                ELSE NULL
           END AS "result",
           'current sessionId' AS "acomment",
           t."auser"
    FROM "security"."COMPANY_OTP" t
    WHERE t."sessionId" = parSESSIONID
      AND SYSDATE - t."time" >= 10/(24*60)
    UNION ALL
    SELECT
           t."ID",
           t."sessionId",
           t."tries",
           t."password",
           TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS asysdate,
           TO_CHAR(t."time", 'DD.MM.YYYY HH24:MI:SS') AS "time",
           ROUND((SYSDATE - t."time")*24*60,1) AS "lifetime_minutes",
           CASE WHEN SYSDATE - t."time" >= 10/(24*60)
                THEN 3
                ELSE NULL
           END AS "result",
           'all found values (previously inserted)' AS "acomment",
           t."auser"
    FROM "security"."COMPANY_OTP" t
    WHERE SYSDATE - t."time" >= 10/(24*60)
      AND t."sessionId" <> parSESSIONID
    UNION ALL
    SELECT
           t."ID",
           t."sessionId",
           t."tries",
           t."password",
           TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS asysdate,
           TO_CHAR(t."time", 'DD.MM.YYYY HH24:MI:SS') AS "time",
           ROUND((SYSDATE - t."time")*24*60,1) AS "lifetime_minutes",
           CASE WHEN NOT ( SYSDATE - t."time" >= 10/(24*60) )
                THEN
                     CASE WHEN T."tries" > 2
                          THEN 2
                          ELSE
                               CASE WHEN T."tries" <= 2 AND t."sessionId" = parSESSIONID AND T."password" <> parPASSWORD
                                    THEN 0
                                    ELSE 1
                               END
                     END
                ELSE NULL
           END AS "result",
           'all found values (previously inserted)' AS "acomment",
           t."auser"
    FROM "security"."COMPANY_OTP" t
    WHERE NOT ( SYSDATE - t."time" >= 10/(24*60) )
      AND t."sessionId" = parSESSIONID

         )
LOOP

  v_obj."ID"                              := e."ID";
  v_obj."sessionId"                       := e."sessionId";
  v_obj."time"                            := e."time";
  v_obj."tries"                           := e."tries";
  v_obj."password"                        := e."password";
  v_obj."acomment"                        := e."acomment";
  v_obj."lifetime_minutes"                := e."lifetime_minutes";
  v_obj."result"                          := e."result";
  v_obj."auser"                           := e."auser";


PIPE ROW (v_obj);
IF e."result" = 3 THEN pr_del_2b(
                                 parSESSIONID => e."sessionId"
                                );
END IF;
IF e."result" = 1 THEN pr_del_2c(
                                 parSESSIONIDpr => parSESSIONID,
                                 parPASSWORDpr  => parPASSWORD
                                );
END IF;
IF e."result" = 2 THEN pr_del_2d(
                                 parSESSIONIDpr => parSESSIONID,
                                 parPASSWORDpr  => parPASSWORD
                                );
END IF;
/*IF e."result" = 0 THEN pr_upd_2e(
                                 parSESSIONIDpr => parSESSIONID,
                                 parPASSWORDpr  => parPASSWORD
                                );
END IF;*/
END LOOP;

RETURN;

END;

PROCEDURE pr_del_2b(parSESSIONID VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE FROM "security"."COMPANY_OTP" T WHERE T."sessionId" = parSESSIONID;
  COMMIT;
END;

PROCEDURE pr_del_2c(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE FROM "security"."COMPANY_OTP" T
  WHERE T."sessionId" = parSESSIONIDpr
    AND T."password"  = parPASSWORDpr
    AND T."tries" <= 2;
  COMMIT;
END;

PROCEDURE pr_del_2d(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE FROM "security"."COMPANY_OTP" T
  WHERE T."sessionId" = parSESSIONIDpr
    AND T."password" <> parPASSWORDpr
    AND T."tries" > 2;
  COMMIT;
END;

PROCEDURE pr_upd_2e(parSESSIONIDpr VARCHAR2, parPASSWORDpr VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE "security"."COMPANY_OTP" T
  SET T."tries" = T."tries" + 1
  WHERE T."sessionId" = parSESSIONIDpr
    AND T."password" <> parPASSWORDpr;
COMMIT;
END;

END;

/