<?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];
    } elseif (isset($result->Header) && is_array($result->Header)) {
        $headers = $result->Header;
    } 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");
    }

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

    $last_lss_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 ?? '');
        $lss_mst_lss_no       	= trim($Header->lss_mst_lss_no ?? '');
        $lss_mst_lss_date     	= trim($Header->request_date_time ?? '');
        $lss_mst_hod    		= trim($Header->HOD ?? '');
        $lss_mst_hod_id      	= trim($Header->HOD_ID ?? '');
		$lss_mst_lss_year 		= trim($Header->lss_mst_lss_year ?? '');
		$lss_mst_lss_month 		= trim($Header->lss_mst_lss_month ?? '');
        $LOGINID              	= trim($Header->LoginID ?? '');
		$rawStatus 				= $Header->lss_mst_status ?? false;
		$status 				= filter_var($rawStatus, FILTER_VALIDATE_BOOLEAN) ? 'ISU' : 'OPN';
		

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

		
		// ---------------------------------------------------------------------
		// 1) Duplicate check by site_cd + Document No + date
		// ---------------------------------------------------------------------
		$sql_check = "
			SELECT TOP 1
				lss_mst_lss_no
			FROM lss_mst WITH (NOLOCK)
			WHERE site_cd = ?
			  AND lss_mst_department =?
			 
			  AND lss_mst_lss_month = ?  
			  AND lss_mst_lss_year = ?
			ORDER BY RowID DESC
		";

		$stmt = sqlsrv_query($conn, $sql_check, [$site_cd, $dept_mst_dept_cod,$lss_mst_lss_month,$lss_mst_lss_year]);
		if ($stmt === false) {
			throw new Exception("Duplicate check error: " . print_r(sqlsrv_errors(), true));
		}

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

		if ($dup) {
			throw new Exception("There is already a Satisfaction Survey for this department. Survey No: " . $dup['lss_mst_lss_no']);
		}

        // Generate doc no if empty
        if ($lss_mst_lss_no === '') {

            $sql_cnt = "
                SELECT doc_no = cnt_mst_prefix + RIGHT('000000' + CAST(cnt_mst_counter + 1 AS VARCHAR(6)), 6)
                FROM cnt_mst WITH (NOLOCK)
                WHERE site_cd = ?
                  AND cnt_mst_module_cd = 'LSF';

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

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

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

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

            $lss_mst_lss_no = $generated_doc_no;

            $sql_lss_mst = "
                INSERT INTO lss_mst (
                    site_cd,
                    lss_mst_lss_no,
                    lss_mst_lss_date,
                    lss_mst_status,
                    lss_mst_desc,
                    lss_mst_department,
                    lss_mst_hod,
                    lss_mst_hod_id,
					lss_mst_create_date,
					lss_mst_lss_year,
					lss_mst_lss_month,
                    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_lss_mst = [
                $site_cd,
                $lss_mst_lss_no,
                $lss_mst_lss_date,
                $status,
                $dept_mst_description,
                $dept_mst_dept_cod,
                $lss_mst_hod,
                $lss_mst_hod_id,
				$lss_mst_lss_year,
				$lss_mst_lss_month,
                $LOGINID,
                $LOGINID
            ];

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

        // Get mst RowID
        $sql_get = "SELECT RowID FROM lss_mst WHERE site_cd = ? AND lss_mst_lss_no = ?";
        $stmt = sqlsrv_query($conn, $sql_get, [$site_cd, $lss_mst_lss_no]);
        if ($stmt === false) {
            throw new Exception("Select lss_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 lss_mst_lss_no: " . $lss_mst_lss_no);
        }

        // Insert into lss_det only once per master if not exists
        $sql_chk_det = "
            SELECT TOP 1 RowID
            FROM lss_det WITH (NOLOCK)
            WHERE site_cd = ? AND mst_RowID = ?
        ";
        $stmt = sqlsrv_query($conn, $sql_chk_det, [$site_cd, $mst_RowID]);
        if ($stmt === false) {
            throw new Exception("Check lss_det exists error: " . print_r(sqlsrv_errors(), true));
        }
        $det_exists = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
        sqlsrv_free_stmt($stmt);

        if (!$det_exists) {
            $sql_lss_det = "
                INSERT INTO lss_det (
                    site_cd, mst_RowID,
                    lss_det_varchar1, lss_det_varchar2, lss_det_varchar3, lss_det_varchar4, lss_det_varchar5,
                    lss_det_varchar6, lss_det_varchar7, lss_det_varchar8, lss_det_varchar9, lss_det_varchar10,
                    lss_det_numeric1, lss_det_numeric2, lss_det_numeric3, lss_det_numeric4, lss_det_numeric5,
                    lss_det_datetime1, lss_det_datetime2, lss_det_datetime3, lss_det_datetime4, lss_det_datetime5,
                    lss_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,
                    ?, GETDATE(),
                    ?, GETDATE(),
                    NULL, NULL, NULL, NULL, NULL
                )
            ";

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

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

        // Insert detail lines into lss_ls1
        $sql_ins_ls1 = "
            INSERT INTO lss_ls1 (
                site_cd,mst_RowID,
                lss_ls1_question,
                lss_ls1_answer,
                lss_ls1_varchar1,
                audit_user,
                audit_date,
                mbl_audit_user,
                mbl_audit_date,
                column1, column2, column3, column4, column5
            )
            VALUES (
                ?, ?,
                ?, ?, ?,
                ?, GETDATE(),
                ?, GETDATE(),
                NULL, NULL, NULL, NULL, NULL
            )
        ";

        foreach ($details as $d) {
            $question = is_object($d) ? ($d->stp_desc ?? '') : ($d['stp_desc'] ?? '');
            $answer   = is_object($d) ? ($d->stp_zom_data ?? '') : ($d['stp_zom_data'] ?? '');
            $varchar1 = is_object($d) ? ($d->stp_datatype ?? '') : ($d['stp_datatype'] ?? '');
           
            $question = trim((string)$question);
            $answer   = trim((string)$answer);
            $varchar1 = trim((string)$varchar1);
           

            if ($question === '' && $answer === '' && $varchar1 === '') {
                continue;
            }

            $params_ls1 = [
                $site_cd,
                $mst_RowID,
                $question,
                $answer,
                $varchar1,
                $LOGINID,
                $LOGINID
            ];

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

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

        $lastStatus = '';
        if ($r = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $lastStatus = trim($r['lss_ls2_varchar1'] ?? '');
        }
        sqlsrv_free_stmt($stmt);

        $sql_chk_exists = "
            SELECT 1
            FROM lss_ls2 WITH (NOLOCK)
            WHERE site_cd = ? AND mst_RowID = ? AND lss_ls2_varchar1 = ?
        ";
        $stmt = sqlsrv_query($conn, $sql_chk_exists, [$site_cd, $mst_RowID, $status]);
        if ($stmt === false) {
            throw new Exception("Select lss_ls2 exists status error: " . print_r(sqlsrv_errors(), true));
        }
        $existsSameStatus = (sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC) !== null);
        sqlsrv_free_stmt($stmt);

        if ($lastStatus !== $status && !$existsSameStatus) {
            $originator = $LOGINID;
            $name       = $LOGINID;

            $sql_lss_ls2 = "
                INSERT INTO lss_ls2 (
                    site_cd, mst_RowID,
                    lss_ls2_varchar1,
                    lss_ls2_varchar2,
                    lss_ls2_varchar3,
                    lss_ls2_varchar4,
                    lss_ls2_datetime1,
                    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,
                $lss_mst_lss_no,
                $status,
                $originator,
                $name,
                $LOGINID,
                $LOGINID
            ];

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

        $last_lss_no = $lss_mst_lss_no;
        $last_rowid  = $mst_RowID;
    }

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

    sqlsrv_close($conn);

    echo json_encode([
        'status' => 'SUCCESS',
        'message' => 'New Satisfaction Survey ' . $last_lss_no . ' created successfully',
        'lss_mst_lss_no' => $last_lss_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' => $e->getMessage(),
        'data' => []
    ]);
    exit();
}
?>