package store

import (
	"context"
	"database/sql"
	"encoding/json"

	"atlas9.dev/c/core"
	"atlas9.dev/c/core/dbi"
	"atlas9.dev/c/core/iam"
	"atlas9.dev/c/demo/lib"
)

// SqliteAccessStore resolves a principal's permissions by joining grants with roles.
type SqliteAccessStore struct {
	db dbi.DBI
}

var _ lib.AccessStore = (*SqliteAccessStore)(nil)

func NewSqliteAccessStore(db dbi.DBI) *SqliteAccessStore {
	return &SqliteAccessStore{db: db}
}

func (s *SqliteAccessStore) Load(ctx context.Context, principalID core.ID, out *lib.Access) error {

	// Query grants with an optional join to the roles table.
	// System roles (defined in code) won't have a roles table row,
	// so we use a LEFT JOIN and resolve caps from either source.
	rows, err := s.db.Query(ctx, `
		SELECT g.tenant, g.path, g.role, g.system, r.caps
		FROM grants g
		LEFT JOIN roles r ON r.slug = g.role AND r.tenant = g.tenant
		WHERE (g.type = 'user' AND g.principal = $1)
		   OR (g.type = 'group' AND g.principal IN (
		       SELECT group_path FROM group_members WHERE user_id = $1
		   ))
	`, principalID)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var tenant core.ID
		var path core.Path
		var roleSlug string
		var system bool
		var capsJSON sql.NullString

		if err := rows.Scan(&tenant, &path, &roleSlug, &system, &capsJSON); err != nil {
			return err
		}

		var caps []iam.Cap
		if capsJSON.Valid {
			// DB role
			if err := json.Unmarshal([]byte(capsJSON.String), &caps); err != nil {
				return err
			}
		} else if systemCaps, ok := lib.AppRoles[roleSlug]; ok {
			// System role
			caps = systemCaps
		} else {
			// Grant references a role that doesn't exist anywhere; skip.
			continue
		}

		for _, cap := range caps {
			if system {
				out.GrantSystem(cap)
			} else {
				out.Grant(cap, tenant, path)
			}
		}
	}
	return rows.Err()
}