<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
ini_set('display_errors', 1);
error_reporting(E_ALL);

$host = 'mysql3109.db.sakura.ne.jp';
$user = 'vegecoop_skillsheet';
$pass = 'vegess1045';
$dbname = 'vegecoop_skillsheet';

$conn = new mysqli($host, $user, $pass, $dbname);
$conn->set_charset('utf8mb4');

function bind_params_safe($stmt, $types, array &$params) {
    if (empty($params)) return;
    $bind_names = [];
    $bind_names[] = $types;
    for ($i = 0; $i < count($params); $i++) {
        $bind_names[] = &$params[$i];
    }
    return call_user_func_array([$stmt, 'bind_param'], $bind_names);
}

// ---------------- GET PARAMETERS ----------------
$user_type   = trim($_GET['user_type'] ?? '');
$kanriNo     = trim($_GET['kanriNo'] ?? '');
$name        = trim($_GET['name'] ?? '');
$furigana    = trim($_GET['furigana'] ?? '');
$gender      = trim($_GET['gender'] ?? '');
$nationality = trim($_GET['nationality'] ?? '');
$birthdate   = trim($_GET['birthdate'] ?? '');
$live        = trim($_GET['live'] ?? '');
$move        = trim($_GET['move'] ?? '');
$language    = trim($_GET['language'] ?? '');
$cer         = trim($_GET['cer'] ?? '');
$user_name   = trim($_GET['user_name'] ?? '');

$do_debug = (isset($_GET['debug']) && $_GET['debug'] === '1');

// ---------------- 携帯販売 filters ----------------
$filtered_ids = null;

if ($user_type === '携帯販売') {
    $k_filters_present = ($live !== '' || $move !== '' || $language !== '');

    if ($k_filters_present) {
        $sql_k = "SELECT DISTINCT s_id FROM k_info WHERE 1=1";
        $params_k = [];
        $types_k = '';

        if ($live !== '') {
            $sql_k .= " AND k_place LIKE ?";
            $params_k[] = "%$live%";
            $types_k .= 's';
        }
        if ($move !== '') {
            $sql_k .= " AND k_move LIKE ?";
            $params_k[] = "%$move%";
            $types_k .= 's';
        }
        if ($language !== '') {
            $sql_k .= " AND (k_lang1 LIKE ? OR k_lang2 LIKE ? OR k_lang3 LIKE ? OR k_lang4 LIKE ?)";
            $params_k = array_merge($params_k, array_fill(0, 4, "%$language%"));
            $types_k .= 'ssss';
        }

        $stmt_k = $conn->prepare($sql_k);
        if ($params_k) bind_params_safe($stmt_k, $types_k, $params_k);
        $stmt_k->execute();
        $res_k = $stmt_k->get_result();

        $ids_k = [];
        while ($r = $res_k->fetch_assoc()) {
            $ids_k[] = (string)$r['s_id'];
        }
        $stmt_k->close();

        $filtered_ids = $ids_k;
    }

    if ($cer !== '') {
        $sql_c = "SELECT DISTINCT s_id FROM cer_info WHERE cer_name LIKE ?";
        $like_cer = "%$cer%";

        $stmt_c = $conn->prepare($sql_c);
        $stmt_c->bind_param('s', $like_cer);
        $stmt_c->execute();
        $res_c = $stmt_c->get_result();

        $ids_c = [];
        while ($r = $res_c->fetch_assoc()) {
            $ids_c[] = (string)$r['s_id'];
        }
        $stmt_c->close();

        if ($filtered_ids === null) {
            $filtered_ids = $ids_c;
        } else {
            $filtered_ids = array_values(array_intersect($filtered_ids, $ids_c));
        }
    }
}

if ($filtered_ids !== null && count($filtered_ids) === 0) {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([], JSON_UNESCAPED_UNICODE);
    exit;
}

// ---------------- MAIN QUERY ----------------
$sql = "
SELECT
    s.s_id,
    s.s_name,
    s.s_furi,
    s.s_sex,
    s.s_from,
    s.s_stage,
    s.s_birth,
    s.s_type
FROM staff_info s
WHERE 1=1
";

$params = [];
$types  = '';

// ★ ここが修正点：ログイン user_type で検索制限
if ($user_type !== '' && $user_type !== 'all' && $user_type !== 'ビザ') {
    $sql .= " AND s.s_type = ?";
    $params[] = $user_type;
    $types .= 's';
}

if ($kanriNo !== '') {
    $sql .= " AND s.s_id LIKE ?";
    $params[] = "%$kanriNo%";
    $types .= 's';
}
if ($name !== '') {
    $sql .= " AND s.s_name LIKE ?";
    $params[] = "%$name%";
    $types .= 's';
}
if ($furigana !== '') {
    $sql .= " AND s.s_furi LIKE ?";
    $params[] = "%$furigana%";
    $types .= 's';
}
if ($gender !== '') {
    $sql .= " AND s.s_sex = ?";
    $params[] = $gender;
    $types .= 's';
}
if ($nationality !== '') {
    $sql .= " AND s.s_from LIKE ?";
    $params[] = "%$nationality%";
    $types .= 's';
}
if ($birthdate !== '') {
    $d = date_create($birthdate);
    if ($d !== false) {
        $sql .= " AND s.s_birth = ?";
        $params[] = $d->format('Y-m-d');
        $types .= 's';
    }
}
if ($filtered_ids !== null) {
    $placeholders = implode(',', array_fill(0, count($filtered_ids), '?'));
    $sql .= " AND s.s_id IN ($placeholders)";
    foreach ($filtered_ids as $fid) $params[] = $fid;
    $types .= str_repeat('s', count($filtered_ids));
}

$sql .= " ORDER BY s.s_id ASC";

$stmt = $conn->prepare($sql);
if ($params) bind_params_safe($stmt, $types, $params);
$stmt->execute();
$res = $stmt->get_result();

// ---------------- DISPLAY FILTER RULE ----------------
$data = [];
$allow_admin_users = ['admin', 'iwasaki', 'takatsu'];

while ($row = $res->fetch_assoc()) {

    $is_honsya = (mb_strpos($row['s_type'], '本社') !== false);

    if ($user_name === 'inaba') {
        if (!$is_honsya) continue;

    } elseif ($user_type === 'ビザ') {
        // 全表示

    } elseif (in_array($user_name, $allow_admin_users, true)) {
        // 全表示

    } else {
        if ($is_honsya) continue;
    }

    foreach ($row as $k => $v) {
        $row[$k] = ($v === null ? null : (string)$v);
    }

    $data[] = $row;
}

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

if ($do_debug) {
    echo json_encode([
        'sql' => $sql,
        'types' => $types,
        'params' => $params,
        'result_count' => count($data),
        'result_sample' => array_slice($data, 0, 30)
    ], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
} else {
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
}

$stmt->close();
$conn->close();
?>