package models

import (
	"database/sql"
	"fmt"
	"log"
	"time"
	"xugu_license/internal/global"
)

type OALicenseInfo struct {
	LicInfo     TargetOALicenseInfo
	GenrateInfo LicenseGenerateInfo
}

type TargetOALicenseInfo struct {
	ID                     sql.NullInt32  `json:"id"` // 自增主键，从1开始，每次递增1
	OAId                   sql.NullInt64  `json:"OA_Id"`
	UniqueID               sql.NullString `json:"unique_id"`                 // 每行数据的唯一值ID，理论上有oa的REQUESTID就可以了
	OARequestID            sql.NullInt64  `json:"oa_request_id"`             // oa里的申请单请求ID，假设唯一标识
	OARequestName          sql.NullString `json:"oa_request_name"`           // 请求名称
	OARequestNameNew       sql.NullString `json:"oa_request_name_new"`       // 新请求名称
	OARequestNameHTMLNew   sql.NullString `json:"oa_request_name_html_new"`  // 新请求名称（HTML格式）
	OAGLXMID               sql.NullInt64  `json:"oa_glxm_id"`                // 关联项目ID
	OAGLXMName             sql.NullString `json:"oa_glxm_name"`              // 关联项目
	OASQSJ                 sql.NullString `json:"oa_sqsj"`                   // 申请时间
	OASalespersonName      sql.NullString `json:"oa_salesperson_name"`       // 销售人员名称
	OAXSJSYX               sql.NullString `json:"oa_xsjsyx"`                 // 销售邮箱
	OAOperationsPersonName sql.NullString `json:"oa_operations_person_name"` // 运维人员名称
	OAJFJSYX               sql.NullString `json:"oa_jfjsyx"`                 // 运维邮箱
	OASYDW                 sql.NullString `json:"oa_sydw"`                   // 使用单位
	OAXMXXMS               sql.NullString `json:"oa_xmxxms"`                 // 项目详细描述
	OAJDS                  sql.NullInt64  `json:"oa_jds"`                    // 节点数
	OANodeCount            sql.NullInt64  `json:"oa_node_count"`             // 总节点数
	OAProductCode          sql.NullString `json:"oa_product_code"`           // 产品编号
	OAProductName          sql.NullString `json:"oa_product_name"`           // 产品名称
	OAProductVersion       sql.NullString `json:"oa_product_version"`        // 产品版本
	OACPU                  sql.NullString `json:"oa_cpu"`                    // CPU 信息
	OAOperatingSystem      sql.NullString `json:"oa_operating_system"`       // 操作系统信息
	OAMainMAC              sql.NullString `json:"oa_main_mac"`               // 主 MAC 地址
	OASecondMAC            sql.NullString `json:"oa_second_mac"`             // 副 MAC 地址
	OACreationDate         sql.NullString `json:"oa_creation_date"`          // 创建日期
	OACreationTime         sql.NullString `json:"oa_creation_time"`          // 创建时间
	OALastOperateDate      sql.NullString `json:"oa_last_operate_date"`      // 最后操作日期
	OALastOperateTime      sql.NullString `json:"oa_last_operate_time"`      // 最后操作时间
	CaptureTime            sql.NullTime   `json:"capture_time"`              // 抓取时间，用于记录数据抓取的时间
	DelTime                sql.NullTime   `json:"del_Time"`                  //该行的删除时间
	LastOperateTime        sql.NullTime   `json:"LAST_OPERATE_TIME"`         // 该表的行最后操作时间，非oa表中的最后操作时间'
}

type LicenseGenerateInfo struct {
	ID              sql.NullInt32  `json:"id"` // 自增主键，从1开始，每次递增1
	OAId            sql.NullInt32  `json:"OA_Id"`
	LicenseUniqueID sql.NullString `json:"license_unique_id"` // LicenseApplication表的唯一值ID
	LicenseFlage    sql.NullString `json:"license_flage"`     // License分发状态
	Lic1            sql.NullString `json:"lic1"`              // 主License
	Lic2            sql.NullString `json:"lic2"`              // 副License
	CreatorGenerate sql.NullTime   `json:"creator_generate"`  // 生成时间
}

// LicenseRecord 包含 LicenseRecordToUser 和 LicenseRecordToEmails 两个结构体
type LicenseRecord struct {
	LicenseRecordToUser   []LicenseRecordToUser   `json:"license_record_to_user"`
	LicenseRecordToEmails []LicenseRecordToEmails `json:"license_record_to_emails"`
}

type LicenseRecordToUser struct {
	ID               int       `json:"id"`
	OARequestId      int       `json:"oa_request_id"`
	LicenseUniqueID  string    `json:"license_unique_id"`
	UserUniqueID     string    `json:"user_unique_id"`
	UserAccount      string    `json:"user_account"`
	OperatorUniqueID string    `json:"operator_unique_id"`
	UpTime           time.Time `json:"up_time"`
	DelTime          time.Time `json:"del_time"` // 删除或失效的时间
}

type LicenseRecordToEmails struct {
	ID               int       `json:"id"`
	OARequestId      int       `json:"oa_request_id"`
	LicenseUniqueID  string    `json:"license_unique_id"`
	Emails           string    `json:"emails"`
	OperatorUniqueID string    `json:"operator_unique_id"`
	UpTime           time.Time `json:"up_time"`
	DelTime          time.Time `json:"del_time"` // 删除或失效的时间
}

// 获取多个或单个license信息，或者指定某个用户能查询到的所有license信息
func GetOALicenseInfo(page int, pageSize int, permission string, licUniqueID string, userUniqueID string, OA_REQUESTID string) (*[]OALicenseInfo, int, error) {
	//offset := (page - 1) * pageSize
	// 查询总记录数
	// var total int
	// err := global.XuguDB.QueryRow(`SELECT COUNT(*) FROM target_OA_license WHERE del_Time IS NULL`).Scan(&total)
	// if err != nil {
	// 	return nil, 0, fmt.Errorf("GetOALicenseInfocount query error: %v", err)
	// }

	switch {
	// 根据LicUniqueID查询指定页的数据
	case licUniqueID != "":
		data, err := getSingleLicInfoByUniqueID(licUniqueID)
		if err != nil {
			return nil, 0, fmt.Errorf("getOAlicInfoToUniqueID query error: %v", err)
		}

		return &[]OALicenseInfo{*data}, 1, nil

	// 根据userUniqueID查询指定页的数据 未完成
	case userUniqueID != "":
		data, err := getLicInfoByUserUniqueID(userUniqueID, pageSize, page)
		if err != nil {
			return nil, 0, fmt.Errorf("getOAlicInfoToUniqueID query error: %v", err)
		}

		return data, 1, nil
	//case permission == "read_license":
	//读取所有的lic信息
	case permission == "read_all_license":
		data, total, err := getAllLicInfo(userUniqueID, pageSize, page)
		if err != nil {
			return nil, 0, fmt.Errorf("getOAlicInfoToUniqueID query error: %v", err)
		}
		return data, total, nil
		//读取所有
	case OA_REQUESTID != "":
		data, err := getLicInfoByOA_REQUESTID(OA_REQUESTID)
		if err != nil {
			return nil, 0, fmt.Errorf("getOAlicInfoToUniqueID query error: %v", err)
		}
		return data, len(*data), nil
	}

	return nil, 0, fmt.Errorf("getOAlicInfoToUniqueID 没有指定查询条件")

}

func getAllLicInfo(userUniqueID string, limit int, offset int) (*[]OALicenseInfo, int, error) {
	fmt.Println("getAllLicInfo", limit, offset)
	total := 0
	err := global.XuguDB.QueryRow(`
SELECT COUNT(*) from (SELECT OA_REQUESTID AS RequestCount
FROM TARGET_OA_LICENSE
where  Del_Time IS NULL
GROUP BY OA_REQUESTID 
)
HAVING COUNT(*) > 1;`).Scan(&total)
	if err != nil {
		return nil, 0, fmt.Errorf("count query error: %v", err)
	}

	sql := `
						SELECT *
FROM SYSDBA.TARGET_OA_LICENSE  tl 
INNER JOIN 
			License_generate_Info li
		ON 
			tl.Unique_ID = li.License_UniqueID
		WHERE OA_REQUESTID  IN(
		SELECT OA_REQUESTID FROM (
		SELECT OA_REQUESTID ,rownum AS a FROM (SELECT DISTINCT OA_REQUESTID  FROM TARGET_OA_LICENSE) a) s
								WHERE a BETWEEN ? AND ?)
		AND tl.del_Time IS NULL

`
	rows, err := global.XuguDB.Query(sql, limit, offset)
	if err != nil {
		global.Logger.Errorln(" getAllLicInfo rows not found ", err.Error())
		return nil, 0, fmt.Errorf("getAllLicInfo query error: %v", err)
	}
	defer rows.Close()

	var results []OALicenseInfo
	for rows.Next() {
		var info OALicenseInfo
		err := rows.Scan(
			&info.LicInfo.ID, &info.LicInfo.UniqueID, &info.LicInfo.OAId, &info.LicInfo.OARequestID,
			&info.LicInfo.OARequestName, &info.LicInfo.OARequestNameNew, &info.LicInfo.OARequestNameHTMLNew,
			&info.LicInfo.OAGLXMID, &info.LicInfo.OAGLXMName,
			&info.LicInfo.OASQSJ, &info.LicInfo.OASalespersonName, &info.LicInfo.OAXSJSYX,
			&info.LicInfo.OAOperationsPersonName, &info.LicInfo.OAJFJSYX, &info.LicInfo.OASYDW,
			&info.LicInfo.OAXMXXMS, &info.LicInfo.OAJDS, &info.LicInfo.OANodeCount,
			&info.LicInfo.OAProductCode, &info.LicInfo.OAProductName, &info.LicInfo.OAProductVersion,
			&info.LicInfo.OACPU, &info.LicInfo.OAOperatingSystem, &info.LicInfo.OAMainMAC,
			&info.LicInfo.OASecondMAC, &info.LicInfo.OACreationDate, &info.LicInfo.OACreationTime,
			&info.LicInfo.OALastOperateDate, &info.LicInfo.OALastOperateTime, &info.LicInfo.CaptureTime,
			&info.LicInfo.DelTime, &info.LicInfo.LastOperateTime,
			&info.GenrateInfo.ID, &info.GenrateInfo.OAId, &info.GenrateInfo.LicenseUniqueID, &info.GenrateInfo.LicenseFlage,
			&info.GenrateInfo.Lic1, &info.GenrateInfo.Lic2, &info.GenrateInfo.CreatorGenerate,
		)
		if err != nil {
			global.Logger.Errorln(" getAllLicInfo scan error: ", err.Error())
			return nil, 0, fmt.Errorf("getAllLicInfo scan error: %v", err)
		}
		results = append(results, info)
	}

	// 打印结果或进一步处理
	// for _, result := range results {
	// 	fmt.Println(result)
	// }
	return &results, total, nil
}

func getSingleLicInfoByUniqueID(unique_ID string) (*OALicenseInfo, error) {
	sql := `
	SELECT 
		la.ID, la.Unique_ID, la.OA_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
		la.OA_REQUESTNAMEHTMLNEW, la.OA_GLXMID ,la.OA_GLXMNAME ,la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
		la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
		la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
		la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
		la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
		la.del_Time, la.LAST_OPERATE_TIME,
		li.ID AS License_ID, li.OA_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
	FROM 
		target_OA_license la
	INNER JOIN 
		License_generate_Info li
	ON 
		la.Unique_ID = li.License_UniqueID
	WHERE 
		la.del_Time IS NULL
	AND 
		la.Unique_ID = ?`

	rows, err := global.XuguDB.Query(sql, unique_ID)
	if err != nil {
		global.Logger.Errorln("getOAlicInfoToUniqueID 数据查询失败： ", err.Error())
		return nil, fmt.Errorf("getOAlicInfoToUniqueID 数据查询失败: %v", err)
	}
	defer rows.Close()

	var info OALicenseInfo
	rows.Next()

	err = rows.Scan(

		&info.LicInfo.ID, &info.LicInfo.UniqueID, &info.LicInfo.OAId, &info.LicInfo.OARequestID,
		&info.LicInfo.OARequestName, &info.LicInfo.OARequestNameNew, &info.LicInfo.OARequestNameHTMLNew,
		&info.LicInfo.OAGLXMID, &info.LicInfo.OAGLXMName,
		&info.LicInfo.OASQSJ, &info.LicInfo.OASalespersonName, &info.LicInfo.OAXSJSYX,
		&info.LicInfo.OAOperationsPersonName, &info.LicInfo.OAJFJSYX, &info.LicInfo.OASYDW,
		&info.LicInfo.OAXMXXMS, &info.LicInfo.OAJDS, &info.LicInfo.OANodeCount,
		&info.LicInfo.OAProductCode, &info.LicInfo.OAProductName, &info.LicInfo.OAProductVersion,
		&info.LicInfo.OACPU, &info.LicInfo.OAOperatingSystem, &info.LicInfo.OAMainMAC,
		&info.LicInfo.OASecondMAC, &info.LicInfo.OACreationDate, &info.LicInfo.OACreationTime,
		&info.LicInfo.OALastOperateDate, &info.LicInfo.OALastOperateTime, &info.LicInfo.CaptureTime,
		&info.LicInfo.DelTime, &info.LicInfo.LastOperateTime,
		&info.GenrateInfo.ID, &info.GenrateInfo.OAId, &info.GenrateInfo.LicenseUniqueID, &info.GenrateInfo.LicenseFlage,
		&info.GenrateInfo.Lic1, &info.GenrateInfo.Lic2, &info.GenrateInfo.CreatorGenerate,
	)
	if err != nil {
		log.Fatal(err)
	}

	return &info, nil
}

func getLicInfoByUserUniqueID(userUniqueID string, limit int, offset int) (*[]OALicenseInfo, error) {
	sql := `
	 SELECT     la.ID, la.Unique_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
            la.OA_REQUESTNAMEHTMLNEW,
			la.OA_GLXMID ,la.OA_GLXMNAME,
			la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
            la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
            la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
            la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
            la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
            la.del_Time, la.LAST_OPERATE_TIME,
            li.ID AS License_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
FROM SYSDBA.TARGET_OA_LICENSE  la 
INNER JOIN 
			License_generate_Info li
		ON 
			la.Unique_ID = li.License_UniqueID
INNER JOIN 
			LICENSERECORDTOUSER LU 
		ON
			la.UNIQUE_ID = lu.LICENSE_UNIQUEID 
				AND lu.USER_UNIQUEID = ?
				
		WHERE OA_REQUESTID IN(
			SELECT OA_REQUESTID FROM (
		SELECT OA_REQUESTID ,rownum AS a FROM (SELECT DISTINCT OA_REQUESTID FROM LICENSERECORDTOUSER) a) s
								WHERE a BETWEEN ? AND ?) 
								
		AND la.del_Time IS NULL
	

`
	rows, err := global.XuguDB.Query(sql, userUniqueID, limit, offset)
	if err != nil {
		global.Logger.Errorln(" getAllLicInfo rows not found ", err.Error())
		return nil, fmt.Errorf("getAllLicInfo query error: %v", err)
	}
	defer rows.Close()

	var results []OALicenseInfo
	for rows.Next() {
		var info OALicenseInfo
		err := rows.Scan(
			&info.LicInfo.ID, &info.LicInfo.UniqueID, &info.LicInfo.OARequestID,
			&info.LicInfo.OARequestName, &info.LicInfo.OARequestNameNew, &info.LicInfo.OARequestNameHTMLNew,
			&info.LicInfo.OAGLXMID, &info.LicInfo.OAGLXMName,
			&info.LicInfo.OASQSJ, &info.LicInfo.OASalespersonName, &info.LicInfo.OAXSJSYX,
			&info.LicInfo.OAOperationsPersonName, &info.LicInfo.OAJFJSYX, &info.LicInfo.OASYDW,
			&info.LicInfo.OAXMXXMS, &info.LicInfo.OAJDS, &info.LicInfo.OANodeCount,
			&info.LicInfo.OAProductCode, &info.LicInfo.OAProductName, &info.LicInfo.OAProductVersion,
			&info.LicInfo.OACPU, &info.LicInfo.OAOperatingSystem, &info.LicInfo.OAMainMAC,
			&info.LicInfo.OASecondMAC, &info.LicInfo.OACreationDate, &info.LicInfo.OACreationTime,
			&info.LicInfo.OALastOperateDate, &info.LicInfo.OALastOperateTime, &info.LicInfo.CaptureTime,
			&info.LicInfo.DelTime, &info.LicInfo.LastOperateTime,
			&info.GenrateInfo.ID, &info.GenrateInfo.LicenseUniqueID, &info.GenrateInfo.LicenseFlage,
			&info.GenrateInfo.Lic1, &info.GenrateInfo.Lic2, &info.GenrateInfo.CreatorGenerate,
		)
		if err != nil {
			global.Logger.Errorln(" getAllLicInfo scan error: ", err.Error())
			return nil, fmt.Errorf("getAllLicInfo scan error: %v", err)
		}
		results = append(results, info)
	}

	// 打印结果或进一步处理
	for _, result := range results {
		fmt.Println(result)
	}
	return &results, nil
}

func getLicInfoByOA_REQUESTID(unique_ID string) (*[]OALicenseInfo, error) {
	sql := `
	SELECT 
		la.ID, la.Unique_ID, la.OA_ID, la.OA_REQUESTID, la.OA_REQUESTNAME, la.OA_REQUESTNAMENEW,
		la.OA_REQUESTNAMEHTMLNEW, la.OA_GLXMID ,la.OA_GLXMNAME ,la.OA_SQSJ, la.OA_SALESPERSONNAME, la.OA_XSJSYX,
		la.OA_OPERATIONSPERSONNAME, la.OA_JFJSYX, la.OA_SYDW, la.OA_XMXXMS, la.OA_JDS,
		la.OA_NODECOUNT, la.OA_PRODUCTCODE, la.OA_PRODUCTNAME, la.OA_PRODUCTVERSION,
		la.OA_CPU, la.OA_OPERATINGSYSTEM, la.OA_MAINMAC, la.OA_SECONDMAC, la.OA_CREATIONDATE,
		la.OA_CREATIONTIME, la.OA_LASTOPERATEDATE, la.OA_LASTOPERATETIME, la.capture_Time,
		la.del_Time, la.LAST_OPERATE_TIME,
		li.ID AS License_ID, li.OA_ID, li.License_UniqueID, li.License_Flage, li.lic1, li.lic2, li.Creator_generate
	FROM 
		target_OA_license la
	INNER JOIN 
		License_generate_Info li
	ON 
		la.Unique_ID = li.License_UniqueID
	WHERE 
		la.del_Time IS NULL
	AND 
		la.OA_REQUESTID = ?`

	rows, err := global.XuguDB.Query(sql, unique_ID)
	if err != nil {
		global.Logger.Errorln("getLicInfoByOAUniqueID 数据查询失败： ", err.Error())
		return nil, fmt.Errorf("getLicInfoByOAUniqueID 数据查询失败: %v", err)
	}
	defer rows.Close()
	var rest []OALicenseInfo
	for rows.Next() {
		var info OALicenseInfo
		err = rows.Scan(
			&info.LicInfo.ID, &info.LicInfo.UniqueID, &info.LicInfo.OAId, &info.LicInfo.OARequestID,
			&info.LicInfo.OARequestName, &info.LicInfo.OARequestNameNew, &info.LicInfo.OARequestNameHTMLNew,
			&info.LicInfo.OAGLXMID, &info.LicInfo.OAGLXMName,
			&info.LicInfo.OASQSJ, &info.LicInfo.OASalespersonName, &info.LicInfo.OAXSJSYX,
			&info.LicInfo.OAOperationsPersonName, &info.LicInfo.OAJFJSYX, &info.LicInfo.OASYDW,
			&info.LicInfo.OAXMXXMS, &info.LicInfo.OAJDS, &info.LicInfo.OANodeCount,
			&info.LicInfo.OAProductCode, &info.LicInfo.OAProductName, &info.LicInfo.OAProductVersion,
			&info.LicInfo.OACPU, &info.LicInfo.OAOperatingSystem, &info.LicInfo.OAMainMAC,
			&info.LicInfo.OASecondMAC, &info.LicInfo.OACreationDate, &info.LicInfo.OACreationTime,
			&info.LicInfo.OALastOperateDate, &info.LicInfo.OALastOperateTime, &info.LicInfo.CaptureTime,
			&info.LicInfo.DelTime, &info.LicInfo.LastOperateTime,
			&info.GenrateInfo.ID, &info.GenrateInfo.OAId, &info.GenrateInfo.LicenseUniqueID, &info.GenrateInfo.LicenseFlage,
			&info.GenrateInfo.Lic1, &info.GenrateInfo.Lic2, &info.GenrateInfo.CreatorGenerate,
		)
		if err != nil {
			log.Fatal(err)
		}
		rest = append(rest, info)
	}

	return &rest, nil
}

// 插入生成好的lic1，2的信息
func UpdateOALicenseStr(uniqueID string, lic1 []byte, lic2 []byte) error {

	tx, err := global.XuguDB.Begin()
	if err != nil {
		global.Logger.Errorln("begin transaction失败： ", err.Error())
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()

	//插入 LicenseInfo 表
	if lic2 == nil {
		_, err = tx.Exec(`
UPDATE License_generate_Info SET License_Flage = ? ,lic1 = ?,Creator_generate =?  WHERE License_UniqueID = ?
	`,
			"已生成", string(lic1), time.Now(), uniqueID)
	} else {
		_, err = tx.Exec(`
UPDATE License_generate_Info SET License_Flage = ? ,lic1 = ?,lic2 = ?,Creator_generate =?   WHERE License_UniqueID = ?
	`,
			"已生成", string(lic1), string(lic2), time.Now(), uniqueID)
	}

	if err != nil {
		global.Logger.Errorln("插入lic1 ，2 失败：  ", err.Error())
		return err
	}
	return nil
}

// 修改一行前端可改的license信息
func UpdatelicenseInfoRow(license OALicenseInfo) error {
	tx, err := global.XuguDB.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()

	_, err = tx.Exec(`
		UPDATE target_OA_license SET OA_REQUESTNAME =? ,OA_CREATIONDATE =? ,OA_GLXMNAME = ? ,
		OA_SALESPERSONNAME = ? , OA_XSJSYX = ?  , OA_OPERATIONSPERSONNAME = ? , OA_JFJSYX = ? , OA_JDS = ? ,
		OA_SYDW = ? , OA_PRODUCTNAME = ? , OA_PRODUCTVERSION = ? , OA_NODECOUNT = ? 
		WHERE  Unique_ID =?;
	`, license.LicInfo.OARequestName.String, license.LicInfo.OACreationDate.String, license.LicInfo.OAGLXMName.String,
		license.LicInfo.OASalespersonName.String, license.LicInfo.OAXSJSYX.String, license.LicInfo.OAOperationsPersonName.String, license.LicInfo.OAJFJSYX.String, license.LicInfo.OAJDS.Int64,
		license.LicInfo.OASYDW.String, license.LicInfo.OAProductName.String, license.LicInfo.OAProductVersion.String, license.LicInfo.OANodeCount.Int64, license.LicInfo.UniqueID.String)
	if err != nil {
		global.Logger.Errorln("UpdatelicenseInfoRow失败 ", err.Error())
		return err
	}

	return nil
}

// 插入分发记录用户表
func InsertlicenseRecordByUserRow(oaRequestID string, LicenseUniqueID string, userUniqueID string, UserAccount string, operatorUniqueID string) error {

	tx, err := global.XuguDB.Begin()
	if err != nil {
		global.Logger.Errorln("begin transaction失败：  ", err.Error())
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()
	_, err = tx.Exec(`
	Insert into  licenseRecordToUser(OA_REQUESTID,License_UniqueID ,user_UniqueID,User_Account,up_Time,operator_UniqueID) values(?,?,?,?,?,?)`,
		oaRequestID, LicenseUniqueID, userUniqueID, UserAccount, time.Now(), operatorUniqueID)
	if err != nil {
		global.Logger.Errorln("插入分发记录用户表失败： ", err.Error())
		return err
	}
	return nil

}

// 插入分发记录邮箱表
func InsertlicenseRecordByEmailRow(oaRequestID, LicenseUniqueID string, emails string, operatorUniqueID string) error {

	tx, err := global.XuguDB.Begin()
	if err != nil {
		global.Logger.Errorln("begin transaction失败：  ", err.Error())
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()
	_, err = tx.Exec(`
	Insert into  licenseRecordToEmails(OA_REQUESTID,License_UniqueID ,emails,operator_UniqueID,up_Time) values(?,?,?,?,?)`,
		oaRequestID, LicenseUniqueID, emails, operatorUniqueID, time.Now())
	if err != nil {

		global.Logger.Errorln("插入分发记录邮箱表失败： ", err.Error())
		return err
	}
	return nil

}

// 搜索分发记录用户表
func GetlicenseRecordByUser(OA_REQUESTID int, License_UniqueID string) ([]LicenseRecordToUser, error) {
	fmt.Println("搜索分发记录表 OA_REQUESTID", OA_REQUESTID)

	if License_UniqueID == "" {
		return nil, fmt.Errorf("uniqueID is empty")

	}
	if OA_REQUESTID <= 0 {
		return nil, fmt.Errorf("OA_REQUESTID is empty or error")
	}
	sqlLicUser := `select OA_REQUESTID,License_UniqueID,user_UNIQUEID,User_Account,operator_UniqueID,up_Time from licenseRecordToUser where License_UniqueID  = ? AND  del_time IS NULL`
	rows, err := global.XuguDB.Query(sqlLicUser, License_UniqueID)
	if err != nil {
		global.Logger.Errorln("query data: ", err.Error())
		return nil, fmt.Errorf("query error: %v", err)
	}
	defer rows.Close()

	var results []LicenseRecordToUser
	for rows.Next() {
		var lRU LicenseRecordToUser

		err = rows.Scan(
			&lRU.OARequestId,
			&lRU.LicenseUniqueID,
			&lRU.UserUniqueID,
			&lRU.UserAccount,
			&lRU.OperatorUniqueID,
			&lRU.UpTime,
		)

		if err != nil {

			global.Logger.Errorln("scan row: ", err.Error())

			return nil, fmt.Errorf("scan row error: %v", err)
		}

		results = append(results, lRU)
	}
	fmt.Println("LicenseRecordToUser ", results)
	return results, nil
}

// 搜索分发记录邮箱表
func GetlicenseRecordByEmail(OA_REQUESTID int, License_UniqueID string) ([]LicenseRecordToEmails, error) {

	if License_UniqueID == "" {
		return nil, fmt.Errorf("uniqueID is empty")

	}
	if OA_REQUESTID <= 0 {
		return nil, fmt.Errorf("OA_REQUESTID is empty or error")
	}

	sqlLicEmail := `select OA_REQUESTID, License_UniqueID,emails,operator_UniqueID ,up_Time from licenseRecordToEmails  where License_UniqueID  = ? AND  del_time IS NULL;`
	rows, err := global.XuguDB.Query(sqlLicEmail, License_UniqueID)
	if err != nil {
		global.Logger.Errorln("query data: ", err.Error())
		return nil, fmt.Errorf("query error: %v", err)
	}

	var results []LicenseRecordToEmails
	for rows.Next() {
		var lRU LicenseRecordToEmails

		err = rows.Scan(
			&lRU.OARequestId,
			&lRU.LicenseUniqueID,
			&lRU.Emails,
			&lRU.OperatorUniqueID,
			&lRU.UpTime,
		)

		if err != nil {

			global.Logger.Errorln("scan row: ", err.Error())

			return nil, fmt.Errorf("scan row error: %v", err)
		}

		results = append(results, lRU)
	}

	if err = rows.Err(); err != nil {
		global.Logger.Errorln("rows error: ", err.Error())
		return nil, fmt.Errorf("rows error: %v", err)
	}

	return results, nil
}

// 删除LicenseApplication和LicenseInfo表中的数据
func DelLicenseInfoRow(uniqueID string) error {
	fmt.Println("UniqueID12313", uniqueID)
	tx, err := global.XuguDB.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()
	//	"update LicenseApplication set DelTime = ? where UniqueID = ?",
	//DELETE FROM LicenseInfo WHERE LicenseApplicationUniqueID = ?
	// _, err = tx.Exec("DELETE FROM LicenseInfo WHERE LicenseApplicationUniqueID = ?", uniqueID)
	// if err != nil {
	// 	return err
	// }

	// _, err = tx.Exec("DELETE FROM LicenseApplication WHERE UniqueID = ?", uniqueID)
	// if err != nil {
	// 	return err
	// }
	_, err = tx.Exec("UPDATE target_OA_license SET Del_Time = ?  WHERE Unique_ID = ?", time.Now(), uniqueID)
	if err != nil {
		return err
	}
	return nil
}

type SQLResult struct {
	OARequestName          sql.NullString `json:"oa_request_name"`           // 请求名称 (wr.REQUESTNAME)
	OARequestNameNew       sql.NullString `json:"oa_request_name_new"`       // 新请求名称 (wr.REQUESTNAMENEW)
	OARequestNameHTMLNew   sql.NullString `json:"oa_request_name_html_new"`  // 新请求名称（HTML格式） (wr.REQUESTNAMEHTMLNEW)
	OAGLXMID               sql.NullInt64  `json:"oa_glxm_id"`                // 关联项目ID (fm.glxm)
	OAGLXMName             sql.NullString `json:"oa_glxm_name"`              // 关联项目 (PP.NAME)
	OASQSJ                 sql.NullString `json:"oa_sqsj"`                   // 申请时间 (fm.SQSJ)
	OASalespersonName      sql.NullString `json:"oa_salesperson_name"`       // 销售人员名称 (hrm1.LASTNAME)
	OAOperationsPersonName sql.NullString `json:"oa_operations_person_name"` // 运维人员名称 (hrm2.LASTNAME)
	OAXSJSYX               sql.NullString `json:"oa_xsjsyx"`                 // 销售邮箱 (fm.XSJSYX)
	OAJFJSYX               sql.NullString `json:"oa_jfjsyx"`                 // 运维邮箱 (fm.JFJSYX)
	OASYDW                 sql.NullString `json:"oa_sydw"`                   // 使用单位 (fm.SYDW)
	OAXMXXMS               sql.NullString `json:"oa_xmxxms"`                 // 项目详细描述 (fm.XMXXMS)
	OAJDS                  sql.NullInt64  `json:"oa_jds"`                    // 节点数 (fm.JDS)
	OANodeCount            sql.NullInt64  `json:"oa_node_count"`             // 总节点数 (fmd.JDS)
	OAProductName          sql.NullString `json:"oa_product_name"`           // 产品编号 (ws1.SELECTNAME)
	OAProductVersion       sql.NullString `json:"oa_product_version"`        // 产品版本 (ws2.SELECTNAME)
	CLQ                    sql.NullString `json:"clq"`                       // cpu
	CZXT                   sql.NullString `json:"czxt"`                      // 操作系统 (fmd.CZXT)
	IP                     sql.NullString `json:"ip"`                        // IP 地址 (fmd.IP)
	MAC                    sql.NullString `json:"mac"`                       // MAC 地址 (fmd.MAC)
	OACreationDate         sql.NullString `json:"oa_creation_date"`          // 创建日期 (wr.CREATEDATE)
	OACreationTime         sql.NullString `json:"oa_creation_time"`          // 创建时间 (wr.CREATETIME)
	OALastOperateDate      sql.NullString `json:"oa_last_operate_date"`      // 最后操作日期 (wr.LASTOPERATEDATE)
	OALastOperateTime      sql.NullString `json:"oa_last_operate_time"`      // 最后操作时间 (wr.LASTOPERATETIME)
}

// 检验与oa库数据是否一致
func CheckLicenseInfoInOADB(licInfo *TargetOALicenseInfo) (bool, error) {
	//fmt.Println("该死的id", licInfo.ID)
	if !licInfo.OAId.Valid {
		return false, fmt.Errorf("uniqueID is empty")

	}

	sql := `	
		SELECT
		wr.REQUESTNAME, wr.REQUESTNAMENEW, wr.REQUESTNAMEHTMLNEW, 
		fm.glxm,PP.NAME,fm.SQSJ, hrm1.LASTNAME, 
		hrm2.LASTNAME, fm.XSJSYX, fm.JFJSYX, 
		fm.SYDW, fm.XMXXMS, fm.JDS, 
		fmd.JDS,  ws1.SELECTNAME, 
		ws2.SELECTNAME, fmd.CLQ, fmd.CZXT, 
		fmd.IP, fmd.MAC,wr.CREATEDATE, wr.CREATETIME, 
		wr.LASTOPERATEDATE, wr.LASTOPERATETIME 
	FROM XUGU.formtable_main_146 fm
	LEFT JOIN XUGU.HRMRESOURCE hrm1 ON TO_NUMBER(fm.XSRY) = hrm1.id 
	LEFT JOIN XUGU.HRMRESOURCE hrm2 ON TO_NUMBER(fm.jfry) = hrm2.id
	LEFT JOIN XUGU.FORMTABLE_MAIN_146_dt1 fmd ON fmd.mainid = fm.id 
	LEFT JOIN XUGU.WORKFLOW_SELECTITEM ws1 ON fmd.cpmc = ws1.SELECTVALUE AND ws1.FIELDID = 14627
	LEFT JOIN XUGU.WORKFLOW_SELECTITEM ws2 ON fmd.BB = ws2.SELECTVALUE AND ws2.FIELDID = 14628
	LEFT JOIN XUGU.WORKFLOW_REQUESTBASE WR ON fm.REQUESTID = WR.REQUESTID 
	LEFT JOIN XUGU.PRJ_PROJECTINFO PP ON fm.glxm = PP.ID 
where fmd.id  = ?
	PARALLEL 8;
`
	var record SQLResult
	//Wvar sqsj string
	err := global.OaDB.QueryRow(sql, licInfo.OAId.Int64).Scan(
		&record.OARequestName,
		&record.OARequestNameNew,
		&record.OARequestNameHTMLNew,
		&record.OAGLXMID,
		&record.OAGLXMName,
		&record.OASQSJ,
		&record.OASalespersonName,
		&record.OAOperationsPersonName,
		&record.OAXSJSYX,
		&record.OAJFJSYX,
		&record.OASYDW,
		&record.OAXMXXMS,
		&record.OAJDS,
		&record.OANodeCount,
		&record.OAProductName,
		&record.OAProductVersion,
		&record.CLQ,
		&record.CZXT,
		&record.IP,
		&record.MAC,
		&record.OACreationDate,
		&record.OACreationTime,
		&record.OALastOperateDate,
		&record.OALastOperateTime,
	)
	if err != nil {
		//global.Logger.Errorln("query data: ", err.Error())
		return false, fmt.Errorf("CheckLicenseInfoInOADB query error: %v", err)
	}

	// 将字符串转换为 time.Time 对象
	//record.OASQSJ.Time, err = time.Parse("2006-01-02", sqsj)

	// if err != nil {
	// 	//fmt.Println("Error parsing date:", err)
	// 	return false, fmt.Errorf("CheckLicenseInfoInOADB  将字符串转换为 time.Time  Error : %v", err)
	// }

	if isEqual := compareAndCopy(record, licInfo); !isEqual {

		return true, nil
	}

	return false, nil
}

func SearchLicInfoToDb(userInput string) ([]TargetOALicenseInfo, error) {
	query := fmt.Sprintf(`
		SELECT 
			ID, UNIQUE_ID, OA_REQUESTID, OA_REQUESTNAME, OA_REQUESTNAMENEW, OA_REQUESTNAMEHTMLNEW,
			OA_GLXMID, OA_GLXMNAME, OA_SQSJ, OA_SALESPERSONNAME, OA_XSJSYX, OA_OPERATIONSPERSONNAME,
			OA_JFJSYX, OA_SYDW, OA_XMXXMS, OA_JDS, OA_NODECOUNT, OA_PRODUCTCODE, OA_PRODUCTNAME,
			OA_PRODUCTVERSION, OA_CPU, OA_OPERATINGSYSTEM, OA_MAINMAC, OA_SECONDMAC, 
			OA_CREATIONDATE, OA_CREATIONTIME, OA_LASTOPERATEDATE, OA_LASTOPERATETIME
		FROM SYSDBA.TARGET_OA_LICENSE
		WHERE 
			UNIQUE_ID LIKE '%%%s%%' OR
			OA_REQUESTNAME LIKE '%%%s%%' OR
			OA_REQUESTNAMENEW LIKE '%%%s%%' OR
			OA_REQUESTNAMEHTMLNEW LIKE '%%%s%%' OR
			OA_GLXMNAME LIKE '%%%s%%' OR
			OA_SQSJ LIKE '%%%s%%' OR
			OA_SALESPERSONNAME LIKE '%%%s%%' OR
			OA_XSJSYX LIKE '%%%s%%' OR
			OA_OPERATIONSPERSONNAME LIKE '%%%s%%' OR
			OA_JFJSYX LIKE '%%%s%%' OR
			OA_SYDW LIKE '%%%s%%' OR
			OA_XMXXMS LIKE '%%%s%%' OR
			OA_PRODUCTCODE LIKE '%%%s%%' OR
			OA_PRODUCTNAME LIKE '%%%s%%' OR
			OA_PRODUCTVERSION LIKE '%%%s%%' OR
			OA_CPU LIKE '%%%s%%' OR
			OA_OPERATINGSYSTEM LIKE '%%%s%%' OR
			OA_MAINMAC LIKE '%%%s%%' OR
			OA_SECONDMAC LIKE '%%%s%%' OR
			OA_CREATIONDATE LIKE '%%%s%%' OR
			OA_CREATIONTIME LIKE '%%%s%%' OR
			OA_LASTOPERATEDATE LIKE '%%%s%%' OR
			OA_LASTOPERATETIME LIKE '%%%s%%';
	`, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput, userInput)

	rows, err := global.XuguDB.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 处理查询结果
	var licenses []TargetOALicenseInfo
	for rows.Next() {
		var license TargetOALicenseInfo
		err := rows.Scan(
			&license.ID, &license.UniqueID, &license.OARequestID, &license.OARequestName,
			&license.OARequestNameNew, &license.OARequestNameHTMLNew, &license.OAGLXMID,
			&license.OAGLXMName, &license.OASQSJ, &license.OASalespersonName, &license.OAXSJSYX,
			&license.OAOperationsPersonName, &license.OAJFJSYX, &license.OASYDW, &license.OAXMXXMS,
			&license.OAJDS, &license.OANodeCount, &license.OAProductCode, &license.OAProductName,
			&license.OAProductVersion, &license.OACPU, &license.OAOperatingSystem, &license.OAMainMAC,
			&license.OASecondMAC, &license.OACreationDate, &license.OACreationTime, &license.OALastOperateDate,
			&license.OALastOperateTime,
		)
		if err != nil {
			log.Fatal(err)
		}
		licenses = append(licenses, license)
	}
	if err != nil {
		log.Fatal(err)
	}

	// 检查错误
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	return licenses, nil
}

// 检测该单个license是否分配给用户
func CheckLicenseToUser(LicenseUniqueID string, userUNIQUEID string) (bool, error) {
	if LicenseUniqueID == "" || userUNIQUEID == "" {
		global.Logger.Errorln("LicenseUniqueID 或 userUNIQUEID 为空")
		return false, fmt.Errorf("LicenseUniqueID 或 userUNIQUEID 为空")
	}

	type checkLicens struct {
		check int
	}

	sql := `SELECT ID FROM licenseRecordToUser WHERE  License_UniqueID = ? AND user_UNIQUEID = ?`

	rows, err := global.XuguDB.Query(sql, LicenseUniqueID, userUNIQUEID)
	if err != nil {
		global.Logger.Errorln("query data: ", err.Error())
		return false, fmt.Errorf("query error: %v", err)
	}

	var cl checkLicens
	for rows.Next() {

		err = rows.Scan(
			&cl.check,
		)

		if err != nil {

			global.Logger.Errorln("scan row: ", err.Error())

			return false, fmt.Errorf("scan row error: %v", err)
		}

	}

	if err = rows.Err(); err != nil {
		global.Logger.Errorln("rows error: ", err.Error())
		return false, fmt.Errorf("rows error: %v", err)
	}

	fmt.Println("check: ", cl)
	if cl.check == 0 {
		return false, nil
	}
	return true, nil
}

// 按oa的申请单号来检测是否分发给用户
func CheckOaLicRequest(oaRequestID string, userUNIQUEID string) (bool, error) {
	if oaRequestID == "" || userUNIQUEID == "" {
		global.Logger.Errorln("CheckOaLicRequest : LicenseUniqueID 或 userUNIQUEID 为空")
		return false, fmt.Errorf("oaRequestID 或 userUNIQUEID 为空")
	}

	type checkLicens struct {
		check int
	}

	sql := `SELECT ID FROM licenseRecordToUser WHERE  OA_REQUESTID = ? AND user_UNIQUEID = ?`

	rows, err := global.XuguDB.Query(sql, oaRequestID, userUNIQUEID)
	if err != nil {
		global.Logger.Errorln("CheckOaLicRequest : query data: ", err.Error())
		return false, fmt.Errorf("CheckOaLicRequest : query error: %v", err)
	}

	var cl checkLicens
	for rows.Next() {

		err = rows.Scan(
			&cl.check,
		)

		if err != nil {

			global.Logger.Errorln("CheckOaLicRequest : scan row: ", err.Error())

			return false, fmt.Errorf("CheckOaLicRequest : scan row error: %v", err)
		}

	}

	if err = rows.Err(); err != nil {
		global.Logger.Errorln("CheckOaLicRequest : rows error: ", err.Error())
		return false, fmt.Errorf("CheckOaLicRequest : rows error: %v", err)
	}

	if cl.check == 0 {
		return false, nil
	}

	return true, nil
}