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

header('Content-Type: application/json; charset=utf-8');

try {
    // Read JSON (POST)
    $body = json_decode(file_get_contents("php://input"), true);

    $site_cd      			= trim($body['site_cd'] ?? '');
    $dept_cd      			= trim($body['dept_mst_dept_cod'] ?? '');
    $lss_mst_lss_month     	= trim($body['lss_mst_lss_month'] ?? '');
    $lss_mst_lss_year      	= trim($body['lss_mst_lss_year'] ?? '');
    $selectedDate 			= trim($body['selectedDate'] ?? ''); // dd-mm-yyyy

    if ($site_cd === '') {
        throw new Exception("Missing required parameter: site_cd");
    }

    // Optional date conversion
    $sqlDate = '';
    if ($selectedDate !== '') {
        $dt = DateTime::createFromFormat('d-m-Y', $selectedDate);
        if (!$dt) {
            throw new Exception("Invalid selectedDate format. Use dd-mm-yyyy ");
        }
        $sqlDate = $dt->format('Y-m-d');
    }

    $json = [];

    $sql = "
        SELECT
            m.RowID,
            m.site_cd,
            m.lss_mst_lss_no,
            m.lss_mst_lss_date,
            m.lss_mst_status,
            m.lss_mst_desc,
            m.lss_mst_department,
            m.lss_mst_hod,
            m.lss_mst_hod_id,
			m.lss_mst_lss_year,
			m.lss_mst_lss_month,
            d.dept_mst_description,

            ISNULL(q.total_qns, 0) AS total_questions,
            ISNULL(q.answered_qns, 0) AS answered_questions,

            CASE
                WHEN ISNULL(q.total_qns, 0) = 0 THEN 0
                ELSE ROUND((CAST(ISNULL(q.answered_qnss, 0) AS FLOAT) / ISNULL(q.total_qns, 0)) * 100, 2)
            END AS answered_percentage

        FROM lss_mst m WITH (NOLOCK)

        LEFT JOIN dept_mst d WITH (NOLOCK)
            ON d.site_cd = m.site_cd
           AND d.dept_mst_dept_cod = m.lss_mst_department

        OUTER APPLY (
            SELECT
                COUNT(*) AS total_qns,
                ISNULL(SUM(
                    CASE
                        WHEN ISNULL(l.lss_ls1_answer, '') <> '' THEN 1
                        ELSE 0
                    END
                ), 0) AS answered_qns,
				
				ISNULL(SUM(
                    CASE
                        WHEN ISNULL(l.lss_ls1_answer, '') <> '' 
						AND  ISNULL(l.lss_ls1_answer, 'No') <> 'No'
						THEN 1
                        ELSE 0
                    END
                ), 0) AS answered_qnss
            FROM lss_ls1 l WITH (NOLOCK)
            WHERE l.site_cd = m.site_cd
              AND l.mst_rowid = m.RowID
        ) q

        WHERE m.site_cd = ?
    ";

    $params = [$site_cd];

    // Department filter
    if ($dept_cd !== '') {
        $sql .= " AND m.lss_mst_department = ? ";
        $params[] = $dept_cd;
    }

    // Date filter
    if ($sqlDate !== '') {
        $sql .= " AND CONVERT(date, m.lss_mst_lss_date) = ? ";
        $params[] = $sqlDate;
    }

	// Year filter
    if ($lss_mst_lss_month !== '') {
        $sql .= " AND m.lss_mst_lss_month = ? ";
        $params[] = $lss_mst_lss_month;
    }
	
	// Month filter
    if ($lss_mst_lss_year !== '') {
        $sql .= " AND m.lss_mst_lss_year = ? ";
        $params[] = $lss_mst_lss_year;
    }
	
    $sql .= " ORDER BY m.lss_mst_lss_date DESC, m.RowID DESC ";

    $stmt = sqlsrv_query($conn, $sql, $params);

    if ($stmt === false) {
        throw new Exception("Query error: " . print_r(sqlsrv_errors(), true));
    }

    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

        // Format SQL date safely
        $docDate = '';
        if (!empty($row['lss_mst_lss_date'])) {
            if ($row['lss_mst_lss_date'] instanceof DateTime) {
                $docDate = $row['lss_mst_lss_date']->format('d-m-Y H:i:s');
            } else {
                $docDate = date('d-m-Y H:i:s', strtotime($row['lss_mst_lss_date']));
            }
        }

        $json[] = [
            'RowID'                => $row['RowID'],
            'site_cd'              => $row['site_cd'],
            'lss_mst_lss_no'       => $row['lss_mst_lss_no'],
            'lss_mst_lss_date'     => $docDate,
            'lss_mst_status'       => $row['lss_mst_status'],
            'lss_mst_desc'         => $row['lss_mst_desc'],
            'lss_mst_department'   => $row['lss_mst_department'],
            'dept_mst_description' => $row['dept_mst_description'],
            'lss_mst_hod'    	   => $row['lss_mst_hod'],
            'lss_mst_hod_id'       => $row['lss_mst_hod_id'],
            'lss_mst_lss_year'       => $row['lss_mst_lss_year'],
            'lss_mst_lss_month'       => $row['lss_mst_lss_month'],
            'total_questions'      => (int)$row['total_questions'],
            'answered_questions'   => (int)$row['answered_questions'],
            'answered_percentage'  => (float)$row['answered_percentage']
        ];
    }

    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);

    echo json_encode([
        "status"  => "SUCCESS",
        "message" => count($json) > 0 ? "Record found" : "No record found",
        "data"    => $json
    ]);

} catch (Exception $e) {

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

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