package bootstate

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
)

const listViewLegacyCutoverColumnsReadySQL = `
SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = '_System_ListView'
   AND COLUMN_NAME IN ('Id', 'OwnerId', 'Visibility')`

const listViewDanglingOwnerCountSQL = `
SELECT COUNT(*)
  FROM _System_ListView
 WHERE NULLIF(TRIM(OwnerId), '') IS NOT NULL
   AND NOT EXISTS (
       SELECT 1
         FROM ` + "`User`" + ` u
        WHERE u.Id = _System_ListView.OwnerId
   )`

const listViewOwnerForeignKeyShapeSQL = `
SELECT k.CONSTRAINT_NAME, COALESCE(rc.DELETE_RULE, '')
  FROM information_schema.KEY_COLUMN_USAGE k
  LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
    ON rc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
   AND rc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
   AND rc.TABLE_NAME = k.TABLE_NAME
 WHERE k.TABLE_SCHEMA = DATABASE()
   AND k.TABLE_NAME = '_System_ListView'
   AND k.COLUMN_NAME = 'OwnerId'
   AND k.REFERENCED_TABLE_NAME = 'User'
   AND k.REFERENCED_COLUMN_NAME = 'Id'
 LIMIT 1`

// ListViewOwnerForeignKeyShape is the live physical FK shape for
// _System_ListView.OwnerId.
type ListViewOwnerForeignKeyShape struct {
	Exists         bool
	ConstraintName string
	DeleteRule     string
}

// ListViewLegacyCutoverColumnsReady reports whether the columns required for
// the list-view ownership cutover exist. Repair code must run schema
// reconciliation before relying on this.
func ListViewLegacyCutoverColumnsReady(ctx context.Context, db *sql.DB) (bool, error) {
	var count int
	if err := db.QueryRowContext(ctx, listViewLegacyCutoverColumnsReadySQL).Scan(&count); err != nil {
		return false, fmt.Errorf("listViewLegacyCutoverColumnsReady: %w", err)
	}
	return count == 3, nil
}

// ListViewDanglingOwnerCount reports how many ownerful list-view rows point at
// missing User rows.
func ListViewDanglingOwnerCount(ctx context.Context, db *sql.DB) (int, error) {
	var remaining int
	if err := db.QueryRowContext(ctx, listViewDanglingOwnerCountSQL).Scan(&remaining); err != nil {
		return 0, err
	}
	return remaining, nil
}

// ListViewLegacyCutoverClosed reports whether no dangling ownerful list-view
// rows remain and OwnerId has the desired User cascade FK.
func ListViewLegacyCutoverClosed(ctx context.Context, db *sql.DB) (bool, error) {
	remaining, err := ListViewDanglingOwnerCount(ctx, db)
	if err != nil {
		return false, err
	}
	if remaining != 0 {
		return false, nil
	}
	return ListViewOwnerForeignKeyCascadeExists(ctx, db)
}

// ListViewOwnerForeignKeyCascadeExists reports whether _System_ListView.OwnerId
// references User(Id) with ON DELETE CASCADE.
func ListViewOwnerForeignKeyCascadeExists(ctx context.Context, db *sql.DB) (bool, error) {
	shape, err := ListViewOwnerForeignKeyShapeForDB(ctx, db)
	if err != nil {
		return false, err
	}
	return shape.Exists && strings.EqualFold(strings.TrimSpace(shape.DeleteRule), "CASCADE"), nil
}

// ListViewOwnerForeignKeyShapeForDB reads the live OwnerId FK shape for repair
// code that may need to replace an existing non-cascade constraint.
func ListViewOwnerForeignKeyShapeForDB(ctx context.Context, db *sql.DB) (ListViewOwnerForeignKeyShape, error) {
	var shape ListViewOwnerForeignKeyShape
	err := db.QueryRowContext(ctx, listViewOwnerForeignKeyShapeSQL).Scan(&shape.ConstraintName, &shape.DeleteRule)
	if err == sql.ErrNoRows {
		return shape, nil
	}
	if err != nil {
		return shape, fmt.Errorf("listViewOwnerForeignKeyShape: %w", err)
	}
	shape.Exists = true
	return shape, nil
}