package layoutroutingsecurity

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

const shadowStateTableDDL = `CREATE TABLE IF NOT EXISTS _System_RuntimeSecurityStateV2Shadow (
OrgId VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
RuntimeAudience VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
SecurityEpoch DECIMAL(20,0) NOT NULL,
LayoutRoutingSecurityViewDigest VARCHAR(71) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
CreatedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (OrgId))`

const shadowAttestationTableDDL = `CREATE TABLE IF NOT EXISTS _System_LayoutRoutingSecurityAttestationV2Shadow (
OrgId VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
SecurityEpoch DECIMAL(20,0) NOT NULL,
LayoutRoutingSecurityViewDigest VARCHAR(71) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
CreatedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (OrgId, SecurityEpoch))`

const shadowViewStoreTableDDL = `CREATE TABLE IF NOT EXISTS _System_LayoutRoutingSecurityViewV2Shadow (
OrgId VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
LayoutRoutingSecurityViewDigest VARCHAR(71) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
CanonicalViewBytes LONGBLOB NOT NULL,
CreatedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (OrgId, LayoutRoutingSecurityViewDigest))`

type columnShape struct {
	name       string
	columnType string
	nullable   string
	charSet    string
	collation  string
	defaultSQL string
}

var strictStateColumns = []columnShape{
	{name: "OrgId", columnType: "varchar(255)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "RuntimeAudience", columnType: "varchar(255)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "SecurityEpoch", columnType: "decimal(20,0)", nullable: "NO"},
	{name: "LayoutRoutingSecurityViewDigest", columnType: "varchar(71)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "CreatedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
	{name: "LastModifiedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
}

var strictAttestationColumns = []columnShape{
	{name: "OrgId", columnType: "varchar(255)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "SecurityEpoch", columnType: "decimal(20,0)", nullable: "NO"},
	{name: "LayoutRoutingSecurityViewDigest", columnType: "varchar(71)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "CreatedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
	{name: "LastModifiedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
}

var strictViewStoreColumns = []columnShape{
	{name: "OrgId", columnType: "varchar(255)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "LayoutRoutingSecurityViewDigest", columnType: "varchar(71)", nullable: "NO", charSet: "utf8mb4", collation: "utf8mb4_bin"},
	{name: "CanonicalViewBytes", columnType: "longblob", nullable: "NO"},
	{name: "CreatedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
	{name: "LastModifiedDate", columnType: "datetime(6)", nullable: "NO", defaultSQL: "current_timestamp(6)"},
}

type indexColumnShape struct {
	indexName string
	nonUnique int64
	sequence  int64
	column    string
}

var strictStateIndexes = []indexColumnShape{
	{indexName: "PRIMARY", nonUnique: 0, sequence: 1, column: "OrgId"},
}

var strictAttestationIndexes = []indexColumnShape{
	{indexName: "PRIMARY", nonUnique: 0, sequence: 1, column: "OrgId"},
	{indexName: "PRIMARY", nonUnique: 0, sequence: 2, column: "SecurityEpoch"},
}

var strictViewStoreIndexes = []indexColumnShape{
	{indexName: "PRIMARY", nonUnique: 0, sequence: 1, column: "OrgId"},
	{indexName: "PRIMARY", nonUnique: 0, sequence: 2, column: "LayoutRoutingSecurityViewDigest"},
}

const columnsQuery = `SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
COLUMN_DEFAULT, EXTRA, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION`

const indexesQuery = `SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
ORDER BY INDEX_NAME, SEQ_IN_INDEX`

// CreateHaltedShadow creates only the two fixed strict-v2 shadow tables and
// returns a capability after exact structural readback of both. It is an
// explicit offline primitive, not a startup migration.
func CreateHaltedShadow(ctx context.Context, db *sql.DB) (*VerifiedTables, error) {
	if db == nil {
		return nil, databaseRequiredError()
	}
	if _, err := db.ExecContext(ctx, shadowStateTableDDL); err != nil {
		return nil, err
	}
	if _, err := db.ExecContext(ctx, shadowAttestationTableDDL); err != nil {
		return nil, err
	}
	return verifyTables(ctx, db, shadowStateTableIdentity, shadowAttestationTableIdentity)
}

// VerifyShadow returns a capability only when both halted-migration shadows
// have the complete strict-v2 shapes.
func VerifyShadow(ctx context.Context, db *sql.DB) (*VerifiedTables, error) {
	return verifyTables(ctx, db, shadowStateTableIdentity, shadowAttestationTableIdentity)
}

// VerifyLive returns a capability only when both future canonical tables are
// already the exact strict-v2 shapes. It never alters either table.
func VerifyLive(ctx context.Context, db *sql.DB) (*VerifiedTables, error) {
	return verifyTables(ctx, db, liveStateTableIdentity, liveAttestationTableIdentity)
}

// CreateHaltedShadowViewStore creates only the fixed strict-v2 canonical-view
// shadow table and returns a capability after exact structural readback. It is
// an explicit offline primitive, not a startup migration.
func CreateHaltedShadowViewStore(ctx context.Context, db *sql.DB) (*VerifiedViewStore, error) {
	if db == nil {
		return nil, databaseRequiredError()
	}
	if _, err := db.ExecContext(ctx, shadowViewStoreTableDDL); err != nil {
		return nil, err
	}
	return verifyViewStore(ctx, db, shadowViewStoreTableIdentity)
}

// VerifyShadowViewStore returns a capability only when the halted-migration
// canonical-view shadow has the complete strict-v2 shape.
func VerifyShadowViewStore(ctx context.Context, db *sql.DB) (*VerifiedViewStore, error) {
	return verifyViewStore(ctx, db, shadowViewStoreTableIdentity)
}

// VerifyLiveViewStore returns a capability only when the future canonical
// canonical-view table already has the exact strict-v2 shape. It never alters
// the table.
func VerifyLiveViewStore(ctx context.Context, db *sql.DB) (*VerifiedViewStore, error) {
	return verifyViewStore(ctx, db, liveViewStoreTableIdentity)
}

func verifyTables(
	ctx context.Context,
	db *sql.DB,
	stateIdentity *tableIdentity,
	attestationIdentity *tableIdentity,
) (*VerifiedTables, error) {
	if db == nil {
		return nil, databaseRequiredError()
	}
	if !isExactIdentityPair(stateIdentity, attestationIdentity) {
		return nil, ErrSchema
	}
	if err := verifyTableShape(ctx, db, stateIdentity.name, strictStateColumns, strictStateIndexes); err != nil {
		return nil, err
	}
	if err := verifyTableShape(ctx, db, attestationIdentity.name, strictAttestationColumns, strictAttestationIndexes); err != nil {
		return nil, err
	}
	return &VerifiedTables{
		db:          db,
		state:       stateIdentity,
		attestation: attestationIdentity,
	}, nil
}

func verifyViewStore(
	ctx context.Context,
	db *sql.DB,
	tableIdentity *tableIdentity,
) (*VerifiedViewStore, error) {
	if db == nil {
		return nil, databaseRequiredError()
	}
	if tableIdentity != liveViewStoreTableIdentity && tableIdentity != shadowViewStoreTableIdentity {
		return nil, ErrSchema
	}
	if err := verifyTableShape(ctx, db, tableIdentity.name, strictViewStoreColumns, strictViewStoreIndexes); err != nil {
		return nil, err
	}
	return &VerifiedViewStore{db: db, table: tableIdentity}, nil
}

func isExactIdentityPair(stateIdentity, attestationIdentity *tableIdentity) bool {
	return stateIdentity == liveStateTableIdentity && attestationIdentity == liveAttestationTableIdentity ||
		stateIdentity == shadowStateTableIdentity && attestationIdentity == shadowAttestationTableIdentity
}

func verifyTableShape(
	ctx context.Context,
	db *sql.DB,
	tableName string,
	wantColumns []columnShape,
	wantIndexes []indexColumnShape,
) error {
	columns, err := readColumnShapes(ctx, db, tableName)
	if err != nil {
		return err
	}
	if !reflect.DeepEqual(columns, wantColumns) {
		return fmt.Errorf(
			"%w: table %s columns = %#v, want %#v",
			ErrSchema,
			tableName,
			columns,
			wantColumns,
		)
	}
	indexes, err := readIndexShapes(ctx, db, tableName)
	if err != nil {
		return err
	}
	if !reflect.DeepEqual(indexes, wantIndexes) {
		return fmt.Errorf(
			"%w: table %s indexes = %#v, want %#v",
			ErrSchema,
			tableName,
			indexes,
			wantIndexes,
		)
	}
	return nil
}

func readColumnShapes(ctx context.Context, db *sql.DB, tableName string) ([]columnShape, error) {
	rows, err := db.QueryContext(ctx, columnsQuery, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []columnShape
	for rows.Next() {
		var shape columnShape
		var defaultValue, charSet, collation sql.NullString
		var extra string
		if err := rows.Scan(
			&shape.name,
			&shape.columnType,
			&shape.nullable,
			&defaultValue,
			&extra,
			&charSet,
			&collation,
		); err != nil {
			return nil, err
		}
		if extra != "" {
			return nil, fmt.Errorf(
				"%w: table %s column %s has extra %q",
				ErrSchema,
				tableName,
				shape.name,
				extra,
			)
		}
		if defaultValue.Valid {
			shape.defaultSQL = strings.ToLower(strings.TrimSpace(defaultValue.String))
		}
		if charSet.Valid {
			shape.charSet = charSet.String
		}
		if collation.Valid {
			shape.collation = collation.String
		}
		result = append(result, shape)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return result, nil
}

func readIndexShapes(ctx context.Context, db *sql.DB, tableName string) ([]indexColumnShape, error) {
	rows, err := db.QueryContext(ctx, indexesQuery, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []indexColumnShape
	for rows.Next() {
		var shape indexColumnShape
		var subPart sql.NullInt64
		if err := rows.Scan(
			&shape.indexName,
			&shape.nonUnique,
			&shape.sequence,
			&shape.column,
			&subPart,
		); err != nil {
			return nil, err
		}
		if subPart.Valid {
			return nil, fmt.Errorf(
				"%w: table %s index %s uses a prefix length",
				ErrSchema,
				tableName,
				shape.indexName,
			)
		}
		result = append(result, shape)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	sort.Slice(result, func(i, j int) bool {
		if result[i].indexName != result[j].indexName {
			return result[i].indexName < result[j].indexName
		}
		return result[i].sequence < result[j].sequence
	})
	return result, nil
}

func databaseRequiredError() error {
	return fmt.Errorf("layout routing security: database is required")
}