#pragma once
// GlassLogDb.h - µ¥Àý·â×°£ºSQLite дÈë/²éѯ/·ÖÒ³/ͳ¼Æ/CSV µ¼³ö£¨ÒÑ¶Ô½Ó SERVO::CGlass£©

#include <string>
#include <vector>
#include <mutex>
#include <optional>
#include <chrono>
#include <cstdint>
#include <memory>   // std::unique_ptr

#include "CGlass.h"   // ÐèÒª SERVO::CGlass

// ¿ÉÔÚ±àÒëÃüÁî»ò±¾Í·ÎļþÇ°×Ô¶¨Òå±íÃû£º#define GLASS_LOG_TABLE "your_table_name"
#ifndef GLASS_LOG_TABLE
#define GLASS_LOG_TABLE "glass_log"
#endif

// Ç°ÖÃÉùÃ÷£¬±ÜÃâÍ·ÎļþÒÀÀµ sqlite3.h
struct sqlite3;
struct sqlite3_stmt;

class GlassLogDb {
public:
    // ====== µ¥Àý½Ó¿Ú ======
    // ³õʼ»¯£¨»òÇл»£©Êý¾Ý¿âÎļþ·¾¶£»µÚÒ»´Îµ÷Óûᴴ½¨ÊµÀý
    static void Init(const std::string& dbPath);
    // »ñÈ¡È«¾ÖʵÀý£»Èôδ³õʼ»¯»áÅ×Òì³£
    static GlassLogDb& Instance();
    // ÊÇ·ñÒѳõʼ»¯
    static bool IsInitialized() noexcept;
    // ÔËÐÐÖÐÇл»µ½ÁíÒ»·ÝÊý¾Ý¿âÎļþ£¨µÈͬ Init£¬²»´æÔÚÔò´´½¨£©
    static void Reopen(const std::string& dbPath);
    // µ±Ç°Êý¾Ý¿âÎļþ·¾¶£¨Î´³õʼ»¯·µ»Ø¿Õ´®£©
    static std::string CurrentPath();

    // ====== ÊÂÎñ¿ØÖÆ ======
    void beginTransaction();
    void commit();
    void rollback();

    // ====== дÈë ======
    // Ö±½Ó´Ó SERVO::CGlass дÈ루עÒ⣺²ÎÊýΪ ·Ç const ÒýÓã¬ÒòΪÄãµÄÈô¸É getter ·Ç const£©
    long long insertFromCGlass(SERVO::CGlass& g);

    // ÏÔʽ²ÎÊý²åÈ루tStart/tEnd ¿É¿Õ£»¾ùдÈë UTC ISO8601 Îı¾»ò NULL£©
    long long 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);

    // ====== ²éѯ·µ»Ø½á¹¹ ======
    struct Row {
        long long id = 0;
        int cassetteSeqNo = 0;
        int jobSeqNo = 0;
        std::string classId;
        int materialType = 0;
        int state = 0;
        std::string tStart;   // ISO8601£¨¿âÖÐΪ NULL ÔòΪ¿Õ´®£©
        std::string tEnd;     // ISO8601£¨¿âÖÐΪ NULL ÔòΪ¿Õ´®£©
        std::string buddyId;
        int aoiResult = 0;
        std::string path;
        std::string params;
        std::string pretty;
    };

    // ====== ¹ýÂËÌõ¼þ ======
    struct Filters {
        std::optional<std::string> classId;       // ¾«È·Æ¥Åä class_id
        std::optional<int> cassetteSeqNo;
        std::optional<int> jobSeqNo;

        // ¹Ø¼ü×ÖÄ£ºý£º¶Ô class_id / buddy_id / path / params / pretty ×ö OR LIKE
        std::optional<std::string> keyword;

        // ʱ¼ä·¶Î§£º¶Ô t_start ¹ýÂË£¬º¬±ß½ç£¨ISO8601 Îı¾±È½Ï£©
        std::optional<std::chrono::system_clock::time_point> tStartFrom;
        std::optional<std::chrono::system_clock::time_point> tStartTo;
    };

    // ====== ²éѯ / ͳ¼Æ / ·ÖÒ³ ======
    // ·ÖÒ³²éѯ£¨limit/offset£©£¬°´ id DESC
    std::vector<Row> query(
        const Filters& filters = {},
        int limit = 50,
        int offset = 0);

    // °´ ID ²éѯµ¥Ìõ¼Ç¼£¨°üº¬ pretty ×ֶΣ©
    std::optional<Row> queryById(long long id);

    // ͳ¼ÆÓë filters ÏàͬÌõ¼þµÄ×ÜÊý
    long long count(const Filters& filters = {});

    struct Page {
        std::vector<Row> items;  // µ±Ç°Ò³Êý¾Ý
        long long total = 0;     // ·ûºÏÌõ¼þµÄ×ܼǼÊý
        int limit = 0;           // ±¾´Î²éѯµÄÒ³ÈÝÁ¿
        int offset = 0;          // ±¾´Î²éѯµÄÆðʼƫÒÆ
    };
    // Ò»´ÎÈ¡»ØÁбí + ×ÜÊý
    Page queryPaged(
        const Filters& filters = {},
        int limit = 50,
        int offset = 0);

    // ====== µ¼³ö ======
    // µ¼³öÂú×ã filters µÄ¡°È«²¿¼Ç¼¡±£¨²»ÊÜ·ÖÒ³ÏÞÖÆ£©Îª CSV£¨UTF-8£©
    // ·µ»Øд³öµÄÊý¾ÝÐÐÊý£¨²»º¬±íÍ·£©
    long long exportCsv(const std::string& csvPath, const Filters& filters = {});

    // ====== Îö¹¹ / ¿½±´¿ØÖÆ ======
    ~GlassLogDb();
    GlassLogDb(const GlassLogDb&) = delete;
    GlassLogDb& operator=(const GlassLogDb&) = delete;

private:
    // ½öÔÊÐíͨ¹ýµ¥Àý½Ó¿Ú¹¹Ôì
    explicit GlassLogDb(const std::string& dbPath);

    // ÄÚ²¿´ò¿ª/¹Ø±Õ/ÖØ¿ª
    void openDb(const std::string& dbPath);
    void closeDb() noexcept;
    void reopenInternal(const std::string& dbPath);

    // ½¨±í / Ô¤±àÒë / ÊÍ·Å
    void ensureSchema();
    void prepareStatements();
    void finalizeStatements() noexcept;

    // ¹¤¾ß£ºtime_point -> "YYYY-MM-DDTHH:MM:SSZ"£¨UTC£©
    static std::string toIso8601Utc(std::chrono::system_clock::time_point tp);

    // ʵ¼Ê²åÈëÖ´ÐУ¨Ö§³Ö¿É¿Õʱ¼ä£©
    long long 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);

private:
    // SQLite ¾ä±ú
    sqlite3* db_ = nullptr;
    sqlite3_stmt* stmtInsert_ = nullptr;

    // ʵÀýÄÚ²¢·¢±£»¤£¨Í¬Ò»Á¬½ÓÉϵÄд/¶Á´®Ðл¯£©
    std::mutex mtx_;

    // µ±Ç°Êý¾Ý¿âÎļþ·¾¶
    std::string dbPath_;

    // µ¥Àý¾²Ì¬¶ÔÏóÓëÆ以³â
    static std::unique_ptr<GlassLogDb> s_inst;
    static std::mutex s_instMtx;
};