package awssso

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"strings"
	"time"
)

const (
	mysql_user = "root"
	mysql_pass = "123456"
	mysql_port = "3306"
	database   = "security"
)

/*
连接数据库查询授予权限集的数据，返回过期的权限集，且删除已经过期的权限集条目
*/
func Connect_mysql_query() (error, []string) {
	var rets []string
	var dsn string = mysql_user + ":" + mysql_pass + "@tcp(127.0.0.1:" + mysql_port + ")/" + database + "?charset=utf8"
	conn, err := Connect_MysqlDB(dsn)
	defer conn.Close()
	if err != nil {
		return err, nil
	}

	//======================查询数据========================================
	//2024.10.17 重新迁移回老飞书，这里表名从 ntgper_new 修改为 ntgper
	res, err := conn.Query("select * from ntgper")
	if err != nil {
		return err, nil
	}
	for res.Next() {
		var account, user, pername, stime, etime, days string
		res.Scan(&account, &user, &pername, &stime, &etime, &days) //通过scan把查询到的变量的地址分配过去
		account = strings.TrimSpace(account)
		user = strings.TrimSpace(user)
		pername = strings.TrimSpace(pername)
		stime = strings.TrimSpace(stime)
		etime = strings.TrimSpace(etime)
		days = strings.TrimSpace(days)
		//cnemail = strings.TrimSpace(cnemail)
		if Check_permission_time(etime) == false { //权限到期
			rets = append(rets, etime+"|"+account+"|"+user+"|"+pername)
		}
	}

	//根据 rets 移除 ntgper 表中已经过期的权限集
	if len(rets) >= 1 {
		for _, columns := range rets {
			columns_spi := strings.Split(columns, "|")
			delstr, delstr_err := conn.Exec(`delete from ntgper where etime=? and account=? and user=? and pername=?`, columns_spi[0], columns_spi[1], columns_spi[2], columns_spi[3])
			if delstr_err != nil {
				return delstr_err, nil
			}
			_, rows_err := delstr.RowsAffected()
			if rows_err != nil {
				return rows_err, nil
			}
		}
	}
	return nil, rets
}

// 连接数据库函数，连接成功返回连接套接字，失败返回error
func Connect_MysqlDB(dsn string) (*sql.DB, error) {
	var conn *sql.DB
	var err error
	//var dsn string = "用户名:密码@tcp(IP:端口)/数据库名?charset=utf8"
	dsn = strings.TrimSpace(dsn)
	//Open函数不会与数据库建立连接，只会检测DNS语法是否正确
	conn, err = sql.Open("mysql", dsn)
	if err != nil {
		return conn, err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = conn.Ping()
	if err != nil {
		return conn, err
	}
	return conn, err
}

// 移除数据库中的列
func Remove_value_from_mysql(etime, account, user, pername string) error {
	var dsn string = mysql_user + ":" + mysql_pass + "@tcp(127.0.0.1:" + mysql_port + ")/" + database + "?charset=utf8"
	conn, err := Connect_MysqlDB(dsn)
	defer conn.Close()
	if err != nil {
		return nil
	}

	//删除数据
	delstr, delstr_err := conn.Exec(`delete from ntgper where etime=? and account=? and user=? and pername=?`, etime, account, user, pername)
	//判断语句是否执行成功
	if delstr_err != nil {
		return delstr_err
	}
	//判断是否删除成功，delstr.RowsAffected()如果没有删除数据，则返回0，如果删除了数据则返回>=1的数
	_, rows_err := delstr.RowsAffected()
	if rows_err != nil {
		return rows_err
	}
	return nil
}

/*
权限在授权时间范围内返回 true，不在授权时间范围内返回 false, 如果函数遇到错误返回 true
这里带入从数据库中查询到的 etime 字段
*/
func Check_permission_time(timestr string) bool {
	nowstr := time.Now().Format("2006-01-02 15:04:05")
	timestr = strings.TrimSpace(timestr)

	//t1 t2 为时间字符串，时间字符串转换为时间,通过Parse方法实现，2006-01-02 15:04:05是go的诞生之日，固定写法
	now_time, now_err := time.Parse("2006-01-02 15:04:05", nowstr)
	if now_err != nil {
		return true
	}

	zdstr_time, zdstr_err := time.Parse("2006-01-02 15:04:05", timestr)
	if zdstr_err != nil {
		return true
	}

	minus_time := zdstr_time.Sub(now_time).Hours()
	if minus_time <= 0 {
		return false
	} else {
		return true
	}
}