using Microsoft.AspNetCore.Http;
using Npgsql;
using Server.SQL;
using Server.System;
using System.Globalization;

namespace Server.Git
{
    public class ExcelSQL
    {
        List<sheet> sheets;

        public ExcelSQL(List<sheet> sheets)
        {
            this.sheets = sheets;
        }

        public void DataUpdate()
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(STATICS.EXCEL_SQL_URL))
            {
                try
                {
                    // 데이터베이스 연결 열기
                    connection.Open();

                    // 쿼리 작성 및 실행
                    //모든 쿼리 삭제
                    string query;
                    string header1 = "CREATE TABLE IF NOT EXISTS excel.";
                    string header2 = "INSERT INTO excel.";
                    string newTableQuery;
                    string tableDatas;
                    #region 모든 테이블 삭제
                    query = "DROP SCHEMA excel CASCADE;" +
                        "CREATE SCHEMA excel AUTHORIZATION manager;";
                    ExecuteNonQuery(connection, query);
                    #endregion
                    #region 테이블 세팅
                    query = "";
                    for (int n = 0; n < sheets.Count; n++)
                    {
                        //초기화
                        newTableQuery = "";
                        tableDatas = "(";

                        #region 신규 테이블 생성
                        newTableQuery += header1;
                        newTableQuery += sheets[n].name + "(";
                        for (int m = 0; m < sheets[n].variable.Count; m++)
                        {
                            if (sheets[n].type[m] == "long" && sheets[n].variable[m] == "index")
                            {
                                newTableQuery += "index SERIAL PRIMARY KEY";
                                tableDatas += "index";
                                continue;
                            }
                            if (m != 0)
                            {
                                newTableQuery += ",\n";
                                tableDatas += ", ";
                            }
                         
                            switch (sheets[n].type[m])
                            {
                                case "int":
                                    newTableQuery += $"{sheets[n].variable[m]} INT ";
                                    break;
                                case "string":
                                    newTableQuery += $"{sheets[n].variable[m]} VARCHAR(255) ";
                                    break;
                                case "enum":
                                    newTableQuery += $"{sheets[n].variable[m]} INT2 ";
                                    break;
                                case "text":
                                    newTableQuery += $"{sheets[n].variable[m]} TEXT ";
                                    break;
                                case "time":
                                    newTableQuery += $"{sheets[n].variable[m]} timestamp ";
                                    break;
                            }
                            tableDatas += sheets[n].variable[m];
                        }
                        newTableQuery += ");\n";
                        tableDatas += ") VALUES ";
                        bool isStart = true;
                        query += newTableQuery;

                        #endregion
                        #region 신규 테이블 데이터 올리기

                        query += header2;
                        query += sheets[n].name;
                        query += tableDatas;
                        foreach(KeyValuePair<long, Dictionary<string, object>> pair in sheets[n].dicViewer)
                        {
                            if (isStart)
                            {
                                isStart = !isStart;
                            }
                            else
                            {
                                query += ", ";
                            }
                            query += "(";
                            for (int m = 0; m < sheets[n].variable.Count; m++)
                            {
                                switch (sheets[n].type[m])
                                {
                                    case "int":
                                    case "enum":
                                        query += $"{pair.Value[sheets[n].variable[m]]}";
                                        break;
                                    case "string":
                                    case "text":
                                        query += $"'{pair.Value[sheets[n].variable[m]]}'";
                                        break;
                                    case "time":
                                        query += $"'{((DateTime)pair.Value[sheets[n].variable[m]]).ToString("yyyy-MM-dd HH:mm:ss")}'";
                                        break;
                                }
                                if(m != sheets[n].variable.Count -1)
                                {
                                    query += ", ";
                                }
                            }
                            query += ")";
                        }
                        #endregion
                    }
                    ExecuteNonQuery(connection, query);

                    #endregion
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
        private void ExecuteNonQuery(NpgsqlConnection connection, string query)
        {
            using (NpgsqlCommand command = new NpgsqlCommand(query, connection))
            {
                Console.WriteLine(query);
                command.ExecuteNonQuery();
            }
        }
    }
}