<?php
include_once("globarvar.php");

$show_platform = $show_team = $show_privacy = $show_webSecurity = "0";
$stmt = $conn->prepare("SELECT * FROM platform WHERE itemNo IN ('platform', 'team', 'privacy','webSecurity') LIMIT 4");
$stmt->execute();
while ($row = $stmt->fetch()) {
  ${'show_' . $row['itemNo']} = $row['flag_view'];
}

function check_login()
{
  if (!isset($_SESSION['Nchc_userId']) || !isset($_SESSION['Nchc_userNo'])) {
    header("Location: login.php");
  }
  return true;
}

function check_admin()
{
  if (!isset($_SESSION['Nchc_userId']) || !isset($_SESSION['Nchc_userNo']) || $_SESSION['Nchc_role'] != "admin") {
    header("Location: ../login.php");
  }
  return true;
}


function login($account, $password)
{
  global $conn;
  $sql = "SELECT *  FROM users  WHERE userNo = ? LIMIT 1;";
  try {
    $stmt = $conn->prepare($sql);
    $stmt->execute([$account]);
    $result = $stmt->fetch();

    if (is_array($result)) {
      if (isset($result['userStatus']) && $result['userStatus'] == 'N') {
        return '請先到信箱確認信件!';
      } else if (isset($result['userPassword']) && password_verify($password, $result['userPassword'])) {
        $_SESSION['Nchc_userId'] = $result['user_id'];  // 使用者id
        $_SESSION['Nchc_userNo'] = $result['userNo'];  // 使用者帳號
        $_SESSION['Nchc_userName'] = $result['userName'];  // 使用者名稱
        $_SESSION['Nchc_userDepartment'] = $result['department'];  // 服務單位
        $_SESSION['Nchc_role'] = $result['userRole'] ?? 'User';  // 使用者權限        
        log_use("login");
        header("Location: index.php");
        return '登入成功!';
      } else {
        return '帳號或密碼錯誤!';
      }
    } else {
      return  '帳號或密碼錯誤!!';
    }
  } catch (PDOException $e) {

    // sql_err_insert($sql, $e->getMessage(), 'insert_data1');    //紀錄 
    return $e;
  }

  return '發生錯誤!';
}


function insert_data1($tablename, $fieldarray)
{
  global $conn;
  $n_rid = 0;
  try {

    $columns = implode(", ", array_keys($fieldarray));
    $placeholders = implode(", ", array_fill(0, count($fieldarray), "?"));

    $sql = "INSERT INTO $tablename ($columns) VALUES ($placeholders) ON DUPLICATE KEY UPDATE ";

    // 为 ON DUPLICATE KEY UPDATE 准备设置
    $updateAssignments = [];
    foreach ($fieldarray as $key => $value) {
      $updateAssignments[] = "$key = VALUES($key)";
    }
    $sql .= implode(", ", $updateAssignments);

    // 准备和绑定参数
    $stmt = $conn->prepare($sql);
    $stmt->execute(array_values($fieldarray));
    $n_rid = $conn->lastInsertId();
    // sql_err_insert("sql", $sql,  'insert_data1');    //紀錄 

  } catch (PDOException $e) {
    // sql_err_insert($sql, $e->getMessage(), 'insert_data1');    //紀錄 
  }

  return $n_rid;
}

function update_data($tablename, $fieldarray, $where_clause = '')
{
  global $conn;
  try {
    $update_parts = [];
    foreach ($fieldarray as $field => $value) {
      $update_parts[] = "$field = :$field";
    }
    $update_str = implode(", ", $update_parts);
    $sql = "UPDATE {$tablename} SET {$update_str}";
    if (!empty($where_clause)) {
      $sql .= " WHERE {$where_clause}";
    }

    $stmt = $conn->prepare($sql);
    foreach ($fieldarray as $field => $value) {
      $stmt->bindValue(":$field", $value);
    }
    $stmt->execute($fieldarray);


    // sql_err_insert("sql", $sql,  'update_data');    //紀錄 
  } catch (PDOException $e) {
    // echo "錯誤: " . $e->getMessage();
    // sql_err_insert($sql, $e->getMessage(), 'update_data');    //紀錄
    // return $e->getMessage();
    return false;
  }
  return true;
}

function sql_err_insert($sql_err = '', $sql_errmsg = '', $msmag = '')
{
  global $conn,  $_SERVER;
  $sql = "INSERT INTO sql_err (sql_msg, sql_err ,sql_errmsg ,PHP_SELF ,REQUEST_URI ,REQUEST) VALUES 
    (:sql_msg, :sql_err ,:sql_errmsg ,:PHP_SELF ,:REQUEST_URI ,:REQUEST)";
  $stmt = $conn->prepare($sql);
  $rs = $stmt->execute(array(
    ':sql_msg' => $msmag,
    ':sql_err' => $sql_err,
    ':sql_errmsg' => $sql_errmsg,
    ':PHP_SELF' => $_SERVER['PHP_SELF'],
    ':REQUEST_URI' => $_SERVER['REQUEST_URI'],
    ':REQUEST' => (isset($_REQUEST) ? print_r($_REQUEST, true) : "")
  ));
  if ($rs) {
    return true;
  } else {
    return false;
  }
}


function checkData($data, $type)
{
  if ($type === 'string') {
    if (empty($data) || !preg_match("/^[\p{L}\p{N} _\/\.,;:!?\"()\-。，、！？\r\n&'#\’ ]+$/u", $data)) {

      return null;
    } else {

      $search = "&#039;";
      $replace = "'";
      $data = str_replace($search, $replace, $data);

      $search = "&amp;";
      $replace = "&";
      $data = str_replace($search, $replace, $data);

      $search = "&quot;";
      $replace = '"';
      $data = str_replace($search, $replace, $data);

      return $data;
    }
  } else if ($type === 'number') {
    if (empty($data) || !preg_match("/^-?[0-9]+$/", $data)) {
      return null;
    }
  } else if ($type === 'email') {
    if (empty($data) || !filter_var($data, FILTER_VALIDATE_EMAIL)) {
      return null;
    }
  } else if ($type === 'date') {
    if (empty($data) || !preg_match("/^\d{4}-\d{2}-\d{2}$/", $data)) {
      return null;
    }
  }
  return $data;
}

/**
 * 新增公告函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function News_Insert($data)
{
  global $conn;  // 使用全域變數 $conn 作為資料庫連接

  // 準備 SQL 插入語句
  $sql = "INSERT INTO system_news (title, content, publish_date, publish_unit, attachment_url, remove_date, updated_by,update_date)
          VALUES (:title, :content, :publish_date, :publish_unit, :attachment_url, :remove_date, :updated_by,:update_date)";

  // 使用 PDO 預處理語句來執行插入
  $stmt = $conn->prepare($sql);
  $stmt->bindValue(':title', $data['title']);
  $stmt->bindValue(':content', $data['content']);
  $stmt->bindValue(':publish_date', $data['publish_date']);
  $stmt->bindValue(':publish_unit', $data['publish_unit']);
  $stmt->bindValue(':attachment_url', $data['attachment_url']);
  $stmt->bindValue(':remove_date', $data['remove_date']);
  $stmt->bindValue(':updated_by', $_SESSION['Nchc_userNo'] ?? "");
  $stmt->bindValue(':update_date', date("Y-m-d H:i:s"));
  // 綁定表單資料至 SQL 語句
  /*  $stmt->bindParam(':title', $data['title']);
  $stmt->bindParam(':content', $data['content']);
  $stmt->bindParam(':publish_date', $data['publish_date']);
  $stmt->bindParam(':publish_unit', $data['publish_unit']);
  $stmt->bindParam(':attachment_url', $data['attachment_url']);
  $stmt->bindParam(':remove_date', $data['remove_date']);
  //$stmt->bindParam(':updated_by', $data['updated_by']);
  $stmt->bindParam(':updated_by', $_SESSION['Nchc_userNo']??""); */
  $stmt->execute();
  $n_rid = $conn->lastInsertId();
  // 執行並返回結果
  return $n_rid;
}

/**
 * 更新公告函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function News_Update($data)
{
  global $conn;  // 使用全域變數 $conn 作為資料庫連接

  // 準備 SQL 更新語句
  $sql = "UPDATE system_news 
          SET title = :title, content = :content, publish_date = :publish_date, 
              publish_unit = :publish_unit, attachment_url = :attachment_url, 
              remove_date = :remove_date, updated_by = :updated_by, update_date = :update_date
          WHERE news_id = :news_id";

  // 使用 PDO 預處理語句來執行更新
  $stmt = $conn->prepare($sql);

  $stmt->bindValue(':title', $data['title']);
  $stmt->bindValue(':content', $data['content']);
  $stmt->bindValue(':publish_date', $data['publish_date']);
  $stmt->bindValue(':publish_unit', $data['publish_unit']);
  $stmt->bindValue(':attachment_url', $data['attachment_url']);
  $stmt->bindValue(':remove_date', $data['remove_date'] ?? null);
  /* $stmt->bindValue(':updated_by', $data['updated_by']);   */
  $stmt->bindValue(':updated_by', $_SESSION['Nchc_userNo'] ?? null);
  $stmt->bindValue(':update_date', date("Y-m-d H:i:s"));
  $stmt->bindValue(':news_id', $data['news_id']);
  /* // 綁定表單資料至 SQL 語句
  $stmt->bindParam(':title', $data['title']);
  $stmt->bindParam(':content', $data['content']);
  $stmt->bindParam(':publish_date', $data['publish_date']);
  $stmt->bindParam(':publish_unit', $data['publish_unit']);
  $stmt->bindParam(':attachment_url', $data['attachment_url']);
  $stmt->bindParam(':remove_date', $data['remove_date']);
  $stmt->bindParam(':updated_by', $data['updated_by']);
  $stmt->bindParam(':update_date', date("Y-m-d H:i:s"));  // 更新日期
  $stmt->bindParam(':news_id', $data['news_id']);  // 更新時需指定 news_id */

  // 執行並返回結果
  return $stmt->execute();
}

/**
 * 新增使用者函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function Users_Insert($data)
{
  global $conn;  // 使用全域變數 $conn 作為資料庫連接

  // 準備 SQL 插入語句
  $sql = "INSERT INTO users (userNo, userPassword,email,userName,department,startDay, endDay, userRole, userStatus, updated_by,update_date)
          VALUES (:userNo, :userPassword, :email, :userName, :department, :startDay, :endDay, :userRole, :userStatus, :updated_by,:update_date)";
  $mhsah1 = password_hash($data['userPassword'], PASSWORD_DEFAULT); // 哈希密碼
  // 使用 PDO 預處理語句來執行插入
  $stmt = $conn->prepare($sql);
  $stmt->bindValue(':userNo', $data['userNo']);
  $stmt->bindValue(':userPassword', $mhsah1);
  $stmt->bindValue(':email', $data['email']);
  $stmt->bindValue(':userName', $data['userName']);
  $stmt->bindValue(':department', $data['department']);
  $stmt->bindValue(':startDay', $data['startDay']);
  $stmt->bindValue(':endDay', $data['endDay']);
  $stmt->bindValue(':userRole', $data['userRole']);
  $stmt->bindValue(':userStatus', $data['userStatus']);
  $stmt->bindValue(':updated_by', $_SESSION['Nchc_userNo'] ?? "");
  $stmt->bindValue(':update_date', date("Y-m-d H:i:s"));

  $stmt->execute();
  $n_rid = $conn->lastInsertId();
  // 執行並返回結果
  return $n_rid;
}

/**
 * 更新使用者函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function Users_Update($data)
{
  global $conn;  // 使用全域變數 $conn 作為資料庫連接
  $res = false;
  try {
    $sql = "UPDATE users 
          SET email = :email, userName = :userName, department = :department, 
              startDay = :startDay, endDay = :endDay, userRole = :userRole, userStatus = :userStatus, 
              updated_by = :updated_by, update_date = :update_date
          WHERE user_id = :user_id";
    $stmt = $conn->prepare($sql);
    $startDay = !empty($data['startDay']) ? $data['startDay'] : date("Y-m-d");
    $endDay = !empty($data['endDay']) ? $data['endDay'] : null;
    $fieldarray = array(
      ':email' => $data['email'],
      ':userName' => $data['userName'],
      ':department' => $data['department'],
      ':startDay' => $startDay,
      ':endDay' => $endDay ,
      ':userRole' => $data['userRole'] ?? '',
      ':userStatus' => $data['userStatus'] ?? 'N',
      ':updated_by' => $data['updated_by'],
      ':update_date' =>  date("Y-m-d H:i:s"),
      ':user_id' =>  $data['user_id']
    );
    $res = $stmt->execute($fieldarray);
  } catch (PDOException $e) {
    return $e;
  }

  return $res;
  // 使用 PDO 預處理語句來執行更新
  /* $stmt = $conn->prepare($sql);
  $stmt->bindValue(':email', $data['email']);
  $stmt->bindValue(':userName', $data['userName']);
  $stmt->bindValue(':department', $data['department']);
  $stmt->bindValue(':startDay', $data['startDay']);
  $stmt->bindValue(':endDay', $data['endDay']);
  $stmt->bindValue(':userRole', $data['userRole']);
  $stmt->bindValue(':userStatus', $data['userStatus']);
  $stmt->bindValue(':updated_by', $_SESSION['Nchc_userNo'] ?? "");
  $stmt->bindValue(':update_date', date("Y-m-d H:i:s"));
  $stmt->bindValue(':user_id', $data['user_id']);
  // 執行並返回結果
  return $stmt->execute(); */
}



function Data_No($mCategory = "WE") // 產生資料編號
{
  global $conn;
  $mNo = ""; // 資料領域編號 (例如：WD, WA, WE, GE)    
  // 產生唯一資料編號 (dataNo)
  // $datePrefix = date('Ymd'); // 取得當前日期 (YYYYMMDD)
  // $uniqueNumber = substr(uniqid('', true), -4); // 產生 4 位數的唯一流水號
  // $dataNo = $dataCategory . '-' . $datePrefix . $uniqueNumber; // 資料編號格式: WD-YYYYMMDD1234
  $mdataNo = $mCategory . '-' . date('Ymd'); // $datePrefix; // 資料編號格式: WD-YYYYMMDD1234
  $sql = "SELECT dataNo FROM data_service WHERE dataNo like :dataNo order by dataNo Desc Limit 1";
  $stmt = $conn->prepare($sql);
  $stmt->execute(array(':dataNo' => $mdataNo . "%"));
  $data = $stmt->fetch(PDO::FETCH_ASSOC);

  if ($data && isset($data['dataNo'])) {
    $lastThreeDigits = substr($data['dataNo'], -3);
    $incrementedNumber = str_pad(intval($lastThreeDigits) + 1, 3, '0', STR_PAD_LEFT);
    // $mRNo = substr($data['dataNo'], 0, -3) . $incrementedNumber;
    $mRNo = $incrementedNumber;
  } else {
    $mRNo = '001';
  }
  $mNo = $mdataNo . $mRNo;
  return $mNo;
}

/**
 * 新增資料服務函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function Data_Insert($data)
{
  global $conn;
  $n_rid = 0;
  $mdataNo = Data_No($data['dataCategory']); //$data['dataNo']; // 資料編號

  $sql = "INSERT INTO data_service (dataNo,dataTitle,dataSummary, dataSource,StartDate,EndDate, dataCategory, licenseMode, update_date,updated_by) 
  VALUES (:dataNo,:dataTitle,:dataSummary, :dataSource,:StartDate,:EndDate, :dataCategory, :licenseMode,:update_date,:updated_by)";
  $stmt = $conn->prepare($sql);
  $fieldarray = array(
    ':dataNo' => $mdataNo,
    ':dataTitle' => $data['dataTitle'],
    ':dataSummary' => $data['dataSummary'],
    ':dataSource' => $data['dataSource'],
    ':StartDate' => $data['StartDate'] ?? date("Y-m-01"),
    ':EndDate' => $data['EndDate'],
    ':dataCategory' => $data['dataCategory'],
    ':licenseMode' => $data['licenseMode'],
    ':update_date' =>  date("Y-m-d H:i:s"),
    ':updated_by' => ($_SESSION['Nchc_userNo'] ?? "")
  );

  $stmt->execute($fieldarray);
  $n_rid = $conn->lastInsertId();

  return $n_rid;
}

/**
 * 更新資料服務函式
 * @param array $data - 傳入表單資料
 * @return bool 是否成功
 */
function Data_Update($data)
{
  global $conn;
  $res = 0;
  $mrid = intval($data['data_id']);
  $sql = "UPDATE data_service
          SET dataTitle = :dataTitle,dataSummary = :dataSummary, dataSource = :dataSource,
          StartDate = :StartDate,EndDate = :EndDate, dataCategory = :dataCategory, 
          licenseMode = :licenseMode, updated_by = :updated_by, update_date = :update_date
          WHERE data_id = :data_id";

  // 使用 PDO 預處理語句來執行更新
  $stmt = $conn->prepare($sql);
  $fieldarray = array(
    ':dataTitle' => $data['dataTitle'],
    ':dataSummary' => $data['dataSummary'],
    ':dataSource' => $data['dataSource'],
    ':StartDate' => $data['StartDate'],
    ':EndDate' => $data['EndDate'],
    ':dataCategory' => $data['dataCategory'],
    ':licenseMode' => $data['licenseMode'],
    ':update_date' =>  date("Y-m-d H:i:s"),
    ':updated_by' => ($_SESSION['Nchc_userNo'] ?? ""),
    ':data_id' => $mrid
  );
  $res = $stmt->execute($fieldarray);

  return $res;
}

/**
 * 熱門關鍵字
 * @param array $mkey - 傳入關鍵字資料
 * @return bool 是否成功
 */
function log_DataKey($mkey) //熱門關鍵字
{
  $fieldarray = array(
    'keyWord' => $mkey,
    'user_id' => ($_SESSION['Nchc_userId']  ?? 0),
  );

  $n_rid = insert_data1("log_datakey", $fieldarray);
  return $n_rid;
}

/**
 * 使用記錄
 * @param array $mkey - 傳入關鍵字資料
 * @return bool 是否成功
 */
function log_use($mcodeNo = "", $mremark = "")
{
  $fieldarray = array(
    'codeNo' => $mcodeNo,
    'remark' => $mremark,
    'user_ip' => getClientIP(),
    'user_id' => ($_SESSION['Nchc_userId']  ?? 0),
  );

  $n_rid = insert_data1("log_use", $fieldarray);
  return $n_rid;
}

function getClientIP()
{
  if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
    // 檢查是否有透過 HTTP_CLIENT_IP 來取得用戶端的 IP
    $ip = $_SERVER['HTTP_CLIENT_IP'];
  } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
    // 檢查是否有透過 HTTP_X_FORWARDED_FOR 來取得用戶端的 IP
    $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
  } else {
    // 最後使用 REMOTE_ADDR 來獲取 IP 位址
    $ip = $_SERVER['REMOTE_ADDR'];
  }
  return $ip;
}
