<?php
require_once('config.php');

$now = date("YmdHis");
$file_name = __DIR__ . "\\mobile_log\\_insert_linen_master.log";

$json = file_get_contents('php://input');
$result = json_decode($json);

try {
    if (!$result) {
        throw new Exception("Invalid JSON payload");
    }

    if (isset($result->header)) {
        $headers = [$result->header]; // single header
    } elseif (isset($result->Header) && is_array($result->Header)) {
        $headers = $result->Header;   // header array
    } else {
        throw new Exception("Missing header (header/Header)");
    }

    if (isset($result->details) && is_array($result->details)) {
        $details = $result->details;
    } elseif (isset($result->Details) && is_array($result->Details)) {
        $details = $result->Details;
    } else {
        throw new Exception("Missing details (details/Details)");
    }

    if (count($details) == 0) {
        throw new Exception("Details is empty");
    }

    // Begin transaction
    if (!sqlsrv_begin_transaction($conn)) {
        throw new Exception("Failed to begin transaction: " . print_r(sqlsrv_errors(), true));
    }

    $last_lri_no = '';
    $last_rowid  = '';

    foreach ($headers as $Header) {

        $site_cd              	= trim($Header->site_cd ?? '');
        $dept_mst_dept_cod    	= trim($Header->dept_mst_dept_cod ?? '');
        $dept_mst_description 	= trim($Header->dept_mst_description ?? '');
        $ler_mst_ler_no       	= trim($Header->ler_mst_ler_no ?? '');
        $ler_mst_ler_date     	= trim($Header->request_date_time ?? '');
        $ler_mst_requester    	= trim($Header->requester ?? '');
        $ler_mst_requester_id   = trim($Header->requester_id ?? '');
		$status 			  	= 'OPN';
        $LOGINID              	= trim($Header->LoginID ?? '');

        // totals (optional from mobile)
        $total_line_count = (int)($Header->total_line_count ?? count($details));
        $total_req_count = (int)($Header->total_line_count ?? count($details));
       

        if ($site_cd === '' || $dept_mst_dept_cod === '' || $ler_mst_ler_date === '') {
            throw new Exception("Required header fields missing: site_cd / dept_mst_dept_cod / ler_mst_ler_date");
        }


        // ---------------------------------------------------------------------
        // 1) If LRI NO empty -> generate from cnt_mst and INSERT lri_mst
        // ---------------------------------------------------------------------
        if ($ler_mst_ler_no === '') {

            $sql_cnt = "
                SELECT ls_doc_no = cnt_mst_prefix + SUBSTRING(CONVERT(VARCHAR(7), cnt_mst_counter + 1000000), 2, 6)
                FROM   cnt_mst WITH (NOLOCK)
                WHERE  site_cd = ?
                  AND  cnt_mst_module_cd = 'LRQ';

                UPDATE cnt_mst WITH (UPDLOCK)
                SET    cnt_mst_counter = cnt_mst_counter + 1
                WHERE  site_cd = ?
                  AND  cnt_mst_module_cd = 'LRQ';
            ";

            $stmt = sqlsrv_query($conn, $sql_cnt, [$site_cd, $site_cd]);
            if ($stmt === false) {
                throw new Exception("cnt_mst error: " . print_r(sqlsrv_errors(), true));
            }

            $linen_nos = '';
            do {
                while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                    $linen_nos = $row['ls_doc_no'];
                }
            } while (sqlsrv_next_result($stmt));
            sqlsrv_free_stmt($stmt);

            if ($linen_nos === '') throw new Exception("Failed to generate doc no");

            $ler_mst_ler_no = $linen_nos;

            // Insert lri_mst (your column list)
            $sql_ler_mst = "INSERT INTO ler_mst (
													site_cd,                ler_mst_ler_no,        ler_mst_ler_date,     ler_mst_status,
													ler_mst_desc,           ler_mst_department,    ler_mst_requester,    ler_mst_requester_id,
													ler_mst_ttl_item,       ler_mst_ttl_req,       ler_mst_ttl_issue,    ler_mst_ttl_sf,
													ler_mst_remark,			ler_mst_create_by,      ler_mst_create_date, audit_user,           
													audit_date,				mbl_audit_user,         mbl_audit_date,      column1,              
													column2,				column3,                column4,               column5
												)
										VALUES (
													?,                      ?,                     ?,                    ?,
													Null,                   ?,                     ?,                    ?,
													?,                      ?,                     NULL,                 NULL,
													Null,					?,                     GETDATE(),            ?,                    
													GETDATE(),				?,                     GETDATE(),            NULL,
													NULL,					NULL,                  NULL,                 NULL
												)";

			$params_ler_mst = [
				$site_cd,
				$ler_mst_ler_no,
				$ler_mst_ler_date,
				$status,
				$dept_mst_dept_cod,
				$ler_mst_requester,
				$ler_mst_requester_id,
				$total_line_count,
				$total_req_count,
				$LOGINID,
				$LOGINID,
				$LOGINID
			];

			$stmt = sqlsrv_query($conn, $sql_ler_mst, $params_ler_mst);
			if ($stmt === false) {
				throw new Exception("Insert ler_mst error: " . print_r(sqlsrv_errors(), true));
			}
			sqlsrv_free_stmt($stmt);

        } 

        // ---------------------------------------------------------------------
        // 2) Get mst RowID from lri_mst
        // ---------------------------------------------------------------------
        $sql_get = "SELECT RowID FROM ler_mst WHERE site_cd = ? AND ler_mst_ler_no = ?  AND ler_mst_ler_date >= ? AND ler_mst_ler_date < DATEADD(DAY,1,?)";
		$stmt = sqlsrv_query($conn, $sql_get, [$site_cd, $ler_mst_ler_no, $ler_mst_ler_date,$ler_mst_ler_date]);
        if ($stmt === false) {
            throw new Exception("Select ler_mst RowID error: " . print_r(sqlsrv_errors(), true));
        }

        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
        sqlsrv_free_stmt($stmt);

        $mst_RowID = $row['RowID'] ?? null;
        if (!$mst_RowID) throw new Exception("RowID not found for ler_mst_ler_no: " . $ler_mst_ler_no);
		
		
		
		// ---------------------------------------------------------------------
		// 3) Insert into lri_det
		// ---------------------------------------------------------------------
		$sql_ler_det = "
        INSERT INTO ler_det (
            site_cd, mst_RowID,
            ler_det_sign_by,   ler_det_sign_by_id, 	ler_det_sign_by_contact, 	ler_det_sign_by_title, 	ler_det_sign_date,
            ler_det_varchar1,  ler_det_varchar2, 	ler_det_varchar3, 			ler_det_varchar4, 		ler_det_varchar5,
            ler_det_varchar6,  ler_det_varchar7, 	ler_det_varchar8, 			ler_det_varchar9, 		ler_det_varchar10,
            ler_det_numeric1,  ler_det_numeric2, 	ler_det_numeric3, 			ler_det_numeric4, 		ler_det_numeric5,
            ler_det_datetime1, ler_det_datetime2, 	ler_det_datetime3, 			ler_det_datetime4, 		ler_det_datetime5,
            ler_det_note1,
            audit_user, audit_date,
            mbl_audit_user, mbl_audit_date,
            column1, column2, column3, column4, column5
        )
        VALUES (
            ?, ?,
            NULL, NULL, NULL, NULL, NULL,
            NULL, NULL, NULL, NULL, NULL,
            NULL, NULL, NULL, NULL, NULL,
            NULL, NULL, NULL, NULL, NULL,
            NULL, NULL, NULL, NULL, NULL,
            NULL,
            ?, GETDATE(),
            ?, GETDATE(),
            NULL, NULL, NULL, NULL, NULL
        )
		";

		$params_ler_det = [$site_cd, $mst_RowID, $LOGINID, $LOGINID];

		$stmt = sqlsrv_query($conn, $sql_ler_det, $params_ler_det);
		if ($stmt === false) {
			throw new Exception("Insert ler_det error: " . print_r(sqlsrv_errors(), true));
		}
		sqlsrv_free_stmt($stmt);
		

        // ---------------------------------------------------------------------
        // 4) Insert lri_ls1 lines (loop details)
        // Mapping:
        //   lri_ls1_linen     = dept_par_lin_cd
        //   lri_ls1_par_qty   = dept_par_qty
        //   lri_ls1_bal_qty   = qty_entered
        // ---------------------------------------------------------------------
        $sql_ins_ls1 = "
        INSERT INTO ler_ls1 (
            site_cd, mst_RowID,
            ler_ls1_linen,
            ler_ls1_req_qty,
            ler_ls1_issue1_qty,
            ler_ls1_issue2_qty,
            ler_ls1_sf_qty,
            ler_ls1_remark,
            audit_user, audit_date,
            mbl_audit_user, mbl_audit_date,
            column1, column2, column3, column4, column5
        )
        VALUES (
            ?, ?,
            ?,
            ?,
            NULL,
            NULL,
            NULL,
            NULL,
            ?, GETDATE(),
            ?, GETDATE(),
            NULL, NULL, NULL, NULL, NULL
        )
		";

		foreach ($details as $d) {

			// Read linen
			$linen  = is_object($d) ? ($d->linen_item ?? '') : ($d['linen_item'] ?? '');

			// Request qty
			$reqQty = is_object($d) ? ($d->req_qty ?? null) : ($d['req_qty'] ?? null);

			$linen = trim((string)$linen);
			if ($linen === '') continue;

			// reqQty: empty -> NULL else float
			$reqQty = trim((string)$reqQty);
			$reqQty = ($reqQty === '') ? null : (float)$reqQty;

			$params_ls1 = [
				$site_cd,
				$mst_RowID,
				$linen,
				$reqQty,
				$LOGINID,
				$LOGINID
			];

			$stmt = sqlsrv_query($conn, $sql_ins_ls1, $params_ls1);
			if ($stmt === false) {
				throw new Exception("Insert ler_ls1 error: " . print_r(sqlsrv_errors(), true));
			}
			sqlsrv_free_stmt($stmt);
		}
		
		
		// ---------------------------------------------------------------------
        // 5) Insert STATUS AUDIT into ler_ls2 (avoid inserting same status multiple times)
        //    Rule: if latest status == new status -> SKIP
        // ---------------------------------------------------------------------

        // Get latest status for this mst_RowID
        $sql_chk_ls2 = "
            SELECT TOP 1 ler_ls2_varchar2
            FROM ler_ls2 WITH (NOLOCK)
            WHERE site_cd = ? AND mst_RowID = ?
            ORDER BY ler_ls2_datetime1 DESC, RowID DESC
        ";
        $stmt = sqlsrv_query($conn, $sql_chk_ls2, [$site_cd, $mst_RowID]);
        if ($stmt === false) {
            throw new Exception("Select ler_ls2 latest status error: " . print_r(sqlsrv_errors(), true));
        }

        $lastStatus = '';
        if ($r = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $lastStatus = trim($r['ler_ls2_varchar2'] ?? '');
        }
        sqlsrv_free_stmt($stmt);
		
		
		// B) Check if same status already exists anywhere for this mst_RowID
		$sql_chk_exists = "
			SELECT 1
			FROM ler_ls2 WITH (NOLOCK)
			WHERE site_cd = ? AND mst_RowID = ? AND ler_ls2_varchar2 = ?
		";
		$stmt = sqlsrv_query($conn, $sql_chk_exists, [$site_cd, $mst_RowID, $status]);
		if ($stmt === false) {
			throw new Exception("Select ler_ls2 exists status error: " . print_r(sqlsrv_errors(), true));
		}
		$existsSameStatus = (sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC) !== null);
		sqlsrv_free_stmt($stmt);

        // Insert only if status changed
        if ($lastStatus !== $status && !$existsSameStatus) {

            
            $originator = $LOGINID; // you can change if you have separate originator
            $name       = $LOGINID; // replace with actual name if you have it

            $sql_lri_ls2 = "
                INSERT INTO ler_ls2 (
                    site_cd, mst_RowID,
                    ler_ls2_varchar1,   -- status cd
                    ler_ls2_varchar2,   -- status desc
                    ler_ls2_varchar3,   -- originator
                    ler_ls2_varchar4,   -- name
                    ler_ls2_datetime1,  -- start date
                    audit_user, audit_date,
                    mbl_audit_user, mbl_audit_date,
                    column1, column2, column3, column4, column5
                )
                VALUES (
                    ?, ?,
                    ?, ?,
                    ?, ?,
                    GETDATE(),
                    ?, GETDATE(),
                    ?, GETDATE(),
                    NULL, NULL, NULL, NULL, NULL
                )
            ";

            $params_ls2 = [
                $site_cd,
                $mst_RowID,
                $ler_mst_ler_no,
                $status,
                $originator,
                $name,
                $LOGINID,
                $LOGINID
            ];

            $stmt = sqlsrv_query($conn, $sql_lri_ls2, $params_ls2);
            if ($stmt === false) {
                throw new Exception("Insert lri_ls2 (status audit) error: " . print_r(sqlsrv_errors(), true));
            }
            sqlsrv_free_stmt($stmt);
        }

        $last_lri_no = $ler_mst_ler_no;
        $last_rowid  = $mst_RowID;
		
		
    }

    // Commit transaction
    if (!sqlsrv_commit($conn)) {
        throw new Exception("Commit failed: " . print_r(sqlsrv_errors(), true));
    }

    sqlsrv_close($conn);

    echo json_encode([
        'status' => 'SUCCESS',
        'message' => 'Additional Linen Request ' . $last_lri_no . ' created successfully',
        'ler_mst_ler_no' => $last_lri_no,
        'mst_RowID' => $last_rowid
    ]);

} catch (Exception $e) {

    if (isset($conn)) @sqlsrv_rollback($conn);

    file_put_contents($file_name, $now . "\r\n" . $e->getMessage() . "\r\n\r\n", FILE_APPEND);

    if (isset($conn)) @sqlsrv_close($conn);

    echo json_encode([
        'status' => 'Alert',
        'message' => "(".$now.") ".$e->getMessage(),
        'data' => []
    ]);
    exit();
}
?>