<?php
require_once '../../config.php';
require_once __DIR__ . '/collection_provider_inc.php';

header('Content-Type: application/json; charset=utf-8');

// 处理 OPTIONS 预检请求
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode([
        'error' => true,
        'message' => 'POST method only'
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    $input = file_get_contents('php://input');
    $data = json_decode($input, true);

    if (!$data) {
        throw new Exception('Invalid JSON payload');
    }

    if (!isset($data['WebsiteID']) || !isset($data['TableType']) || !isset($data['Date'])) {
        throw new Exception('Missing required fields: WebsiteID, TableType or Date');
    }

    $website_id = intval($data['WebsiteID']);
    $table_type = $data['TableType'];
    $date = $data['Date'];

    if (empty($table_type) || strlen($table_type) > 100) {
        throw new Exception('TableType is required and must be less than 100 characters');
    }

    $needs_rate = in_array($table_type, ['Buy', 'Sell', 'Payment Gateway']);

    $date_formats = ['Y-m-d', 'Y-n-j', 'Y-m-j', 'Y-n-d'];
    $date_obj = false;
    $normalized_date = $date;

    foreach ($date_formats as $format) {
        $date_obj = DateTime::createFromFormat($format, $date);
        if ($date_obj) {
            $normalized_date = $date_obj->format('Y-m-d');
            break;
        }
    }

    if (!$date_obj) {
        throw new Exception('Invalid date format: ' . $date);
    }

    $date = $normalized_date;

    // 检查是否提供了 RecordID，如果提供了，表示这是部分更新（只更新提供的字段）
    $record_id = isset($data['RecordID']) && $data['RecordID'] !== '' && $data['RecordID'] !== null ? intval($data['RecordID']) : null;
    $is_partial_update = $record_id !== null; // 如果提供了 RecordID，就是部分更新

    // 只有在 payload 中明确提供的字段才会被处理
    $description = null;
    $updateDescription = false;
    if (isset($data['DESCRIPTION'])) {
        $updateDescription = true;
        $description = ($data['DESCRIPTION'] !== '' && $data['DESCRIPTION'] !== null) ? $data['DESCRIPTION'] : null;
    }
    
    // 处理 InlineWebID（用于 Inline 类型分类）
    $inlineWebId = null;
    $updateInlineWebId = false;
    if (isset($data['InlineWebID'])) {
        $updateInlineWebId = true;
        if ($data['InlineWebID'] !== '' && $data['InlineWebID'] !== null) {
            $inlineWebId = intval($data['InlineWebID']);
            // 验证 Website 是否存在
            $checkWebsiteSql = "SELECT ID FROM Website WHERE ID = ?";
            $checkWebsiteStmt = mysqli_prepare($conn, $checkWebsiteSql);
            if ($checkWebsiteStmt) {
                mysqli_stmt_bind_param($checkWebsiteStmt, 'i', $inlineWebId);
                mysqli_stmt_execute($checkWebsiteStmt);
                $checkWebsiteResult = mysqli_stmt_get_result($checkWebsiteStmt);
                if (!mysqli_fetch_assoc($checkWebsiteResult)) {
                    $inlineWebId = null; // Website 不存在，设为 NULL
                }
                mysqli_stmt_close($checkWebsiteStmt);
            }
        } else {
            $inlineWebId = null;
        }
    }
    
    // 处理 MC 分类：获取或创建 MCID
    $mcId = null;
    $mcName = null;
    $updateMC = false;
    
    // 如果提供了 MCID 或 MC，才处理 MC
    if (isset($data['MCID'])) {
        $updateMC = true;
        if ($data['MCID'] !== '' && $data['MCID'] !== null) {
            $mcId = intval($data['MCID']);
        }
    } elseif (isset($data['MC'])) {
        $updateMC = true;
        if ($data['MC'] !== '' && $data['MC'] !== null) {
            $mcName = trim($data['MC']);
        }
    } elseif (
        !$is_partial_update &&
        $description !== null &&
        jkbo_collection_extract_provider($description) !== null
    ) {
        $collectionSettings = jkbo_collection_get_website_settings($conn, $website_id);
        $collectionFields = jkbo_collection_apply_buy_sell_fields($description, $collectionSettings);
        if ($collectionFields['mcName'] !== null) {
            $mcName = $collectionFields['mcName'];
            $updateMC = true;
        }
    }
    
    // 如果有 MC 名称，查找或创建 SplitMonthlyMC 记录
    if ($mcName !== null && $mcId === null) {
        $mcId = jkbo_collection_resolve_mc_id($conn, $mcName);
    }
    
    // 处理 Expense Category 分类：获取或创建 ExpenseCategoryID
    $expenseCategoryId = null;
    $expenseCategoryName = null;
    $updateExpenseCategory = false;
    
    // 如果提供了 ExpenseCategoryID 或 CategoryName，才处理 Expense Category（仅 Expense 类型）
    if (in_array($table_type, ['Expense', 'Payment Gateway'], true)) {
        if (isset($data['ExpenseCategoryID'])) {
            $updateExpenseCategory = true;
            if ($data['ExpenseCategoryID'] !== '' && $data['ExpenseCategoryID'] !== null) {
                $expenseCategoryId = intval($data['ExpenseCategoryID']);
            }
        } elseif (isset($data['CategoryName']) || isset($data['Category'])) {
            $updateExpenseCategory = true;
            $categoryNameValue = isset($data['CategoryName']) ? $data['CategoryName'] : $data['Category'];
            if ($categoryNameValue !== '' && $categoryNameValue !== null) {
                $expenseCategoryName = trim($categoryNameValue);
            }
        }
    }
    
    // 如果有 Category 名称，查找或创建 ExpenseCategory 记录
    if ($expenseCategoryName !== null && $expenseCategoryId === null) {
        // 确保 ExpenseCategory 表存在
        $checkTableSql = "SHOW TABLES LIKE 'ExpenseCategory'";
        $checkTableResult = mysqli_query($conn, $checkTableSql);
        if (mysqli_num_rows($checkTableResult) == 0) {
            $createTableSql = "CREATE TABLE IF NOT EXISTS `ExpenseCategory` (
              `ID` int NOT NULL AUTO_INCREMENT,
              `CategoryName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL COMMENT 'Category 名称',
              `Status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1=启用，0=停用',
              `Created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
              `Updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`ID`),
              UNIQUE KEY `uk_category_name` (`CategoryName`),
              KEY `idx_status` (`Status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci";
            
            if (!mysqli_query($conn, $createTableSql)) {
                // 如果创建表失败，继续执行，但不设置 ExpenseCategoryID
            }
        }
        
        // 查找 Category
        $checkCategorySql = "SELECT ID FROM ExpenseCategory WHERE CategoryName = ?";
        $checkCategoryStmt = mysqli_prepare($conn, $checkCategorySql);
        if ($checkCategoryStmt) {
            mysqli_stmt_bind_param($checkCategoryStmt, 's', $expenseCategoryName);
            mysqli_stmt_execute($checkCategoryStmt);
            $checkCategoryResult = mysqli_stmt_get_result($checkCategoryStmt);
            $categoryExists = mysqli_fetch_assoc($checkCategoryResult);
            mysqli_stmt_close($checkCategoryStmt);
            
            if ($categoryExists) {
                $expenseCategoryId = intval($categoryExists['ID']);
            } else {
                // 创建新的 Category
                $insertCategorySql = "INSERT INTO ExpenseCategory (CategoryName, Status) VALUES (?, 1)";
                $insertCategoryStmt = mysqli_prepare($conn, $insertCategorySql);
                if ($insertCategoryStmt) {
                    mysqli_stmt_bind_param($insertCategoryStmt, 's', $expenseCategoryName);
                    mysqli_stmt_execute($insertCategoryStmt);
                    $expenseCategoryId = mysqli_insert_id($conn);
                    mysqli_stmt_close($insertCategoryStmt);
                }
            }
        }
    }
    
    // 处理其他字段：只有在 payload 中提供的字段才会被处理
    $in = null;
    $updateIn = false;
    if (isset($data['IN'])) {
        $updateIn = true;
        $in = ($data['IN'] !== '' && $data['IN'] !== null) ? floatval($data['IN']) : null;
    }
    
    $out = null;
    $updateOut = false;
    if (isset($data['OUT'])) {
        $updateOut = true;
        $out = ($data['OUT'] !== '' && $data['OUT'] !== null) ? floatval($data['OUT']) : null;
    }
    
    $time = null;
    $updateTime = false;
    if (isset($data['TIME'])) {
        $updateTime = true;
        $time = ($data['TIME'] !== '' && $data['TIME'] !== null) ? $data['TIME'] : null;
    }
    
    $id_field = null;
    $updateIdField = false;
    if (isset($data['ID'])) {
        $updateIdField = true;
        $id_field = ($data['ID'] !== '' && $data['ID'] !== null) ? $data['ID'] : null;
    }
    
    $match = null;
    $updateMatch = false;
    if (isset($data['MATCH'])) {
        $updateMatch = true;
        $match = ($data['MATCH'] !== '' && $data['MATCH'] !== null) ? $data['MATCH'] : null;
    }
    
    $fee = null;
    $updateFee = false;
    if (isset($data['FEE'])) {
        $updateFee = true;
        $fee = ($data['FEE'] !== '' && $data['FEE'] !== null) ? floatval($data['FEE']) : null;
    }
    
    $remarks = null;
    $updateRemarks = false;
    if (isset($data['REMARKS'])) {
        $updateRemarks = true;
        $remarks = ($data['REMARKS'] !== '' && $data['REMARKS'] !== null) ? $data['REMARKS'] : null;
    }

    // 处理 DataType
    $data_type = isset($data['DataType']) && in_array($data['DataType'], ['normal', 'cutoff', 'closing']) ? $data['DataType'] : 'normal';
    $updateDataType = isset($data['DataType']);

    $rate = null;
    $myr_total = null;
    $updateRate = false;
    $updateMyrTotal = false;
    if (isset($data['RATE'])) {
        $updateRate = true;
        $rate = ($data['RATE'] !== '' && $data['RATE'] !== null) ? floatval($data['RATE']) : null;
    } elseif (!$is_partial_update && $needs_rate) {
        // 非部分更新时，Buy/Sell 类型需要 RATE
        $updateRate = true;
    }
    
    if (isset($data['MYR_TOTAL'])) {
        $updateMyrTotal = true;
        $myr_total = ($data['MYR_TOTAL'] !== '' && $data['MYR_TOTAL'] !== null) ? floatval($data['MYR_TOTAL']) : null;
    } elseif (!$is_partial_update && $needs_rate) {
        // 非部分更新时，Buy/Sell 类型需要 MYR_TOTAL
        $updateMyrTotal = true;
    }

    // $record_id 已在前面定义，这里直接使用
    $existing_id = null;
    
    // 如果提供了 RecordID，验证该记录存在，然后直接更新
    if ($record_id) {
        $verify_sql = "SELECT ID FROM DailyData WHERE ID = " . intval($record_id) . " LIMIT 1";
        $verify_result = mysqli_query($conn, $verify_sql);
        if (!$verify_result || mysqli_num_rows($verify_result) === 0) {
            throw new Exception('Record with ID ' . $record_id . ' does not exist');
        }
        mysqli_free_result($verify_result);
        // 如果提供了 RecordID，直接使用它更新，不进行任何冲突检查或删除操作
        $existing_id = $record_id;
    } else {
        // 如果没有提供 RecordID，检查是否存在相同的记录（基于唯一索引）
        // 这样可以决定是更新现有记录还是插入新记录
        if ($time !== null) {
            if ($description !== null) {
                $check_sql = "
                SELECT ID FROM DailyData 
                WHERE WebsiteID = " . intval($website_id) . "
                AND TableType = '" . mysqli_real_escape_string($conn, $table_type) . "'
                AND `Date` = '" . mysqli_real_escape_string($conn, $date) . "'
                AND TIME = '" . mysqli_real_escape_string($conn, $time) . "'
                AND DataType = '" . mysqli_real_escape_string($conn, $data_type) . "'
                AND DESCRIPTION = '" . mysqli_real_escape_string($conn, $description) . "'
                LIMIT 1
                ";
            } else {
                $check_sql = "
                SELECT ID FROM DailyData 
                WHERE WebsiteID = " . intval($website_id) . "
                AND TableType = '" . mysqli_real_escape_string($conn, $table_type) . "'
                AND `Date` = '" . mysqli_real_escape_string($conn, $date) . "'
                AND TIME = '" . mysqli_real_escape_string($conn, $time) . "'
                AND DataType = '" . mysqli_real_escape_string($conn, $data_type) . "'
                AND DESCRIPTION IS NULL
                LIMIT 1
                ";
            }
        } else {
            if ($description !== null) {
                $check_sql = "
                SELECT ID FROM DailyData 
                WHERE WebsiteID = " . intval($website_id) . "
                AND TableType = '" . mysqli_real_escape_string($conn, $table_type) . "'
                AND `Date` = '" . mysqli_real_escape_string($conn, $date) . "'
                AND TIME IS NULL
                AND DataType = '" . mysqli_real_escape_string($conn, $data_type) . "'
                AND DESCRIPTION = '" . mysqli_real_escape_string($conn, $description) . "'
                LIMIT 1
                ";
            } else {
                $check_sql = "
                SELECT ID FROM DailyData 
                WHERE WebsiteID = " . intval($website_id) . "
                AND TableType = '" . mysqli_real_escape_string($conn, $table_type) . "'
                AND `Date` = '" . mysqli_real_escape_string($conn, $date) . "'
                AND TIME IS NULL
                AND DataType = '" . mysqli_real_escape_string($conn, $data_type) . "'
                AND DESCRIPTION IS NULL
                LIMIT 1
                ";
            }
        }

        $check_result = mysqli_query($conn, $check_sql);
        if ($check_result && mysqli_num_rows($check_result) > 0) {
            $row = mysqli_fetch_assoc($check_result);
            $existing_id = $row['ID'];
        }
    }

    if ($existing_id) {
        // 构建 UPDATE 语句，只更新提供的字段
        $updateFields = [];
        
        // 只有在 payload 中提供的字段才会被添加到 UPDATE 语句
        if ($updateDescription) {
            $updateFields[] = "DESCRIPTION = " . ($description !== null ? "'" . mysqli_real_escape_string($conn, $description) . "'" : "NULL");
        }
        
        if ($updateMC) {
            $updateFields[] = "MCID = " . ($mcId !== null ? intval($mcId) : "NULL");
        }
        
        if ($updateInlineWebId) {
            $updateFields[] = "InlineWebID = " . ($inlineWebId !== null ? intval($inlineWebId) : "NULL");
        }
        
        if ($updateExpenseCategory) {
            $updateFields[] = "ExpenseCategoryID = " . ($expenseCategoryId !== null ? intval($expenseCategoryId) : "NULL");
        }
        
        if ($updateIn) {
            $updateFields[] = "`IN` = " . ($in !== null ? floatval($in) : "NULL");
        }
        
        if ($updateOut) {
            $updateFields[] = "`OUT` = " . ($out !== null ? floatval($out) : "NULL");
        }
        
        if ($updateTime) {
            $updateFields[] = "TIME = " . ($time !== null ? "'" . mysqli_real_escape_string($conn, $time) . "'" : "NULL");
        }
        
        if ($updateIdField) {
            $updateFields[] = "ID_Field = " . ($id_field !== null ? "'" . mysqli_real_escape_string($conn, $id_field) . "'" : "NULL");
        }
        
        if ($updateMatch) {
            $updateFields[] = "`MATCH` = " . ($match !== null ? "'" . mysqli_real_escape_string($conn, $match) . "'" : "NULL");
        }
        
        if ($updateFee) {
            $updateFields[] = "FEE = " . ($fee !== null ? floatval($fee) : "NULL");
        }
        
        if ($updateRemarks) {
            $updateFields[] = "REMARKS = " . ($remarks !== null ? "'" . mysqli_real_escape_string($conn, $remarks) . "'" : "NULL");
        }
        
        if ($updateRate) {
            $updateFields[] = "RATE = " . ($rate !== null ? floatval($rate) : "NULL");
        }
        
        if ($updateMyrTotal) {
            $updateFields[] = "MYR_TOTAL = " . ($myr_total !== null ? floatval($myr_total) : "NULL");
        }
        
        if ($updateDataType) {
            $updateFields[] = "DataType = '" . mysqli_real_escape_string($conn, $data_type) . "'";
        }
        
        // 总是更新 Updated_at
        $updateFields[] = "Updated_at = CURRENT_TIMESTAMP";
        
        // 如果没有要更新的字段，直接返回成功
        if (empty($updateFields)) {
            echo json_encode([
                'success' => true,
                'message' => 'No fields to update',
                'action' => 'updated',
                'affected_rows' => 0,
                'insert_id' => $existing_id
            ], JSON_UNESCAPED_UNICODE);
            mysqli_close($conn);
            exit;
        }
        
        $sql = "UPDATE DailyData SET " . implode(", ", $updateFields) . " WHERE ID = " . intval($existing_id);

        $result = mysqli_query($conn, $sql);

        if (!$result) {
            throw new Exception('Update failed: ' . mysqli_error($conn));
        }

        $affected_rows = mysqli_affected_rows($conn);
        $insert_id = $existing_id;
        $action = 'updated';
    } else {
        $sql = "
        INSERT INTO DailyData 
        (WebsiteID, TableType, `Date`, DESCRIPTION, MCID, InlineWebID, ExpenseCategoryID, `IN`, `OUT`, TIME, ID_Field, `MATCH`, FEE, REMARKS, RATE, MYR_TOTAL, DataType)
        VALUES (
            " . intval($website_id) . ",
            '" . mysqli_real_escape_string($conn, $table_type) . "',
            '" . mysqli_real_escape_string($conn, $date) . "',
            " . ($description !== null ? "'" . mysqli_real_escape_string($conn, $description) . "'" : "NULL") . ",
            " . ($mcId !== null ? intval($mcId) : "NULL") . ",
            " . ($inlineWebId !== null ? intval($inlineWebId) : "NULL") . ",
            " . ($expenseCategoryId !== null ? intval($expenseCategoryId) : "NULL") . ",
            " . ($in !== null ? floatval($in) : "NULL") . ",
            " . ($out !== null ? floatval($out) : "NULL") . ",
            " . ($time !== null ? "'" . mysqli_real_escape_string($conn, $time) . "'" : "NULL") . ",
            " . ($id_field !== null ? "'" . mysqli_real_escape_string($conn, $id_field) . "'" : "NULL") . ",
            " . ($match !== null ? "'" . mysqli_real_escape_string($conn, $match) . "'" : "NULL") . ",
            " . ($fee !== null ? floatval($fee) : "NULL") . ",
            " . ($remarks !== null ? "'" . mysqli_real_escape_string($conn, $remarks) . "'" : "NULL") . ",
            " . ($rate !== null ? floatval($rate) : "NULL") . ",
            " . ($myr_total !== null ? floatval($myr_total) : "NULL") . ",
            '" . mysqli_real_escape_string($conn, $data_type) . "'
        )
        ";

        $result = mysqli_query($conn, $sql);

        if (!$result) {
            throw new Exception('Insert failed: ' . mysqli_error($conn));
        }

        $affected_rows = mysqli_affected_rows($conn);
        $insert_id = mysqli_insert_id($conn);
        $action = 'inserted';
    }

    echo json_encode([
        'success' => true,
        'message' => $action === 'updated' ? 'Daily data updated successfully' : 'Daily data inserted successfully',
        'action' => $action,
        'affected_rows' => $affected_rows,
        'insert_id' => $insert_id
    ], JSON_UNESCAPED_UNICODE);
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'error' => true,
        'success' => false,
        'message' => $e->getMessage()
    ], JSON_UNESCAPED_UNICODE);
}

mysqli_close($conn);