package mongodb

import (
	"WM_CMS_SERVER/config"
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"
	"time"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo/options"
)

type UserInfo struct {
	Username       string
	DisplayName    string
	CompID         string
	CompName       string
	BrID           string
	BrName         string
	WorkStatus     string
	WorkStatusDesc string
}

func CheckHRISDBData() ([]UserInfo, error) {
	connString := fmt.Sprintf("server=%s;port=%s;user id=%s;password=%s;database=%s;%s",
		config.HRISDB_SQL_DB_ADDRESS, config.HRISDB_SQL_DB_PORT, config.HRISDB_SQL_DB_USER, config.HRISDB_SQL_DB_PASSWORD, config.HRISDB_SQL_DB_NAME, "encrypt=disable")
	var err error
	sqlConn, err := sql.Open("sqlserver", connString)
	if err != nil {
		log.Printf("Failed to open SQL Server connection: %v\n", err)
		return nil, err
	}
	defer sqlConn.Close()

	// 確保 SQL 連線正常
	if err = sqlConn.Ping(); err != nil {
		log.Printf("CheckHRISDBData fail - Unable to connect to SQL Server: %s\n", connString)
		return nil, err
	}

	err = updateUserEmpShareData(sqlConn)
	if err != nil {
		log.Println("updateUserEmpShareData fail: ", err)
		return nil, err
	}

	var ssoACInfoList []UserInfo
	ssoACInfoList, err = checkEmpShareLogData(sqlConn)
	if err != nil {
		log.Println(err)
		return nil, err
	}

	return ssoACInfoList, nil
}

func checkEmpShareLogData(sqlConn *sql.DB) ([]UserInfo, error) {
	// 從 MongoDB 獲取所有用戶
	userCollection := mongodb.Collection("users")
	opts := options.Find().SetProjection(bson.M{
		"username":       1,
		"display_name":   1,
		"create_time":    1,
		"last_login":     1,
		"CompID":         1,
		"CompName":       1,
		"BrID":           1,
		"BrName":         1,
		"WorkStatus":     1,
		"account_status": 1,
	})

	// filter := bson.M{"account_status": "enabled"}
	filter := bson.M{}
	cursor, err := userCollection.Find(context.Background(), filter, opts)
	if err != nil {
		return nil, fmt.Errorf("[db.checkEmpShareData] 查詢 MongoDB users 失敗: %v", err)
	}
	defer cursor.Close(context.Background())

	// 定義用戶結構
	type User struct {
		UserId         string    `bson:"username"`
		DisplayName    string    `bson:"display_name"`
		CreateTime     time.Time `bson:"create_time"`
		LastLogin      time.Time `bson:"last_login,omitempty"`
		CompID         string    `bson:"CompID,omitempty"`
		CompName       string    `bson:"CompName,omitempty"`
		BrID           string    `bson:"BrID,omitempty"`
		BrName         string    `bson:"BrName,omitempty"`
		WorkStatus     string    `bson:"WorkStatus,omitempty"`
		WorkStatusDesc string    `bson:"WorkStatusDesc,omitempty"`
		AccountStatus  string    `bson:"account_status,omitempty"`
	}

	var users []User
	if err = cursor.All(context.Background(), &users); err != nil {
		return nil, fmt.Errorf("[db.checkEmpShareData] 解析 MongoDB 用戶數據失敗: %v", err)
	}

	// 建立用戶名清單
	var enabledUserList []string
	// var disabledUserList []string
	userInfoMap := make(map[string]User)
	for _, user := range users {
		if user.UserId != "" && user.UserId != SUPER_USER {
			switch user.AccountStatus {
			case "enabled", "":
				enabledUserList = append(enabledUserList, fmt.Sprintf("'%s'", user.UserId))
			case "disabled":
			}
			userInfoMap[user.UserId] = user
		}
	}

	// 查詢 Daily_EmpShareLog 取得 EmpID, AfterID, AfterRemark
	query := fmt.Sprintf(`
		SELECT EmpID, ChgID, AfterID, AfterRemark FROM Daily_EmpShareLog 
		WHERE LogDate = '%s' 
		AND EmpID IN (%s)`,
		time.Now().Format("2006/01/02"),
		strings.Join(enabledUserList, ","))

	rows, err := sqlConn.Query(query)
	if err != nil {
		return nil, fmt.Errorf("[db.checkEmpShareData] 查詢 Daily_EmpShareLog 失敗: %v", err)
	}
	defer rows.Close()

	// 準備回傳的 UserInfo
	ssoACInfoList := make([]UserInfo, 0)

	// 需要停用的用戶列表
	var disabledUsers []string
	for rows.Next() {
		var empID, chgID, afterID, afterRemark string
		if err := rows.Scan(&empID, &chgID, &afterID, &afterRemark); err != nil {
			return nil, fmt.Errorf("[db.checkEmpShareData] 讀取行數據失敗: %v", err)
		}

		if chgID == "01" {
			_user := userInfoMap[empID]
			if afterID == "3" {
				log.Printf("[db.checkEmpShareData] 使用者離職 - EmpID:%s, ChgID:%s, AfterID:%s, AfterRemark:%s\n", empID, chgID, afterID, afterRemark)

				_user.WorkStatus = "2"
			} else {
				_user.WorkStatus = "3"
			}

			_user.WorkStatusDesc = afterRemark
			userInfoMap[empID] = _user
			disabledUsers = append(disabledUsers, empID)

			_update := bson.M{
				"$set": bson.M{
					"WorkStatus":     _user.WorkStatus,
					"WorkStatusDesc": _user.WorkStatusDesc,
				},
			}
			_, err = userCollection.UpdateOne(context.Background(), bson.M{"username": empID}, _update)
			if err != nil {
				return nil, fmt.Errorf("[db.checkEmpShareData] 更新用戶狀態失敗: %v", err)
			}
			log.Printf("[db.checkEmpShareData] 使用者 [%s] 資料已更新:%+v\n", empID, _user)
		}
	}

	// 更新離職員工帳號狀態
	if len(disabledUsers) > 0 {
		filter := bson.M{
			"username": bson.M{"$in": disabledUsers},
		}
		update := bson.M{
			"$set": bson.M{
				"account_status": "disabled",
			},
		}
		result, err := userCollection.UpdateMany(context.Background(), filter, update)
		if err != nil {
			return nil, fmt.Errorf("[db.checkEmpShareData] 更新用戶狀態失敗: %v", err)
		}
		log.Printf("[db.checkEmpShareData] 已停用 %d 個使用者", result.ModifiedCount)
	}

	// 需要停用的用戶列表
	var expiredUsers []string
	// 檢查最近登入時間
	twoMonthsAgo := time.Now().AddDate(0, -2, 0)
	for userId, user := range userInfoMap {
		if user.UserId == "" || user.UserId == SUPER_USER {
			continue
		}
		// 檢查 last_login 或 create_time
		checkTime := user.LastLogin
		if checkTime.IsZero() {
			checkTime = user.CreateTime
		}
		if checkTime.Before(twoMonthsAgo) {
			user.WorkStatus = "3"
			user.WorkStatusDesc = "禁用"
			userInfoMap[userId] = user
			expiredUsers = append(expiredUsers, user.UserId)
			log.Printf("[db.checkEmpShareData] 禁用使用者:%s , 上次登入時間:%v\n", user.UserId, checkTime)
		}
	}

	// 更新 MongoDB
	if len(expiredUsers) > 0 {
		filter := bson.M{"username": bson.M{"$in": expiredUsers}}
		update := bson.M{
			"$set": bson.M{
				"account_status": "disabled",
				"WorkStatus":     "3",
				"WorkStatusDesc": "禁用",
			},
		}
		result, err := userCollection.UpdateMany(context.Background(), filter, update)
		if err != nil {
			return nil, fmt.Errorf("[db.checkEmpShareData] 更新用戶狀態失敗: %v", err)
		}
		log.Printf("[db.checkEmpShareData] 已停用 %d 個使用者", result.ModifiedCount)
	}

	// 準備回傳的 UserInfo
	for _, user := range userInfoMap {
		ssoACInfoList = append(ssoACInfoList, UserInfo{
			Username:       user.UserId,
			DisplayName:    user.DisplayName,
			CompID:         user.CompID,
			CompName:       user.CompName,
			BrID:           user.BrID,
			BrName:         user.BrName,
			WorkStatus:     user.WorkStatus,
			WorkStatusDesc: user.WorkStatusDesc,
		})
	}

	return ssoACInfoList, nil
}

func updateUserEmpShareData(sqlConn *sql.DB) error {
	log.Println("[db.updateUserEmpShareData] 更新使用者資料")
	userCollection := mongodb.Collection("users")

	// 查找欄位為空字串或不存在的使用者
	// filter := bson.M{
	// 	"$or": []bson.M{
	// 		{"CompID": bson.M{"$in": []interface{}{"", nil}}},
	// 		{"CompName": bson.M{"$in": []interface{}{"", nil}}},
	// 		{"BrID": bson.M{"$in": []interface{}{"", nil}}},
	// 		{"BrName": bson.M{"$in": []interface{}{"", nil}}},
	// 	},
	// }
	filter := bson.M{
		"username": bson.M{"$ne": SUPER_USER},
	}
	opts := options.Find().SetProjection(bson.M{
		"username": 1,
	})

	cursor, err := userCollection.Find(context.Background(), filter, opts)
	if err != nil {
		return fmt.Errorf("[db.updateUserEmpShareData] 查詢 MongoDB users 失敗: %v", err)
	}
	defer cursor.Close(context.Background())

	var userDocs []bson.M
	if err = cursor.All(context.Background(), &userDocs); err != nil {
		return fmt.Errorf("[db.updateUserEmpShareData] 解析 MongoDB 用戶數據失敗: %v", err)
	}

	var usernames []string
	for _, doc := range userDocs {
		if username, ok := doc["username"].(string); ok {
			if username != SUPER_USER {
				usernames = append(usernames, username)
				log.Println("[DEBUG] 需更新人員：", username)
			}
		}
	}

	log.Println("[DEBUG] 更新人員名單：", usernames)

	if len(usernames) > 0 {
		quoted := make([]string, len(usernames))
		for i, name := range usernames {
			quoted[i] = fmt.Sprintf("'%s'", name)
		}
		query := fmt.Sprintf(`
			SELECT EmpID, CompID, CompName, DeptID, DeptName, WorkStatus FROM vw_EmpShare
			WHERE EmpID IN (%s)`, strings.Join(quoted, ","))

		log.Println("[DEBUG] [db.updateUserEmpShareData] query: ", query)

		rows, err := sqlConn.Query(query)
		if err != nil {
			return fmt.Errorf("[db.updateUserEmpShareData] 查詢 vw_EmpShare 失敗: %v", err)
		}
		defer rows.Close()

		for rows.Next() {
			var empID, compID, compName, deptID, deptName, workStatus string
			if err := rows.Scan(&empID, &compID, &compName, &deptID, &deptName, &workStatus); err != nil {
				return fmt.Errorf("[db.updateUserEmpShareData] 讀取行數據失敗: %v", err)
			}

			workStatusDesc := ""
			switch workStatus {
			case "1":
				workStatusDesc = "在職"
			case "3":
				workStatusDesc = "離職"
			default:
				workStatusDesc = "其他"
			}

			// 更新回 MongoDB
			update := bson.M{
				"$set": bson.M{
					"CompID":         compID,
					"CompName":       compName,
					"BrID":           deptID,
					"BrName":         deptName,
					"WorkStatus":     workStatus,
					"WorkStatusDesc": workStatusDesc,
				},
			}

			// log.Printf("[DEBUG][db.updateUserEmpShareData] 使用者 [%s] 資料已更新\n", empID)

			_, err = userCollection.UpdateOne(context.Background(), bson.M{"username": empID}, update)
			if err != nil {
				return fmt.Errorf("[db.updateUserEmpShareData] 更新 MongoDB user %s 失敗: %v", empID, err)
			}

			log.Printf("[db.updateUserEmpShareData] 使用者 [%s] 資料已更新:%+v\n", empID, update)
		}
	}

	return nil
}
