<?php
require_once 'config.php';
header('Content-Type: application/json');

checkAdmin();

$log_db = getDB(DB_LOG_HOST, DB_LOG_NAME, DB_LOG_USER, DB_LOG_PASS);

$action = $_REQUEST['action'] ?? '';

try {
    if ($action === 'list') {
        // ดึงชุดข้อมูลทั้งหมดมาก่อน
        $stmt = $log_db->query("SELECT * FROM export_profiles ORDER BY id DESC");
        $profiles = $stmt->fetchAll();
        
        // รวบรวม CID ของผู้สร้างทั้งหมดที่ไม่ซ้ำกัน
        $cids = [];
        foreach ($profiles as $profile) {
            if (!empty($profile['created_by'])) {
                $cids[] = $profile['created_by'];
            }
        }
        
        // ถ้ามีผู้สร้าง ให้ไปดึงชื่อจากฐานข้อมูล HR (เพื่อป้องกันปัญหา Cross-Database Join และ Permission)
        $creator_names = [];
        if (!empty($cids)) {
            $cids = array_unique($cids);
            $inQuery = implode(',', array_fill(0, count($cids), '?'));
            
            try {
                $hr_db = getDB(DB_HR_HOST, DB_HR_NAME, DB_HR_USER, DB_HR_PASS);
                $hr_stmt = $hr_db->prepare("
                    SELECT a.HR_CID, CONCAT(e.HR_PREFIX_NAME, a.HR_FNAME, ' ', a.HR_LNAME) as full_name
                    FROM hr_person a
                    LEFT JOIN hr_prefix e ON a.HR_PREFIX_ID = e.HR_PREFIX_ID
                    WHERE a.HR_CID IN ($inQuery)
                ");
                $hr_stmt->execute(array_values($cids));
                
                while ($row = $hr_stmt->fetch()) {
                    $creator_names[$row['HR_CID']] = $row['full_name'];
                }
            } catch (Exception $ex) {
                // ถ้าเชื่อมต่อ HR_DB ไม่ได้หรือ query ผิดพลาด ให้ข้ามไป (ปล่อยให้ชื่อเป็นว่างๆ)
                error_log("Failed to fetch creator names: " . $ex->getMessage());
            }
        }
        
        foreach ($profiles as &$profile) {
            $cid = $profile['created_by'];
            $profile['creator_name'] = !empty($cid) && isset($creator_names[$cid]) ? $creator_names[$cid] : null;
            $profile['created_at'] = formatThaiDate($profile['created_at']);
        }
        
        echo json_encode(['data' => $profiles]);
    } 
    elseif ($action === 'get') {
        $id = $_GET['id'] ?? 0;
        $stmt = $log_db->prepare("SELECT * FROM export_profiles WHERE id = ?");
        $stmt->execute([$id]);
        $profile = $stmt->fetch();
        echo json_encode(['status' => 'success', 'data' => $profile]);
    }
    elseif ($action === 'save') {
        $id = $_POST['id'] ?? '';
        $profile_name = trim($_POST['profile_name'] ?? '');
        $excel_columns = trim($_POST['excel_columns'] ?? '');
        $sql_query = trim($_POST['sql_query'] ?? '');
        
        if (empty($profile_name) || empty($excel_columns) || empty($sql_query)) {
            echo json_encode(['status' => 'error', 'message' => 'กรุณากรอกข้อมูลให้ครบถ้วน']);
            exit;
        }
        
        if (empty($id)) {
            // Insert
            $cid = $_SESSION['cid'] ?? null;
            $stmt = $log_db->prepare("INSERT INTO export_profiles (profile_name, excel_columns, sql_query, created_by) VALUES (?, ?, ?, ?)");
            $stmt->execute([$profile_name, $excel_columns, $sql_query, $cid]);
            
            addSystemLog('CREATE_PROFILE', "สร้างชุดข้อมูลใหม่: $profile_name");
            
            echo json_encode(['status' => 'success', 'message' => 'บันทึกชุดข้อมูลสำเร็จ']);
        } else {
            // Update
            $stmt = $log_db->prepare("UPDATE export_profiles SET profile_name = ?, excel_columns = ?, sql_query = ? WHERE id = ?");
            $stmt->execute([$profile_name, $excel_columns, $sql_query, $id]);
            
            addSystemLog('EDIT_PROFILE', "แก้ไขชุดข้อมูล: $profile_name (ID: $id)");
            
            echo json_encode(['status' => 'success', 'message' => 'แก้ไขชุดข้อมูลสำเร็จ']);
        }
    }
    elseif ($action === 'delete') {
        $id = $_POST['id'] ?? 0;
        
        // Get name before delete
        $stmt = $log_db->prepare("SELECT profile_name FROM export_profiles WHERE id = ?");
        $stmt->execute([$id]);
        $profile = $stmt->fetch();
        
        if ($profile) {
            $stmt = $log_db->prepare("DELETE FROM export_profiles WHERE id = ?");
            $stmt->execute([$id]);
            
            addSystemLog('DELETE_PROFILE', "ลบชุดข้อมูล: {$profile['profile_name']} (ID: $id)");
        }
        
        echo json_encode(['status' => 'success', 'message' => 'ลบชุดข้อมูลสำเร็จ']);
    }
    else {
        echo json_encode(['status' => 'error', 'message' => 'Unknown action']);
    }
} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => 'Database Error: ' . $e->getMessage()]);
}