package storage

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
)

type UserbaseSnapshot struct {
	NextUIDNumber        int64
	NextGIDNumber        int64
	Users                []UserbaseUser
	Groups               []UserbaseGroup
	Memberships          []UserbaseMembership
	GoogleLinks          []UserbaseGoogleLink
	Invitations          []UserbaseInvitation
	InvitationGroups     []UserbaseInvitationGroup
	PendingConfirmations []UserbasePendingConfirmation
	SSHKeys              []UserbaseSSHKey
}

type UserbaseUser struct {
	ID           string
	Username     string
	DisplayName  string
	PasswordHash []byte
	Disabled     bool
	UIDNumber    int64
	CreatedAt    int64
}

type UserbaseGroup struct {
	ID          string
	Name        string
	Description string
	GIDNumber   int64
	CreatedAt   int64
}

type UserbaseMembership struct {
	UserID  string
	GroupID string
}

type UserbaseGoogleLink struct {
	UserID    string
	GoogleSub string
	LinkedAt  int64
}

type UserbaseInvitation struct {
	ID        string
	TokenHash []byte
	CreatedBy *string
	CreatedAt int64
	ExpiresAt int64
	UsedBy    *string
	UsedAt    *int64
	RevokedAt *int64
	Label     string
	BatchID   *string
}

type UserbaseInvitationGroup struct {
	InvitationID string
	GroupID      *string
	GroupName    string
}

type UserbasePendingConfirmation struct {
	UserID     string
	CodeHash   []byte
	IssuedAt   int64
	ExpiresAt  int64
	WrongCodes int64
}

type UserbaseSSHKey struct {
	ID          string
	UserID      string
	Fingerprint string
	KeyType     string
	KeyData     []byte
	Label       string
	AddedAt     int64
}

type userbaseTable struct {
	name    string
	columns []string
	order   string
}

var userbaseTables = []userbaseTable{
	{"users", []string{"id", "username", "display_name", "password_hash", "disabled", "uid_number", "created_at"}, "id"},
	{"groups", []string{"id", "name", "description", "gid_number", "created_at"}, "id"},
	{"memberships", []string{"user_id", "group_id"}, "user_id, group_id"},
	{"google_links", []string{"user_id", "google_sub", "linked_at"}, "user_id"},
	{"invitations", []string{"id", "token_hash", "created_by", "created_at", "expires_at", "used_by", "used_at", "revoked_at", "label", "batch_id"}, "id"},
	{"invitation_groups", []string{"invitation_id", "group_id", "group_name"}, "invitation_id, group_name"},
	{"pending_confirmations", []string{"user_id", "code_hash", "issued_at", "expires_at", "wrong_codes"}, "user_id"},
	{"ssh_keys", []string{"id", "user_id", "fingerprint", "key_type", "key_data", "label", "added_at"}, "id"},
	{"settings", []string{"next_uid_number", "next_gid_number"}, "id"},
}

var afterUserbaseTableRead = func(string) {}

func UserbaseExportColumns() map[string][]string {
	columns := map[string][]string{}
	for _, table := range userbaseTables {
		columns[table.name] = append([]string(nil), table.columns...)
	}
	return columns
}

func (t userbaseTable) query() string {
	return `SELECT ` + strings.Join(t.columns, ", ") + ` FROM ` + t.name + ` ORDER BY ` + t.order
}

func (s *Store) ExportUserbase(ctx context.Context) (UserbaseSnapshot, error) {
	const operation = "export userbase"
	if err := s.checkOpen(operation); err != nil {
		return UserbaseSnapshot{}, err
	}
	tx, err := s.sql.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})
	if err != nil {
		return UserbaseSnapshot{}, classify("begin "+operation, err)
	}
	defer tx.Rollback()
	var snap UserbaseSnapshot
	for _, table := range userbaseTables {
		if err := readUserbaseTable(ctx, tx, table, &snap); err != nil {
			return UserbaseSnapshot{}, classify(operation+" "+table.name, err)
		}
		afterUserbaseTableRead(table.name)
	}
	if err := tx.Commit(); err != nil {
		return UserbaseSnapshot{}, classify("end "+operation, err)
	}
	return snap, nil
}

type userbaseQuerier interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

func readUserbaseTable(ctx context.Context, q userbaseQuerier, table userbaseTable, snap *UserbaseSnapshot) error {
	rows, err := q.QueryContext(ctx, table.query())
	if err != nil {
		return err
	}
	defer rows.Close()
	settings := 0
	for rows.Next() {
		switch table.name {
		case "users":
			var u UserbaseUser
			var disabled int
			if err := rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.PasswordHash, &disabled, &u.UIDNumber, &u.CreatedAt); err != nil {
				return err
			}
			u.Disabled = disabled != 0
			snap.Users = append(snap.Users, u)
		case "groups":
			var g UserbaseGroup
			if err := rows.Scan(&g.ID, &g.Name, &g.Description, &g.GIDNumber, &g.CreatedAt); err != nil {
				return err
			}
			snap.Groups = append(snap.Groups, g)
		case "memberships":
			var m UserbaseMembership
			if err := rows.Scan(&m.UserID, &m.GroupID); err != nil {
				return err
			}
			snap.Memberships = append(snap.Memberships, m)
		case "google_links":
			var l UserbaseGoogleLink
			if err := rows.Scan(&l.UserID, &l.GoogleSub, &l.LinkedAt); err != nil {
				return err
			}
			snap.GoogleLinks = append(snap.GoogleLinks, l)
		case "invitations":
			var i UserbaseInvitation
			var createdBy, usedBy, batch sql.NullString
			var usedAt, revokedAt sql.NullInt64
			if err := rows.Scan(&i.ID, &i.TokenHash, &createdBy, &i.CreatedAt, &i.ExpiresAt, &usedBy, &usedAt, &revokedAt, &i.Label, &batch); err != nil {
				return err
			}
			i.CreatedBy, i.UsedBy = nullStringPtr(createdBy), nullStringPtr(usedBy)
			i.UsedAt, i.RevokedAt = nullInt64Ptr(usedAt), nullInt64Ptr(revokedAt)
			i.BatchID = nullStringPtr(batch)
			snap.Invitations = append(snap.Invitations, i)
		case "invitation_groups":
			var g UserbaseInvitationGroup
			var groupID sql.NullString
			if err := rows.Scan(&g.InvitationID, &groupID, &g.GroupName); err != nil {
				return err
			}
			g.GroupID = nullStringPtr(groupID)
			snap.InvitationGroups = append(snap.InvitationGroups, g)
		case "pending_confirmations":
			var p UserbasePendingConfirmation
			if err := rows.Scan(&p.UserID, &p.CodeHash, &p.IssuedAt, &p.ExpiresAt, &p.WrongCodes); err != nil {
				return err
			}
			snap.PendingConfirmations = append(snap.PendingConfirmations, p)
		case "ssh_keys":
			var k UserbaseSSHKey
			if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.KeyType, &k.KeyData, &k.Label, &k.AddedAt); err != nil {
				return err
			}
			snap.SSHKeys = append(snap.SSHKeys, k)
		case "settings":
			if err := rows.Scan(&snap.NextUIDNumber, &snap.NextGIDNumber); err != nil {
				return err
			}
			settings++
		default:
			return fmt.Errorf("no reader for table %s", table.name)
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	if table.name == "settings" && settings != 1 {
		return fmt.Errorf("settings holds %d rows, want 1: %w", settings, ErrSchemaInvalid)
	}
	return nil
}

func nullStringPtr(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	v := value.String
	return &v
}

func nullInt64Ptr(value sql.NullInt64) *int64 {
	if !value.Valid {
		return nil
	}
	v := value.Int64
	return &v
}