package dal

import (
	"database/sql"
	"errors"
	"time"

	"gitee.com/lite89/cache"
	"gitee.com/lite89/wa"
	"gitee.com/lite89/ws"
)

// UserDAO represents the user DAO.
type UserDAO struct {
	db         *sql.DB
	userCache  *cache.Manager[wa.ID, wa.User]
	tokenCache *cache.Manager[wa.ID, []byte]
}

// NewUserDAO creates a new user DAO.
func NewUserDAO(config *wa.Config, db *sql.DB) (*UserDAO, error) {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS t_sys_user (
			"uid"			INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
			"gid"			INTEGER NOT NULL,
			"code"			TEXT NOT NULL CHECK(LENGTH("code")<=32),
			"name"			TEXT NOT NULL CHECK(LENGTH("name")<=64),
			"locked"		INTEGER NOT NULL,
			"auth_token"	BLOB,
			"session_token"	BLOB,
			"session_time"	INTEGER
		);
		CREATE INDEX IF NOT EXISTS i_sys_user_gid ON t_sys_user ("gid");
		CREATE UNIQUE INDEX IF NOT EXISTS i_sys_user_code ON t_sys_user ("code");
		INSERT OR IGNORE INTO t_sys_user ("uid", "gid", "code", "name", "locked") VALUES (1, 0, 'admin', '系统管理员', 0);
	`)
	cap := config.Int("SessionCacheCap", 1024)
	ttl := time.Duration(config.Int("SessionCacheTTL", 3600)) * time.Second
	return &UserDAO{
		db:         db,
		userCache:  cache.New[wa.ID, wa.User](cap, ttl),
		tokenCache: cache.New[wa.ID, []byte](cap, ttl),
	}, err
}

// Create creates a new user.
func (a *UserDAO) Create(user *wa.User) (*wa.User, error) {
	tx, err := a.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	if err := a.checkCode(tx, 0, user.Code); err != nil {
		return nil, err
	}
	if _, err := GetGroupPath(tx, user.GID); err != nil {
		return nil, err
	}

	result, err := tx.Exec(`
		INSERT INTO t_sys_user (
			"gid",
			"code",
			"name",
			"locked"
		) VALUES (
			@gid,
			@code,
			@name,
			@locked
		)`,
		sql.Named("gid", user.GID),
		sql.Named("code", user.Code),
		sql.Named("name", user.Name),
		sql.Named("locked", user.Locked))
	if err != nil {
		return nil, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}
	user.UID = wa.ID(id)
	return user, tx.Commit()
}

// Delete deletes the user.
func (a *UserDAO) Delete(uid wa.ID) error {
	a.userCache.Delete(uid)

	_, err := a.db.Exec(`
		DELETE FROM t_sys_claim WHERE "uid"=@uid;
		DELETE FROM t_sys_user WHERE "uid"=@uid;`,
		sql.Named("uid", uid))
	return err
}

// Update updates the user.
func (a *UserDAO) Update(user *wa.User) (*wa.User, error) {
	a.userCache.Delete(user.UID)

	tx, err := a.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	if err := a.checkCode(tx, user.UID, user.Code); err != nil {
		return nil, err
	}
	if _, err := GetGroupPath(tx, user.GID); err != nil {
		return nil, err
	}

	_, err = tx.Exec(`
		UPDATE t_sys_user SET
			"gid"=@gid,
			"code"=@code,
			"name"=@name,
			"locked"=@locked
		WHERE "uid"=@uid`,
		sql.Named("uid", user.UID),
		sql.Named("gid", user.GID),
		sql.Named("code", user.Code),
		sql.Named("name", user.Name),
		sql.Named("locked", user.Locked))
	if err != nil {
		return nil, err
	}
	return user, tx.Commit()
}

// Get gets the user by id.
func (a *UserDAO) Get(uid wa.ID) (*wa.User, error) {
	user, ttl := a.userCache.Load(uid)
	if ttl >= 0 {
		return &user, nil
	}

	err := a.db.QueryRow(`
		SELECT
			"uid",
			"gid",
			"code",
			"name",
			"locked"
		FROM t_sys_user
		WHERE "uid"=?`, uid).Scan(
		&user.UID,
		&user.GID,
		&user.Code,
		&user.Name,
		&user.Locked)
	if err == nil {
		a.userCache.Store(uid, user)
	} else if errors.Is(err, sql.ErrNoRows) {
		err = ws.Errorf("用户(%v)不存在", uid)
	}
	return &user, err
}

// Find finds the user by code.
func (a *UserDAO) Find(code string) (*wa.User, error) {
	var user wa.User
	err := a.db.QueryRow(`
		SELECT
			"uid",
			"gid",
			"code",
			"name",
			"locked"
		FROM t_sys_user
		WHERE "code"=?`, code).Scan(
		&user.UID,
		&user.GID,
		&user.Code,
		&user.Name,
		&user.Locked)
	if errors.Is(err, sql.ErrNoRows) {
		err = ws.Errorf("用户(%v)不存在", code)
	}
	return &user, err
}

// Query queries the users by keyword.
func (a *UserDAO) Query(gid wa.ID, key string, value string, keyword string) (users []*wa.User, err error) {
	tx, err := a.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	query := `
		SELECT
			u."uid",
			u."gid",
			u."code",
			u."name",
			u."locked"
		FROM t_sys_user u
	`
	var rows *sql.Rows
	if keyword == "" && key == "" {
		rows, err = tx.Query(query+`WHERE u."gid"=?`, gid)
		if err != nil {
			return nil, err
		}
	} else {
		path, err := GetGroupPath(tx, gid)
		if err != nil {
			return nil, err
		}
		rows, err = tx.Query(query+`
			LEFT JOIN t_sys_group g ON g."gid"=u."gid"
			LEFT JOIN (SELECT "uid", "value" FROM t_sys_claim WHERE "key"=@key) c ON c."uid"=u."uid"
			WHERE INSTR(g."path", @path)=1 AND (@key='' OR c."value"=@value) AND (@keyword='' OR u."code" LIKE @keyword OR u."name" LIKE @keyword)`,
			sql.Named("path", path),
			sql.Named("key", key),
			sql.Named("value", value),
			sql.Named("keyword", "%"+keyword+"%"))
		if err != nil {
			return nil, err
		}
	}
	defer rows.Close()

	for rows.Next() {
		var user wa.User
		err := rows.Scan(
			&user.UID,
			&user.GID,
			&user.Code,
			&user.Name,
			&user.Locked)
		if err != nil {
			return nil, err
		}
		users = append(users, &user)
	}
	return users, rows.Err()
}

// SetAuthToken sets the user auth token.
func (a *UserDAO) SetAuthToken(uid wa.ID, token []byte) error {
	_, err := a.db.Exec(`
		UPDATE t_sys_user SET
			"auth_token"=@auth_token
		WHERE "uid"=@uid`,
		sql.Named("uid", uid),
		sql.Named("auth_token", token))
	return err
}

// GetAuthToken gets the user auth token.
func (a *UserDAO) GetAuthToken(uid wa.ID) (token []byte, err error) {
	err = a.db.QueryRow(`SELECT "auth_token" FROM t_sys_user WHERE "uid"=?`, uid).Scan(&token)
	if errors.Is(err, sql.ErrNoRows) {
		err = ws.Errorf("用户(%v)不存在", uid)
	}
	return
}

// SetSessionToken sets the user session token.
func (a *UserDAO) SetSessionToken(uid wa.ID, token []byte) error {
	a.tokenCache.Delete(uid)

	_, err := a.db.Exec(`
		UPDATE t_sys_user SET
			"session_token"=@session_token,
			"session_time"=@session_time
		WHERE "uid"=@uid`,
		sql.Named("uid", uid),
		sql.Named("session_token", token),
		sql.Named("session_time", time.Now().Unix()))
	return err
}

// GetSessionToken gets the user session token.
func (a *UserDAO) GetSessionToken(uid wa.ID) (token []byte, err error) {
	token, ttl := a.tokenCache.Load(uid)
	if ttl >= 0 {
		return token, nil
	}

	err = a.db.QueryRow(`SELECT "session_token" FROM t_sys_user WHERE "uid"=?`, uid).Scan(&token)
	if err == nil {
		a.tokenCache.Store(uid, token)
	} else if errors.Is(err, sql.ErrNoRows) {
		err = ws.Errorf("用户(%v)不存在", uid)
	}
	return token, err
}

func (a *UserDAO) checkCode(tx *sql.Tx, uid wa.ID, code string) error {
	var id wa.ID
	if code == "" {
		return ws.Errorf("用户代码不能为空")
	}
	err := tx.QueryRow(`SELECT "uid" FROM t_sys_user WHERE "code"=?`, code).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return nil
	}
	if err != nil {
		return err
	}
	if uid != 0 && uid != id {
		return ws.Errorf("用户(%v)已存在", code)
	}
	return nil
}