// GlassLogDb.cpp - µ¥Àý·â×°£ºSQLite дÈë/²éѯ/·ÖÒ³/ͳ¼Æ/CSV µ¼³ö£¨ÒÑ¶Ô½Ó SERVO::CGlass£©
#include "stdafx.h"
#include "GlassLogDb.h"
#include "sqlite3.h"
#include <stdexcept>
#include <sstream>
#include <iomanip>
#include <ctime>
#include <fstream>
#include "GlassJson.h"


using namespace SERVO;

#ifndef GLASS_LOG_TABLE
#define GLASS_LOG_TABLE "glass_log"
#endif

// ================== ¹¤¾ßº¯Êý ==================
static void throwIf(int rc, sqlite3* db, const char* msg) {
    if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) {
        std::ostringstream oss;
        oss << msg << " (rc=" << rc << "): " << (db ? sqlite3_errmsg(db) : "null db");
        throw std::runtime_error(oss.str());
    }
}
static inline const char* safe_text(sqlite3_stmt* s, int col) {
    const unsigned char* p = sqlite3_column_text(s, col);
    return p ? reinterpret_cast<const char*>(p) : "";
}
static std::string csvEscape(const std::string& s) {
    bool needQuote = s.find_first_of(",\"\n\r") != std::string::npos;
    if (!needQuote) return s;
    std::string out; out.reserve(s.size() + 2);
    out.push_back('"');
    for (char c : s) out += (c == '"') ? "\"\"" : std::string(1, c);
    out.push_back('"');
    return out;
}
static std::string toIso8601String(const std::optional<std::chrono::system_clock::time_point>& tp) {
    if (!tp.has_value()) return "";
    using namespace std::chrono;
    auto t = system_clock::to_time_t(*tp);
    std::tm tm{};
#if defined(_WIN32)
    gmtime_s(&tm, &t);
#else
    gmtime_r(&t, &tm);
#endif
    std::ostringstream oss;
    oss << std::put_time(&tm, "%Y-%m-%dT%H:%M:%SZ");
    return oss.str();
}

// ================== µ¥Àý¾²Ì¬³ÉÔ± ==================
std::unique_ptr<GlassLogDb> GlassLogDb::s_inst;
std::mutex GlassLogDb::s_instMtx;

// ================== µ¥Àý½Ó¿ÚʵÏÖ ==================
void GlassLogDb::Init(const std::string& dbPath) {
    std::lock_guard<std::mutex> g(s_instMtx);
    if (!s_inst) {
        s_inst.reset(new GlassLogDb(dbPath));
    }
    else if (s_inst->dbPath_ != dbPath) {
        s_inst->reopenInternal(dbPath);
    }
}
GlassLogDb& GlassLogDb::Instance() {
    std::lock_guard<std::mutex> g(s_instMtx);
    if (!s_inst) throw std::runtime_error("GlassLogDb::Instance() called before Init()");
    return *s_inst;
}
bool GlassLogDb::IsInitialized() noexcept {
    std::lock_guard<std::mutex> g(s_instMtx);
    return static_cast<bool>(s_inst);
}
void GlassLogDb::Reopen(const std::string& dbPath) {
    std::lock_guard<std::mutex> g(s_instMtx);
    if (!s_inst) {
        s_inst.reset(new GlassLogDb(dbPath));
    }
    else {
        s_inst->reopenInternal(dbPath);
    }
}
std::string GlassLogDb::CurrentPath() {
    std::lock_guard<std::mutex> g(s_instMtx);
    return s_inst ? s_inst->dbPath_ : std::string();
}

// ================== ¹¹Ôì/´ò¿ª/¹Ø±Õ/ÖØ¿ª ==================
GlassLogDb::GlassLogDb(const std::string& dbPath) {
    openDb(dbPath);
}
void GlassLogDb::openDb(const std::string& dbPath) {
    int rc = sqlite3_open_v2(dbPath.c_str(), &db_,
        SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX,
        nullptr);
    if (rc != SQLITE_OK) {
        std::string err = db_ ? sqlite3_errmsg(db_) : "open failed";
        if (db_) sqlite3_close(db_);
        db_ = nullptr;
        throw std::runtime_error("Failed to open sqlite DB: " + err + " | path=" + dbPath);
    }
    dbPath_ = dbPath;
    ensureSchema();
    prepareStatements();
}
void GlassLogDb::closeDb() noexcept {
    finalizeStatements();
    if (db_) {
        sqlite3_close(db_);
        db_ = nullptr;
    }
}
void GlassLogDb::reopenInternal(const std::string& dbPath) {
    std::lock_guard<std::mutex> lk(mtx_);
    if (dbPath == dbPath_) return;
    closeDb();
    openDb(dbPath);
}

// ================== Îö¹¹ ==================
GlassLogDb::~GlassLogDb() {
    closeDb();
}

// ================== DDL & Ô¤±àÒë ==================
void GlassLogDb::ensureSchema() {
    const char* ddl =
        "CREATE TABLE IF NOT EXISTS " GLASS_LOG_TABLE " ("
        "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "  cassette_seq_no INTEGER,"
        "  job_seq_no INTEGER,"
        "  class_id TEXT,"
        "  material_type INTEGER,"
        "  state INTEGER,"
        "  t_start TEXT,"      // ISO8601 UTC£¨¿ÉΪ NULL£©
        "  t_end TEXT,"        // ISO8601 UTC£¨¿ÉΪ NULL£©
        "  buddy_id TEXT,"
        "  aoi_result INTEGER,"
        "  path TEXT,"
        "  params TEXT,"
        "  pretty TEXT"
        ");"
        "CREATE INDEX IF NOT EXISTS idx_glass_key "
        "  ON " GLASS_LOG_TABLE " (cassette_seq_no, job_seq_no);"
        "CREATE INDEX IF NOT EXISTS idx_class_id "
        "  ON " GLASS_LOG_TABLE " (class_id);"
        "CREATE INDEX IF NOT EXISTS idx_t_start "
        "  ON " GLASS_LOG_TABLE " (t_start);";

    char* errMsg = nullptr;
    int rc = sqlite3_exec(db_, ddl, nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK) {
        std::string err = errMsg ? errMsg : "unknown";
        sqlite3_free(errMsg);
        throw std::runtime_error("Failed to create schema: " + err);
    }
}
void GlassLogDb::prepareStatements() {
    const char* sql =
        "INSERT INTO " GLASS_LOG_TABLE " ("
        " cassette_seq_no, job_seq_no, class_id, material_type, state,"
        " t_start, t_end, buddy_id, aoi_result, path, params, pretty)"
        " VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmtInsert_, nullptr);
    throwIf(rc, db_, "prepare insert");
}
void GlassLogDb::finalizeStatements() noexcept {
    if (stmtInsert_) {
        sqlite3_finalize(stmtInsert_);
        stmtInsert_ = nullptr;
    }
}

// ================== ÊÂÎñ ==================
void GlassLogDb::beginTransaction() {
    std::lock_guard<std::mutex> lk(mtx_);
    char* err = nullptr;
    int rc = sqlite3_exec(db_, "BEGIN IMMEDIATE;", nullptr, nullptr, &err);
    if (rc != SQLITE_OK) {
        std::string e = err ? err : "unknown";
        sqlite3_free(err);
        throw std::runtime_error("BEGIN failed: " + e);
    }
}
void GlassLogDb::commit() {
    std::lock_guard<std::mutex> lk(mtx_);
    char* err = nullptr;
    int rc = sqlite3_exec(db_, "COMMIT;", nullptr, nullptr, &err);
    if (rc != SQLITE_OK) {
        std::string e = err ? err : "unknown";
        sqlite3_free(err);
        throw std::runtime_error("COMMIT failed: " + e);
    }
}
void GlassLogDb::rollback() {
    std::lock_guard<std::mutex> lk(mtx_);
    char* err = nullptr;
    sqlite3_exec(db_, "ROLLBACK;", nullptr, nullptr, &err);
    if (err) sqlite3_free(err);
}

// ================== ʱ¼ä¸ñʽ ==================
std::string GlassLogDb::toIso8601Utc(std::chrono::system_clock::time_point tp) {
    using namespace std::chrono;
    auto t = system_clock::to_time_t(tp);
    std::tm tm{};
#if defined(_WIN32)
    gmtime_s(&tm, &t);
#else
    gmtime_r(&t, &tm);
#endif
    std::ostringstream oss;
    oss << std::put_time(&tm, "%Y-%m-%dT%H:%M:%SZ");
    return oss.str();
}

// ================== ²åÈëʵÏÖ ==================
long long GlassLogDb::doInsert(
    int cassetteSeqNo,
    int jobSeqNo,
    const std::string& classId,
    int materialType,
    int state,
    std::optional<std::chrono::system_clock::time_point> tStart,
    std::optional<std::chrono::system_clock::time_point> tEnd,
    const std::string& buddyId,
    int aoiResult,
    const std::string& pathDesc,
    const std::string& paramsDesc,
    const std::string& prettyString)
{
    std::lock_guard<std::mutex> lk(mtx_);
    int idx = 1;
    throwIf(sqlite3_reset(stmtInsert_), db_, "reset insert");
    throwIf(sqlite3_clear_bindings(stmtInsert_), db_, "clear bindings");

    throwIf(sqlite3_bind_int(stmtInsert_, idx++, cassetteSeqNo), db_, "bind cassette");
    throwIf(sqlite3_bind_int(stmtInsert_, idx++, jobSeqNo), db_, "bind job");
    throwIf(sqlite3_bind_text(stmtInsert_, idx++, classId.c_str(), -1, SQLITE_TRANSIENT), db_, "bind class_id");
    throwIf(sqlite3_bind_int(stmtInsert_, idx++, materialType), db_, "bind material_type");
    throwIf(sqlite3_bind_int(stmtInsert_, idx++, state), db_, "bind state");

    if (tStart.has_value()) {
        auto s = toIso8601Utc(*tStart);
        throwIf(sqlite3_bind_text(stmtInsert_, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db_, "bind t_start");
    }
    else {
        throwIf(sqlite3_bind_null(stmtInsert_, idx++), db_, "bind t_start null");
    }
    if (tEnd.has_value()) {
        auto e = toIso8601Utc(*tEnd);
        throwIf(sqlite3_bind_text(stmtInsert_, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db_, "bind t_end");
    }
    else {
        throwIf(sqlite3_bind_null(stmtInsert_, idx++), db_, "bind t_end null");
    }

    throwIf(sqlite3_bind_text(stmtInsert_, idx++, buddyId.c_str(), -1, SQLITE_TRANSIENT), db_, "bind buddy_id");
    throwIf(sqlite3_bind_int(stmtInsert_, idx++, aoiResult), db_, "bind aoi_result");
    throwIf(sqlite3_bind_text(stmtInsert_, idx++, pathDesc.c_str(), -1, SQLITE_TRANSIENT), db_, "bind path");
    throwIf(sqlite3_bind_text(stmtInsert_, idx++, paramsDesc.c_str(), -1, SQLITE_TRANSIENT), db_, "bind params");
    throwIf(sqlite3_bind_text(stmtInsert_, idx++, prettyString.c_str(), -1, SQLITE_TRANSIENT), db_, "bind pretty");

    int rc = sqlite3_step(stmtInsert_);
    throwIf(rc, db_, "insert step");
    return sqlite3_last_insert_rowid(db_);
}

long long GlassLogDb::insertFromCGlass(CGlass& g)
{
    const int cassette = static_cast<int>(g.getCassetteSequenceNo());
    const int job = static_cast<int>(g.getJobSequenceNo());
    const std::string& classId = g.getID();
    const int material = static_cast<int>(g.getType());
    const int state = static_cast<int>(g.state());
    const std::string& buddy = g.getBuddyId();
    const int aoiResult = static_cast<int>(g.getAOIInspResult());
    const std::string pathDesc = g.getPathDescription();
    const std::string paramsDesc = g.getParamsDescription();
    const std::string pretty = GlassJson::ToPrettyString(g);

    auto tStart = g.tStart();
    auto tEnd = g.tEnd();

    return doInsert(cassette, job, classId, material, state,
        tStart, tEnd, buddy, aoiResult, pathDesc, paramsDesc, pretty);
}

long long GlassLogDb::insertExplicit(
    int cassetteSeqNo,
    int jobSeqNo,
    const std::string& classId,
    int materialType,
    int state,
    std::optional<std::chrono::system_clock::time_point> tStart,
    std::optional<std::chrono::system_clock::time_point> tEnd,
    const std::string& buddyId,
    int aoiResult,
    const std::string& pathDesc,
    const std::string& paramsDesc,
    const std::string& prettyString)
{
    return doInsert(cassetteSeqNo, jobSeqNo, classId, materialType, state,
        tStart, tEnd, buddyId, aoiResult, pathDesc, paramsDesc, prettyString);
}

// ================== ¹ýÂ˹¹Ô죨where + bind£© ==================
static std::string buildWhereSql(const GlassLogDb::Filters& f) {
    std::ostringstream where;
    bool first = true;
    auto add = [&](const std::string& cond) {
        if (first) { where << " WHERE " << cond; first = false; }
        else { where << " AND " << cond; }
    };
    if (f.classId)       add("class_id = ?");
    if (f.cassetteSeqNo) add("cassette_seq_no = ?");
    if (f.jobSeqNo)      add("job_seq_no = ?");
    if (f.keyword)       add("(class_id LIKE ? OR buddy_id LIKE ? OR path LIKE ? OR params LIKE ? OR pretty LIKE ?)");
    if (f.tStartFrom && f.tStartTo) add("(t_start >= ? AND t_start <= ?)");
    else if (f.tStartFrom)          add("t_start >= ?");
    else if (f.tStartTo)            add("t_start <= ?");
    return where.str();
}
static void bindFilters(sqlite3_stmt* stmt, sqlite3* db, int& idx, const GlassLogDb::Filters& f) {
    if (f.classId)       throwIf(sqlite3_bind_text(stmt, idx++, f.classId->c_str(), -1, SQLITE_TRANSIENT), db, "bind classId");
    if (f.cassetteSeqNo) throwIf(sqlite3_bind_int(stmt, idx++, *f.cassetteSeqNo), db, "bind cassette");
    if (f.jobSeqNo)      throwIf(sqlite3_bind_int(stmt, idx++, *f.jobSeqNo), db, "bind job");
    if (f.keyword) {
        std::string kw = "%" + *f.keyword + "%";
        for (int i = 0; i < 5; ++i)
            throwIf(sqlite3_bind_text(stmt, idx++, kw.c_str(), -1, SQLITE_TRANSIENT), db, "bind keyword");
    }
    if (f.tStartFrom && f.tStartTo) {
        std::string s = toIso8601String(f.tStartFrom);
        std::string e = toIso8601String(f.tStartTo);
        throwIf(sqlite3_bind_text(stmt, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartFrom");
        throwIf(sqlite3_bind_text(stmt, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartTo");
    }
    else if (f.tStartFrom) {
        std::string s = toIso8601String(f.tStartFrom);
        throwIf(sqlite3_bind_text(stmt, idx++, s.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartFrom");
    }
    else if (f.tStartTo) {
        std::string e = toIso8601String(f.tStartTo);
        throwIf(sqlite3_bind_text(stmt, idx++, e.c_str(), -1, SQLITE_TRANSIENT), db, "bind tStartTo");
    }
}

// ================== ²éѯ / ͳ¼Æ / ·ÖÒ³ ==================
std::vector<GlassLogDb::Row> GlassLogDb::query(
    const Filters& filters,
    int limit,
    int offset)
{
    std::lock_guard<std::mutex> lk(mtx_);

    std::ostringstream sql;
    sql << "SELECT id, cassette_seq_no, job_seq_no, class_id, material_type, state,"
        " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_start, 'localtime'), ''),"
        " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_end,   'localtime'), ''),"
        " buddy_id, aoi_result, path, params, pretty"
        " FROM " GLASS_LOG_TABLE;

    std::string where = buildWhereSql(filters);
    sql << where
        << " ORDER BY id DESC"
        << " LIMIT " << (limit < 0 ? 0 : limit)
        << " OFFSET " << (offset < 0 ? 0 : offset);

    sqlite3_stmt* stmt = nullptr;
    throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare query");

    int idx = 1;
    bindFilters(stmt, db_, idx, filters);

    std::vector<Row> rows;
    for (;;) {
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_ROW) {
            Row r;
            r.id = sqlite3_column_int64(stmt, 0);
            r.cassetteSeqNo = sqlite3_column_int(stmt, 1);
            r.jobSeqNo = sqlite3_column_int(stmt, 2);
            r.classId = safe_text(stmt, 3);
            r.materialType = sqlite3_column_int(stmt, 4);
            r.state = sqlite3_column_int(stmt, 5);
            r.tStart = safe_text(stmt, 6);
            r.tEnd = safe_text(stmt, 7);
            r.buddyId = safe_text(stmt, 8);
            r.aoiResult = sqlite3_column_int(stmt, 9);
            r.path = safe_text(stmt, 10);
            r.params = safe_text(stmt, 11);
            r.pretty = safe_text(stmt, 12);
            rows.push_back(std::move(r));
        }
        else if (rc == SQLITE_DONE) {
            break;
        }
        else {
            sqlite3_finalize(stmt);
            throwIf(rc, db_, "query step");
        }
    }
    sqlite3_finalize(stmt);
    return rows;
}

long long GlassLogDb::count(const Filters& filters) {
    std::lock_guard<std::mutex> lk(mtx_);

    std::ostringstream sql;
    sql << "SELECT COUNT(1) FROM " GLASS_LOG_TABLE;

    std::string where = buildWhereSql(filters);
    sql << where;

    sqlite3_stmt* stmt = nullptr;
    throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare count");

    int idx = 1;
    bindFilters(stmt, db_, idx, filters);

    long long total = 0;
    int rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW) {
        total = sqlite3_column_int64(stmt, 0);
    }
    else if (rc != SQLITE_DONE) {
        sqlite3_finalize(stmt);
        throwIf(rc, db_, "count step");
    }
    sqlite3_finalize(stmt);
    return total;
}

GlassLogDb::Page GlassLogDb::queryPaged(
    const Filters& filters,
    int limit,
    int offset)
{
    Page p;
    p.limit = (limit < 0 ? 0 : limit);
    p.offset = (offset < 0 ? 0 : offset);
    p.total = count(filters);
    p.items = query(filters, p.limit, p.offset);
    return p;
}

// ================== µ¼³ö CSV£¨È«²¿·ûºÏÌõ¼þ£© ==================
long long GlassLogDb::exportCsv(const std::string& csvPath, const Filters& filters) {
    std::lock_guard<std::mutex> lk(mtx_);

    std::ofstream ofs(csvPath, std::ios::binary);
    if (!ofs) {
        throw std::runtime_error("Failed to open csv for write: " + csvPath);
    }

    // ±íÍ·
    ofs << "id,cassette_seq_no,job_seq_no,class_id,material_type,state,t_start,t_end,"
        "buddy_id,aoi_result,path,params,pretty\n";

    std::ostringstream sql;
    sql << "SELECT id, cassette_seq_no, job_seq_no, class_id, material_type, state,"
        " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_start, 'localtime'), ''),"
        " IFNULL(strftime('%Y-%m-%d %H:%M:%S', t_end,   'localtime'), ''),"
        " buddy_id, aoi_result, path, params, pretty"
        " FROM " GLASS_LOG_TABLE;

    std::string where = buildWhereSql(filters);
    sql << where << " ORDER BY id DESC";

    sqlite3_stmt* stmt = nullptr;
    throwIf(sqlite3_prepare_v2(db_, sql.str().c_str(), -1, &stmt, nullptr), db_, "prepare export");

    int idx = 1;
    bindFilters(stmt, db_, idx, filters);

    long long rows = 0;
    for (;;) {
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_ROW) {
            std::string id = std::to_string(sqlite3_column_int64(stmt, 0));
            std::string cassette = std::to_string(sqlite3_column_int(stmt, 1));
            std::string job = std::to_string(sqlite3_column_int(stmt, 2));
            std::string class_id = safe_text(stmt, 3);
            std::string material = std::to_string(sqlite3_column_int(stmt, 4));
            std::string state = std::to_string(sqlite3_column_int(stmt, 5));
            std::string t_start = safe_text(stmt, 6);
            std::string t_end = safe_text(stmt, 7);
            std::string buddy = safe_text(stmt, 8);
            std::string aoi = std::to_string(sqlite3_column_int(stmt, 9));
            std::string path = safe_text(stmt, 10);
            std::string params = safe_text(stmt, 11);
            std::string pretty = safe_text(stmt, 12);

            ofs << id << ','
                << cassette << ','
                << job << ','
                << csvEscape(class_id) << ','
                << material << ','
                << state << ','
                << t_start << ','
                << t_end << ','
                << csvEscape(buddy) << ','
                << aoi << ','
                << csvEscape(path) << ','
                << csvEscape(params) << ','
                << csvEscape(pretty) << '\n';
            ++rows;
        }
        else if (rc == SQLITE_DONE) {
            break;
        }
        else {
            sqlite3_finalize(stmt);
            throwIf(rc, db_, "export step");
        }
    }
    sqlite3_finalize(stmt);
    ofs.flush();
    return rows;
}