package test

import (
	"RenewCMS/internal/infrastructure/persistence/database"
	"fmt"
	"path/filepath"
	"reflect"
	"strings"
	"testing"
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type legacyArticle struct {
	gorm.Model
	ID        uint32 `gorm:"primary_key;auto_increment;not_null"`
	Title     string
	Body      string
	IsOnline  bool      `gorm:"not_null;default:false"`
	CreatedAt time.Time `gorm:"autoCreateTime"`
	UpdatedAt time.Time `gorm:"autoUpdateTime"`
}

func (legacyArticle) TableName() string { return "articles" }

type legacyImage struct {
	gorm.Model
	ID           uint32 `gorm:"primary_key;auto_increment;not_null"`
	Path         string
	Filename     string
	Size         int64
	ContentType  string
	Width        int
	Height       int
	SHA256       string `gorm:"column:sha256;index"`
	UploadedByID uint32
	CreatedAt    time.Time `gorm:"autoCreateTime"`
	UpdatedAt    time.Time `gorm:"autoUpdateTime"`
}

func (legacyImage) TableName() string { return "images" }

type legacyUser struct {
	ID                      uint32  `gorm:"primaryKey;autoIncrement"`
	Username                string  `gorm:"unique;not null"`
	Password                string  `gorm:"not null"`
	PasswordResetCode       *string `gorm:"unique"`
	Email                   string  `gorm:"unique;not null"`
	DisplayName             string  `gorm:"not null;default:''"`
	IsVerified              bool    `gorm:"default=false;not null"`
	Role                    string  `gorm:"not null;default:''"`
	VerificationCode        *string `gorm:"unique"`
	VerificationExpiration  time.Time
	PasswordResetExpiration time.Time
	TokenVersion            uint32    `gorm:"default:0;not null"`
	CreatedAt               time.Time `gorm:"autoCreateTime"`
	UpdatedAt               time.Time `gorm:"autoUpdateTime"`
}

func (legacyUser) TableName() string { return "users" }

type legacyInvite struct {
	ID         uint32 `gorm:"primaryKey;autoIncrement"`
	Email      string `gorm:"index;not null"`
	HashedCode string `gorm:"not null"`
	ExpiresAt  time.Time
	InvitedBy  uint32 `gorm:"not null"`
	AcceptedAt *time.Time
	CreatedAt  time.Time `gorm:"autoCreateTime"`
}

func (legacyInvite) TableName() string { return "invites" }

type legacyAPIKey struct {
	ID         uint32 `gorm:"primaryKey;autoIncrement"`
	UserID     uint32 `gorm:"index;not null"`
	Name       string `gorm:"not null"`
	Prefix     string `gorm:"not null"`
	HashedKey  string `gorm:"uniqueIndex;not null"`
	ExpiresAt  *time.Time
	RevokedAt  *time.Time
	LastUsedAt *time.Time
	CreatedAt  time.Time `gorm:"autoCreateTime"`
}

func (legacyAPIKey) TableName() string { return "api_keys" }

type legacyPurgeSettings struct {
	ID                       uint32 `gorm:"primaryKey"`
	AutoPurgeEnabled         bool   `gorm:"not null"`
	AutoPurgeIntervalDays    int    `gorm:"not null"`
	LastPurgeAt              *time.Time
	LastPurgeMerged          int `gorm:"not null"`
	LastPurgeArticlesUpdated int `gorm:"not null"`
	EnabledAt                *time.Time
}

func (legacyPurgeSettings) TableName() string { return "purge_settings" }

type sqliteSnapshot struct {
	schema map[string]string
	rows   map[string][]map[string]any
}

func takeSqliteSnapshot(t *testing.T, db *gorm.DB) sqliteSnapshot {
	t.Helper()
	snapshot := sqliteSnapshot{schema: map[string]string{}, rows: map[string][]map[string]any{}}
	var objects []struct{ Name, Sql string }
	if err := db.Raw("SELECT name, sql FROM sqlite_master WHERE sql IS NOT NULL ORDER BY name").Scan(&objects).Error; err != nil {
		t.Fatalf("read schema: %v", err)
	}
	for _, object := range objects {
		snapshot.schema[object.Name] = object.Sql
	}
	for _, table := range []string{"articles", "images", "users", "invites", "api_keys", "purge_settings"} {
		var rows []map[string]any
		if err := db.Raw(fmt.Sprintf("SELECT * FROM %s ORDER BY 1", table)).Scan(&rows).Error; err != nil {
			t.Fatalf("read %s: %v", table, err)
		}
		snapshot.rows[table] = rows
	}
	return snapshot
}

func seedLegacyRows(t *testing.T, db *gorm.DB) {
	t.Helper()
	at := time.Date(2026, 1, 2, 3, 4, 5, 0, time.UTC)
	reset := "reset-1"
	seeds := []any{
		&legacyArticle{Title: "First", Body: "Body", IsOnline: true},
		&legacyImage{Path: "/static/uploadedImages/a.png", Filename: "a.png", Size: 12, ContentType: "image/png", Width: 1, Height: 2, SHA256: "abc", UploadedByID: 1},
		&legacyUser{Username: "Bob", Password: "x", Email: "bob@x.example", DisplayName: "Bob", IsVerified: true, Role: "owner", PasswordResetCode: &reset, VerificationExpiration: at},
		&legacyUser{Username: "bob", Password: "y", Email: "bob2@x.example", Role: "admin"},
		&legacyInvite{Email: "new@x.example", HashedCode: "h", ExpiresAt: at, InvitedBy: 1},
		&legacyAPIKey{UserID: 1, Name: "ci", Prefix: "rcms_abc", HashedKey: "0123456789abcdef", ExpiresAt: &at},
		&legacyPurgeSettings{ID: 1, AutoPurgeEnabled: true, AutoPurgeIntervalDays: 7, LastPurgeAt: &at},
	}
	for _, seed := range seeds {
		if err := db.Create(seed).Error; err != nil {
			t.Fatalf("seed %T: %v", seed, err)
		}
	}
}

func TestSQLiteSchemaIsUntouchedByTheModelChanges(t *testing.T) {
	t.Run("SQLite schema is untouched by the model changes", func(t *testing.T) {
		path := filepath.ToSlash(filepath.Join(t.TempDir(), "legacy.db"))
		db, err := gorm.Open(sqlite.Open(database.SQLiteDSN(path)), &gorm.Config{Logger: logger.Discard})
		if err != nil {
			t.Fatalf("open: %v", err)
		}
		t.Cleanup(func() {
			if sqlDB, err := db.DB(); err == nil {
				_ = sqlDB.Close()
			}
		})
		if err := db.AutoMigrate(&legacyArticle{}, &legacyImage{}, &legacyUser{}, &legacyInvite{}, &legacyAPIKey{}, &legacyPurgeSettings{}); err != nil {
			t.Fatalf("legacy migrate: %v", err)
		}
		seedLegacyRows(t, db)
		before := takeSqliteSnapshot(t, db)

		if err := database.Migrate(db, migrationPublicURL); err != nil {
			t.Fatalf("migrate: %v", err)
		}
		after := takeSqliteSnapshot(t, db)

		for name := range after.schema {
			if isNewTableObject(name) {
				delete(after.schema, name)
			}
		}
		if _, indexed := after.schema["idx_articles_site_slug"]; !indexed {
			t.Errorf("the unique (site_id, slug) index is missing: %v", after.schema)
		}
		for _, name := range []string{"articles", "idx_articles_site_id", "idx_articles_site_slug", "idx_articles_cover_image_id", "idx_articles_social_image_id"} {
			delete(before.schema, name)
			delete(after.schema, name)
		}
		if !reflect.DeepEqual(before.schema, after.schema) {
			t.Errorf("schema changed:\nbefore %v\nafter  %v", before.schema, after.schema)
		}
		assertSiteColumnAdded(t, db)
		if after.rows["articles"][0]["slug"] != "first" {
			t.Errorf("the legacy article was not backfilled: %v", after.rows["articles"][0])
		}
		for _, column := range addedArticleColumns {
			delete(after.rows["articles"][0], column)
		}
		if !reflect.DeepEqual(before.rows, after.rows) {
			t.Errorf("rows changed:\nbefore %v\nafter  %v", before.rows, after.rows)
		}
	})
}

var addedArticleColumns = []string{"site_id", "slug", "excerpt", "seo_title", "seo_description", "cover_image_id", "cover_image_alt", "social_title", "social_description", "social_image_id", "canonical_url", "noindex", "seo_dismissed"}

func assertSiteColumnAdded(t *testing.T, db *gorm.DB) {
	t.Helper()
	var columns []string
	if err := db.Raw("SELECT name FROM pragma_table_info('articles') ORDER BY cid").Scan(&columns).Error; err != nil {
		t.Fatalf("read article columns: %v", err)
	}
	if !reflect.DeepEqual(columns[len(columns)-len(addedArticleColumns):], addedArticleColumns) {
		t.Errorf("expected the added article columns %v at the end, got %v", addedArticleColumns, columns)
	}
	for _, table := range []string{"sites", "authors", "article_authors"} {
		var found int
		if err := db.Raw("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?", table).Scan(&found).Error; err != nil || found != 1 {
			t.Errorf("expected the %s table to exist: %d %v", table, found, err)
		}
	}
}

func isNewTableObject(name string) bool {
	for _, prefix := range []string{"audit_", "idx_audit_", "image_variant", "idx_image_variant", "idx_images_path", "idx_sites_key", "sites", "authors", "idx_authors_", "article_authors", "idx_article_authors_"} {
		if strings.HasPrefix(name, prefix) {
			return true
		}
	}
	return false
}