using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using FCOMMON;

namespace Project.Web
{
    public partial class MachineBridge
    {
        #region Holyday (근태) API

        /// <summary>
        /// 근태 목록 조회 (사용자 선택 가능)
        /// </summary>
        public string Holyday_GetList(string sd, string ed, string uid)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                
                var sql = @"SELECT h.*, u.name as UserName
                           FROM Holyday h WITH (nolock)
                           LEFT JOIN Users u ON h.uid = u.id
                           WHERE h.gcode = @gcode";

                var parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@gcode", info.Login.gcode));

                // 권한에 따라 사용자 필터링
                if (curLevel < 5)
                {
                    // 일반 사용자는 본인 것만
                    sql += " AND h.uid = @uid";
                    parameters.Add(new SqlParameter("@uid", info.Login.no));
                }
                else if (!string.IsNullOrEmpty(uid) && uid != "%")
                {
                    // 관리자가 특정 사용자 선택
                    sql += " AND h.uid = @uid";
                    parameters.Add(new SqlParameter("@uid", uid));
                }

                if (!string.IsNullOrEmpty(sd))
                {
                    sql += " AND h.sdate >= @sd";
                    parameters.Add(new SqlParameter("@sd", sd));
                }
                if (!string.IsNullOrEmpty(ed))
                {
                    sql += " AND h.sdate <= @ed";
                    parameters.Add(new SqlParameter("@ed", ed));
                }

                sql += " ORDER BY h.sdate DESC, h.idx DESC";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(new { Success = true, Data = dt });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 상세 조회
        /// </summary>
        public string Holyday_GetDetail(int idx)
        {
            try
            {
                var sql = @"SELECT h.*, u.name as UserName
                           FROM Holyday h WITH (nolock)
                           LEFT JOIN Users u ON h.uid = u.id
                           WHERE h.idx = @idx AND h.gcode = @gcode";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@idx", idx);
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            var row = dt.Rows[0];
                            var data = new Dictionary<string, object>();
                            foreach (DataColumn col in dt.Columns)
                            {
                                data[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                            }
                            return JsonConvert.SerializeObject(new { Success = true, Data = data });
                        }
                        return JsonConvert.SerializeObject(new { Success = false, Message = "데이터를 찾을 수 없습니다." });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 추가
        /// </summary>
        public string Holyday_Add(string cate, string sdate, string edate, double term, double crtime, 
            double termDr, double drTime, string contents, string uid)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                if (curLevel < 5)
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = "권한이 없습니다." });
                }

                // 마감 체크
                var smon = sdate.Substring(0, 7);
                if (DBM.GetMagamStatus(smon))
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                }

                var sql = @"INSERT INTO Holyday (gcode, cate, sdate, edate, term, crtime, termDr, DrTime, contents, uid, wdate, wuid)
                           VALUES (@gcode, @cate, @sdate, @edate, @term, @crtime, @termDr, @drTime, @contents, @uid, GETDATE(), @wuid);
                           SELECT SCOPE_IDENTITY();";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    cmd.Parameters.AddWithValue("@cate", cate ?? "");
                    cmd.Parameters.AddWithValue("@sdate", sdate);
                    cmd.Parameters.AddWithValue("@edate", edate);
                    cmd.Parameters.AddWithValue("@term", term);
                    cmd.Parameters.AddWithValue("@crtime", crtime);
                    cmd.Parameters.AddWithValue("@termDr", termDr);
                    cmd.Parameters.AddWithValue("@drTime", drTime);
                    cmd.Parameters.AddWithValue("@contents", contents ?? "");
                    cmd.Parameters.AddWithValue("@uid", uid);
                    cmd.Parameters.AddWithValue("@wuid", info.Login.no);

                    cn.Open();
                    var newId = Convert.ToInt32(cmd.ExecuteScalar());
                    return JsonConvert.SerializeObject(new { Success = true, Message = "저장되었습니다.", Data = new { idx = newId } });
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 수정
        /// </summary>
        public string Holyday_Edit(int idx, string cate, string sdate, string edate, double term, double crtime,
            double termDr, double drTime, string contents)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                if (curLevel < 5)
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = "권한이 없습니다." });
                }

                // 마감 체크
                var smon = sdate.Substring(0, 7);
                if (DBM.GetMagamStatus(smon))
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                }

                // 외부 연동 데이터 체크
                var checkSql = "SELECT extcate, extidx FROM Holyday WHERE idx = @idx AND gcode = @gcode";
                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                {
                    using (var cmd = new SqlCommand(checkSql, cn))
                    {
                        cmd.Parameters.AddWithValue("@idx", idx);
                        cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cn.Open();
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                var extcate = reader["extcate"] != DBNull.Value ? reader["extcate"].ToString() : "";
                                var extidx = reader["extidx"] != DBNull.Value ? Convert.ToInt32(reader["extidx"]) : -1;
                                
                                if (!string.IsNullOrEmpty(extcate) && extidx > 0)
                                {
                                    return JsonConvert.SerializeObject(new { 
                                        Success = false, 
                                        Message = $"이 자료는 외부에서 자동생성된 자료입니다. (소스: {extcate}:{extidx})" 
                                    });
                                }
                            }
                        }
                    }

                    var sql = @"UPDATE Holyday SET
                               cate = @cate, sdate = @sdate, edate = @edate, term = @term, crtime = @crtime,
                               termDr = @termDr, DrTime = @drTime, contents = @contents, wuid = @wuid, wdate = GETDATE()
                               WHERE idx = @idx AND gcode = @gcode";

                    using (var cmd = new SqlCommand(sql, cn))
                    {
                        cmd.Parameters.AddWithValue("@idx", idx);
                        cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cmd.Parameters.AddWithValue("@cate", cate ?? "");
                        cmd.Parameters.AddWithValue("@sdate", sdate);
                        cmd.Parameters.AddWithValue("@edate", edate);
                        cmd.Parameters.AddWithValue("@term", term);
                        cmd.Parameters.AddWithValue("@crtime", crtime);
                        cmd.Parameters.AddWithValue("@termDr", termDr);
                        cmd.Parameters.AddWithValue("@drTime", drTime);
                        cmd.Parameters.AddWithValue("@contents", contents ?? "");
                        cmd.Parameters.AddWithValue("@wuid", info.Login.no);

                        var result = cmd.ExecuteNonQuery();
                        return JsonConvert.SerializeObject(new { Success = result > 0, Message = result > 0 ? "수정되었습니다." : "수정에 실패했습니다." });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 삭제
        /// </summary>
        public string Holyday_Delete(int idx)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                if (curLevel < 5)
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = "권한이 없습니다." });
                }

                // 외부 연동 데이터 및 마감 체크
                var checkSql = "SELECT extcate, extidx, sdate FROM Holyday WHERE idx = @idx AND gcode = @gcode";
                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                {
                    using (var cmd = new SqlCommand(checkSql, cn))
                    {
                        cmd.Parameters.AddWithValue("@idx", idx);
                        cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cn.Open();
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                var extcate = reader["extcate"] != DBNull.Value ? reader["extcate"].ToString() : "";
                                var extidx = reader["extidx"] != DBNull.Value ? Convert.ToInt32(reader["extidx"]) : -1;
                                var sdate = reader["sdate"] != DBNull.Value ? reader["sdate"].ToString() : "";

                                if (!string.IsNullOrEmpty(extcate) && extidx > 0)
                                {
                                    return JsonConvert.SerializeObject(new { 
                                        Success = false, 
                                        Message = $"이 자료는 외부에서 자동생성된 자료입니다. (소스: {extcate}:{extidx})" 
                                    });
                                }

                                if (!string.IsNullOrEmpty(sdate) && sdate.Length >= 7)
                                {
                                    var smon = sdate.Substring(0, 7);
                                    if (DBM.GetMagamStatus(smon))
                                    {
                                        return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                                    }
                                }
                            }
                        }
                    }

                    var sql = "DELETE FROM Holyday WHERE idx = @idx AND gcode = @gcode";
                    using (var cmd = new SqlCommand(sql, cn))
                    {
                        cmd.Parameters.AddWithValue("@idx", idx);
                        cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);

                        var result = cmd.ExecuteNonQuery();
                        return JsonConvert.SerializeObject(new { Success = result > 0, Message = result > 0 ? "삭제되었습니다." : "삭제에 실패했습니다." });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 사용자 목록 조회 (기간 내 데이터가 있는 사용자)
        /// </summary>
        public string Holyday_GetUserList(string sd, string ed)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));

                var sql = @"SELECT DISTINCT h.uid, u.name as UserName
                           FROM Holyday h WITH (nolock)
                           LEFT JOIN Users u ON h.uid = u.id
                           WHERE h.gcode = @gcode";

                var parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@gcode", info.Login.gcode));

                if (!string.IsNullOrEmpty(sd))
                {
                    sql += " AND h.sdate >= @sd";
                    parameters.Add(new SqlParameter("@sd", sd));
                }
                if (!string.IsNullOrEmpty(ed))
                {
                    sql += " AND h.sdate <= @ed";
                    parameters.Add(new SqlParameter("@ed", ed));
                }

                sql += " ORDER BY u.name";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Holyday_GetUserList 오류: {ex.Message}");
                return "[]";
            }
        }

        /// <summary>
        /// 근태 권한 정보 조회
        /// </summary>
        public string Holyday_GetPermission()
        {
            try
            {
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                bool canManage = curLevel >= 5;

                return JsonConvert.SerializeObject(new
                {
                    Success = true,
                    CurrentUserId = info.Login.no,
                    Level = curLevel,
                    CanManage = canManage
                });
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 근태 잔량 조회 (연도별)
        /// </summary>
        public string Holyday_GetBalance(string year, string uid)
        {
            try
            {
                // 권한 확인
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.holyday));
                
                // 본인 확인 또는 관리자 권한 확인
                if (uid != info.Login.no && curLevel < 5)
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = "권한이 없습니다." });
                }

                var sql = @"SELECT 
                                cate,
                                ISNULL(SUM(termDr), 0) as TotalGenDays,
                                ISNULL(SUM(DrTime), 0) as TotalGenHours,
                                ISNULL(SUM(term), 0) as TotalUseDays,
                                ISNULL(SUM(crtime), 0) as TotalUseHours
                           FROM Holyday WITH (nolock)
                           WHERE gcode = @gcode AND uid = @uid AND sdate LIKE @year + '%'
                           GROUP BY cate";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    cmd.Parameters.AddWithValue("@uid", uid);
                    cmd.Parameters.AddWithValue("@year", year);

                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(new { Success = true, Data = dt });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        #endregion
    }
}