/*
 * Copyright (c) 2006-2021, RT-Thread Development Team
 *
 * SPDX-License-Identifier: Apache-2.0
 *
 * Change Logs:
 * Date           Author       Notes
 * 2025-10-20     Administrator       the first version
 */
#include <rtthread.h>
#include <rtdevice.h>
#include <board.h>
#include <dfs_posix.h>
#include "DB_SQLite.h"
#include "sqlite3.h"

#define DB_NAME "/sddisk/SC828.db"
int db_HelperInit;
rt_mq_t db_mq = RT_NULL;  //

#define DBG_TAG "dat_sql"
#define DBG_LVL DBG_LOG
#include <rtdbg.h>

// 消息队列对象与缓冲区（静态分配）
//static struct rt_mq db_mq;
//static char mq_pool[DB_QUEUE_SIZE * sizeof(struct db_command)];

// 全局数据库连接与状态
static sqlite3 *g_db = RT_NULL;
static rt_bool_t db_enabled = RT_FALSE;           // 是否允许数据库操作
static rt_mutex_t state_mutex = RT_NULL;          // 状态访问锁

// 函数前向声明
static void return_timeout(struct db_command *cmd);
static void return_disabled(struct db_command *cmd);
static void handle_db_command(struct db_command *cmd);
static int callback(void *result_str, int argc, char **argv, char **azColName);
extern int sqlite3_init_with_sdram_heap(void);
static const char *sql_upgrade_workorder_steps =
    "CREATE TABLE WorkorderSteps (WorkOrder VARCHAR,DYELOT VARCHAR,ProgramID VARCHAR,Program VARCHAR,ReDye INT DEFAULT (0),"
        "Mode VARCHAR,Step INT,StepID VARCHAR,StepName VARCHAR,ParameterName VARCHAR,P1 DOUBLE,P2 DOUBLE,P3 DOUBLE,"
        "P4 DOUBLE,P5 DOUBLE,P6 INT,P7 INT,P8 INT,P9 INT,P10 INT,Remark VARCHAR,StepTime INT,StepID_S1 VARCHAR,"
        "StepID_S2 VARCHAR,StepID_S3 VARCHAR,StepName_S1 VARCHAR,StepName_S2 VARCHAR,StepName_S3 VARCHAR,P1_S1 DOUBLE,"
        "P1_S2 DOUBLE,P1_S3 DOUBLE,P2_S1 DOUBLE,P2_S2 DOUBLE,P2_S3 DOUBLE,P3_S1 DOUBLE,P3_S2 DOUBLE,P3_S3 DOUBLE,"
        "P4_S1 DOUBLE,P4_S2 DOUBLE,P4_S3 DOUBLE,P5_S1 DOUBLE,P5_S2 DOUBLE,P5_S3 DOUBLE); ";

static const char *sql_upgrade_workorder_set =
    "CREATE TABLE WorkOrderSet (WorkOrder VARCHAR,ReDye INT DEFAULT (0),PumpSpeed INT,Blower INT,Swing INT,ClothWheel INT,Nozzle INT); ";

static const char *sql_upgrade_workorder =
    "CREATE TABLE WorkOrder (WorkOrder VARCHAR,Dyelot VARCHAR,ReDye INT DEFAULT (0),ProgramName VARCHAR,StartTime DATETIME,EndTime DATETIME,"
        "Time TEXT,lock INT,State INT,ProgramID VARCHAR,Machines VARCHAR,color VARCHAR,ColorNumber VARCHAR,Client VARCHAR,ClothWeight VARCHAR,"
        "ClothSpecies VARCHAR,BathRatio VARCHAR,Total VARCHAR,USER VARCHAR,ColorName VARCHAR,Remark TEXT); ";

static const char *sql_upgrade_dyelot_table =
    "CREATE TABLE Dyelot (WorkOrder VARCHAR,Dyelot VARCHAR,ReDye INT,Machine VARCHAR,Step INT,Tank INT,State INT,ProductCode VARCHAR,"
        "ProductName VARCHAR,ProductType INT,Grams FLOAT,Amount FLOAT,CALL_TIME VARCHAR,DispenseEndTime VARCHAR,Type INT); ";

static const char *sql_upgrade_iolog_table =
    "CREATE TABLE IOLog (ID varchar,IOName varchar,type varchar,Value DOUBLE DEFAULT (0),DIO BOOLEAN,AIO INTEGER DEFAULT (0),PLC varchar); ";

static const char *sql_upgrade_chart_table =
    "CREATE TABLE Chart (WorkOrder varchar,DYELOT varchar,ReDye INTEGER,Name varchar,Time varchar,MTT DOUBLE DEFAULT (0),MTL DOUBLE DEFAULT (0),"
        "STTA DOUBLE DEFAULT (0),STLA DOUBLE DEFAULT (0),STTB DOUBLE DEFAULT (0),STLB DOUBLE DEFAULT (0),STTC DOUBLE DEFAULT (0),"
        "STLC DOUBLE DEFAULT (0),MTH  DOUBLE DEFAULT (0),MST  DOUBLE DEFAULT (0),MSL  DOUBLE DEFAULT (0),MUT  DOUBLE DEFAULT (0)); ";

// ------------------------
// 内部工具函数
// ------------------------

/**
 * @brief 返回超时错误（排队时间过长）
 */
static void return_timeout(struct db_command *cmd) {
    cmd->result_code = -RT_ETIMEOUT;
    rt_strncpy(cmd->result, "Cmd timeout: waited too long in queue", MAX_RESULT_LEN - 1);
    if (cmd->reply_sem) {
        rt_sem_release(cmd->reply_sem);
    }
}

/**
 * @brief 返回“数据库已禁用”错误
 */
static void return_disabled(struct db_command *cmd) {
    cmd->result_code = -RT_ENODEV;
    rt_strncpy(cmd->result, "Error: Database is disabled", MAX_RESULT_LEN - 1);
    if (cmd->reply_sem) {
        rt_sem_release(cmd->reply_sem);
    }
}

/**
 * @brief SELECT 查询回调函数
 */
static int callback(void *result_str, int argc, char **argv, char **azColName) {
    char *str = (char *)result_str;
    int len = rt_strlen(str);
    for (int i = 0; i < argc; i++) {
        rt_snprintf(str + len, MAX_RESULT_LEN - len, "%s=%s|",
                    azColName[i], argv[i] ? argv[i] : "NULL");
        len = rt_strlen(str);
    }
    return 0;
}

/**
 * @brief 执行具体 SQL 操作
 */
static void handle_db_command(struct db_command *cmd) {
    char *err_msg = 0;
    int rc;

    switch (cmd->type)
    {
        case DB_CMD_INSERT:
        case DB_CMD_DELETE:
        case DB_CMD_UPDATE:
        case DB_CMD_EXEC:
            rc = sqlite3_exec(g_db, cmd->sql, 0, 0, &err_msg);
            if (rc != SQLITE_OK) {
                cmd->result_code = rc;
                rt_strncpy(cmd->result, err_msg, MAX_RESULT_LEN - 1);
                sqlite3_free(err_msg);
            } else {
                cmd->result_code = 0;
                rt_strncpy(cmd->result, "OK", MAX_RESULT_LEN - 1);
            }
            break;

        case DB_CMD_SELECT:
            cmd->result[0] = '\0';
            rc = sqlite3_exec(g_db, cmd->sql, callback, cmd->result, &err_msg);
            if (rc != SQLITE_OK) {
                cmd->result_code = rc;
                rt_strncpy(cmd->result, err_msg, MAX_RESULT_LEN - 1);
                sqlite3_free(err_msg);
            } else {
                cmd->result_code = 0;
                if (cmd->result[0] == '\0')
                    rt_strncpy(cmd->result, "No data", MAX_RESULT_LEN - 1);
            }
            break;

        case DB_CMD_EXIT:
            rt_strncpy(cmd->result, "Exit", MAX_RESULT_LEN - 1);
            cmd->result_code = 0;
            break;

        default:
            cmd->result_code = -1;
            rt_strncpy(cmd->result, "Invalid command", MAX_RESULT_LEN - 1);
            break;
    }
}

// ------------------------
// 外部接口实现
// ------------------------

/**
 * @brief 外部调用：立即关闭数据库并禁用所有操作
 */
void close_db_immediately(void) {
    if (!state_mutex) return;

    rt_mutex_take(state_mutex, RT_WAITING_FOREVER);
    {
        if (g_db) {
            sqlite3_close(g_db);
            g_db = RT_NULL;
        }
        db_enabled = RT_FALSE;
    }
    rt_mutex_release(state_mutex);
    rt_kprintf("Database disabled.\n");
}

void db_sqlite_init_full(void)
{
    LOG_D("[DataBase] Init SQLite");
    sqlite3_initialize();

    if (access(DB_NAME, F_OK) != 0)
    {
       // 创建新数据库
       if (sqlite3_open(DB_NAME, &g_db) != SQLITE_OK)
       {
           if (g_db)
           {
               sqlite3_close(g_db);
               //限制缓存大小 (100KB)
               sqlite3_exec(g_db, "PRAGMA cache_size = 50;", 0, 0, 0);
               g_db = RT_NULL;
           }
           return; //
       } else{
           if(sqlite3_exec(g_db,sql_upgrade_workorder_steps, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]WorkorderSteps Created successfully ");
           }else{
               LOG_E("[DataBASE]WorkorderSteps Creation failed ");
           }
           if(sqlite3_exec(g_db,sql_upgrade_workorder_set, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]WorkOrderSet Created successfully ");
           }else{
               LOG_E("[DataBASE]WorkOrderSet Creation failed ");
           }
           if(sqlite3_exec(g_db,sql_upgrade_workorder, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]WorkOrder Created successfully ");
           }else{
               LOG_E("[DataBASE]WorkOrder Creation failed ");
           }
           if(sqlite3_exec(g_db,sql_upgrade_dyelot_table, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]Dyelot Created successfully ");
           }else{
               LOG_E("[DataBASE]Dyelot Creation failed \n");
           }
           if(sqlite3_exec(g_db,sql_upgrade_iolog_table, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]IOLog Created successfully ");
           }else{
               LOG_E("[DataBASE]IOLog Creation failed ");
           }
           if(sqlite3_exec(g_db,sql_upgrade_chart_table, 0, 0, 0)==0)
           {
               LOG_D("[DataBASE]Chart Created successfully ");
           }else{
               LOG_E("[DataBASE]Chart Creation failed ");
           }

           LOG_D("[DataBase] New database created. Creating tables Name (%s)",DB_NAME);
           sqlite3_close(g_db);
           g_db=RT_NULL;
       }
    }else {
        if (sqlite3_open(DB_NAME, &g_db) == SQLITE_OK)
        {
            LOG_D("[DataBase] NAME (%s)",DB_NAME);
            sqlite3_close(g_db);
            g_db=RT_NULL;
        }
    }
}

/**
 * @brief 数据库处理线程入口函数
 */
static void db_sqlite(void *parameter) {
    struct db_command cmd;
    rt_tick_t max_wait_ticks = rt_tick_from_millisecond(DB_MAX_WAITING_MS);

    // 检查并初始化数据库文件
    db_sqlite_init_full();

    //使用 rt_mq_create 动态创建消息队列
    db_mq = rt_mq_create("db_mq", sizeof(struct db_command), DB_QUEUE_SIZE, RT_IPC_FLAG_FIFO);
    if (db_mq == RT_NULL) {
        LOG_E("Failed to create message queue 'db_mq'!");
        return;
    }

    // 创建状态锁
    state_mutex = rt_mutex_create("db_lock", RT_IPC_FLAG_FIFO);
    if (!state_mutex) {
        LOG_E("Failed to create db_lock mutex!");
        rt_mq_delete(db_mq);  // 创建失败要清理
        db_mq = RT_NULL;
        return;
    }

    // 默认启用数据库
    db_enabled = RT_TRUE;
    static rt_bool_t  first_wait_done = RT_FALSE;
    //连续链接次数
    rt_int16_t op_link=0;
    while (1)
    {
        // 接收命令
        if (rt_mq_recv(db_mq, &cmd, sizeof(struct db_command), 500) <= RT_EOK)
        {
            if (first_wait_done)
            {
                first_wait_done = RT_FALSE;
                sqlite3_exec(g_db, "PRAGMA shrink_memory;", 0, 0, 0);
                sqlite3_close(g_db);
                g_db=RT_NULL;
                op_link=0;
            }
            continue;
        }else{
            if(op_link>255)
            {//连续操作数据库255次后强制关闭防止内存占用
                sqlite3_close(g_db);
                g_db=RT_NULL;
                op_link=0;
            }
            op_link++;

            first_wait_done = RT_TRUE;
        }

        //检查排队超时
        rt_tick_t now = rt_tick_get();
        if ((now - cmd.send_tick) > max_wait_ticks) {
            int wait_ms = (now - cmd.send_tick) * 1000 / RT_TICK_PER_SECOND;
            rt_kprintf("DROP cmd[type=%d]: waited %dms > limit(%dms)\n", cmd.type, wait_ms, DB_MAX_WAITING_MS);
            return_timeout(&cmd);
            continue;
        }

        //获取当前启用状态
        rt_bool_t current_enabled;
        rt_mutex_take(state_mutex, RT_WAITING_FOREVER);
        current_enabled = db_enabled;
        rt_mutex_release(state_mutex);

        //若已禁用 → 返回错误
        if (!current_enabled) {
            rt_kprintf("Rejected cmd[type=%d]: database is disabled\n", cmd.type);
            return_disabled(&cmd);
            if (cmd.type == DB_CMD_EXIT) break;
            continue;
        }

        //打开数据库（如果未打开）
        rt_mutex_take(state_mutex, RT_WAITING_FOREVER);
        if (!g_db && current_enabled)
        {
            int rc = sqlite3_open(DB_NAME, &g_db);
            if (rc != SQLITE_OK)
            {
                rt_kprintf("Failed to open DB at %s: %s\n", DB_NAME, sqlite3_errmsg(g_db));
                sqlite3_close(g_db);
                g_db = RT_NULL;
                rt_mutex_release(state_mutex);
                return_disabled(&cmd);
                continue;
            } else {
                //限制缓存大小 (KB)
                sqlite3_exec(g_db, "PRAGMA cache_size = 8;", 0, 0, 0);
                //设置临时存储为文件模式 (节省内存)
                //sqlite3_exec(g_db, "PRAGMA temp_store = FILE;", 0, 0, 0);
                //rt_kprintf("Opened database: %s\n", DB_NAME);
            }
        }

        rt_mutex_release(state_mutex);
        //执行命令
        handle_db_command(&cmd);

        //返回结果
        if (cmd.reply_sem != RT_NULL) {
            rt_sem_release(cmd.reply_sem);
        }

        //退出命令
        if (cmd.type == DB_CMD_EXIT) break;
    }

    //清理资源
    if (g_db) {
        sqlite3_close(g_db);
        g_db = RT_NULL;
    }
    db_enabled = RT_FALSE;

    if (state_mutex) {
        rt_mutex_delete(state_mutex);
        state_mutex = RT_NULL;
    }

    //删除动态消息队列
    if (db_mq) {
        rt_mq_delete(db_mq);
        db_mq = RT_NULL;
    }

    LOG_D("Database handler exited.\n");
}

/**
 * @brief 向数据库发送命令并等待响应
 *
 * @param type      命令类型（INSERT/SELECT等）
 * @param sql       SQL 语句字符串
 * @param timeout_ms 超时时间（毫秒），0 表示不等待
 * @return RT_EOK 成功，RT_ETIMEOUT 超时，RT_ERROR 失败
 */
rt_err_t db_send_command(enum db_cmd_type type, const char* sql, rt_int32_t timeout_ms)
{
    if (!sql || !db_mq) {
          return -RT_EINVAL;
      }

      struct db_command cmd;
      rt_sem_t sem = rt_sem_create("db_rep", 0, RT_IPC_FLAG_FIFO);
      if (sem == RT_NULL) {
          return -RT_ENOMEM;
      }

      // 初始化命令结构体
      rt_memset(&cmd, 0, sizeof(cmd));
      cmd.type = type;
      rt_strncpy(cmd.sql, sql, MAX_SQL_LEN - 1);
      cmd.reply_sem = sem;
      cmd.send_tick = rt_tick_get();

      // 发送命令到数据库线程队列
      if (rt_mq_send(db_mq, &cmd, sizeof(cmd)) != RT_EOK) {
          rt_sem_delete(sem);
          return -RT_ERROR;
      }

      // 计算等待 tick（兼容 RT_WAITING_FOREVER）
      rt_int32_t wait_ticks;
      if (timeout_ms == RT_WAITING_FOREVER) {
          wait_ticks = RT_WAITING_FOREVER;
      } else if (timeout_ms <= 0) {
          wait_ticks = 0; // 立即超时
      } else {
          wait_ticks = rt_tick_from_millisecond(timeout_ms);
      }

      // 等待数据库线程处理完成
      rt_err_t result = rt_sem_take(sem, wait_ticks);
      rt_sem_delete(sem);
      return result;
}

extern struct rt_memheap sram_DTCMRAM;
static struct rt_thread dat_sqlite_thread;
static void *dat_sqlite_stack __attribute__((aligned(4)))= RT_NULL ;
/* 线程 */
void thread_DB_SQLite(void)
{
    rt_err_t db_err;
    dat_sqlite_stack = rt_memheap_alloc(&sram_DTCMRAM, 1024*16);
    db_err = rt_thread_init(&dat_sqlite_thread, "db_sql", db_sqlite, RT_NULL,dat_sqlite_stack, 1024*16, 15, 10);
    if(db_err != RT_EOK)
    {
        LOG_D("Failed to create SQLite thread!");
    }
    rt_thread_startup(&dat_sqlite_thread);

    /*rt_thread_t tid;
    tid = rt_thread_create("db_sql", db_sqlite, RT_NULL, 1024 *16, 18, 10);

    if (tid != RT_NULL)
    {
        rt_thread_startup(tid);
    }else{
        LOG_D("Failed to create SQLite thread!\n");
    }*/
}