package storage

import (
	"database/sql"
	"fmt"
	"time"

	"github.com/google/uuid"
)

// RoleConfigRow is one versioned row in the role_configs table. ConfigJSON
// holds the raw JSON-encoded role.RoleConfig; storage intentionally does not
// import internal/role (no need to — it never inspects the payload, only
// stores/retrieves it), so callers (internal/executor, internal/scheduler,
// internal/api) decode it themselves via encoding/json.
type RoleConfigRow struct {
	ID          string
	Role        string
	Version     int
	Status      string // "draft" | "active" | "retired"
	ConfigJSON  string
	CreatedAt   time.Time
	ActivatedAt *time.Time
	RetiredAt   *time.Time
	ProposedBy  string
}

// CreateRoleConfig inserts a new draft version for role, auto-assigning the
// next version number for that role (1 if none exist yet).
func (s *DB) CreateRoleConfig(roleName, configJSON, proposedBy string) (*RoleConfigRow, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback() //nolint:errcheck

	var maxVersion sql.NullInt64
	if err := tx.QueryRow(`SELECT MAX(version) FROM role_configs WHERE role = ?`, roleName).Scan(&maxVersion); err != nil {
		return nil, err
	}
	version := int(maxVersion.Int64) + 1

	id := uuid.NewString()
	now := time.Now().UTC()
	if _, err := tx.Exec(`
		INSERT INTO role_configs (id, role, version, status, config_json, created_at, proposed_by)
		VALUES (?, ?, ?, 'draft', ?, ?, ?)`,
		id, roleName, version, configJSON, now, proposedBy,
	); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}

	return &RoleConfigRow{
		ID:         id,
		Role:       roleName,
		Version:    version,
		Status:     "draft",
		ConfigJSON: configJSON,
		CreatedAt:  now,
		ProposedBy: proposedBy,
	}, nil
}

// GetActiveRoleConfig returns the currently active role_configs row for
// role. Returns sql.ErrNoRows (unwrapped, matching GetTask/GetProject
// convention in this package) if no version is active.
func (s *DB) GetActiveRoleConfig(roleName string) (*RoleConfigRow, error) {
	row := s.db.QueryRow(`
		SELECT id, role, version, status, config_json, created_at, activated_at, retired_at, proposed_by
		FROM role_configs WHERE role = ? AND status = 'active' LIMIT 1`, roleName)
	return scanRoleConfigRow(row)
}

// ListRoleConfigVersions returns all versions for role, oldest first.
func (s *DB) ListRoleConfigVersions(roleName string) ([]*RoleConfigRow, error) {
	rows, err := s.db.Query(`
		SELECT id, role, version, status, config_json, created_at, activated_at, retired_at, proposed_by
		FROM role_configs WHERE role = ? ORDER BY version ASC`, roleName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*RoleConfigRow
	for rows.Next() {
		r, err := scanRoleConfigRow(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ActivateRoleConfigVersion promotes the given version of role to active,
// atomically retiring whatever version is currently active for that role (if
// any) in the same transaction. This enforces "at most one active row per
// role" without a DB-level constraint, the same way UpdateTaskState enforces
// the task state machine in a transaction rather than in schema.
func (s *DB) ActivateRoleConfigVersion(roleName string, version int) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint:errcheck

	var exists int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM role_configs WHERE role = ? AND version = ?`, roleName, version).Scan(&exists); err != nil {
		return err
	}
	if exists == 0 {
		return fmt.Errorf("role %q version %d not found", roleName, version)
	}

	now := time.Now().UTC()
	if _, err := tx.Exec(`UPDATE role_configs SET status = 'retired', retired_at = ? WHERE role = ? AND status = 'active'`, now, roleName); err != nil {
		return err
	}
	if _, err := tx.Exec(`UPDATE role_configs SET status = 'active', activated_at = ? WHERE role = ? AND version = ?`, now, roleName, version); err != nil {
		return err
	}
	return tx.Commit()
}

// ListRoleNames returns every distinct role name that has at least one
// role_configs row, alphabetically. There is no other way to discover which
// roles exist short of guessing names — this backs GET /api/roles, the
// "which roles exist" entry point the role/config management panel needs.
func (s *DB) ListRoleNames() ([]string, error) {
	rows, err := s.db.Query(`SELECT DISTINCT role FROM role_configs ORDER BY role ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := []string{}
	for rows.Next() {
		var r string
		if err := rows.Scan(&r); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func scanRoleConfigRow(row scanner) (*RoleConfigRow, error) {
	var r RoleConfigRow
	var createdAt time.Time
	var activatedAt, retiredAt sql.NullTime
	var proposedBy sql.NullString
	if err := row.Scan(&r.ID, &r.Role, &r.Version, &r.Status, &r.ConfigJSON, &createdAt, &activatedAt, &retiredAt, &proposedBy); err != nil {
		return nil, err
	}
	r.CreatedAt = createdAt
	if activatedAt.Valid {
		t := activatedAt.Time
		r.ActivatedAt = &t
	}
	if retiredAt.Valid {
		t := retiredAt.Time
		r.RetiredAt = &t
	}
	r.ProposedBy = proposedBy.String
	return &r, nil
}