#include "sdatabase.h"

#include <QDateTime>

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QDebug>
#include <QThread>

#include "mainwindow.h"

uint64_t SDatabase::m_nUsage = 0;

using namespace SUTIL;

SDatabase::SDatabase(const QString& connectionName, QObject *parent)
    : QObject(parent), m_connectionName(connectionName)
{
    m_nUsage++;
}

SDatabase::~SDatabase()
{
    CloseDatabase();
    QSqlDatabase::removeDatabase(m_connectionName);
}

QString SDatabase::connectionName()
{
    return m_connectionName;
}

int SDatabase::OpenDatabase(QString strFilename)
{
    // 현재 스레드에 대한 데이터베이스 커넥션을 가져오거나 추가합니다.
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
    db.setDatabaseName(strFilename);

    // SQLite가 busy 상태일 때 5초간 대기하도록 설정 (기존의 tryLock 로직 대체)
    db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=5000");

    if (!db.open()) {
        qCritical() << "Database connection error:" << db.lastError().text();
        return -1; // 실패
    }

    // 처음 DB 파일을 생성하는 경우 테이블 초기화
    if (db.tables().isEmpty()) {
        qInfo() << "First time opening the database. Initializing tables...";
        MakeTableDataInfo();
        MakeAcquisitionTempTable();

        // ExecuteNonQuery는 아래에 새로 정의된 함수입니다.
        ExecuteNonQuery("insert into ConnectionInfo (WorklistPort) values (0);");
        ExecuteNonQuery("insert into AdvancedSetting (EnableNetworkShare) values ('Disabled');");
        ExecuteNonQuery("insert into LoginUser (LoginID, LoginPassword) values ('admin', 'smartquadra')");

        MakeViewTable();
    }

    // PRAGMA 설정 (QSqlQuery 사용)
    QSqlQuery query(db);
    query.exec("PRAGMA synchronous = FULL;");
    query.exec("PRAGMA page_size = 4096;");
    query.exec("PRAGMA cache_size = 16384;");
    query.exec("PRAGMA temp_store = MEMORY;");
    query.exec("PRAGMA journal_mode = WAL;");
    query.exec("PRAGMA locking_mode = NORMAL;");

    if(m_nUsage==1)
    {
        query.exec("VACUUM;");
    }

    // 나머지 초기화 로직 (Check...Table, MakeViewTable 등)
    CheckAdavancedTable();
    CheckLoginTable();
    //MakeAcquisitionTempTable();
    //MakeViewTable();

    return 0; // 성공

}

void SDatabase::CloseDatabase()
{
    if (QSqlDatabase::contains(m_connectionName)) {
        QSqlDatabase db = QSqlDatabase::database(m_connectionName, false); // false: 존재하지 않으면 경고 출력 안함
        if (db.isOpen()) {
            db.close();
        }
    }

}



// SELECT 쿼리 실행
bool SDatabase::ExecuteQuery(const QString& strQuery, QList<QStringList*>& resultList)
{
    // 이 함수는 SQL Injection에 취약하므로, 아래의 파라미터 바인딩 버전을 사용하는 것이 좋습니다.
    return ExecuteQuery(strQuery, QVariantList(), resultList);
}

// INSERT, UPDATE, DELETE 등 결과가 필요 없는 쿼리 실행
bool SDatabase::ExecuteNonQuery(const QString& strQuery)
{
    // 이 함수는 SQL Injection에 취약하므로, 아래의 파라미터 바인딩 버전을 사용하는 것이 좋습니다.
    return ExecuteNonQuery(strQuery, QVariantList());
}

// (추천) 파라미터 바인딩을 사용하는 SELECT 쿼리 실행
bool SDatabase::ExecuteQuery(const QString& strQuery, const QVariantList& params, QList<QStringList*>& resultList)
{
    m_bExecute = true;
    SDatabase::DeleteListReponse(resultList); // 이전 결과 초기화

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    if (!db.isOpen()) {
        qWarning() << "Query executed on a closed database connection:" << m_connectionName;
        m_bExecute = false;
        return false;
    }

    QSqlQuery query(db);
    query.prepare(strQuery);

    // 파라미터 바인딩
    for (int i = 0; i < params.size(); ++i) {
        query.bindValue(i, params.at(i));
    }

    if (!query.exec()) {
        qWarning() << "Query failed:" << query.lastError().text() << "Query:" << strQuery;
        m_bExecute = false;
        return false;
    }

    int columnCount = query.record().count();
    while (query.next()) {
        QStringList* row = new QStringList();
        for (int i = 0; i < columnCount; ++i) {
            row->append(query.value(i).toString());
        }
        resultList.append(row);
    }

    m_bExecute = false;
    return true;
}

// (추천) 파라미터 바인딩을 사용하는 INSERT, UPDATE, DELETE 쿼리 실행
bool SDatabase::ExecuteNonQuery(const QString& strQuery, const QVariantList& params)
{
    m_bExecute = true;
    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    if (!db.isOpen()) {
        qWarning() << "Query executed on a closed database connection:" << m_connectionName;
        m_bExecute = false;
        return false;
    }

    QSqlQuery query(db);
    query.prepare(strQuery);

    // 파라미터 바인딩
    for (int i = 0; i < params.size(); ++i) {
        query.bindValue(i, params.at(i));
    }

    if (!query.exec()) {
        qWarning() << "Non-query failed:" << query.lastError().text() << "Query:" << strQuery;
        m_bExecute = false;
        return false;
    }

    m_bExecute = false;
    return true;
}

void SDatabase::MakeTableUser()
{
    ExecuteNonQuery("DROP TABLE IF EXISTS LoginUser; \
                  CREATE TABLE LoginUser (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `ModifyDateTime` DateTime DEFAULT (datetime('now','localtime')),\
                  `LoginID` varchar(32) DEFAULT '',\
                  `LoginPassword` varchar(32) DEFAULT '',\
                  `ConnectionInfoIndex` int(10) DEFAULT '1',\
                  `AdvancedSettingIndex` int(10) DEFAULT '1');");
}

void SDatabase::MakeAcquisitionTempTable()
{
    ExecuteNonQuery( /*"DROP TABLE IF EXISTS AcquisitionTemp; \*/
              "CREATE TABLE IF NOT EXISTS AcquisitionTemp (\
              `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
              `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
              `StudyIndex` int DEFAULT 0,\
              `SeriesIndex` int DEFAULT 0,\
              `ImageNumber` int DEFAULT 0,\
              `ImageDelete` int DEFAULT 0,\
              `ImageStorage` int DEFAULT 0,\
              `ImageSend` int DEFAULT 0,\
              `FileType` int DEFAULT 0,\
              `ImageInstanceUID` varchar(256) DEFAULT '' UNIQUE,\
              `FileLocation` varchar(512) DEFAULT '', \
              `ImageType` varchar(10) DEFAULT '0', \
              `AcquisitionDate` varchar(10) DEFAULT '', \
              `AcquisitionTime` varchar(16) DEFAULT '', \
              `ContentDate` varchar(10) DEFAULT '', \
              `ContentTime` varchar(16) DEFAULT '', \
              `AcquisitionNumber` int DEFAULT 0, \
              `InstanceNumber` int DEFAULT 0, \
              `PatientOrientation` varchar(8) DEFAULT '', \
              `ImageLaterality` varchar(2) DEFAULT '', \
              `ImageComments` varchar(512) DEFAULT '');");

}

void SDatabase::MakeTableAdvancedSetting()
{
    ExecuteNonQuery("DROP TABLE IF EXISTS AdvancedSetting; \
                  CREATE TABLE AdvancedSetting (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `ModifyDateTime` DateTime DEFAULT (datetime('now','localtime')),\
                  `AutoCaptureSendDICOM` INTEGER DEFAULT '0',\
                  `AutoCloseSendFinish` INTEGER DEFAULT '0',\
                  `AutoExitCaptureSendDICOM` INTEGER DEFAULT '0',\
                  `AutoLogin` INTEGER DEFAULT '1',\
                  `DICOMCharacterSet` varchar(16) DEFAULT 'EUC-KR',\
                  `AutoVideoCapture` INTEGER DEFAULT '0',\
                  `DICOMCompress` INTEGER DEFAULT '0',\
                  `VideoMode` INTEGER DEFAULT '0',\
                  `CropMode` INTEGER DEFAULT '0',\
                  `ScreenSaverTime` INTEGER DEFAULT '0',\
                  `UseFixWidthLevel` INTEGER DEFAULT '0',\
                  `UseLogLevel` INTEGER DEFAULT '0',\
                  `EnableNetworkShare` varchar(10) DEFAULT 'Disabled',\
                  `NetworkAutoSend` varchar(10) DEFAULT 'Disabled',\
                  `NetworkShareIP` varchar(32) DEFAULT '127.0.0.1',\
                  `NetworkShareFolder` varchar(256) DEFAULT 'Share Folder',\
                  `NetworkSMBVersion` varchar(10) DEFAULT 'SMB Version',\
                  `NetworkShareID` varchar(256) DEFAULT 'Share ID',\
                  `NetworkSharePassword` varchar(256) DEFAULT 'Share Password',\
                  `USBBackup` INTEGER DEFAULT '0');");
}

void SDatabase::MakeTableReserved()
{
    ExecuteNonQuery("DROP TABLE IF EXISTS SReserveTable; \
                  CREATE TABLE SReserveTable (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `Reserve1` varchar(10) DEFAULT '',\
                  `Reserve2` varchar(10) DEFAULT '',\
                  `Reserve3` varchar(10) DEFAULT '',\
                  `Reserve4` varchar(10) DEFAULT '',\
                  `Reserve5` varchar(10) DEFAULT '',\
                  `Reserve6` varchar(10) DEFAULT '',\
                  `Reserve7` varchar(10) DEFAULT '',\
                  `Reserve8` varchar(10) DEFAULT '',\
                  `Reserve9` varchar(10) DEFAULT '',\
                  `Reserve10` varchar(10) DEFAULT '',\
                  `Reserve11` varchar(10) DEFAULT '',\
                  `Reserve12` varchar(10) DEFAULT '',\
                  `Reserve13` varchar(10) DEFAULT '',\
                  `Reserve14` varchar(10) DEFAULT '',\
                  `Reserve15` varchar(10) DEFAULT '',\
                  `Reserve16` varchar(10) DEFAULT '',\
                  `Reserve17` varchar(10) DEFAULT '',\
                  `Reserve18` varchar(10) DEFAULT '',\
                  `Reserve19` varchar(10) DEFAULT '',\
                  `Reserve20` varchar(10) DEFAULT '');");
}

void SDatabase::MakeTableDataInfo()
{
    ExecuteNonQuery("DROP TABLE IF EXISTS ConnectionInfo; \
                  CREATE TABLE ConnectionInfo (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `ModifyDateTime` DateTime DEFAULT (datetime('now','localtime')),\
                  `WorklistServerIP` varchar(15) DEFAULT '',\
                  `WorklistPort` int(10) DEFAULT '0',\
                  `WorklistServerTitle` varchar(64) DEFAULT '',\
                  `WorklistClientTitle` varchar(64) DEFAULT '',\
                  `StorageServerIP` varchar(15) DEFAULT '',\
                  `StoragePort` int(10) DEFAULT '0',\
                  `StorageServerTitle` varchar(64) DEFAULT '',\
                  `StorageClientTitle` varchar(64) DEFAULT '',\
                  `Modality` varchar(10) DEFAULT 'ES',\
                  `HospitalName` varchar(64) DEFAULT '',\
                  `StationName` varchar(64) DEFAULT '',\
                  `EnableLog` varchar(10) DEFAULT 'Disabled',\
                  `LocalImageFolder` varchar(256) DEFAULT '/work/test/storage',\
                  `LocalLogFolder` varchar(256) DEFAULT '/work/test',\
                  `LocalHistoryFolder` varchar(256) DEFAULT '/work/test');");

    ExecuteNonQuery("DROP TABLE IF EXISTS Patient; \
                  CREATE TABLE Patient (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
                  `PatientName` varchar(32) DEFAULT '',\
                  `PatientAge` varchar(16) DEFAULT '',\
                  `PatientID` varchar(32) DEFAULT '', \
                  `PatientSex` varchar(8) DEFAULT '', \
                  `PatientBirthDate` varchar(10) DEFAULT '', \
                  `PatientBirthTime` varchar(16) DEFAULT '', \
                  `PatientWeight` varchar(8) DEFAULT '', \
                  `PatientComments` varchar(16) DEFAULT '');");


    ExecuteNonQuery("DROP TABLE IF EXISTS Study; \
                  CREATE TABLE Study (\
                  `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
                  `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
                  `StudyInstanceUID` varchar(256) DEFAULT '' UNIQUE,\
                  `PatientIndex` int DEFAULT 0,\
                  `PatientLocation` varchar(8) DEFAULT '', \
                  `StudyDate` varchar(10) DEFAULT '', \
                  `StudyTime` varchar(16) DEFAULT '', \
                  `StudyCodeValue` varchar(64) DEFAULT '', \
                  `AccessionNumber` varchar(64) DEFAULT '', \
                  `StudyDescription` varchar(256) DEFAULT '', \
                  `ReferringPhysiciansName` varchar(32) DEFAULT '');");

    ExecuteNonQuery("DROP TABLE IF EXISTS Series; \
              CREATE TABLE Series (\
              `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
              `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
              `StudyIndex` int DEFAULT 0,\
              `SeriesDate` varchar(10) DEFAULT '', \
              `SeriesTime` varchar(16) DEFAULT '', \
              `SeriesNumber` int DEFAULT 0,\
              `SeriesInstanceUID` varchar(256) DEFAULT '' UNIQUE,\
              `Modality` varchar(8) DEFAULT '', \
              `BodyPartExamined` varchar(32) DEFAULT '', \
              `SeriesDescription` varchar(256) DEFAULT '', \
              `OperatorsName` varchar(64) DEFAULT '');");


    ExecuteNonQuery("DROP TABLE IF EXISTS Image; \
              CREATE TABLE Image (\
              `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
              `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
              `StudyIndex` int DEFAULT 0,\
              `SeriesIndex` int DEFAULT 0,\
              `ImageNumber` int DEFAULT 0,\
              `ImageDelete` int DEFAULT 0,\
              `ImageStorage` int DEFAULT 0,\
              `ImageSend` int DEFAULT 0,\
              `FileType` int DEFAULT 0,\
              `ImageInstanceUID` varchar(256) DEFAULT '' UNIQUE,\
              `FileLocation` varchar(512) DEFAULT '', \
              `ImageType` varchar(10) DEFAULT '0', \
              `AcquisitionDate` varchar(10) DEFAULT '', \
              `AcquisitionTime` varchar(16) DEFAULT '', \
              `ContentDate` varchar(10) DEFAULT '', \
              `ContentTime` varchar(16) DEFAULT '', \
              `AcquisitionNumber` int DEFAULT 0, \
              `InstanceNumber` int DEFAULT 0, \
              `PatientOrientation` varchar(8) DEFAULT '', \
              `ImageLaterality` varchar(2) DEFAULT '', \
              `ImageComments` varchar(512) DEFAULT '');");

    ExecuteNonQuery("DROP TABLE IF EXISTS WorklistTableHeader; \
              CREATE TABLE WorklistTableHeader (\
              `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
              `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
              `DefaultIndex` int DEFAULT 0,\
              `DisplayIndex` int DEFAULT 0,\
              `DisplayWidth` int DEFAULT 120,\
              `ValueString` varchar(64) DEFAULT '',\
              `Visible` int DEFAULT 0);");

    ExecuteNonQuery("DROP TABLE IF EXISTS HistoryTableHeader; \
              CREATE TABLE HistoryTableHeader (\
              `ID` INTEGER Primary Key AutoIncrement UNIQUE,\
              `CreateDateTime` DateTime DEFAULT (datetime('now','localtime')),\
              `DefaultIndex` int DEFAULT 0,\
              `DisplayIndex` int DEFAULT 0,\
              `DisplayWidth` int DEFAULT 120,\
              `ValueString` int DEFAULT 0,\
              `Visible` int DEFAULT 0);");

    ExecuteNonQuery("DROP VIEW IF EXISTS Storage;"
    "CREATE VIEW Storage as select d2.SeriesNumber, d3.ID as ImageIndex, d1.ID as StudyIndex, d2.ID as SeriesIndex, d1.PatientIndex, d2.Modality, d1.StudyDate, d1.StudyTime, d3.FileLocation, d1.StudyInstanceUID, d2.SeriesInstanceUID, d3.ImageInstanceUID, d3.CreateDateTime, d3.ImageDelete, d3.ImageSend, d3.FileType, d3.AcquisitionDate, d3.AcquisitionTime, d3.AcquisitionNumber, d3.ImageStorage from Study as d1 join (Series as d2  join Image as d3 on d3.seriesIndex=d2.id) on d1.id=d2.studyindex;");

    ExecuteNonQuery("DROP VIEW IF EXISTS  StorageInfo;"
            "CREATE VIEW StorageInfo as select d1.*, d3.PatientID, d3.PatientName, d3.PatientAge, d3.PatientSex, (select count(*) from Storage as d2 where d1.StudyInstanceUID=d2.StudyInstanceUID) as TotalImageCount  from Storage as d1 join Patient as d3 on (d1.PatientIndex=d3.ID)");

    //ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
    //"CREATE VIEW HistoryStudy as select d4.ID, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, d4.SeriesNumber from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.ImageDelete=0) as ImageCount, (select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID and d3.ImageDelete=0) as ImageSend from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");


    /*
    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
    "CREATE VIEW HistoryStudy as "
            "select d4.StudyIndex, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, max(d4.SeriesNumber), d4.VideoCount "
            "from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=1  and d3.ImageDelete=0) as ImageCount, "
            "(select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=2 and d3.ImageDelete=0) as VideoCount, "
            "(select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID) as ImageSend "
            "from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");

    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudyWithDelete;"
    "CREATE VIEW HistoryStudyWithDelete as "
            "select d4.StudyIndex, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, max(d4.SeriesNumber), d4.VideoCount "
            "from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=1) as ImageCount, "
            "(select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=2) as VideoCount, "
            "(select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID) as ImageSend "
            "from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");
            */

    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
            "CREATE VIEW HistoryStudy as "
            "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSendCount, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount from (select DISTINCT(d1.ID), d3.StudyIndex, d1.PatientIndex, d4.PatientID, d4.PatientName, d4.PatientAge, d4.PatientSex, d4.PatientBirthDate, d4.PatientWeight, d4.CreateDateTime, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d2.SeriesInstanceUID, d1.StudyDescription, d2.SeriesDescription, d2.OperatorsName, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0 and Image.ImageSend=1) as ImageSendCount, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0) as ImageCount, d2.Modality, d2.SeriesDate, d2.SeriesTime, d2.SeriesNumber, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1  and Image.ImageDelete=0) as VideoCount from Study as d1 join Series as d2 on (d1.ID=d2.StudyIndex) join Image as d3 on(d2.ID=d3.SeriesIndex and d2.SeriesNumber=1) join Patient as d4 on (d4.ID=d1.PatientIndex))");


    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudyWithDelete;"
            "CREATE VIEW HistoryStudyWithDelete as "
            "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSend, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount from (select DISTINCT(d1.ID), d3.StudyIndex, d1.PatientIndex, d4.PatientID, d4.PatientName, d4.PatientAge, d4.PatientSex, d4.PatientBirthDate, d4.PatientWeight, d4.CreateDateTime, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d2.SeriesInstanceUID, d1.StudyDescription, d2.SeriesDescription, d2.OperatorsName, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0 ) as ImageCount, d3.ImageSend, d2.Modality, d2.SeriesDate, d2.SeriesTime, d2.SeriesNumber, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1) as VideoCount from Study as d1 join Series as d2 on (d1.ID=d2.StudyIndex) join Image as d3 on(d2.ID=d3.SeriesIndex and d2.SeriesNumber=1) join Patient as d4 on (d4.ID=d1.PatientIndex))");


    MakeTableAdvancedSetting();
    MakeTableUser();

    MakeViewTable();


    int i=0;
    QStringList strList;
    strList << "ID" << "Name" << "Sex" << "Weight" << "BirthDate" << "Age" << "Patient Location" << "Study Instance UID" << "Study Description" << "Study Date" << "Study Time" << "Study Create Date" << "Refer Doctor" << "Series Instance UID" << "Operator" << "Modality" << "Bodypart" << "Series Description" << "Instance UID" << "Instance Create Date" << "Instance Create Time" << "Scheduled Date" << "Scheduled Time" << "Procedure Step ID" << "Procedure ID" << "Requested Comments" << "Index" << "Image Count" << "Send Count" << "Movie Count" << "Total Count" << "Accession Number" << "Performing Physician" << "CodeValue";

    QString strQuery;
    for(i=0 ; i<SDI_TOTAL ; i++)
    {
        strQuery = QString("insert into WorklistTableHeader(`DefaultIndex`, `DisplayIndex`, `ValueString`, `Visible`) values ('%1', '%2', '%3', '%4'); ").arg(i).arg(i).arg(strList[i]).arg(1);
        ExecuteNonQuery(strQuery);

        strQuery = QString("insert into HistoryTableHeader(`DefaultIndex`, `DisplayIndex`, `ValueString`, `Visible`) values ('%1', '%2', '%3', '%4'); ").arg(i).arg(i).arg(strList[i]).arg(1);
        ExecuteNonQuery(strQuery);
    }
}

QList<QStringList> SDatabase::QueryWorklistTableHeader()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, Visible, DisplayWidth from WorklistTableHeader order by DisplayIndex asc;";

    if (ExecuteQuery(strQuery, QVariantList(), tempResult))
    {
        for (const QStringList* rowPtr : tempResult)
        {
            if (rowPtr) {
                finalList.append(*rowPtr);
            }
        }
    }
    else
    {
        qWarning() << "QueryWorklistTableHeader failed because ExecuteQuery returned false.";
    }

    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}


QList<QStringList> SDatabase::QueryWorklistTableHeaderVisible()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    // 'Visible=1'을 'Visible=?'로 변경하여 파라미터 바인딩 사용
    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, DisplayWidth from WorklistTableHeader where Visible=? order by DisplayIndex asc;";

    QVariantList params;
    params << 1; // Visible = 1

    if (ExecuteQuery(strQuery, params, tempResult))
    {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }

    SDatabase::DeleteListReponse(tempResult); // 메모리 누수 방지
    return finalList;
}

QList<QStringList> SDatabase::QueryWorklistTableHeaderInvisible()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, DisplayWidth from WorklistTableHeader where Visible=? order by DisplayIndex asc;";

    QVariantList params;
    params << 0; // Visible = 0

    if (ExecuteQuery(strQuery, params, tempResult))
    {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }

    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

QList<QStringList> SDatabase::QueryHistoryTableHeader()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, Visible, DisplayWidth from HistoryTableHeader order by DisplayIndex asc;";

    // 이 쿼리는 파라미터가 없으므로 빈 QVariantList를 전달
    if (ExecuteQuery(strQuery, QVariantList(), tempResult))
    {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }

    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

QList<QStringList> SDatabase::QueryHistoryTableHeaderVisible()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, DisplayWidth from HistoryTableHeader where Visible=? order by DisplayIndex asc;";

    QVariantList params;
    params << 1; // Visible = 1

    if (ExecuteQuery(strQuery, params, tempResult))
    {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }

    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

QList<QStringList> SDatabase::QueryHistoryTableHeaderInvisible()
{
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;

    QString strQuery = "select DefaultIndex, DisplayIndex, ValueString, DisplayWidth from HistoryTableHeader where Visible=? order by DisplayIndex asc;";

    QVariantList params;
    params << 0; // Visible = 0

    if (ExecuteQuery(strQuery, params, tempResult))
    {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }

    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

/*
void SDatabase::Clear()
{
    QList<QStringList*>::iterator it;
    for(it=m_pResult->begin() ; it!=m_pResult->end() ; ++it)
    {
        QStringList* pDataList = *it;
        if(pDataList!=NULL)
        {
            pDataList->clear();
            delete pDataList;

        }
    }

    m_pResult->clear();
}
*/

int SDatabase::InsertPatient(ACQUISITION_INFO *pAcquisitionInfo)
{
    int nPatientIndex = 0;
    QString strPatientID = pAcquisitionInfo->GetPatientInfo()->strPatientID;
    QString strPatientName = pAcquisitionInfo->GetPatientInfo()->strPatientName;

    // 1. 먼저 환자가 존재하는지 확인
    QString selectQuery = "select ID from Patient where PatientID=? and PatientName=?;";
    QVariantList selectParams;
    selectParams << strPatientID << strPatientName;

    QList<QStringList*> listResponse;
    if (!ExecuteQuery(selectQuery, selectParams, listResponse)) {
        return -1; // 쿼리 실패
    }

    if (listResponse.isEmpty()) { // 환자가 없으면 새로 추가
        SDatabase::DeleteListReponse(listResponse);

        QString insertQuery = "insert into Patient(`PatientID`, `PatientName`, `PatientAge`, `PatientSex`, `PatientBirthDate`, `PatientBirthTime`, `PatientWeight`, `PatientComments`) "
                              "values (?, ?, ?, ?, ?, ?, ?, ?);";
        QVariantList insertParams;
        insertParams << strPatientID
                     << strPatientName
                     << pAcquisitionInfo->GetPatientInfo()->strPatientAge
                     << pAcquisitionInfo->GetPatientInfo()->strPatientSex
                     << pAcquisitionInfo->GetPatientInfo()->strPatientBirthDate
                     << pAcquisitionInfo->GetPatientInfo()->strPatientBirthTime
                     << pAcquisitionInfo->GetPatientInfo()->strPatientWeight
                     << pAcquisitionInfo->GetPatientInfo()->strPatientComments;

        if (!ExecuteNonQuery(insertQuery, insertParams)) {
            return -1; // 삽입 실패
        }

        // 다시 조회해서 새로 생성된 ID를 가져옴
        ExecuteQuery(selectQuery, selectParams, listResponse);
    }

    if(!listResponse.isEmpty()) {
        nPatientIndex = listResponse.at(0)->at(0).toInt();
        pAcquisitionInfo->m_nIndexPatient = nPatientIndex;
        pAcquisitionInfo->GetStudyInfo()->nPatientIndex = nPatientIndex;
    }

    SDatabase::DeleteListReponse(listResponse);
    return nPatientIndex;
}

int SDatabase::InsertStudy(ACQUISITION_INFO *pAcquisitionInfo)
{
    int nIndexStudy = 0;
    QString strDate;
    QString strTime;

    // 1. StudyInstanceUID 준비 (기존 로직과 동일)
    QString strStudyInstanceUID = pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID;
    if (strStudyInstanceUID.isEmpty()) {
        strStudyInstanceUID = QString("1111.2222.3333.%1").arg(QDateTime::currentDateTime().toString("yyyyMMddhhmmss"));
    }

    // 2. 파라미터 바인딩을 사용하여 Study가 이미 존재하는지 안전하게 확인
    QList<QStringList*> listResponse;
    QString selectQuery = "SELECT ID, StudyDate, StudyTime FROM Study WHERE StudyInstanceUID = ?;";
    QVariantList selectParams;
    selectParams << strStudyInstanceUID;

    if (!ExecuteQuery(selectQuery, selectParams, listResponse)) {
        // 쿼리 실행 자체가 실패한 경우
        qWarning() << "Failed to execute study selection query.";
        SDatabase::DeleteListReponse(listResponse); // 혹시 모를 부분적 할당에 대비해 정리
        return 0; // 혹은 -1과 같은 에러 코드
    }

    if (!listResponse.isEmpty())
    {
        // 3. Study가 이미 존재하는 경우: DB에서 정보를 가져옴
        const QStringList* pDataList = listResponse.at(0);
        nIndexStudy = pDataList->at(0).toInt();
        strDate = pDataList->at(1);
        strTime = pDataList->at(2);
    }
    else
    {
        // 4. Study가 없는 경우: 새로 INSERT
        QDateTime dt = QDateTime::currentDateTime();
        strDate = dt.toString("yyyyMMdd");
        strTime = dt.toString("hhmmss");

        QString insertQuery = "INSERT INTO Study(`StudyInstanceUID`, `PatientIndex`, `StudyDate`, `StudyTime`, `StudyCodeValue`, "
                              "`AccessionNumber`, `PatientLocation`, `StudyDescription`, `ReferringPhysiciansName`) "
                              "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";

        QVariantList insertParams;
        insertParams << strStudyInstanceUID
                     << pAcquisitionInfo->m_nIndexPatient
                     << strDate
                     << strTime
                     << pAcquisitionInfo->GetScheduledDataInfo()->strScheduledCodeValue
                     << pAcquisitionInfo->GetStudyInfo()->strAccessionNumber
                     << pAcquisitionInfo->GetPatientInfo()->strPatientLocation
                     << pAcquisitionInfo->GetStudyInfo()->strStudyDescription
                     << pAcquisitionInfo->GetStudyInfo()->strReferringPhysiciansName;

        // ExecuteNonQuery 대신 QSqlQuery를 직접 사용하여 lastInsertId()를 가져옵니다.
        QSqlDatabase db = QSqlDatabase::database(m_connectionName);
        QSqlQuery query(db);
        query.prepare(insertQuery);
        for (int i = 0; i < insertParams.size(); ++i) {
            query.addBindValue(insertParams.at(i));
        }

        if (query.exec()) {
            // 5. 효율적인 ID 조회: lastInsertId() 사용
            // 별도의 SELECT 없이 방금 삽입된 행의 ID를 즉시 가져옵니다.
            nIndexStudy = query.lastInsertId().toInt();
        } else {
            qWarning() << "Study insertion failed:" << query.lastError().text();
            SDatabase::DeleteListReponse(listResponse);
            return 0; // 실패
        }
    }

    // 6. 공통 로직: pAcquisitionInfo 객체에 최종 정보 업데이트
    if (nIndexStudy > 0) {
        pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID = strStudyInstanceUID;
        pAcquisitionInfo->m_nIndexStudy = nIndexStudy;
        pAcquisitionInfo->GetStudyInfo()->strStudyDate = strDate.remove('/'); // 포맷팅
        pAcquisitionInfo->GetStudyInfo()->strStudyTime = strTime.remove(':'); // 포맷팅
        pAcquisitionInfo->GetSeriesInfo()->nStudyIndex = nIndexStudy;
    }

    // 7. 사용한 메모리 정리
    SDatabase::DeleteListReponse(listResponse);

    return nIndexStudy;
}

int SDatabase::getOrInsertSeries(ACQUISITION_INFO* pAcquisitionInfo, int seriesNumber)
{
    int nStudyIndex = pAcquisitionInfo->m_nIndexStudy;
    int nSeriesIndex = 0;
    QString strDate, strTime, strSeriesInstanceUID;

    // 1. 파라미터 바인딩으로 Series 존재 여부 확인
    QList<QStringList*> listResponse;
    QString selectQuery = "SELECT ID, SeriesInstanceUID, SeriesDate, SeriesTime FROM Series WHERE StudyIndex = ? AND SeriesNumber = ?;";

    QVariantList selectParams;
    selectParams << nStudyIndex << seriesNumber;

    if (!ExecuteQuery(selectQuery, selectParams, listResponse)) {
        qWarning() << "Failed to query series for StudyIndex:" << nStudyIndex;
        return 0;
    }

    if (!listResponse.isEmpty()) { // Series가 이미 존재할 경우
        const QStringList* data = listResponse.at(0);
        nSeriesIndex = data->at(0).toInt();
        strSeriesInstanceUID = data->at(1);
        strDate = data->at(2);
        strTime = data->at(3);

        // m_nAcquisitionImageMax 또는 m_nAcquisitionVideoMax 업데이트
        int maxAcqNum = GetImageMaxAcquisitionNumber(QString::number(nSeriesIndex)); // 이 함수도 리팩토링 필요
        if (seriesNumber == 1) {
            pAcquisitionInfo->m_nAcquisitionImageMax = maxAcqNum;
        } else {
            pAcquisitionInfo->m_nAcquisitionVideoMax = maxAcqNum;
        }

    } else { // Series가 없을 경우, 새로 INSERT
        QDateTime dt = QDateTime::currentDateTime();
        strDate = dt.toString("yyyyMMdd");
        strTime = dt.toString("hhmmss");
        QString strStudyInstanceUID = pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID;
        strSeriesInstanceUID = QString("%1.%2").arg(strStudyInstanceUID).arg(seriesNumber);

        QString insertQuery = "INSERT INTO Series(`StudyIndex`, `SeriesNumber`, `SeriesInstanceUID`, `Modality`, "
                              "`BodyPartExamined`, `SeriesDescription`, `OperatorsName`, `SeriesDate`, `SeriesTime`) "
                              "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";

        QVariantList insertParams;
        insertParams << nStudyIndex << seriesNumber << strSeriesInstanceUID
                     << pAcquisitionInfo->GetSeriesInfo()->strModality
                     << pAcquisitionInfo->GetSeriesInfo()->strBodyPartExamined
                     << pAcquisitionInfo->GetSeriesInfo()->strSeriesDescription
                     << pAcquisitionInfo->GetSeriesInfo()->strOperatorsName
                     << strDate << strTime;

        // lastInsertId()를 위해 QSqlQuery 직접 사용
        QSqlDatabase db = QSqlDatabase::database(m_connectionName);
        QSqlQuery query(db);
        query.prepare(insertQuery);
        for(int i=0; i<insertParams.size(); ++i) query.addBindValue(insertParams.at(i));

        if (query.exec()) {
            nSeriesIndex = query.lastInsertId().toInt();
            if (seriesNumber == 1) pAcquisitionInfo->m_nAcquisitionImageMax = 0;
            else pAcquisitionInfo->m_nAcquisitionVideoMax = 0;
        } else {
            qWarning() << "Series insertion failed:" << query.lastError().text();
            SDatabase::DeleteListReponse(listResponse);
            return 0;
        }
    }

    // 공통 로직: ACQUISITION_INFO 객체 업데이트
    if (nSeriesIndex > 0) {
        auto seriesInfo = pAcquisitionInfo->GetSeriesInfo();
        seriesInfo->strSeriesInstanceUID = strSeriesInstanceUID.toStdString().c_str();
        seriesInfo->nSeriesNumber = seriesNumber;
        seriesInfo->nStudyIndex = nStudyIndex;
        seriesInfo->strSeriesDate = strDate.remove('/').toStdString().c_str();
        seriesInfo->strSeriesTime = strTime.remove(':').toStdString().c_str();
        pAcquisitionInfo->GetImageInfo()->nSeriesIndex = nSeriesIndex;

        if (seriesNumber == 1) pAcquisitionInfo->m_nIndexSeries_Image = nSeriesIndex;
        else pAcquisitionInfo->m_nIndexSeries_Video = nSeriesIndex;
    }

    SDatabase::DeleteListReponse(listResponse);
    return nSeriesIndex;
}

int SDatabase::InsertSeriesImage(ACQUISITION_INFO *pAcquisitionInfo)
{
    return getOrInsertSeries(pAcquisitionInfo, 1);
}

int SDatabase::InsertSeriesMovie(ACQUISITION_INFO *pAcquisitionInfo)
{
    return getOrInsertSeries(pAcquisitionInfo, 2);
}

// sdatabase.cpp

int SDatabase::InsertImage(ACQUISITION_INFO *pAcquisitionInfo)
{
    if (pAcquisitionInfo->m_nIndexSeries_Image == 0) {
        return -1;
    }

    // 1. 파라미터 바인딩을 위한 준비
    QDateTime dt = QDateTime::currentDateTime();
    QString strAcquisitionDate = dt.toString("yyyyMMdd");
    QString strAcquisitionTime = dt.toString("hhmmss");

    int nAcquisitionNumber = pAcquisitionInfo->m_nAcquisitionImageMax; // 기존 로직 유지
    QString strStudyInstanceUID = pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID;
    QString strSeriesInstanceUID = QString("%1.1").arg(strStudyInstanceUID);
    QString strImageInstanceUID = QString("%1.%2").arg(strSeriesInstanceUID).arg(nAcquisitionNumber);

    QString insertQuery = "INSERT INTO AcquisitionTemp(`SeriesIndex`, `ImageNumber`, `ImageInstanceUID`, `FileLocation`, `StudyIndex`, "
                          "`AcquisitionDate`, `AcquisitionTime`, `ContentDate`, `ContentTime`, `AcquisitionNumber`, `InstanceNumber`, "
                          "`PatientOrientation`, `ImageLaterality`, `ImageComments`, `FileType`) "
                          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    QVariantList params;
    params << pAcquisitionInfo->m_nIndexSeries_Image << nAcquisitionNumber << strImageInstanceUID
           << pAcquisitionInfo->m_strImageFilename << pAcquisitionInfo->m_nIndexStudy
           << strAcquisitionDate << strAcquisitionTime << strAcquisitionDate << strAcquisitionTime
           << nAcquisitionNumber << nAcquisitionNumber
           << pAcquisitionInfo->GetImageInfo()->strPatientOrientation
           << pAcquisitionInfo->GetImageInfo()->strImageLaterality
           << pAcquisitionInfo->GetImageInfo()->strImageComments
           << 0; // FileType for Image is 0

    // 2. lastInsertId()를 위해 QSqlQuery 직접 사용
    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(insertQuery);
    for(int i=0; i<params.size(); ++i) query.addBindValue(params.at(i));

    if (query.exec()) {
        // 3. ACQUISITION_INFO 객체 업데이트
        auto imageInfo = pAcquisitionInfo->GetImageInfo();
        imageInfo->nAcquisitionNumber = nAcquisitionNumber;
        imageInfo->nImageNumber = nAcquisitionNumber;
        imageInfo->nInstanceNumber = nAcquisitionNumber;
        imageInfo->strAcquisitionDate = strAcquisitionDate.toStdString().c_str();
        imageInfo->strAcquisitionTime = strAcquisitionTime.toStdString().c_str();
        imageInfo->strContentDate = strAcquisitionDate.toStdString().c_str();
        imageInfo->strContentTime = strAcquisitionTime.toStdString().c_str();

        // 4. 불필요한 SELECT 없이 바로 ID 반환
        return query.lastInsertId().toInt();
    } else {
        qWarning() << "Image insertion into AcquisitionTemp failed:" << query.lastError().text();
        return -1;
    }

    return query.lastInsertId().toInt();
}


// sdatabase.cpp에 새로운 헬퍼 함수 구현
int SDatabase::upsertImages(const QString& tableName, ACQUISITION_INFO* pAcquisitionInfo, const vector<CAPTURE_IMAGE*>& listCaptureImage)
{
    // 테이블 이름 유효성 검사
    if (tableName != "AcquisitionTemp" && tableName != "Image") {
        qWarning() << "Invalid table name provided to upsertImages:" << tableName;
        return -1;
    }

    // --- 1. 트랜잭션 시작 ---
    if (!BeginTransaction()) {
        qWarning() << "upsertImages: Failed to begin transaction.";
        return -1;
    }

    bool bOverallSuccess = true;
    int successCount = 0;
    QSqlDatabase qdb = QSqlDatabase::database(m_connectionName);

    // --- 2. 루프 밖에서 UPDATE와 INSERT 쿼리를 미리 준비 (성능 최적화) ---
    QSqlQuery updateQuery(qdb);
    updateQuery.prepare(QString("UPDATE %1 SET ImageDelete = ?, ImageSend = ? WHERE ImageInstanceUID = ?;").arg(tableName));

    QSqlQuery insertQuery(qdb);
    insertQuery.prepare(QString("INSERT INTO %1 (SeriesIndex, ImageNumber, ImageInstanceUID, FileLocation, StudyIndex, AcquisitionDate, AcquisitionTime, "
                                "ContentDate, ContentTime, AcquisitionNumber, InstanceNumber, PatientOrientation, ImageLaterality, ImageComments, "
                                "FileType, ImageDelete, ImageSend) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);").arg(tableName));

    // --- 3. 이미지 목록을 순회하며 DB 작업 수행 ---
    for (const CAPTURE_IMAGE* pCaptureImage : listCaptureImage)
    {
        // 필요한 변수들 준비 (기존과 동일)
        QDateTime dt = QDateTime::currentDateTime();
        QString strAcquisitionDate = dt.toString("yyyyMMdd");
        QString strAcquisitionTime = dt.toString("hhmmss");
        QString strStudyInstanceUID = pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID;
        int nStudyIndex = pAcquisitionInfo->m_nIndexStudy;
        int seriesIndex = (pCaptureImage->m_nType == DISPLAY_IMAGE) ? pAcquisitionInfo->m_nIndexSeries_Image : pAcquisitionInfo->m_nIndexSeries_Video;
        int seriesNumberForUID = (pCaptureImage->m_nType == DISPLAY_IMAGE) ? 1 : 2;
        QString fileLocation = (pCaptureImage->m_nType == DISPLAY_IMAGE) ? pCaptureImage->m_strImageFilename : pCaptureImage->m_strVideoFilename;
        QString seriesInstanceUID = QString("%1.%2").arg(strStudyInstanceUID).arg(seriesNumberForUID);
        QString imageInstanceUID = QString("%1.%2").arg(seriesInstanceUID).arg(pCaptureImage->m_nAcquisitionNumber);

        // --- 4. 먼저 UPDATE 시도 ---
        updateQuery.addBindValue(pCaptureImage->m_bDelete);
        updateQuery.addBindValue(pCaptureImage->m_bSend);
        updateQuery.addBindValue(imageInstanceUID);

        if (!updateQuery.exec()) {
            qWarning() << "upsertImages UPDATE part failed:" << updateQuery.lastError().text();
            bOverallSuccess = false;
            break; // 실패 시 즉시 루프 중단
        }

        // --- 5. UPDATE된 행이 없다면, INSERT 실행 ---
        if (updateQuery.numRowsAffected() == 0)
        {
            insertQuery.addBindValue(seriesIndex);
            insertQuery.addBindValue(pCaptureImage->m_nAcquisitionNumber);
            insertQuery.addBindValue(imageInstanceUID);
            insertQuery.addBindValue(fileLocation);
            insertQuery.addBindValue(nStudyIndex);
            insertQuery.addBindValue(strAcquisitionDate);
            insertQuery.addBindValue(strAcquisitionTime);
            insertQuery.addBindValue(strAcquisitionDate); // ContentDate
            insertQuery.addBindValue(strAcquisitionTime); // ContentTime
            insertQuery.addBindValue(pCaptureImage->m_nAcquisitionNumber); // AcquisitionNumber
            insertQuery.addBindValue(pCaptureImage->m_nAcquisitionNumber); // InstanceNumber
            insertQuery.addBindValue(pAcquisitionInfo->GetImageInfo()->strPatientOrientation);
            insertQuery.addBindValue(pAcquisitionInfo->GetImageInfo()->strImageLaterality);
            insertQuery.addBindValue(pAcquisitionInfo->GetImageInfo()->strImageComments);
            insertQuery.addBindValue(pCaptureImage->m_nType == DISPLAY_IMAGE ? 0 : 1); // FileType
            insertQuery.addBindValue(pCaptureImage->m_bDelete); // ImageDelete
            insertQuery.addBindValue(pCaptureImage->m_bSend);   // ImageSend

            if (!insertQuery.exec()) {
                qWarning() << "upsertImages INSERT part failed:" << insertQuery.lastError().text();
                bOverallSuccess = false;
                break; // 실패 시 즉시 루프 중단
            }
        }

        successCount++;
    }

    // --- 6. 최종적으로 트랜잭션 완료 또는 롤백 ---
    if (bOverallSuccess) {
        Commit();
    } else {
        Rollback();
        qWarning() << "upsertImages failed and was rolled back.";
        return -1; // 실패 시 에러 코드 반환
    }

    return successCount;
}

// 이제 Public 함수들은 내부 헬퍼를 호출만 합니다.
int SDatabase::UpdateImageOnAcquistionTemp(ACQUISITION_INFO* pAcquisitionInfo, const vector<CAPTURE_IMAGE*>& listCaptureImage)
{
    if (pAcquisitionInfo->m_nIndexSeries_Image == 0 || pAcquisitionInfo->m_nIndexSeries_Video == 0) return -1;
    return upsertImages("AcquisitionTemp", pAcquisitionInfo, listCaptureImage);
}

int SDatabase::UpdateImage(ACQUISITION_INFO* pAcquisitionInfo, const vector<CAPTURE_IMAGE*>& listCaptureImage)
{
    if (pAcquisitionInfo->m_nIndexSeries_Image == 0 || pAcquisitionInfo->m_nIndexSeries_Video == 0) return -1;
    return upsertImages("Image", pAcquisitionInfo, listCaptureImage);
}

int SDatabase::InsertVideo(ACQUISITION_INFO *pAcquisitionInfo)
{
    if (pAcquisitionInfo->m_nIndexSeries_Video == 0) {
        return -1;
    }

    QDateTime dt = QDateTime::currentDateTime();
    QString strAcquisitionDate = dt.toString("yyyyMMdd");
    QString strAcquisitionTime = dt.toString("hhmmss");

    int nAcquisitionNumber = pAcquisitionInfo->m_nAcquisitionVideoMax;
    QString strStudyInstanceUID = pAcquisitionInfo->GetStudyInfo()->strStudyInstanceUID;
    QString strSeriesInstanceUID = QString("%1.2").arg(strStudyInstanceUID); // Movie Series
    QString strImageInstanceUID = QString("%1.%2").arg(strSeriesInstanceUID).arg(nAcquisitionNumber);

    // 파일명 처리 (기존 로직 유지)
    QString strInsertVideoFileName = pAcquisitionInfo->m_strVideoFilename;
    strInsertVideoFileName.replace(MOVIE_FILE_EXTENSION, "");
    strInsertVideoFileName += "_00000" MOVIE_FILE_EXTENSION;

    QString insertQuery = "INSERT INTO AcquisitionTemp(`SeriesIndex`, `ImageNumber`, `ImageInstanceUID`, `FileLocation`, `StudyIndex`, "
                          "`AcquisitionDate`, `AcquisitionTime`, `ContentDate`, `ContentTime`, `AcquisitionNumber`, `InstanceNumber`, "
                          "`PatientOrientation`, `ImageLaterality`, `ImageComments`, `FileType`) "
                          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    QVariantList params;
    params << pAcquisitionInfo->m_nIndexSeries_Video << nAcquisitionNumber << strImageInstanceUID
           << strInsertVideoFileName << pAcquisitionInfo->m_nIndexStudy
           << strAcquisitionDate << strAcquisitionTime << strAcquisitionDate << strAcquisitionTime
           << nAcquisitionNumber << nAcquisitionNumber
           << pAcquisitionInfo->GetImageInfo()->strPatientOrientation
           << pAcquisitionInfo->GetImageInfo()->strImageLaterality
           << pAcquisitionInfo->GetImageInfo()->strImageComments
           << 1; // FileType for Video is 1

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(insertQuery);
    for(int i=0; i<params.size(); ++i) query.addBindValue(params.at(i));

    if (query.exec()) {
        auto imageInfo = pAcquisitionInfo->GetImageInfo();
        imageInfo->nAcquisitionNumber = nAcquisitionNumber;
        imageInfo->nImageNumber = nAcquisitionNumber;
        imageInfo->nInstanceNumber = nAcquisitionNumber;
        // ... 다른 info 업데이트 ...
        return query.lastInsertId().toInt(); // SELECT 없이 바로 ID 반환
    } else {
        qWarning() << "Video insertion into AcquisitionTemp failed:" << query.lastError().text();
        return -1;
    }
}

/*
QList<QStringList*>* SDatabase::GetResult()
{
    return m_pResult;
}
*/

QVariant SDatabase::executeScalar(const QString& query, const QVariantList& params)
{
    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    if (!db.isOpen()) return QVariant();

    QSqlQuery sqlQuery(db);
    sqlQuery.prepare(query);
    for(int i=0; i<params.size(); ++i) sqlQuery.addBindValue(params.at(i));

    if (sqlQuery.exec() && sqlQuery.next()) {
        return sqlQuery.value(0);
    }
    return QVariant(); // 실패 또는 결과 없음
}


int SDatabase::GetHistory(SEARCH_ITEM* pSearchItem)
{
    QString strDateStart = pSearchItem->m_strStudyDate.mid(0, 10);
    QString strDateEnd = pSearchItem->m_strStudyDate.mid(11, 10);

    QString strQuery = "SELECT COUNT(DISTINCT PatientIndex) FROM StorageInfo WHERE StudyDate <= ? AND StudyDate >= ? AND ImageDelete = 0;";

    QVariantList params;
    params << strDateEnd << strDateStart;

    // executeScalar 헬퍼를 사용하여 단일 값을 깔끔하게 가져옴
    return executeScalar(strQuery, params).toInt();
}

QList<QStringList> SDatabase::GetHistoryStudy(SEARCH_ITEM* pSearchItem, bool bWithDelete)
{
    QString strDateStart = pSearchItem->m_strStudyDate.mid(0, 10).remove('/');
    QString strDateEnd = pSearchItem->m_strStudyDate.mid(11, 10).remove('/');

    // LIKE 구문에 사용할 파라미터
    QString strSearchName = QString("%%1%").arg(pSearchItem->m_strPatientName);
    QString strSearchID = QString("%%1%").arg(pSearchItem->m_strPatientID);

    QString strQuery;
    if (bWithDelete) {
        strQuery = "SELECT * FROM HistoryStudyWithDelete WHERE StudyDate <= ? AND StudyDate >= ? AND PatientName LIKE ? AND PatientID LIKE ? ORDER BY CreateDateTime DESC;";
    } else {
        strQuery = "SELECT * FROM HistoryStudy WHERE StudyDate <= ? AND StudyDate >= ? AND PatientName LIKE ? AND PatientID LIKE ? ORDER BY CreateDateTime DESC;";
    }

    QVariantList params;
    params << strDateEnd << strDateStart << strSearchName << strSearchID;

    // 이전과 동일한 어댑터 패턴 사용
    QList<QStringList> finalList;
    QList<QStringList*> tempResult;
    if (ExecuteQuery(strQuery, params, tempResult)) {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }
    SDatabase::DeleteListReponse(tempResult);
    return finalList; // 호출한 쪽에서 .size()로 개수를 얻을 수 있음
}

QList<QStringList> SDatabase::GetImageWithStudyIndex(int nIndex)
{
    QString strQuery = "SELECT * FROM StorageInfo WHERE StudyIndex = ? AND ImageDelete = 0 ORDER BY ImageIndex DESC;";
    QVariantList params;
    params << nIndex;

    QList<QStringList> finalList;
    QList<QStringList*> tempResult;
    if (ExecuteQuery(strQuery, params, tempResult)) {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }
    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

QList<QStringList> SDatabase::GetImageWithStudyIndexASC(int nIndex)
{
    QString strQuery = "SELECT * FROM StorageInfo WHERE StudyIndex = ? AND ImageDelete = 0 ORDER BY ImageIndex ASC;";
    QVariantList params;
    params << nIndex;

    QList<QStringList> finalList;
    QList<QStringList*> tempResult;
    if (ExecuteQuery(strQuery, params, tempResult)) {
        for (const QStringList* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }
    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

bool SDatabase::ImageDeleteFromImage(const QString& strFile, bool bDelete)
{
    QString strQuery = "UPDATE Image SET ImageDelete = ? WHERE FileLocation = ?;";
    QVariantList params;
    params << (bDelete ? 1 : 0) << strFile;
    return ExecuteNonQuery(strQuery, params);
}

bool SDatabase::ImageDeleteFromAcqusitionTemp(const QString& strFile, bool bDelete)
{
    QString strQuery = "UPDATE AcquisitionTemp SET ImageDelete = ? WHERE FileLocation = ?;";
    QVariantList params;
    params << (bDelete ? 1 : 0) << strFile;
    return ExecuteNonQuery(strQuery, params);
}

bool SDatabase::ImageDeleteFromImageInstanceUID(const QString& strInstanceUID, bool bDelete)
{
    QString strQuery = "UPDATE Image SET ImageDelete = ? WHERE ImageInstanceUID = ?;";
    QVariantList params;
    params << (bDelete ? 1 : 0) << strInstanceUID;
    return ExecuteNonQuery(strQuery, params);
}

int SDatabase::getSeriesIndex(const QString& studyInstanceUID, int seriesNumber)
{
    // 서브쿼리를 사용한 원본 쿼리를 그대로 사용하되, 파라미터 바인딩으로 변경
    QString query = "SELECT ID FROM Series WHERE StudyIndex = (SELECT ID FROM Study WHERE StudyInstanceUID = ?) AND SeriesNumber = ?;";
    QVariantList params;
    params << studyInstanceUID << seriesNumber;
    return executeScalar(query, params).toInt();
}

int SDatabase::GetAcquisitionNumber(const QString& strFile)
{
    QString query = "SELECT `AcquisitionNumber` FROM StorageInfo WHERE `FileLocation` = ?;";
    QVariantList params;
    params << strFile;
    return executeScalar(query, params).toInt();
}

int SDatabase::GetSeriesImageIndexWithInstanceUID(const QString& studyInstanceUID)
{
    // 내부 헬퍼 호출
    return getSeriesIndex(studyInstanceUID, 1);
}

int SDatabase::GetSeriesVideoIndexWithInstanceUID(const QString& studyInstanceUID)
{
    // 내부 헬퍼 호출
    return getSeriesIndex(studyInstanceUID, 2);
}

vector<QString> SDatabase::GetImageListWithStudy(const QString& strStudyDate, const QString& strPatientID)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM StorageInfo WHERE StudyDate = ? AND PatientID = ? AND ImageDelete = 0 AND FileType = 0 ORDER BY ImageIndex DESC;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(strStudyDate);
    query.addBindValue(strPatientID);

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}

vector<QString> SDatabase::GetImageListWithStudyIndex(const QString& strStudyIndex)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM StorageInfo WHERE StudyIndex = ? AND ImageDelete = 0 AND FileType = 0 ORDER BY ImageIndex DESC;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(strStudyIndex.toInt()); // StudyIndex는 숫자 타입일 수 있으므로 .toInt()

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}

vector<QString> SDatabase::GetVideoListWithStudy(const QString& strStudyDate, const QString& strPatientID)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM StorageInfo WHERE StudyDate = ? AND PatientID = ? AND ImageDelete = 0 AND FileType = 1 ORDER BY ImageIndex DESC;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(strStudyDate);
    query.addBindValue(strPatientID);

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}

vector<QString> SDatabase::GetVideoListWithStudyIndex(const QString& strStudyIndex)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM StorageInfo WHERE StudyIndex = ? AND ImageDelete = 0 AND FileType = 1 ORDER BY ImageIndex DESC;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(strStudyIndex.toInt());

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}

bool SDatabase::updateSendStatus(const QString& tableName, const QString& filename)
{
    if (tableName != "Image" && tableName != "AcquisitionTemp") return false;
    QString query = QString("UPDATE %1 SET ImageSend = 1 WHERE FileLocation = ?;").arg(tableName);
    return ExecuteNonQuery(query, {filename}); // QVariantList 초기화 축약형
}


void SDatabase::SendUpdate(const QString& strFilename)
{
    updateSendStatus("Image", strFilename);
}

void SDatabase::SendUpdateAcquisitionTemp(const QString& strFilename)
{
    updateSendStatus("AcquisitionTemp", strFilename);
}

QList<QStringList> SDatabase::GetScheduledDeleteFileList()
{
    QString dateStr = QDate::currentDate().addDays(-60).toString("yyyyMMdd");
    QString query = "SELECT * FROM Image WHERE AcquisitionDate < ? AND ImageStorage = 0;";

    QList<QStringList> finalList;
    QList<QStringList*> tempResult;
    if (ExecuteQuery(query, {dateStr}, tempResult)) {
        for (const auto* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }
    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

void SDatabase::UpdateScheduledDeleteCompleteStudyDate()
{
    QString dateStr = QDate::currentDate().addDays(-60).toString("yyyyMMdd");
    QString query = "UPDATE Image SET ImageStorage = 1 WHERE AcquisitionDate < ? AND ImageStorage = 0;";
    ExecuteNonQuery(query, {dateStr});
}


QStringList SDatabase::GetScheduledDeleteStudyDateList()
{
    QString dateStr = QDate::currentDate().addDays(-60).toString("yyyyMMdd");
    QString queryStr = "SELECT DISTINCT(AcquisitionDate) FROM Image WHERE AcquisitionDate < ? AND ImageStorage = 0;";

    QStringList dateList;
    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(dateStr);

    if (query.exec()) {
        while (query.next()) {
            dateList.append(query.value(0).toString());
        }
    }
    return dateList;
}


bool SDatabase::AutoremoveImageTable()
{
    QString dateStr = QDate::currentDate().addDays(-60).toString("yyyyMMdd");

    // 두 개의 작업을 하나의 트랜잭션으로 묶어 원자성을 보장
    if (!BeginTransaction()) return false;

    bool success = true;
    // DELETE 작업
    if (!ExecuteNonQuery("DELETE FROM Image WHERE AcquisitionDate < ?;", {dateStr})) {
        success = false;
    }
    // VACUUM 작업 (성공했을 때만 실행)
    if (success && !ExecuteNonQuery("VACUUM;")) {
        success = false;
    }

    if (success) {
        Commit();
    } else {
        Rollback(); // 하나라도 실패하면 전체 롤백
    }

    return success;
}

/*
bool SDatabase::AutoremoveImageTable()
{
    bool bRet = true;
    int nRet = 0;

    QDate date = QDate::currentDate();
    date = date.addDays(-60);
    int nYear = date.year();
    int nMonth = date.month();
    int nDay = date.day();

    QString strSearchDate = QString("%1%2%3").arg(nYear, 4, 10, QChar('0')).arg(nMonth, 2, 10, QChar('0')).arg(nDay, 2, 10, QChar('0'));

    QString strQuery;
    strQuery = QString("DELETE from Image where AcquisitionDate < '%1';").arg(strSearchDate);
    nRet = ExecuteNonQuery(strQuery);

    strQuery = QString("vacuum;");
    nRet = ExecuteNonQuery(strQuery);


    return bRet;
}
*/

bool SDatabase::SetDeleteStorageFile(const QString& strFilename)
{
    QString query = "UPDATE Image SET ImageStorage = 1 WHERE FileLocation = ?;";
    // ExecuteNonQuery의 성공/실패 결과를 그대로 반환
    return ExecuteNonQuery(query, {strFilename});
}

QStringList SDatabase::GetVideoFileList()
{
    QString queryStr = "SELECT FileLocation FROM Image WHERE ImageStorage = 0 AND FileType = 1 ORDER BY ID ASC;";

    QStringList fileList;
    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);

    if (query.exec(queryStr)) { // 파라미터가 없으므로 바로 exec
        while (query.next()) {
            fileList.append(query.value(0).toString());
        }
    }
    return fileList;
}


QString SDatabase::GetValueAdavancedTable(const QString& strColumn)
{
    // 1. 컬럼 이름 화이트리스트 검증 (SQL Injection 방어)
    const QStringList validColumns = {
        "AutoCaptureSendDICOM", "AutoCloseSendFinish", "AutoExitCaptureSendDICOM", "AutoLogin",
        "DICOMCharacterSet", "AutoVideoCapture", "DICOMCompress", "VideoMode", "CropMode",
        "ScreenSaverTime", "UseFixWidthLevel", "UseLogLevel", "EnableNetworkShare", "NetworkAutoSend",
        "NetworkShareIP", "NetworkShareFolder", "NetworkSMBVersion", "NetworkShareID",
        "NetworkSharePassword", "USBBackup"
    };

    if (!validColumns.contains(strColumn, Qt::CaseInsensitive)) {
        qWarning() << "Invalid or non-whitelisted column name passed to GetValueAdavancedTable:" << strColumn;
        return ""; // 허용되지 않은 컬럼 이름이면 빈 문자열 반환
    }

    // 2. 안전하게 쿼리 실행
    QString query = QString("SELECT %1 FROM AdvancedSetting WHERE ID = 1;").arg(strColumn);

    return executeScalar(query).toString();
}


void SDatabase::CheckAdavancedTable()
{
    QList<QStringList*> resultList;
    QMap<QString, QString> settingsMap;
    bool needsRecreation = false;

            //`ID`, `ModifyDateTime`, `AutoCaptureSendDICOM`, `AutoCloseSendFinish`, `AutoExitCaptureSendDICOM`, `AutoLogin`,
            //`DICOMCharacterSet`, `AutoVideoCapture`, `DICOMCompress`, `VideoMode`, `CropMode`, `ScreenSaverTime`,
            //`UseFixWidthLevel`, `UseLogLevel`, `EnableNetworkShare`, `NetworkAutoSend`, `NetworkShareIP`,
            //`NetworkShareFolder`, `NetworkSMBVersion`, `NetworkShareID`, `NetworkSharePassword`

    // 1. 단 한 번의 쿼리로 AdvancedSetting 테이블의 첫 번째 행을 가져옴
    if (ExecuteQuery("SELECT `ID`, `ModifyDateTime`, `AutoCaptureSendDICOM`, `AutoCloseSendFinish`, `AutoExitCaptureSendDICOM`, `AutoLogin`, "
                     "`DICOMCharacterSet`, `AutoVideoCapture`, `DICOMCompress`, `VideoMode`, `CropMode`, `ScreenSaverTime`, "
                     "`UseFixWidthLevel`, `UseLogLevel`, `EnableNetworkShare`, `NetworkAutoSend`, `NetworkShareIP`, "
                     "`NetworkShareFolder`, `NetworkSMBVersion`, `NetworkShareID`, `NetworkSharePassword` FROM AdvancedSetting WHERE ID = 1;", {}, resultList) && !resultList.isEmpty())
    {
        // 2. 결과를 QMap으로 변환하여 사용하기 쉽게 만듦
        // 이 로직은 ExecuteQuery가 QSqlRecord를 반환하도록 개선하면 더 좋아질 수 있음
        const auto* data = resultList.at(0);
        // (가정) 컬럼 순서: ID, ModifyDateTime, AutoCaptureSendDICOM, ...
        // 이 부분은 스키마에 따라 정확한 인덱스를 사용해야 함


        settingsMap["ID"] = data->at(0);
        settingsMap["ModifyDateTime"] = data->at(1);
        settingsMap["AutoCaptureSendDICOM"] = data->at(2);
        settingsMap["AutoCloseSendFinish"] = data->at(3);
        settingsMap["AutoExitCaptureSendDICOM"] = data->at(4);
        settingsMap["AutoLogin"] = data->at(5);
        settingsMap["DICOMCharacterSet"] = data->at(6);
        settingsMap["AutoVideoCapture"] = data->at(7);
        settingsMap["DICOMCompress"] = data->at(8);
        settingsMap["VideoMode"] = data->at(9);
        settingsMap["CropMode"] = data->at(10);
        settingsMap["ScreenSaverTime"] = data->at(11);
        settingsMap["UseFixWidthLevel"] = data->at(12);
        settingsMap["UseLogLevel"] = data->at(13);
        settingsMap["EnableNetworkShare"] = data->at(14);
        settingsMap["NetworkAutoSend"] = data->at(15);
        settingsMap["NetworkShareIP"] = data->at(16);
        settingsMap["NetworkShareFolder"] = data->at(17);
        settingsMap["NetworkSMBVersion"] = data->at(18);
        settingsMap["NetworkShareID"] = data->at(19);
        settingsMap["NetworkSharePassword"] = data->at(20);

        // 3. 컬럼 값이 비어있는지 확인
        for (const auto& value : settingsMap) {
            if (value.isEmpty()) {
                needsRecreation = true;
                break;
            }
        }
    } else {
        // 테이블이 비어있거나 쿼리가 실패한 경우
        needsRecreation = true;
    }

    SDatabase::DeleteListReponse(resultList); // 임시 리스트 메모리 해제

    // 4. 테이블 재생성이 필요한 경우에만 실행
    if (needsRecreation) {
        qDebug() << "AdvancedSetting table is empty or invalid. Recreating with default values.";

        MakeTableAdvancedSetting(); // DROP and CREATE

        QString insertQuery = "INSERT INTO AdvancedSetting (AutoCaptureSendDICOM, AutoExitCaptureSendDICOM, AutoVideoCapture, "
                              "EnableNetworkShare, NetworkAutoSend, NetworkShareIP, NetworkShareFolder, "
                              "NetworkSMBVersion, NetworkShareID, NetworkSharePassword, DICOMCharacterSet, AutoCloseSendFinish, "
                              "AutoLogin, DICOMCompress, VideoMode, CropMode, UseFixWidthLevel, UseLogLevel, ScreenSaverTime, USBBackup) "
                              "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        // 파라미터 바인딩으로 안전하고 가독성 높게 처리
        QVariantList params;
        params << settingsMap.value("AutoCaptureSendDICOM", "0")
               << settingsMap.value("AutoExitCaptureSendDICOM", "0")
               << settingsMap.value("AutoVideoCapture", "0")
               << settingsMap.value("EnableNetworkShare", "Disabled")
               << settingsMap.value("NetworkAutoSend", "Disabled")
               << settingsMap.value("NetworkShareIP", "127.0.0.1")
               << settingsMap.value("NetworkShareFolder", "Share Folder")
               << settingsMap.value("NetworkSMBVersion", "SMB Version")
               << settingsMap.value("NetworkShareID", "Share ID")
               << settingsMap.value("NetworkSharePassword", "Share Password")
               << settingsMap.value("DICOMCharacterSet", "EUC-KR")
               << settingsMap.value("AutoCloseSendFinish", "0")
               << settingsMap.value("AutoLogin", "1")
               << settingsMap.value("DICOMCompress", "0")
               << settingsMap.value("VideoMode", "0")
               << settingsMap.value("CropMode", "0")
               << settingsMap.value("UseFixWidthLevel", "0")
               << settingsMap.value("UseLogLevel", "0")
               << settingsMap.value("ScreenSaverTime", "0")
               << settingsMap.value("USBBackup", "0");

        ExecuteNonQuery(insertQuery, params);
    }
}

void SDatabase::CheckLoginTable()
{
    // 1. COUNT(*)로 효율적으로 행의 개수만 확인
    if (executeScalar("SELECT COUNT(*) FROM LoginUser;").toInt() == 0)
    {
        // 2. 테이블을 만들고, 파라미터 바인딩으로 안전하게 기본값 삽입
        MakeTableUser();
        QString query = "INSERT INTO LoginUser (LoginID, LoginPassword) VALUES (?, ?);";
        ExecuteNonQuery(query, {"admin", "smartquadra"});
    }
}


void SDatabase::MakeViewTable()
{

    ExecuteNonQuery("DROP VIEW IF EXISTS Storage;"
    "CREATE VIEW Storage as select d2.SeriesNumber, d3.ID as ImageIndex, d1.ID as StudyIndex, d2.ID as SeriesIndex, d1.PatientIndex, d2.Modality, d1.StudyDate, d1.StudyTime, d3.FileLocation, d1.StudyInstanceUID, d2.SeriesInstanceUID, d3.ImageInstanceUID, d3.CreateDateTime, d3.ImageDelete, d3.ImageSend, d3.FileType, d3.AcquisitionDate, d3.AcquisitionTime, d3.AcquisitionNumber, d3.ImageStorage from Study as d1 join (Series as d2  join Image as d3 on d3.seriesIndex=d2.id) on d1.id=d2.studyindex;");

    ExecuteNonQuery("DROP VIEW IF EXISTS  StorageInfo;"
            "CREATE VIEW StorageInfo as select d1.*, d3.PatientID, d3.PatientName, d3.PatientAge, d3.PatientSex, (select count(*) from Storage as d2 where d1.StudyInstanceUID=d2.StudyInstanceUID) as TotalImageCount  from Storage as d1 join Patient as d3 on (d1.PatientIndex=d3.ID)");

    //ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
    //"CREATE VIEW HistoryStudy as select d4.ID, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, d4.SeriesNumber from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.ImageDelete=0) as ImageCount, (select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID and d3.ImageDelete=0) as ImageSend from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");


    /*
    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
    "CREATE VIEW HistoryStudy as "
            "select d4.StudyIndex, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, max(d4.SeriesNumber), d4.VideoCount "
            "from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=1  and d3.ImageDelete=0) as ImageCount, "
            "(select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=2 and d3.ImageDelete=0) as VideoCount, "
            "(select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID) as ImageSend "
            "from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");

    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudyWithDelete;"
    "CREATE VIEW HistoryStudyWithDelete as "
            "select d4.StudyIndex, d4.PatientIndex, d5.PatientID, d5.PatientName, d5.PatientAge, d5.PatientSex, d5.PatientBirthDate, d5.PatientWeight, d4.CreateDateTime, d4.StudyDate, d4.StudyTime, d4.StudyCodeValue, d4.PatientLocation, d4.AccessionNumber, d4.ReferringPhysiciansName, d4.StudyInstanceUID, d4.SeriesInstanceUID, d4.StudyDescription, d4.SeriesDescription, d4.OperatorsName, d4.ImageCount, d4.ImageSend, d4.Modality, d4.SeriesDate, d4.SeriesTime, max(d4.SeriesNumber), d4.VideoCount "
            "from (select *, (select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=1) as ImageCount, "
            "(select count(*) from Image as d3 where d3.StudyIndex=d1.ID and d3.SeriesIndex=2) as VideoCount, "
            "(select sum(ImageSend) from Image as d3 where d3.StudyIndex=d1.ID) as ImageSend "
            "from Study as d1 join Series as d2 on (d2.StudyIndex=d1.ID)) as d4 join Patient as d5 on (d4.PatientIndex=d5.ID)");
            */

    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudy;"
            "CREATE VIEW HistoryStudy as "
            "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSendCount, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount from (select DISTINCT(d1.ID), d3.StudyIndex, d1.PatientIndex, d4.PatientID, d4.PatientName, d4.PatientAge, d4.PatientSex, d4.PatientBirthDate, d4.PatientWeight, d4.CreateDateTime, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d2.SeriesInstanceUID, d1.StudyDescription, d2.SeriesDescription, d2.OperatorsName, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0 and Image.ImageSend=1) as ImageSendCount, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0) as ImageCount, d2.Modality, d2.SeriesDate, d2.SeriesTime, d2.SeriesNumber, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1  and Image.ImageDelete=0) as VideoCount from Study as d1 join Series as d2 on (d1.ID=d2.StudyIndex) join Image as d3 on(d2.ID=d3.SeriesIndex and d2.SeriesNumber=1) join Patient as d4 on (d4.ID=d1.PatientIndex))");


    ExecuteNonQuery("DROP VIEW IF EXISTS HistoryStudyWithDelete;"
            "CREATE VIEW HistoryStudyWithDelete as "
            "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSend, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount from (select DISTINCT(d1.ID), d3.StudyIndex, d1.PatientIndex, d4.PatientID, d4.PatientName, d4.PatientAge, d4.PatientSex, d4.PatientBirthDate, d4.PatientWeight, d4.CreateDateTime, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d2.SeriesInstanceUID, d1.StudyDescription, d2.SeriesDescription, d2.OperatorsName, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0 ) as ImageCount, d3.ImageSend, d2.Modality, d2.SeriesDate, d2.SeriesTime, d2.SeriesNumber, (select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1) as VideoCount from Study as d1 join Series as d2 on (d1.ID=d2.StudyIndex) join Image as d3 on(d2.ID=d3.SeriesIndex and d2.SeriesNumber=1) join Patient as d4 on (d4.ID=d1.PatientIndex))");




    /*
    ExecuteNonQuery(
    "DROP VIEW IF EXISTS HistoryStudy;    "
    "CREATE VIEW HistoryStudy as    "
    "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, "
    "ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSendCount, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount "
    "from (select DISTINCT(d1.ID), d1.PatientIndex, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d1.StudyDescription, "
    "(select d4.PatientID from patient as d4 where d4.ID=d1.PatientIndex) as PatientID,"
    "(select d4.PatientName from patient as d4 where d4.ID=d1.PatientIndex) as PatientName,"
    "(select d4.PatientAge from patient as d4 where d4.ID=d1.PatientIndex) as PatientAge,"
    "(select d4.PatientSex from patient as d4 where d4.ID=d1.PatientIndex) as PatientSex,"
    "(select d4.PatientBirthDate from patient as d4 where d4.ID=d1.PatientIndex) as PatientBirthDate, "
    "(select d4.PatientWeight from patient as d4 where d4.ID=d1.PatientIndex) as PatientWeight, "
    "(select d1.CreateDateTime from patient as d4 where d4.ID=d1.PatientIndex) as CreateDateTime, "
    //"(select d2.StudyIndex from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as StudyIndex, "
    "(d1.ID) as StudyIndex, "
    "(select d2.SeriesInstanceUID from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesInstanceUID, "
    "(select d2.SeriesDescription from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDescription, "
    "(select d2.OperatorsName from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as OperatorsName, "
    "(select d2.Modality from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as Modality, "
    "(select d2.SeriesDate from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDate, "
    "(select d2.SeriesTime from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesTime, "
    "(select d2.SeriesNumber from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesNumber, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0 and Image.ImageSend=1) as ImageSendCount, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  and Image.ImageDelete=0) as ImageCount, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1  and Image.ImageDelete=0) as VideoCount "
    "from Study as d1);"
                );

    ExecuteNonQuery(
    "DROP VIEW IF EXISTS HistoryStudyWithDelete;    "
    "CREATE VIEW HistoryStudyWithDelete as    "
    "select StudyIndex, PatientIndex, PatientID, PatientName, PatientAge, PatientSex, PatientBirthDate, PatientWeight, CreateDateTime, StudyDate, StudyTime, StudyCodeValue, PatientLocation, AccessionNumber, "
    "ReferringPhysiciansName, StudyInstanceUID, SeriesInstanceUID, StudyDescription, SeriesDescription, OperatorsName, ImageCount, ImageSendCount, Modality, SeriesDate, SeriesTime, SeriesNumber, VideoCount "
    "from (select DISTINCT(d1.ID), d1.PatientIndex, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, d1.ReferringPhysiciansName, d1.StudyInstanceUID, d1.StudyDescription, "
    "(select d4.PatientID from patient as d4 where d4.ID=d1.PatientIndex) as PatientID,"
    "(select d4.PatientName from patient as d4 where d4.ID=d1.PatientIndex) as PatientName,"
    "(select d4.PatientAge from patient as d4 where d4.ID=d1.PatientIndex) as PatientAge,"
    "(select d4.PatientSex from patient as d4 where d4.ID=d1.PatientIndex) as PatientSex,"
    "(select d4.PatientBirthDate from patient as d4 where d4.ID=d1.PatientIndex) as PatientBirthDate, "
    "(select d4.PatientWeight from patient as d4 where d4.ID=d1.PatientIndex) as PatientWeight, "
    "(select d1.CreateDateTime from patient as d4 where d4.ID=d1.PatientIndex) as CreateDateTime, "
    //"(select d2.StudyIndex from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as StudyIndex, "
    "(d1.ID) as StudyIndex, "
    "(select d2.SeriesInstanceUID from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesInstanceUID, "
    "(select d2.SeriesDescription from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDescription, "
    "(select d2.OperatorsName from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as OperatorsName, "
    "(select d2.Modality from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as Modality, "
    "(select d2.SeriesDate from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDate, "
    "(select d2.SeriesTime from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesTime, "
    "(select d2.SeriesNumber from series as d2 where d1.ID=d2.StudyIndex and d2.SeriesNumber=1) as SeriesNumber, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0   and Image.ImageSend=1) as ImageSendCount, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=0  ) as ImageCount, "
    "(select count(*) from Image where Image.StudyIndex=d1.ID and Image.FileType=1  ) as VideoCount "
    "from Study as d1);"
                );
*/




    ExecuteNonQuery(
        "DROP VIEW IF EXISTS HistoryStudy;    "
        "CREATE VIEW HistoryStudy as    "
        "select Q.StudyIndex, Q.PatientIndex,  "
        "(select d4.PatientID from patient as d4 where d4.ID=Q.PatientIndex) as PatientID, "
        "(select d4.PatientName from patient as d4 where d4.ID=Q.PatientIndex) as PatientName, "
        "(select d4.PatientAge from patient as d4 where d4.ID=Q.PatientIndex) as PatientAge, "
        "(select d4.PatientSex from patient as d4 where d4.ID=Q.PatientIndex) as PatientSex, "
        "(select d4.PatientBirthDate from patient as d4 where d4.ID=Q.PatientIndex) as PatientBirthDate, "
        "(select d4.PatientWeight from patient as d4 where d4.ID=Q.PatientIndex) as PatientWeight, "
        "(select Q.CreateDateTime from patient as d4 where d4.ID=Q.PatientIndex) as CreateDateTime, "
        "Q.StudyDate as StudyDate, Q.StudyTime as StudyTime, Q.StudyCodeValue as StudyCodeValue, Q.PatientLocation as PatientLocation, Q.AccessionNumber as AccessionNumber, "
        "ReferringPhysiciansName, StudyInstanceUID, "
        "(select d2.SeriesInstanceUID from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesInstanceUID, "
        "StudyDescription, "
        "(select d2.SeriesDescription from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDescription, "
        "(select d2.OperatorsName from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as OperatorsName,  "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=0  and Image.ImageDelete=0) as ImageCount, "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=0  and Image.ImageDelete=0 and Image.ImageSend=1) as ImageSendCount, "
        "(select d2.Modality from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as Modality, "
        "(select d2.SeriesDate from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDate, "
        "(select d2.SeriesTime from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesTime, "
        "(select d2.SeriesNumber from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesNumber, "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=1  and Image.ImageDelete=0) as VideoCount  "
        "from (select DISTINCT(d1.ID), d1.PatientIndex, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, "
        "   d1.ReferringPhysiciansName, d1.StudyInstanceUID, d1.StudyDescription, "
        "   (d1.ID) as StudyIndex, "
        "   (d1.PatientIndex) as PatientIndex,"
        "   (d1.CreateDateTime)"
        "   from Study as d1) as Q; "
        );

    ExecuteNonQuery(
        "DROP VIEW IF EXISTS HistoryStudyWithDelete;    "
        "CREATE VIEW HistoryStudyWithDelete as    "
        "select Q.StudyIndex, Q.PatientIndex,  "
        "(select d4.PatientID from patient as d4 where d4.ID=Q.PatientIndex) as PatientID, "
        "(select d4.PatientName from patient as d4 where d4.ID=Q.PatientIndex) as PatientName, "
        "(select d4.PatientAge from patient as d4 where d4.ID=Q.PatientIndex) as PatientAge, "
        "(select d4.PatientSex from patient as d4 where d4.ID=Q.PatientIndex) as PatientSex, "
        "(select d4.PatientBirthDate from patient as d4 where d4.ID=Q.PatientIndex) as PatientBirthDate, "
        "(select d4.PatientWeight from patient as d4 where d4.ID=Q.PatientIndex) as PatientWeight, "
        "(select Q.CreateDateTime from patient as d4 where d4.ID=Q.PatientIndex) as CreateDateTime, "
        "Q.StudyDate as StudyDate, Q.StudyTime as StudyTime, Q.StudyCodeValue as StudyCodeValue, Q.PatientLocation as PatientLocation, Q.AccessionNumber as AccessionNumber, "
        "ReferringPhysiciansName, StudyInstanceUID, "
        "(select d2.SeriesInstanceUID from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesInstanceUID, "
        "StudyDescription, "
        "(select d2.SeriesDescription from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDescription, "
        "(select d2.OperatorsName from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as OperatorsName,  "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=0) as ImageCount, "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=0 and Image.ImageSend=1) as ImageSendCount, "
        "(select d2.Modality from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as Modality, "
        "(select d2.SeriesDate from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesDate, "
        "(select d2.SeriesTime from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesTime, "
        "(select d2.SeriesNumber from series as d2 where Q.StudyIndex=d2.StudyIndex and d2.SeriesNumber=1) as SeriesNumber, "
        "(select count(*) from Image where Image.StudyIndex=Q.StudyIndex and Image.FileType=1) as VideoCount  "
        "from (select DISTINCT(d1.ID), d1.PatientIndex, d1.StudyDate, d1.StudyTime, d1.StudyCodeValue, d1.PatientLocation, d1.AccessionNumber, "
        "   d1.ReferringPhysiciansName, d1.StudyInstanceUID, d1.StudyDescription, "
        "   (d1.ID) as StudyIndex, "
        "   (d1.PatientIndex) as PatientIndex,"
        "   (d1.CreateDateTime)"
        "   from Study as d1) as Q; "
        );

    ExecuteNonQuery(
    "DROP VIEW IF EXISTS Storage;"
    "CREATE VIEW Storage as "
    "select "
    "(select d1.ID from Study as d1 where d3.StudyIndex=d1.ID) as StudyIndex,"
    "(select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID) as PatientIndex,"
    "(select d1.StudyDate from Study as d1 where d3.StudyIndex=d1.ID) as StudyDate,"
    "(select d1.StudyTime from Study as d1 where d3.StudyIndex=d1.ID) as StudyTime,"
    "(select d1.StudyInstanceUID from Study as d1 where d3.StudyIndex=d1.ID) as StudyInstanceUID,"
    "(select d2.SeriesNumber from Series as d2 where d2.ID=d3.SeriesIndex) as SeriesNumber, "
    "(select d2.Modality from Series as d2 where d2.ID=d3.SeriesIndex) as Modality, "
    "(select d2.SeriesInstanceUID from Series as d2 where d2.ID=d3.SeriesIndex) as SeriesInstanceUID, "
    "d3.SeriesIndex  as SeriesIndex, "
    "d3.ID as ImageIndex,"
    "d3.FileLocation  as FileLocation,"
    "d3.ImageInstanceUID as ImageInstanceUID,"
    "d3.CreateDateTime as CreateDateTime,"
    "d3.ImageDelete as ImageDelete,"
    "d3.ImageSend as ImageSend,"
    "d3.FileType as FileType,"
    "d3.AcquisitionDate as AcquisitionDate,"
    "d3.AcquisitionTime as AcquisitionTime,"
    "d3.AcquisitionNumber as AcquisitionNumber,"
    "d3.ImageStorage as ImageStorage, "
    "d3.ImageNumber as ImageNumber "
    " from Image as d3;"
                );


    ExecuteNonQuery(
    "DROP VIEW IF EXISTS StorageInfo;"
    "CREATE VIEW StorageInfo as "
    "select "
    "(select d1.ID from Study as d1 where d3.StudyIndex=d1.ID) as StudyIndex,"
    "(select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID) as PatientIndex,"
    "(select d1.StudyDate from Study as d1 where d3.StudyIndex=d1.ID) as StudyDate,"
    "(select d1.StudyTime from Study as d1 where d3.StudyIndex=d1.ID) as StudyTime,"
    "(select d1.StudyInstanceUID from Study as d1 where d3.StudyIndex=d1.ID) as StudyInstanceUID,"
    "(select d2.SeriesNumber from Series as d2 where d2.ID=d3.SeriesIndex) as SeriesNumber, "
    "(select d2.Modality from Series as d2 where d2.ID=d3.SeriesIndex) as Modality, "
    "(select d2.SeriesInstanceUID from Series as d2 where d2.ID=d3.SeriesIndex) as SeriesInstanceUID, "
    "d3.SeriesIndex  as SeriesIndex, "
    "d3.ID as ImageIndex,"
    "d3.FileLocation  as FileLocation,"
    "d3.ImageInstanceUID as ImageInstanceUID,"
    "d3.CreateDateTime as CreateDateTime,"
    "d3.ImageDelete as ImageDelete,"
    "d3.ImageSend as ImageSend,"
    "d3.FileType as FileType,"
    "d3.AcquisitionDate as AcquisitionDate,"
    "d3.AcquisitionTime as AcquisitionTime,"
    "d3.AcquisitionNumber as AcquisitionNumber,"
    "d3.ImageStorage as ImageStorage,"
    "(select p.PatientID from Patient as p where (select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID)=p.ID) as PatientID,"
    "(select p.PatientName from Patient as p where (select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID)=p.ID) as PatientName,"
    "(select p.PatientAge from Patient as p where (select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID)=p.ID) as PatientAge, "
    "(select p.PatientSex from Patient as p where (select d1.PatientIndex from Study as d1 where d3.StudyIndex=d1.ID)=p.ID) as PatientSex, "
    "d3.ImageNumber as ImageNumber "
    " from Image as d3; "
                );
}



bool SDatabase::IsExecute()
{
    return m_bExecute;
}



int SDatabase::GetStudyIndexWithStudyInstanceUID(const QString& strStudyInstanceUID)
{
    QString query = "SELECT ID FROM Study WHERE StudyInstanceUID = ?;";
    return executeScalar(query, {strStudyInstanceUID}).toInt();
}


vector<QString> SDatabase::GetSendList(int nStudyIndex)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM Image WHERE StudyIndex = ? AND ImageSend = 0 AND FileType = 0 AND ImageDelete = 0 ORDER BY AcquisitionNumber;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(nStudyIndex);

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}

QList<QStringList> SDatabase::GetExitSendListInfo(int nStudyIndex)
{
    QString queryStr = "SELECT FileLocation, AcquisitionNumber, AcquisitionDate, AcquisitionTime, ImageDelete, ImageSend "
                       "FROM Image WHERE StudyIndex = ? AND FileType = '0' AND ImageDelete = 0 AND ImageSend = 0 ORDER BY AcquisitionNumber;";

    QList<QStringList> finalList;
    QList<QStringList*> tempResult;
    if (ExecuteQuery(queryStr, {nStudyIndex}, tempResult)) {
        for (const auto* rowPtr : tempResult) {
            if (rowPtr) finalList.append(*rowPtr);
        }
    }
    SDatabase::DeleteListReponse(tempResult);
    return finalList;
}

bool SDatabase::CacheWrite()
{
    // 수동 Lock 불필요. Qt DB 드라이버가 스레드별 커넥션을 관리함.
    // wal_checkpoint는 WAL 모드에서 의미가 있으며, 메모리 캐시를 디스크에 기록하도록 강제합니다.
    return ExecuteNonQuery("PRAGMA wal_checkpoint(TRUNCATE);");
}

vector<QString> SDatabase::GetSendListAcquisitionTemp(int nStudyIndex)
{
    vector<QString> fileList;
    QString queryStr = "SELECT FileLocation FROM AcquisitionTemp WHERE StudyIndex = ? AND ImageSend = 0 AND FileType = 0 AND ImageDelete = 0 ORDER BY AcquisitionNumber;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(nStudyIndex);

    if (query.exec()) {
        while (query.next()) {
            QString fileLocation = query.value(0).toString();
            QFile fileCheck(fileLocation);
            // 파일 존재 및 사이즈 체크 로직은 그대로 유지
            if (fileCheck.exists() && fileCheck.size() > 850 && fileCheck.size() < 7230854) {
                fileList.push_back(fileLocation);
            }
        }
    }
    return fileList;
}

int SDatabase::GetImageMaxAcquisitionNumber(const QString& strSeriesIndex)
{
    QString query = "SELECT MAX(AcquisitionNumber) FROM Image WHERE SeriesIndex = ?;";
    // toInt()는 NULL이나 변환 실패 시 0을 반환하므로 안전합니다.
    return executeScalar(query, {strSeriesIndex.toInt()}).toInt();
}

int SDatabase::GetVideoMaxAcquisitionNumber(const QString& strSeriesIndex)
{
    // 위와 로직이 완전히 동일합니다.
    QString query = "SELECT MAX(AcquisitionNumber) FROM Image WHERE SeriesIndex = ?;";
    return executeScalar(query, {strSeriesIndex.toInt()}).toInt();
}


void SDatabase::AddAcquisitionTempToImage()
{
    // 1. 사용자님께서 지적해주신 대로, QList<QStringList*>로 올바르게 선언합니다.
    QList<QStringList*> tempRecords;

    // ExecuteQuery는 QList<QStringList*>&를 인자로 받습니다.
    if (!ExecuteQuery("SELECT * FROM AcquisitionTemp ORDER BY ID ASC;", {}, tempRecords)) {
        qWarning() << "Failed to select data from AcquisitionTemp.";
        DeleteListReponse(tempRecords); // 실패 시에도 할당된 메모리가 있을 수 있으므로 정리
        return;
    }

    if (tempRecords.isEmpty()) {
        DeleteListReponse(tempRecords); // 비어있더라도 DeleteListReponse 호출이 안전합니다.
        return;
    }

    if (!BeginTransaction()) {
        qWarning() << "AddAcquisitionTempToImage: Failed to begin transaction.";
        DeleteListReponse(tempRecords);
        return;
    }

    bool bSuccess = true;
    QSqlDatabase qdb = QSqlDatabase::database(m_connectionName);

    QSqlQuery updateQuery(qdb);
    updateQuery.prepare("UPDATE Image SET ImageDelete = ?, ImageStorage = ?, ImageSend = ?, ImageType = ? "
                        "WHERE ImageInstanceUID = ?;");

    QSqlQuery insertQuery(qdb);
    insertQuery.prepare("INSERT INTO Image(CreateDateTime, StudyIndex, SeriesIndex, ImageNumber, ImageDelete, ImageStorage, ImageSend, FileType, "
                        "ImageInstanceUID, FileLocation, ImageType, AcquisitionDate, AcquisitionTime, ContentDate, ContentTime, AcquisitionNumber, "
                        "InstanceNumber, PatientOrientation, ImageLaterality, ImageComments) "
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

    // 2. tempRecords가 포인터 리스트이므로, 루프도 포인터를 순회하도록 수정합니다.
    for (const QStringList* rowPtr : tempRecords)
    {
        if (!rowPtr) { // 포인터가 NULL일 경우를 대비한 방어 코드
            continue;
        }

        // 사용 편의를 위해 포인터를 역참조하여 참조 변수에 담습니다.
        const QStringList& row = *rowPtr;

        // 3. 데이터를 가져올 때도 .at() 또는 [] 연산자를 사용합니다.
        //    (컬럼 순서는 AcquisitionTemp 테이블의 스키마와 정확히 일치해야 합니다)
        QString imageInstanceUID = row.at(9);

        updateQuery.addBindValue(row.at(5));   // ImageDelete
        updateQuery.addBindValue(row.at(6));   // ImageStorage
        updateQuery.addBindValue(row.at(7));   // ImageSend
        updateQuery.addBindValue(row.at(11));  // ImageType
        updateQuery.addBindValue(imageInstanceUID);

        if (!updateQuery.exec()) {
            qWarning() << "Manual UPSERT-UPDATE failed:" << updateQuery.lastError().text();
            bSuccess = false;
            break;
        }

        if (updateQuery.numRowsAffected() == 0) {
            // INSERT 쿼리에 모든 컬럼 값을 순서대로 바인딩 (ID(0)는 자동 생성이므로 제외)
            for (int i = 1; i < row.size(); ++i) {
                insertQuery.addBindValue(row.at(i));
            }

            if (!insertQuery.exec()) {
                qWarning() << "Manual UPSERT-INSERT failed:" << insertQuery.lastError().text();
                bSuccess = false;
                break;
            }
        }
    }

    if (bSuccess) {
        if (!ExecuteNonQuery("DELETE FROM AcquisitionTemp;")) {
            bSuccess = false;
        }
    }

    if (bSuccess) {
        Commit();
    } else {
        Rollback();
        qWarning() << "AddAcquisitionTempToImage failed and was rolled back.";
    }

    // 4. 이제 tempRecords는 new로 할당된 포인터들의 리스트이므로,
    //    DeleteListReponse를 호출하여 메모리를 해제하는 것이 "반드시" 필요합니다.
    DeleteListReponse(tempRecords);
}

void SDatabase::ClearAcquisitionTempTable()
{
    BeginTransaction();
    if (ExecuteNonQuery("DELETE FROM AcquisitionTemp;") && ExecuteNonQuery("UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = 'AcquisitionTemp';")) {
        Commit();
    } else {
        Rollback();
    }
}

bool SDatabase::AddImageToAcquisitionTemp(int nStudyIndex)
{
    QString query = "INSERT INTO AcquisitionTemp(ID, CreateDateTime, StudyIndex, SeriesIndex, ImageNumber, ImageDelete, ImageStorage, ImageSend, FileType, "
                    "ImageInstanceUID, FileLocation, ImageType, AcquisitionDate, AcquisitionTime, ContentDate, ContentTime, AcquisitionNumber, "
                    "InstanceNumber, PatientOrientation, ImageLaterality, ImageComments) "
                    "SELECT ID, CreateDateTime, StudyIndex, SeriesIndex, ImageNumber, ImageDelete, ImageStorage, ImageSend, FileType, "
                    "ImageInstanceUID, FileLocation, ImageType, AcquisitionDate, AcquisitionTime, ContentDate, ContentTime, AcquisitionNumber, "
                    "InstanceNumber, PatientOrientation, ImageLaterality, ImageComments FROM Image WHERE StudyIndex = ? AND ImageDelete = 0 ORDER BY ID ASC;";

    return ExecuteNonQuery(query, {nStudyIndex});
}


// 트랜잭션 시작
bool SDatabase::BeginTransaction()
{
    return QSqlDatabase::database(m_connectionName).transaction();
}

// 트랜잭션 커밋
bool SDatabase::Commit()
{
    return QSqlDatabase::database(m_connectionName).commit();
}

// 트랜잭션 롤백
bool SDatabase::Rollback()
{
    return QSqlDatabase::database(m_connectionName).rollback();
}



void SDatabase::DeleteListReponse(QList<QStringList*>& listResponse)
{
    QList<QStringList*>::iterator it;

    for(it=listResponse.begin() ; it!=listResponse.end() ; ++it)
    {
        QStringList* pDataList = *it;
        if(pDataList!=NULL)
        {
            pDataList->clear();
            SAFE_DELETE(pDataList);
        }
    }
    listResponse.clear();
}

vector<QString> SDatabase::GetAllFilesForStudyIndex(int studyIndex)
{
    vector<QString> fileList;
    // FileType으로 먼저 정렬하여 이미지, 비디오 순으로 결과를 받도록 함
    QString queryStr = "SELECT FileLocation FROM StorageInfo WHERE StudyIndex = ? AND ImageDelete = 0 ORDER BY FileType, ImageIndex ASC;";

    QSqlDatabase db = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(db);
    query.prepare(queryStr);
    query.addBindValue(studyIndex);

    if (query.exec()) {
        while (query.next()) {
            fileList.push_back(query.value(0).toString());
        }
    }
    return fileList;
}