package version

import (
	"fmt"
	"runtime"
	"strings"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	"git.ilapage.cn/ila/yovision/Sense/server/app/sense/media"
	"git.ilapage.cn/ila/yovision/Sense/server/cmd/migrate/migration"
	migrationModels "git.ilapage.cn/ila/yovision/Sense/server/cmd/migrate/migration/models"
	common "git.ilapage.cn/ila/yovision/Sense/server/common/models"
)

func init() {
	_, fileName, _, _ := runtime.Caller(0)
	migration.Migrate.SetVersion(migration.GetFilename(fileName), migrateSenseMedia)
}

func migrateSenseMedia(db *gorm.DB, version string) error {
	return db.Transaction(func(tx *gorm.DB) error {
		if err := prepareLegacyMediaRouteSchema(tx); err != nil {
			return err
		}
		if err := tx.AutoMigrate(&media.Route{}); err != nil {
			return err
		}
		page, err := ensureDeviceMenu(tx, migrationModels.SysMenu{MenuName: "SenseMedia", Title: "视频服务", Icon: "video-play", Path: "/sense/media", MenuType: "C", Permission: "sense:media:list", Component: "/sense/media/index", Sort: 7, Visible: "0", IsFrame: "1"})
		if err != nil {
			return err
		}
		reconcile, err := ensureDeviceMenu(tx, migrationModels.SysMenu{MenuName: "SenseMediaReconcile", Title: "立即对账", MenuType: "F", Action: "POST", Permission: "sense:media:reconcile", ParentId: page.MenuId, Sort: 1, Visible: "1", IsFrame: "1"})
		if err != nil {
			return err
		}
		stop, err := ensureDeviceMenu(tx, migrationModels.SysMenu{MenuName: "SenseMediaStop", Title: "停止路径", MenuType: "F", Action: "POST", Permission: "sense:media:stop", ParentId: page.MenuId, Sort: 2, Visible: "1", IsFrame: "1"})
		if err != nil {
			return err
		}
		for _, role := range []string{"implementation_operator", "site_admin"} {
			if err = attachDeviceRole(tx, role, []migrationModels.SysMenu{page, reconcile, stop}); err != nil {
				return err
			}
		}
		if err = attachDeviceRole(tx, "viewer", []migrationModels.SysMenu{page}); err != nil {
			return err
		}
		read := [][2]string{{"/api/v1/media/routes", "GET"}, {"/api/v1/media/process", "GET"}}
		write := [][2]string{{"/api/v1/media/reconcile", "POST"}, {"/api/v1/media/routes/:id/reconcile", "POST"}, {"/api/v1/media/routes/:id/stop", "POST"}}
		for _, role := range []string{"implementation_operator", "site_admin", "viewer"} {
			policies := append([][2]string{}, read...)
			if role != "viewer" {
				policies = append(policies, write...)
			}
			for _, policy := range policies {
				if err = tx.Clauses(clause.OnConflict{DoNothing: true}).Create(&deviceCasbinRule{Ptype: "p", V0: role, V1: policy[0], V2: policy[1]}).Error; err != nil {
					return err
				}
			}
		}
		return tx.Create(&common.Migration{Version: version}).Error
	})
}

type mediaPathConstraint struct {
	Name    string
	Columns string
}

// prepareLegacyMediaRouteSchema makes the old PostgreSQL table safe for GORM.
// Older Sense builds used a database-named UNIQUE(path) constraint and lacked
// the runtime-state columns now required by the Route model.
func prepareLegacyMediaRouteSchema(tx *gorm.DB) error {
	if tx.Dialector.Name() != "postgres" {
		return nil
	}
	var tableCount int64
	if err := tx.Raw(`SELECT COUNT(*)
		FROM information_schema.tables
		WHERE table_schema = current_schema()
		  AND table_name = 'sense_media_routes'`).Scan(&tableCount).Error; err != nil {
		return fmt.Errorf("inspect legacy media route table: %w", err)
	}
	if tableCount == 0 {
		return nil
	}
	if err := tx.Exec(`LOCK TABLE "sense_media_routes" IN ACCESS EXCLUSIVE MODE`).Error; err != nil {
		return fmt.Errorf("lock sense_media_routes for legacy migration: %w", err)
	}
	if err := normalizeLegacyMediaPathConstraint(tx); err != nil {
		return err
	}
	if err := initializeLegacyMediaRuntimeColumns(tx); err != nil {
		return err
	}
	return nil
}

func normalizeLegacyMediaPathConstraint(tx *gorm.DB) error {
	var constraints []mediaPathConstraint
	if err := tx.Raw(`SELECT c.conname AS name,
			(SELECT string_agg(a.attname, ',' ORDER BY key.ordinality)
			 FROM unnest(c.conkey) WITH ORDINALITY AS key(attnum, ordinality)
			 JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = key.attnum) AS columns
		FROM pg_constraint c
		JOIN pg_class t ON t.oid = c.conrelid
		JOIN pg_namespace n ON n.oid = t.relnamespace
		WHERE n.nspname = current_schema()
		  AND t.relname = 'sense_media_routes'
		  AND c.contype = 'u'
		  AND EXISTS (
			SELECT 1
			FROM unnest(c.conkey) AS key(attnum)
			JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = key.attnum
			WHERE a.attname = 'path'
		  )
		ORDER BY c.conname`).Scan(&constraints).Error; err != nil {
		return fmt.Errorf("inspect legacy media path constraints: %w", err)
	}
	if len(constraints) == 0 {
		return nil
	}
	if len(constraints) != 1 || constraints[0].Columns != "path" {
		return fmt.Errorf("sense_media_routes.path has unsupported legacy uniqueness structure; migration rolled back")
	}
	const expectedName = "uni_sense_media_routes_path"
	if constraints[0].Name == expectedName {
		return nil
	}
	var conflictingNameCount int64
	if err := tx.Raw(`SELECT COUNT(*)
		FROM pg_constraint c
		JOIN pg_class t ON t.oid = c.conrelid
		JOIN pg_namespace n ON n.oid = t.relnamespace
		WHERE n.nspname = current_schema()
		  AND t.relname = 'sense_media_routes'
		  AND c.conname = ?`, expectedName).Scan(&conflictingNameCount).Error; err != nil {
		return fmt.Errorf("inspect target media path constraint name: %w", err)
	}
	if conflictingNameCount != 0 {
		return fmt.Errorf("sense_media_routes has a conflicting target constraint name; migration rolled back")
	}
	rename := fmt.Sprintf(
		`ALTER TABLE "sense_media_routes" RENAME CONSTRAINT %s TO %s`,
		quotePostgresIdentifier(constraints[0].Name),
		quotePostgresIdentifier(expectedName),
	)
	if err := tx.Exec(rename).Error; err != nil {
		return fmt.Errorf("normalize legacy media path constraint name: %w", err)
	}
	return nil
}

func initializeLegacyMediaRuntimeColumns(tx *gorm.DB) error {
	for _, statement := range []struct {
		name string
		sql  string
	}{
		{name: "add source_ready", sql: `ALTER TABLE "sense_media_routes" ADD COLUMN IF NOT EXISTS "source_ready" boolean`},
		{name: "add failure_count", sql: `ALTER TABLE "sense_media_routes" ADD COLUMN IF NOT EXISTS "failure_count" bigint`},
		{name: "add last_error_code", sql: `ALTER TABLE "sense_media_routes" ADD COLUMN IF NOT EXISTS "last_error_code" varchar(64)`},
		{name: "initialize runtime state", sql: `UPDATE "sense_media_routes"
			SET "source_ready" = COALESCE("source_ready", false),
				"failure_count" = COALESCE("failure_count", 0),
				"last_error_code" = COALESCE("last_error_code", '')
			WHERE "source_ready" IS NULL
			   OR "failure_count" IS NULL
			   OR "last_error_code" IS NULL`},
		{name: "require source_ready", sql: `ALTER TABLE "sense_media_routes" ALTER COLUMN "source_ready" SET NOT NULL`},
		{name: "require failure_count", sql: `ALTER TABLE "sense_media_routes" ALTER COLUMN "failure_count" SET NOT NULL`},
		{name: "require last_error_code", sql: `ALTER TABLE "sense_media_routes" ALTER COLUMN "last_error_code" SET NOT NULL`},
	} {
		if err := tx.Exec(statement.sql).Error; err != nil {
			return fmt.Errorf("%s for legacy media routes: %w", statement.name, err)
		}
	}
	return nil
}

func quotePostgresIdentifier(value string) string {
	return `"` + strings.ReplaceAll(value, `"`, `""`) + `"`
}