package bootstrap

import (
	"fmt"
	"log/slog"

	"gorm.io/gorm"
)

const sanitizeLegacyUserMetadataSQL = `
update users
set metadata = metadata - 'hanmacFamily' - 'userType',
    updated_at = now()
where metadata ? 'hanmacFamily'
   or metadata ? 'userType'
`

const canonicalizeUserAppointmentTenantsSQL = `
with normalized as (
    select
        u.id,
        jsonb_agg(
            case
                when jsonb_typeof(item.value) = 'object' and t.id is not null then
                    item.value || jsonb_build_object(
                        'tenantId', t.id::text,
                        'tenantSlug', t.slug,
                        'tenantName', t.name
                    )
                else item.value
            end
            order by item.ordinality
        ) as appointments
    from users u
    cross join lateral jsonb_array_elements(u.metadata -> 'additionalAppointments') with ordinality as item(value, ordinality)
    left join tenants t on t.id::text = item.value ->> 'tenantId'
        and t.deleted_at is null
    where jsonb_typeof(u.metadata -> 'additionalAppointments') = 'array'
    group by u.id
),
changed as (
    select u.id, normalized.appointments
    from users u
    join normalized on normalized.id = u.id
    where u.metadata -> 'additionalAppointments' is distinct from normalized.appointments
)
update users u
set metadata = jsonb_set(u.metadata, '{additionalAppointments}', changed.appointments, true),
    updated_at = now()
from changed
where changed.id = u.id
`

// SanitizeLegacyUserMetadata removes legacy UI classification flags from Baron user metadata.
func SanitizeLegacyUserMetadata(db *gorm.DB) error {
	if db == nil {
		return fmt.Errorf("database is not configured")
	}
	if !db.Migrator().HasTable("users") {
		slog.Info("[Bootstrap] Legacy user metadata sanitize skipped because users table does not exist")
		return nil
	}

	result := db.Exec(sanitizeLegacyUserMetadataSQL)
	if result.Error != nil {
		return fmt.Errorf("sanitize legacy user metadata: %w", result.Error)
	}
	slog.Info("[Bootstrap] Legacy user metadata sanitized", "rowsAffected", result.RowsAffected)
	return nil
}

// CanonicalizeUserAppointmentTenants rewrites appointment display fields from the tenant UUID source.
func CanonicalizeUserAppointmentTenants(db *gorm.DB) error {
	if db == nil {
		return fmt.Errorf("database is not configured")
	}
	if !db.Migrator().HasTable("users") || !db.Migrator().HasTable("tenants") {
		slog.Info("[Bootstrap] User appointment tenant canonicalization skipped because required tables do not exist")
		return nil
	}

	result := db.Exec(canonicalizeUserAppointmentTenantsSQL)
	if result.Error != nil {
		return fmt.Errorf("canonicalize user appointment tenants: %w", result.Error)
	}
	slog.Info("[Bootstrap] User appointment tenant metadata canonicalized", "rowsAffected", result.RowsAffected)
	return nil
}