#include "stdafx.h"
#include "ProductionLogManager.h"
#include <sstream>
#include <fstream>
#include <iostream>
#include <stdexcept>
#include <iomanip>

const std::string PRODUCTION_DB_FILE = R"(ProductionLog.db)";
std::mutex ProductionLogManager::m_mutex;

ProductionLogManager& ProductionLogManager::getInstance() {
    static ProductionLogManager instance;
    return instance;
}

ProductionLogManager::ProductionLogManager() {
    m_pDB = new BL::SQLiteDatabase();
}

ProductionLogManager::~ProductionLogManager() {
    if (m_pDB) {
        delete m_pDB;
        m_pDB = nullptr;
    }
}

bool ProductionLogManager::initProductionTable() {
    char path[MAX_PATH];
    GetModuleFileName(NULL, path, MAX_PATH);
    std::string exePath(path);
    std::string dbFileDir = exePath.substr(0, exePath.find_last_of("\\/")) + "\\DB";
    if (!CreateDirectory(dbFileDir.c_str(), NULL) && GetLastError() != ERROR_ALREADY_EXISTS) {
        throw std::runtime_error("Failed to create DB directory.");
    }

    std::string dbFilePath = dbFileDir + "\\" + PRODUCTION_DB_FILE;
    if (!m_pDB->connect(dbFilePath, true)) {
        throw std::runtime_error("Failed to connect to production database.");
    }

    const std::string createTableQuery = R"(
        CREATE TABLE IF NOT EXISTS production_log (
            step_id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id TEXT NOT NULL,
            batch_no TEXT NOT NULL,
            device_id INTEGER NOT NULL,
            prev_device_id INTEGER,
            next_device_id INTEGER,
            operator_name TEXT,
            start_time DATETIME,
            end_time DATETIME,
            yield INTEGER,
            good_count INTEGER,
            bad_count INTEGER,
            status TEXT,
            note TEXT
        )
    )";
    return m_pDB->executeQuery(createTableQuery);
}

void ProductionLogManager::termProductionTable() {
    if (m_pDB) {
        m_pDB->disconnect();
    }
}

bool ProductionLogManager::destroyProductionTable() {
    if (!m_pDB) return false;
    const std::string query = "DROP TABLE IF EXISTS production_log";
    return m_pDB->executeQuery(query);
}

void ProductionLogManager::insertMockData() {
    // TODO: ʵÏÖÄ£ÄâÊý¾Ý²åÈëÂß¼­£¨Ê¹Óà std::ostringstream£©
    ProductionStep step;
    step.strProductId = "P888";
    step.strBatchNo = "B999";
    step.nDeviceId = 3;
    step.nPrevDeviceId = 2;
    step.nNextDeviceId = 4;
    step.strOperator = "MockUser";
    step.strStartTime = "2025-04-02 10:00:00";
    step.strEndTime = "2025-04-02 10:20:00";
    step.nYield = 100;
    step.nGoodCount = 98;
    step.nBadCount = 2;
    step.strStatus = "²âÊÔ";
    step.strNote = "ÕâÊÇÄ£ÄâÂÄÀú";
    int id = 0;
    addProductionStep(id, step);
}

bool ProductionLogManager::addProductionStep(int stepId, const ProductionStep& stepData) {
    std::ostringstream query;
    query << "INSERT INTO production_log (product_id, batch_no, device_id, prev_device_id, next_device_id, operator_name, start_time, end_time, yield, good_count, bad_count, status, note) "
        << "VALUES ('" << stepData.strProductId << "', '" << stepData.strBatchNo << "', " << stepData.nDeviceId << ", "
        << stepData.nPrevDeviceId << ", " << stepData.nNextDeviceId << ", '" << stepData.strOperator << "', '"
        << stepData.strStartTime << "', '" << stepData.strEndTime << "', " << stepData.nYield << ", "
        << stepData.nGoodCount << ", " << stepData.nBadCount << ", '" << stepData.strStatus << "', '"
        << stepData.strNote << "') RETURNING step_id;";

    std::lock_guard<std::mutex> lock(m_mutex);
    auto results = m_pDB->fetchResults(query.str());
    if (!results.empty() && !results[0].empty()) {
        try {
            stepId = std::stoi(results[0][0]);
            m_mapStepCache[stepId] = stepData;
            return true;
        }
        catch (...) {
            return false;
        }
    }
    return false;
}

std::vector<ProductionStep> ProductionLogManager::getAllSteps() {
    const std::string query = R"(
        SELECT step_id, product_id, batch_no, device_id, prev_device_id, next_device_id,
               operator_name, start_time, end_time, yield, good_count, bad_count, status, note
        FROM production_log
    )";

    auto results = m_pDB->fetchResults(query);
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

std::vector<ProductionStep> ProductionLogManager::getStepsByProductId(const std::string& productId) {
    std::ostringstream query;
    query << "SELECT * FROM production_log WHERE product_id = '" << productId << "'";
    auto results = m_pDB->fetchResults(query.str());
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

std::vector<ProductionStep> ProductionLogManager::getStepsByBatchNo(const std::string& batchNo) {
    std::ostringstream query;
    query << "SELECT * FROM production_log WHERE batch_no = '" << batchNo << "'";
    auto results = m_pDB->fetchResults(query.str());
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

std::vector<ProductionStep> ProductionLogManager::getStepsByDeviceId(int nDeviceId) {
    std::ostringstream query;
    query << "SELECT * FROM production_log WHERE device_id = " << nDeviceId;
    auto results = m_pDB->fetchResults(query.str());
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

std::vector<ProductionStep> ProductionLogManager::getStepsByTimeRange(const std::string& startTime, const std::string& endTime) {
    std::ostringstream query;
    query << "SELECT * FROM production_log WHERE start_time >= '" << startTime << "' AND end_time <= '" << endTime << "'";
    auto results = m_pDB->fetchResults(query.str());
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

std::vector<ProductionStep> ProductionLogManager::getFilteredSteps(
    const std::string& productId,
    const std::string& batchNo,
    const std::string& deviceId,
    const std::string& operatorName,
    const std::string& status,
    const std::string& startTime,
    const std::string& endTime,
    int pageNumber,
    int pageSize) {

    std::ostringstream query;
    query << "SELECT * FROM production_log WHERE 1=1";

    if (!productId.empty()) {
        query << " AND product_id LIKE '%" << productId << "%'";
    }
    if (!batchNo.empty()) {
        query << " AND batch_no LIKE '%" << batchNo << "%'";
    }
    if (!deviceId.empty()) {
        query << " AND device_id = " << deviceId;
    }
    if (!operatorName.empty()) {
        query << " AND operator_name LIKE '%" << operatorName << "%'";
    }
    if (!status.empty()) {
        query << " AND status LIKE '%" << status << "%'";
    }
    if (!startTime.empty()) {
        query << " AND start_time >= '" << startTime << "'";
    }
    if (!endTime.empty()) {
        query << " AND end_time <= '" << endTime << "'";
    }

    int offset = (pageNumber - 1) * pageSize;
    query << " ORDER BY start_time DESC LIMIT " << pageSize << " OFFSET " << offset;

    auto results = m_pDB->fetchResults(query.str());
    std::vector<ProductionStep> steps;
    for (const auto& row : results) {
        ProductionStep step;
        step.nStepId = std::stoi(row[0]);
        step.strProductId = row[1];
        step.strBatchNo = row[2];
        step.nDeviceId = std::stoi(row[3]);
        step.nPrevDeviceId = std::stoi(row[4]);
        step.nNextDeviceId = std::stoi(row[5]);
        step.strOperator = row[6];
        step.strStartTime = row[7];
        step.strEndTime = row[8];
        step.nYield = std::stoi(row[9]);
        step.nGoodCount = std::stoi(row[10]);
        step.nBadCount = std::stoi(row[11]);
        step.strStatus = row[12];
        step.strNote = row[13];
        steps.push_back(step);
    }
    return steps;
}

int ProductionLogManager::getTotalStepCount(
    const std::string& productId,
    const std::string& batchNo,
    const std::string& deviceId,
    const std::string& operatorName,
    const std::string& status,
    const std::string& startTime,
    const std::string& endTime) {

    std::ostringstream query;
    query << "SELECT COUNT(*) FROM production_log WHERE 1=1";

    if (!productId.empty()) {
        query << " AND product_id LIKE '%" << productId << "%'";
    }
    if (!batchNo.empty()) {
        query << " AND batch_no LIKE '%" << batchNo << "%'";
    }
    if (!deviceId.empty()) {
        query << " AND device_id = " << deviceId;
    }
    if (!operatorName.empty()) {
        query << " AND operator_name LIKE '%" << operatorName << "%'";
    }
    if (!status.empty()) {
        query << " AND status LIKE '%" << status << "%'";
    }
    if (!startTime.empty()) {
        query << " AND start_time >= '" << startTime << "'";
    }
    if (!endTime.empty()) {
        query << " AND end_time <= '" << endTime << "'";
    }

    auto results = m_pDB->fetchResults(query.str());
    if (!results.empty() && !results[0].empty()) {
        return std::stoi(results[0][0]);
    }
    return 0;
}

bool ProductionLogManager::updateStepEndTime(int nStepId, const std::string& newEndTime) {
    std::ostringstream query;
    query << "UPDATE production_log SET end_time = '" << newEndTime << "' WHERE step_id = " << nStepId;
    return m_pDB->executeQuery(query.str());
}

bool ProductionLogManager::saveProductionFile(const std::string& filename) {
    std::ofstream file(filename);
    if (!file.is_open()) return false;
    file << "StepID,ProductID,BatchNo,DeviceID,PrevDeviceID,NextDeviceID,Operator,StartTime,EndTime,Yield,Good,Bad,Status,Note\n";
    for (auto it = m_mapStepCache.begin(); it != m_mapStepCache.end(); ++it) {
        const int id = it->first;
        const ProductionStep& step = it->second;

        file << id << "," << step.strProductId << "," << step.strBatchNo << ","
            << step.nDeviceId << "," << step.nPrevDeviceId << "," << step.nNextDeviceId << ","
            << step.strOperator << "," << step.strStartTime << "," << step.strEndTime << ","
            << step.nYield << "," << step.nGoodCount << "," << step.nBadCount << ","
            << step.strStatus << "," << step.strNote << "\n";
    }
    file.close();
    return true;
}

bool ProductionLogManager::readProductionFile(const std::string& filename) {
    std::ifstream file(filename);
    if (!file.is_open()) return false;
    std::string line;
    bool first = true;
    while (std::getline(file, line)) {
        if (first) { first = false; continue; }
        std::stringstream ss(line);
        std::string cell;
        ProductionStep step;
        std::getline(ss, cell, ','); step.nStepId = std::stoi(cell);
        std::getline(ss, step.strProductId, ',');
        std::getline(ss, step.strBatchNo, ',');
        std::getline(ss, cell, ','); step.nDeviceId = std::stoi(cell);
        std::getline(ss, cell, ','); step.nPrevDeviceId = std::stoi(cell);
        std::getline(ss, cell, ','); step.nNextDeviceId = std::stoi(cell);
        std::getline(ss, step.strOperator, ',');
        std::getline(ss, step.strStartTime, ',');
        std::getline(ss, step.strEndTime, ',');
        std::getline(ss, cell, ','); step.nYield = std::stoi(cell);
        std::getline(ss, cell, ','); step.nGoodCount = std::stoi(cell);
        std::getline(ss, cell, ','); step.nBadCount = std::stoi(cell);
        std::getline(ss, step.strStatus, ',');
        std::getline(ss, step.strNote);
        m_mapStepCache[step.nStepId] = step;
    }
    return true;
}