<?php
    include "../db/headerApi.php";
    include "../db/connection_main.php";

    $json = file_get_contents('php://input');
    $data = json_decode($json);

startagain:
    $sql = "SELECT emp_id, emp_name, NVL(emp_agreement_flag,'N') emp_agreement_flag,
                   to_char(emp_agreement_date,'YYYY-MM-DD HH24:MI:SS') emp_agreement_date,
                   emp_sign_language
              FROM ytycustom.pdpa_users_list 
             WHERE emp_id = :userid";
    
    $sqlarr = oci_parse($dbcon , $sql);
    oci_bind_by_name($sqlarr, ':userid', $data->userid);
    $r = oci_execute($sqlarr);

    if (!$r) {
         $e = oci_error($sqlarr);
         print json_encode($e['message']);
         die();
    }
    oci_fetch_all($sqlarr, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW);

    if (!$rows) {
          $sql = "SELECT emp_id, orgid, employee_name, emp_mobile_no, email_id, trunc(to_char(dbms_random.value(0,9999),'0000'),0) otp
                    FROM production.employee_master 
                   WHERE emp_id = :userid";

          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':userid', $data->userid);
          $r = oci_execute($sqlarr);
          oci_fetch_all($sqlarr, $rows_emp, null, null, OCI_FETCHSTATEMENT_BY_ROW);

          if (!$r) {
               $e = oci_error($sqlarr);
               print json_encode($e['message']);
               die();
          }

          $sql = "INSERT INTO ytycustom.pdpa_users_list 
                              (emp_id, org_id, emp_name, emp_mobile_no, emp_email, emp_otp, emp_otp_valid)
                    VALUES (:emp_id, :org_id, :emp_name, :emp_mobile_no, :emp_email, :emp_otp, (sysdate + interval '1' minute))";
          
          $sqlarr = oci_parse($dbcon , $sql);
          oci_bind_by_name($sqlarr, ':emp_id', $rows_emp[0]["EMP_ID"]);
          oci_bind_by_name($sqlarr, ':org_id', $rows_emp[0]["ORGID"]);
          oci_bind_by_name($sqlarr, ':emp_name', $rows_emp[0]["EMPLOYEE_NAME"]);
          oci_bind_by_name($sqlarr, ':emp_mobile_no', $rows_emp[0]["EMP_MOBILE_NO"]);
          oci_bind_by_name($sqlarr, ':emp_email', $rows_emp[0]["EMAIL_ID"]);
          oci_bind_by_name($sqlarr, ':emp_otp', $rows_emp[0]["OTP"]);
          $r = oci_execute($sqlarr, OCI_NO_AUTO_COMMIT);

          if (!$r) {
               $e = oci_error($sqlarr);
               print json_encode($e['message']);
               die();
          }

          goto startagain;
    } 

    print json_encode($rows);
?>