Template1

using System;
using System.Collections.Generic;
using System.Data;
using ChatworkBulkSender.Dtos;
using ChatworkBulkSender.Utils;

namespace ChatworkBulkSender.Daos
{
    public class PatternMasterDao
    {
        private readonly DBAccess _db = new DBAccess();
        private readonly PatternCustomerMasterDao _customerDao = new PatternCustomerMasterDao();

        /// <summary>
        /// 有効な送信パターン一覧を取得
        /// </summary>
        public List<PatternMasterDto> GetPatterns(bool includeEmptyPattern = false)
        {
            string sql = @"
                SELECT
                  *
                FROM dbo.送信パターンマスタ
                WHERE 削除フラグ = @isDeleted
                  AND 未使用フラグ = @isUnused
                ORDER BY 並び順";
            var param = new Dictionary<string, object> {
                { "@isDeleted", Constants.DELETE_FLAG.NON_DELETED },
                { "@isUnused", Constants.UNUSED_FLAG.USED }
            };

            DataTable dt = _db.ExecQuery(sql, param);
            var list = new List<PatternMasterDto>();

            foreach (DataRow row in dt.Rows)
            {
                var dto = new PatternMasterDto
                {
                    PatternId = row.Field<int>("送信パターンID"),
                    PatternName = row.Field<string>("送信パターン名称"),
                    TemplateText = row.Field<string>("定型文"),
                    Target = row.Field<int>("送信対象"),
                    SortOrder = row.Field<int>("並び順"),
                    IsUnused = row.Field<bool>("未使用フラグ"),
                    IsDeleted = row.Field<bool>("削除フラグ"),
                    CreatedDate = row.Field<DateTime?>("登録日時"),
                    CreatedBy = row.Field<string>("登録ユーザ"),
                    Customers = _customerDao.GetCustomersByPatternId(row.Field<int>("送信パターンID"))
                };

                list.Add(dto);
            }

            // 先頭への空データ挿入
            if (includeEmptyPattern)
            {
                var emptyDto = new PatternMasterDto();
                list.Insert(0, emptyDto);
            }

            return list;
        }

        /// <summary>
        /// 全ての送信パターンを取得（削除済みを除く）
        /// </summary>
        /// <param name="includeUnused">未使用データを含めるかどうか（デフォルト: true）</param>
        public List<PatternMasterDto> GetAll(bool includeUnused = true)
        {
            string sql = @"
                SELECT
                  *
                FROM dbo.送信パターンマスタ
                WHERE 削除フラグ = @isDeleted";

            var param = new Dictionary<string, object> {
                { "@isDeleted", Constants.DELETE_FLAG.NON_DELETED }
            };

            // 未使用データを除外する場合
            if (!includeUnused)
            {
                sql += " AND 未使用フラグ = @isUnused";
                param.Add("@isUnused", Constants.UNUSED_FLAG.USED);
            }

            sql += " ORDER BY 並び順";

            DataTable dt = _db.ExecQuery(sql, param);
            var list = new List<PatternMasterDto>();

            foreach (DataRow row in dt.Rows)
            {
                var dto = new PatternMasterDto
                {
                    PatternId = row.Field<int>("送信パターンID"),
                    PatternName = row.Field<string>("送信パターン名称"),
                    TemplateText = row.Field<string>("定型文"),
                    Target = row.Field<int>("送信対象"),
                    SortOrder = row.Field<int>("並び順"),
                    IsUnused = row.Field<bool>("未使用フラグ"),
                    IsDeleted = row.Field<bool>("削除フラグ"),
                    CreatedDate = row.Field<DateTime?>("登録日時"),
                    CreatedBy = row.Field<string>("登録ユーザ"),
                    UpdatedDate = row.Field<DateTime?>("更新日時"),
                    UpdateBy = row.Field<string>("更新ユーザ"),
                    Customers = _customerDao.GetCustomersByPatternId(row.Field<int>("送信パターンID"))
                };

                list.Add(dto);
            }

            return list;
        }

        /// <summary>
        /// 指定日時以降に更新があるかチェック
        /// </summary>
        public bool HasUpdatesAfter(DateTime? lastLoadTime)
        {
            if (lastLoadTime == null) return false;

            string sql = @"
                SELECT COUNT(*)
                FROM dbo.送信パターンマスタ
                WHERE 削除フラグ = @isDeleted
                  AND (登録日時 > @lastLoadTime OR 更新日時 > @lastLoadTime)";

            var param = new Dictionary<string, object> {
                { "@isDeleted", Constants.DELETE_FLAG.NON_DELETED },
                { "@lastLoadTime", lastLoadTime.Value }
            };

            DataTable dt = _db.ExecQuery(sql, param);
            return (int)dt.Rows[0][0] > 0;
        }

        /// <summary>
        /// 新規送信パターンを登録
        /// </summary>
        public int Insert(PatternMasterDto dto)
        {
            string sql = @"
                INSERT INTO dbo.送信パターンマスタ(
                    送信パターン名称,
                    定型文,
                    送信対象,
                    並び順,
                    未使用フラグ,
                    削除フラグ,
                    登録日時,
                    登録ユーザ,
                    更新日時,
                    更新ユーザ
                )
                VALUES(
                    @PatternName,
                    @TemplateText,
                    @Target,
                    @SortOrder,
                    @IsUnused,
                    @IsDeleted,
                    @CreatedDate,
                    @CreatedBy,
                    @UpdatedDate,
                    @UpdatedBy
                )";

            var param = new Dictionary<string, object>
            {
                {"@PatternName", dto.PatternName},
                {"@TemplateText", dto.TemplateText},
                {"@Target", dto.Target},
                {"@SortOrder", dto.SortOrder},
                {"@IsUnused", dto.IsUnused},
                {"@IsDeleted", dto.IsDeleted},
                {"@CreatedDate", dto.CreatedDate},
                {"@CreatedBy", dto.CreatedBy},
                {"@UpdatedDate", dto.UpdatedDate},
                {"@UpdatedBy", dto.UpdateBy}
            };

            return _db.ExecNonQuery(sql, param);
        }

        public int InsertWithCustomersBatch(PatternMasterDto dto, List<int> selectedCustomerIds)
        {
            // パターンマスタへの挿入（既存のInsertメソッドを使用）
            int result = Insert(dto);

            if (result > 0 && selectedCustomerIds != null && selectedCustomerIds.Count > 0)
            {
                // 最新のパターンIDを取得
                string sqlGetId = @"
            SELECT TOP 1 送信パターンID 
            FROM dbo.送信パターンマスタ 
            WHERE 送信パターン名称 = @PatternName 
            ORDER BY 送信パターンID DESC";

                var param = new Dictionary<string, object> { { "@PatternName", dto.PatternName } };
                var dt = _db.ExecQuery(sqlGetId, param);

                if (dt.Rows.Count > 0)
                {
                    int patternId = Convert.ToInt32(dt.Rows[0]["送信パターンID"]);

                    // 顧客データの一括挿入
                    foreach (var customerId in selectedCustomerIds)
                    {
                        string sqlCustomer = @"
                    INSERT INTO dbo.送信パターンマスタ_顧客(
                        送信パターンID,
                        管理番号,
                        登録日時,
                        登録ユーザ
                    )
                    VALUES(
                        @PatternId,
                        @ManagementNumber,
                        @CreatedDate,
                        @CreatedBy
                    )";

                        var paramCustomer = new Dictionary<string, object>
                    {
                        {"@PatternId", patternId},
                        {"@ManagementNumber", customerId},
                        {"@CreatedDate", DateTime.Now},
                        {"@CreatedBy", Environment.UserName}
                    };

                        _db.ExecNonQuery(sqlCustomer, paramCustomer);

                    }
                }
            }

            return result;
        }




        /// <summary>
        /// 送信パターンを更新（既存データの論理削除＋新規データのINSERT）
        /// </summary>
        /// <returns>新しい送信パターンID。エラーの場合は-1</returns>
        public int Update(PatternMasterDto dto, DateTime currentUpdatedDate)
        {
            return Update(dto, currentUpdatedDate, null);
        }
        
        /// <summary>
        /// 送信パターンを更新（既存データの論理削除＋新規データのINSERT）
        /// 選択された顧客データも同時に更新
        /// </summary>
        /// <returns>新しい送信パターンID。エラーの場合は-1</returns>
        public int Update(PatternMasterDto dto, DateTime currentUpdatedDate, List<int> selectedCustomerIds)
        {
            // 1. まず更新が可能かチェック（DateTime精度問題対策：1秒の許容範囲）
            string checkSql = @"
                SELECT COUNT(*)
                FROM dbo.送信パターンマスタ
                WHERE 送信パターンID = @PatternId
                  AND 削除フラグ = @CurrentIsDeleted
                  AND ABS(DATEDIFF(SECOND, 更新日時, @CurrentUpdatedDate)) <= 1";

            var checkParam = new Dictionary<string, object>
            {
                {"@PatternId", dto.PatternId},
                {"@CurrentIsDeleted", Constants.DELETE_FLAG.NON_DELETED},
                {"@CurrentUpdatedDate", currentUpdatedDate}
            };

            var checkResult = _db.ExecuteScalar(checkSql, checkParam);
            if (checkResult == null || (int)checkResult != 1)
            {
                return -1; // 楽観的ロック
            }

            // 2. 既存レコードを論理削除するSQL（DateTime精度問題対策：1秒の許容範囲）
            string updateSql = @"
                UPDATE dbo.送信パターンマスタ
                SET 削除フラグ = @IsDeleted,
                    更新日時 = @UpdatedDate,
                    更新ユーザ = @UpdatedBy
                WHERE 送信パターンID = @PatternId
                  AND 削除フラグ = @CurrentIsDeleted
                  AND ABS(DATEDIFF(SECOND, 更新日時, @CurrentUpdatedDate)) <= 1";

            var updateParam = new Dictionary<string, object>
            {
                {"@IsDeleted", Constants.DELETE_FLAG.DELETED},
                {"@UpdatedDate", DateTime.Now},
                {"@UpdatedBy", dto.UpdateBy},
                {"@PatternId", dto.PatternId},
                {"@CurrentIsDeleted", Constants.DELETE_FLAG.NON_DELETED},
                {"@CurrentUpdatedDate", currentUpdatedDate}
            };

            // 3. 新規レコードをINSERTするSQL
            string insertSql = @"
                INSERT INTO dbo.送信パターンマスタ(
                    送信パターン名称,
                    定型文,
                    送信対象,
                    並び順,
                    未使用フラグ,
                    削除フラグ,
                    登録日時,
                    登録ユーザ,
                    更新日時,
                    更新ユーザ
                )
                VALUES(
                    @PatternName,
                    @TemplateText,
                    @Target,
                    @SortOrder,
                    @IsUnused,
                    @IsDeleted,
                    @CreatedDate,
                    @CreatedBy,
                    @UpdatedDate,
                    @UpdatedBy
                )";

            var insertParam = new Dictionary<string, object>
            {
                {"@PatternName", dto.PatternName},
                {"@TemplateText", dto.TemplateText},
                {"@Target", dto.Target},
                {"@SortOrder", dto.SortOrder},
                {"@IsUnused", dto.IsUnused},
                {"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED},
                {"@CreatedDate", DateTime.Now},
                {"@CreatedBy", dto.UpdateBy}, // 更新ユーザーを登録ユーザーとして使用
                {"@UpdatedDate", dto.UpdatedDate},
                {"@UpdatedBy", dto.UpdateBy}
            };

            // 4. トランザクションで実行
            var commands = new List<(string Sql, Dictionary<string, object> Parameters)>
            {
                (updateSql, updateParam),
                (insertSql, insertParam)
            };

            int result = _db.ExecNonQuery(commands);

            // 5. 新しく挿入されたパターンIDを取得
            if (result >= 2) // 1行更新 + 1行挿入
            {
                var latestPattern = GetLatestPattern();
                if (latestPattern != null)
                {
                    // 6. 選択された顧客データを新しいパターンIDに紐づけ
                    if (selectedCustomerIds != null && selectedCustomerIds.Count > 0)
                    {
                        foreach (var customerId in selectedCustomerIds)
                        {
                            string sqlCustomer = @"
                                INSERT INTO dbo.送信パターンマスタ_顧客(
                                    送信パターンID,
                                    管理番号,
                                    登録日時,
                                    登録ユーザ
                                )
                                VALUES(
                                    @PatternId,
                                    @ManagementNumber,
                                    @CreatedDate,
                                    @CreatedBy
                                )";

                            var paramCustomer = new Dictionary<string, object>
                            {
                                {"@PatternId", latestPattern.PatternId},
                                {"@ManagementNumber", customerId},
                                {"@CreatedDate", DateTime.Now},
                                {"@CreatedBy", Environment.UserName}
                            };

                            _db.ExecNonQuery(sqlCustomer, paramCustomer);
                        }
                    }
                    return latestPattern.PatternId;
                }
                return -1;
            }

            return -1;
        }

        /// <summary>
        /// 最後に挿入されたパターンを取得
        /// </summary>
        public PatternMasterDto GetLatestPattern()
        {
            string sql = @"
                SELECT TOP 1 *
                FROM dbo.送信パターンマスタ
                WHERE 削除フラグ = @IsDeleted
                ORDER BY 送信パターンID DESC";

            var param = new Dictionary<string, object>
            {
                {"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED}
            };

            DataTable dt = _db.ExecQuery(sql, param);
            if (dt.Rows.Count == 0) return null;

            var row = dt.Rows[0];
            return new PatternMasterDto
            {
                PatternId = row.Field<int>("送信パターンID"),
                PatternName = row.Field<string>("送信パターン名称"),
                TemplateText = row.Field<string>("定型文"),
                Target = row.Field<int>("送信対象"),
                SortOrder = row.Field<int>("並び順"),
                IsUnused = row.Field<bool>("未使用フラグ"),
                IsDeleted = row.Field<bool>("削除フラグ"),
                CreatedDate = row.Field<DateTime?>("登録日時"),
                CreatedBy = row.Field<string>("登録ユーザ"),
                UpdatedDate = row.Field<DateTime?>("更新日時"),
                UpdateBy = row.Field<string>("更新ユーザ")
            };
        }

        /// <summary>
        /// 指定IDの送信パターンを取得
        /// </summary>
        public PatternMasterDto GetById(int patternId)
        {
            string sql = @"
                SELECT *
                FROM dbo.送信パターンマスタ
                WHERE 送信パターンID = @PatternId
                  AND 削除フラグ = @IsDeleted";

            var param = new Dictionary<string, object>
            {
                {"@PatternId", patternId},
                {"@IsDeleted", Constants.DELETE_FLAG.NON_DELETED}
            };

            DataTable dt = _db.ExecQuery(sql, param);
            if (dt.Rows.Count == 0) return null;

            var row = dt.Rows[0];
            return new PatternMasterDto
            {
                PatternId = row.Field<int>("送信パターンID"),
                PatternName = row.Field<string>("送信パターン名称"),
                TemplateText = row.Field<string>("定型文"),
                Target = row.Field<int>("送信対象"),
                SortOrder = row.Field<int>("並び順"),
                IsUnused = row.Field<bool>("未使用フラグ"),
                IsDeleted = row.Field<bool>("削除フラグ"),
                CreatedDate = row.Field<DateTime?>("登録日時"),
                CreatedBy = row.Field<string>("登録ユーザ"),
                UpdatedDate = row.Field<DateTime?>("更新日時"),
                UpdateBy = row.Field<string>("更新ユーザ"),
                Customers = _customerDao.GetCustomersByPatternId(patternId)
            };
        }
    }
}