package sqlite

import (
	"context"
	"database/sql"
	"time"

	"switchboard-core/models"
	"switchboard-core/store"
)

// ── ResourceGrantStore ──────────────────────

type ResourceGrantStore struct{}

func NewResourceGrantStore() *ResourceGrantStore { return &ResourceGrantStore{} }

// Set creates or replaces the grant for a resource (upsert on unique resource_type+resource_id).
func (s *ResourceGrantStore) Set(ctx context.Context, grant *models.ResourceGrant) error {
	groups := grant.GrantedGroups
	if groups == nil {
		groups = []string{}
	}
	grant.ID = store.NewID()
	now := time.Now().UTC()
	_, err := DB.ExecContext(ctx, `
		INSERT INTO resource_grants (id, resource_type, resource_id, grant_scope, granted_groups, created_by, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT (resource_type, resource_id) DO UPDATE SET
			grant_scope    = excluded.grant_scope,
			granted_groups = excluded.granted_groups,
			created_by     = excluded.created_by,
			updated_at     = excluded.updated_at`,
		grant.ID, grant.ResourceType, grant.ResourceID, grant.GrantScope,
		ArrayToJSON(groups), grant.CreatedBy,
		now.Format(timeFmt), now.Format(timeFmt),
	)
	if err != nil {
		return err
	}
	// Fetch actual ID + timestamps (upsert may have returned existing row)
	grant.CreatedAt = now
	grant.UpdatedAt = now
	return DB.QueryRowContext(ctx,
		`SELECT id FROM resource_grants WHERE resource_type = ? AND resource_id = ?`,
		grant.ResourceType, grant.ResourceID,
	).Scan(&grant.ID)
}

// Get retrieves the grant for a specific resource.
func (s *ResourceGrantStore) Get(ctx context.Context, resourceType, resourceID string) (*models.ResourceGrant, error) {
	var rg models.ResourceGrant
	var groupsJSON string
	err := DB.QueryRowContext(ctx, `
		SELECT id, resource_type, resource_id, grant_scope, granted_groups, created_by,
		       created_at, updated_at
		FROM resource_grants
		WHERE resource_type = ? AND resource_id = ?`,
		resourceType, resourceID,
	).Scan(&rg.ID, &rg.ResourceType, &rg.ResourceID, &rg.GrantScope,
		&groupsJSON, &rg.CreatedBy,
		st(&rg.CreatedAt), st(&rg.UpdatedAt))
	if err != nil {
		return nil, err
	}
	rg.GrantedGroups = ScanArray(groupsJSON)
	return &rg, nil
}

// Delete removes the grant for a resource.
func (s *ResourceGrantStore) Delete(ctx context.Context, resourceType, resourceID string) error {
	res, err := DB.ExecContext(ctx,
		"DELETE FROM resource_grants WHERE resource_type = ? AND resource_id = ?",
		resourceType, resourceID)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// UserHasGroupAccess checks whether a user has group-based access to a resource.
// SQLite: uses json_each() to expand the JSON array for group membership checks.
func (s *ResourceGrantStore) UserHasGroupAccess(ctx context.Context, userID, resourceType, resourceID string) (bool, error) {
	var has bool
	err := DB.QueryRowContext(ctx, `
		SELECT EXISTS(
			SELECT 1 FROM resource_grants rg
			WHERE rg.resource_type = ?
			  AND rg.resource_id   = ?
			  AND (
				rg.grant_scope = 'global'
				OR (
					rg.grant_scope = 'groups'
					AND EXISTS (
						SELECT 1 FROM group_members gm
						JOIN json_each(rg.granted_groups) je ON je.value = gm.group_id
						WHERE gm.user_id = ?
					)
				)
			  )
		)`, resourceType, resourceID, userID).Scan(&has)
	return has, err
}
