using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;

namespace AdbRecordCompare
{
    public partial class Form1 : Form
    {
        private MySqlConnection KBManagerConnection;
        private MySqlConnection slaveConnection;
        private MySqlConnection compareConnection;

        public Form1()
        {
            InitializeComponent();

            // DataGridViewCellFormatting 이벤트 핸들러 추가
            dataGridView1.CellFormatting += DataGridView_CellFormatting;

            // 라디오 버튼 CheckedChanged 이벤트 핸들러 연결
            radioAll.CheckedChanged += radioButton_CheckedChanged;
            radioTen.CheckedChanged += radioButton_CheckedChanged;
            radioHundred.CheckedChanged += radioButton_CheckedChanged;
            radioTenAndHundred.CheckedChanged += radioButton_CheckedChanged;
        }

        //==========================================================================================
        //======================================= 버튼 이벤트 =======================================
        private void btnSearch_Click(object sender, EventArgs e)
        {
            string KBManagerServer = "dt11.kabead.com";
            string ADBServer = "dt88.kabead.com";
            string[] servers = { "dt11.kabead.com", "dt22.kabead.com", "dt44.kabead.com", "dt55.kabead.com", "dt66.kabead.com", "dt77.kabead.com", "dt88.kabead.com" };

            string KBManagerConnectionString = $"Server={KBManagerServer};Database=kbmanager;User Id=root;Password=js4188..$!;SslMode=none;";
            string slaveConnectionString = $"Server={ADBServer};Database=ordcsth_adb;User Id=root;Password=js4188..$!;SslMode=none;";

            int totalTables = 0;
            int totalRecords = 0;
            int checkedTables = 0;
            int checkedRecords = 0;
            int tenOverCount = 0;
            int hundredOverCount = 0;

            tenOver.Text = "0";
            hundredOver.Text = "0";

            dataGridView1.Rows.Clear(); // 데이터 조회 전 DataGridView를 초기화

            try
            {
                using (KBManagerConnection = new MySqlConnection(KBManagerConnectionString))
                using (slaveConnection = new MySqlConnection(slaveConnectionString))
                {
                    KBManagerConnection.Open();
                    slaveConnection.Open();

                    if (cmbServerList.Text == "전체조회")
                    {
                        foreach (var compareServer in servers)
                        {
                            string realdbip = GetRealDbIp(compareServer);
                            if (string.IsNullOrEmpty(realdbip)) continue;

                            // 현재 조회 중인 DB IP를 라벨에 설정
                            DataBaseName.Text = compareServer;

                            string compareConnectionString = $"Server={compareServer};User Id=root;Password=js4188..$!;SslMode=none;";
                            using (compareConnection = new MySqlConnection(compareConnectionString))
                            {
                                compareConnection.Open();

                                // 데이터 조회 및 누적 처리
                                PerformDataRetrieval(compareConnection, realdbip, ADBServer, ref totalTables, ref totalRecords, ref checkedTables, ref checkedRecords, ref tenOverCount, ref hundredOverCount);
                            }
                        }
                    }
                    else
                    {
                        // 개별 서버 조회
                        string compareServer = cmbServerList.Text;
                        string realdbip = GetRealDbIp(compareServer);
                        if (string.IsNullOrEmpty(realdbip)) return;

                        // 현재 조회 중인 DB IP를 라벨에 설정
                        DataBaseName.Text = compareServer;

                        string compareConnectionString = $"Server={compareServer};User Id=root;Password=js4188..$!;SslMode=none;";
                        using (compareConnection = new MySqlConnection(compareConnectionString))
                        {
                            compareConnection.Open();

                            // 데이터 조회 및 누적 처리
                            PerformDataRetrieval(compareConnection, realdbip, ADBServer, ref totalTables, ref totalRecords, ref checkedTables, ref checkedRecords, ref tenOverCount, ref hundredOverCount);
                        }
                    }

                    // 최종적으로 누적된 테이블 수와 레코드 수를 UI에 업데이트
                    TableCount.Text = $"{checkedTables}/{totalTables}";
                    RecordCount.Text = $"{checkedRecords}/{totalRecords}";
                    tenOver.Text = tenOverCount.ToString();
                    hundredOver.Text = hundredOverCount.ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void PerformDataRetrieval(MySqlConnection compareConnection, string realdbip, string ADBServer, ref int totalTables, ref int totalRecords, ref int checkedTables, ref int checkedRecords, ref int tenOverCount, ref int hundredOverCount)
        {
            // 각 서버의 테이블 수 계산
            string countQuery = $@"
                SELECT COUNT(*) FROM `kbmanager`.`access` AS a
                WHERE 
                    a.`pggubun` IN ('00', '09', '11')
                    AND a.`hidden` = 0 
                    AND a.`dbname` LIKE 'carcs_%' 
                    AND a.`dbname` NOT IN (
                        'carcs_kabeadcarcs', 'carcs_english', 'carcs_kabead', 
                        'carcs_kabeadcarcs01', 'carcs_kabeadcarcs02', 'carcs_kabeadcarcs03',
                        'carcs_kabeadcarcs04', 'carcs_kabeadcarcs05', 'carcs_kabeadcarcs06',
                        'carcs_kabeadcarcs10', 'carcs_kabeadcarcs11', 'carcs_kabead1111', 
                        'carcs_kabeadvn_car', 'carcs_thecarpet2002', 'carcs_woobo',
                        'carcs_test11', 'carcs_test1234', 'carcs_mnsict', 
                        'carcs_hukio7', 'carcs_gongim7874', 'carcs_ksc0793', 
                        'carcs_kjcjeong', 'carcs_adbtest' , 'carcs_jcar3233' , 'carcs_leemingi88'
                    )
                    AND a.`dbip` = '{realdbip}';

            ";

            MySqlCommand countCommand = new MySqlCommand(countQuery, KBManagerConnection);
            int serverTables = Convert.ToInt32(countCommand.ExecuteScalar());
            totalTables += serverTables;

            //각 테이블의 레코드 수 계산
            string KBManagerQuery = countQuery.Replace("COUNT(*)", "a.`code`, a.`dbname`, a.`offname`, a.`dbip`");

            MySqlCommand KBManagerCommand = new MySqlCommand(KBManagerQuery, KBManagerConnection);
            MySqlDataReader KBManagerReader = KBManagerCommand.ExecuteReader();

            while (KBManagerReader.Read())
            {
                string dbname = KBManagerReader["dbname"].ToString();
                int serverRecords = GetTableRecordCount(compareConnection, dbname, "fixsale_d")
                                  + GetTableRecordCount(compareConnection, dbname, "fixsale_dw")
                                  + GetTableRecordCount(compareConnection, dbname, "fixsale_m");
                totalRecords += serverRecords;
            }

            KBManagerReader.Close();

            //각 테이블의 데이터 처리
            KBManagerReader = KBManagerCommand.ExecuteReader();

            while (KBManagerReader.Read())
            {
                string code = KBManagerReader["code"].ToString();
                string dbip = KBManagerReader["dbip"].ToString();
                string dbname = KBManagerReader["dbname"].ToString();
                string offname = KBManagerReader["offname"].ToString();

                if (IsDbVersionValid(compareConnection, dbname, 98))
                {
                    int fixsaleDCount = GetTableRecordCount(compareConnection, dbname, "fixsale_d") + GetTableRecordCount(compareConnection, dbname, "fixsale_dw");
                    int fixsaleMCount = GetTableRecordCount(compareConnection, dbname, "fixsale_m");

                    int adbFixsaleDCount = GetTableRecordCountByCode(slaveConnection, "adb_fixsale_d", code);
                    int adbFixsaleMCount = GetTableRecordCountByCode(slaveConnection, "adb_fixsale_m", code);

                    int fixsaleDDifference = fixsaleDCount - adbFixsaleDCount;
                    int fixsaleMDifference = fixsaleMCount - adbFixsaleMCount;

                    // 10 이상 100 미만 차이 카운트
                    if (fixsaleDDifference >= 10 && fixsaleDDifference < 100) tenOverCount++;
                    //if (fixsaleMDifference >= 10 && fixsaleMDifference < 100) tenOverCount++;

                    // 100 이상 차이 카운트
                    if (fixsaleDDifference >= 100) hundredOverCount++;
                    //if (fixsaleMDifference >= 100) hundredOverCount++;

                    checkedTables++;
                    checkedRecords += fixsaleDCount + fixsaleMCount;

                    // 데이터 그리드 뷰에 행 추가
                    dataGridView1.Rows.Add(dbip, dbname, offname, code, ADBServer, fixsaleMDifference, fixsaleDDifference);

                    // UI 업데이트
                    TableCount.Text = $"{checkedTables}/{totalTables}";
                    RecordCount.Text = $"{checkedRecords}/{totalRecords}";
                    Application.DoEvents();
                }
            }

            KBManagerReader.Close();
        }

        private string GetRealDbIp(string compareServer)
        {
            switch (compareServer)
            {
                case "dt11.kabead.com": return "dt01.kabead.com";
                case "dt22.kabead.com": return "dt02.kabead.com";
                case "dt44.kabead.com": return "dt04.kabead.com";
                case "dt55.kabead.com": return "dt05.kabead.com";
                case "dt66.kabead.com": return "dt06.kabead.com";
                case "dt77.kabead.com": return "dt07.kabead.com";
                case "dt88.kabead.com": return "dt08.kabead.com";
                default: return null;
            }
        }

        private int GetTableRecordCount(MySqlConnection connection, string dbName, string tableName)
        {
            string query = $@"SELECT COUNT(1) FROM `{dbName}`.`{tableName}` ";
            MySqlCommand command = new MySqlCommand(query, connection);
            return Convert.ToInt32(command.ExecuteScalar());
        }

        private int GetTableRecordCountByCode(MySqlConnection connection, string tableName, string code)
        {
            string query = $"SELECT COUNT(1) FROM `{tableName}` WHERE `accessCode` = @code";
            MySqlCommand command = new MySqlCommand(query, connection);
            command.Parameters.AddWithValue("@code", code);
            return Convert.ToInt32(command.ExecuteScalar());
        }

        private bool IsDbVersionValid(MySqlConnection connection, string dbName, int requiredVersion)
        {
            string query = $"SELECT COUNT(1) FROM `{dbName}`.`setting` WHERE setname = 'dbversion' AND setdata >= @version";
            MySqlCommand command = new MySqlCommand(query, connection);
            command.Parameters.AddWithValue("@version", requiredVersion);
            return Convert.ToInt32(command.ExecuteScalar()) > 0;
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            // 현재 선택된 탭에 따라 해당 GridView를 선택
            DataGridView selectedGridView = dataGridView1;

            if (selectedGridView == null || selectedGridView.Rows.Count == 0)
            {
                MessageBox.Show("데이터가 없습니다.");
                return;
            }

            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "Excel Files|*.xlsx";
                saveFileDialog.Title = "Save as Excel File";
                saveFileDialog.FileName = DateTime.Now.ToString("yyyyMMddhhmm") + "ADB비교.xlsx";

                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    // EPPlus 라이브러리
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                    using (ExcelPackage excel = new ExcelPackage())
                    {
                        ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Sheet1");

                       
                        for (int i = 0; i < selectedGridView.Columns.Count; i++)
                        {
                            worksheet.Cells[1, i + 1].Value = selectedGridView.Columns[i].HeaderText;
                            worksheet.Cells[1, i + 1].Style.Font.Bold = true;
                        }

                        
                        for (int i = 0; i < selectedGridView.Rows.Count; i++)
                        {
                            for (int j = 0; j < selectedGridView.Columns.Count; j++)
                            {
                                worksheet.Cells[i + 2, j + 1].Value = selectedGridView.Rows[i].Cells[j].Value?.ToString();
                            }
                        }

                        
                        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

                        // 저장
                        FileInfo excelFile = new FileInfo(saveFileDialog.FileName);
                        excel.SaveAs(excelFile);

                        MessageBox.Show("Excel 파일이 성공적으로 저장되었습니다.");
                    }
                }
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        //==========================================================================================
        //=========================================== 이벤트 =======================================
        private void DataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            // 이 이벤트는 모든 셀에 대해 호출되므로 특정 컬럼을 필터링
            DataGridView grid = sender as DataGridView;

            if (grid.Columns[e.ColumnIndex].Name == "RecordDifferenceDetail") // "레코드 차이" 컬럼 이름
            {
                if (e.Value != null && int.TryParse(e.Value.ToString(), out int difference))
                {
                    if (difference >= 100)
                    {
                        e.CellStyle.BackColor = System.Drawing.Color.Red;
                        e.CellStyle.ForeColor = System.Drawing.Color.White;
                    }
                    else if (difference >= 10)
                    {
                        e.CellStyle.BackColor = System.Drawing.Color.Orange;
                        e.CellStyle.ForeColor = System.Drawing.Color.Black;
                    }
                }
            }
        }
        private void radioButton_CheckedChanged(object sender, EventArgs e)
        {
            // 라디오 버튼 체크 여부 확인
            RadioButton rb = sender as RadioButton;
            if (rb.Checked)
            {
                ApplyFilter(rb.Name);
            }
        }
        private void ApplyFilter(string filterType)
        {
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                int difference = 0;
                if (int.TryParse(row.Cells["RecordDifferenceDetail"].Value?.ToString(), out difference))
                {
                    switch (filterType)
                    {
                        case "radioTen":
                            row.Visible = difference >= 10 && difference < 100;
                            break;
                        case "radioTenAndHundred":
                            row.Visible = difference >= 10 ;
                            break;
                        case "radioHundred":
                            row.Visible = difference >= 100;
                            break;
                        case "radioAll":
                        default:
                            row.Visible = true;
                            break;
                    }
                }
            }
        }

    }
}