package catalogsystem

import "context"

var schemaStatements = []string{
	`CREATE TABLE IF NOT EXISTS _System_CatalogSystemLayoutBundleSlot (
BundleKeyVersion VARCHAR(64) NOT NULL, SlotOwnerKind VARCHAR(8) NOT NULL,
SlotOwnerNamespace VARBINARY(128) NOT NULL,
LocalComponentKey VARBINARY(71) NOT NULL,
ValueVersion VARCHAR(80) NOT NULL, State VARCHAR(16) NOT NULL,
ValueDigest VARCHAR(71) NOT NULL, LastCommandId CHAR(36) NOT NULL,
CreatedDate DATETIME(6) NOT NULL, LastModifiedDate DATETIME(6) NOT NULL,
PRIMARY KEY (
BundleKeyVersion, SlotOwnerKind, SlotOwnerNamespace, LocalComponentKey),
CONSTRAINT chk_catalog_system_slot_owner CHECK (SlotOwnerKind = 'system'),
CONSTRAINT chk_catalog_system_slot_namespace CHECK (
SlotOwnerNamespace = 'io.nexuscrm.system'),
CONSTRAINT chk_catalog_system_slot_state CHECK (
State IN ('ACTIVE','TOMBSTONE')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogSystemLayoutBundleSlotHistory (
BundleKeyVersion VARCHAR(64) NOT NULL, SlotOwnerKind VARCHAR(8) NOT NULL,
SlotOwnerNamespace VARBINARY(128) NOT NULL,
LocalComponentKey VARBINARY(71) NOT NULL,
TransitionOrdinal TINYINT UNSIGNED NOT NULL, CommandId CHAR(36) NOT NULL,
ValueVersion VARCHAR(80) NOT NULL, State VARCHAR(16) NOT NULL,
ValueDigest VARCHAR(71) NOT NULL, PriorValueDigest VARCHAR(71) NULL,
CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (
BundleKeyVersion, SlotOwnerKind, SlotOwnerNamespace, LocalComponentKey,
TransitionOrdinal),
UNIQUE KEY ux_catalog_system_slot_history_command (CommandId),
UNIQUE KEY ux_catalog_system_slot_history_value (
BundleKeyVersion, SlotOwnerKind, SlotOwnerNamespace, LocalComponentKey,
ValueDigest),
CONSTRAINT chk_catalog_system_history_owner CHECK (SlotOwnerKind = 'system'),
CONSTRAINT chk_catalog_system_history_namespace CHECK (
SlotOwnerNamespace = 'io.nexuscrm.system'),
CONSTRAINT chk_catalog_system_history_transition CHECK (
(TransitionOrdinal = 1 AND State = 'ACTIVE' AND PriorValueDigest IS NULL) OR
(TransitionOrdinal = 2 AND State = 'TOMBSTONE'
AND PriorValueDigest IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogSystemLayoutBundleSlotCommand (
CommandId CHAR(36) PRIMARY KEY,
PrincipalId VARBINARY(255) NOT NULL, PrincipalAuditDigest VARCHAR(71) NOT NULL,
RequestDigest VARCHAR(71) NOT NULL, Operation VARCHAR(16) NOT NULL,
TargetBundleKeyVersion VARCHAR(64) NOT NULL,
TargetSlotOwnerKind VARCHAR(8) NOT NULL,
TargetSlotOwnerNamespace VARBINARY(128) NOT NULL,
TargetLocalComponentKey VARBINARY(71) NOT NULL,
DesiredValueVersion VARCHAR(80) NOT NULL,
DesiredBundleKeyVersion VARCHAR(64) NOT NULL,
DesiredSlotOwnerKind VARCHAR(8) NOT NULL,
DesiredSlotOwnerNamespace VARBINARY(128) NOT NULL,
DesiredLocalComponentKey VARBINARY(71) NOT NULL,
DesiredState VARCHAR(16) NOT NULL,
ExpectedPriorVariant VARCHAR(16) NOT NULL,
ExpectedPriorDigest VARCHAR(71) NULL, CreatedDate DATETIME(6) NOT NULL,
CONSTRAINT chk_catalog_system_command_target CHECK (
TargetSlotOwnerKind = 'system'
AND TargetSlotOwnerNamespace = 'io.nexuscrm.system'),
CONSTRAINT chk_catalog_system_command_desired CHECK (
DesiredSlotOwnerKind = 'system'
AND DesiredSlotOwnerNamespace = 'io.nexuscrm.system'),
CONSTRAINT chk_catalog_system_command_identity CHECK (
TargetBundleKeyVersion = DesiredBundleKeyVersion
AND TargetSlotOwnerKind = DesiredSlotOwnerKind
AND TargetSlotOwnerNamespace = DesiredSlotOwnerNamespace
AND TargetLocalComponentKey = DesiredLocalComponentKey),
CONSTRAINT chk_catalog_system_command_transition CHECK (
(Operation = 'CREATE' AND DesiredState = 'ACTIVE'
AND ExpectedPriorVariant = 'ABSENT' AND ExpectedPriorDigest IS NULL) OR
(Operation = 'RETIRE' AND DesiredState = 'TOMBSTONE'
AND ExpectedPriorVariant = 'EXACT' AND ExpectedPriorDigest IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogSystemLayoutBundleSlotCommandOutcome (
CommandId CHAR(36) PRIMARY KEY, RequestDigest VARCHAR(71) NOT NULL,
PrincipalId VARBINARY(255) NOT NULL, PrincipalAuditDigest VARCHAR(71) NOT NULL,
Result VARCHAR(40) NOT NULL, OutcomeDigest VARCHAR(71) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
UNIQUE KEY ux_catalog_system_slot_outcome_digest (OutcomeDigest),
CONSTRAINT chk_catalog_system_slot_outcome_result CHECK (
Result IN (
'APPLIED','EXPECTED_PRIOR_MISMATCH','AUTHORITY_STATE_CONFLICT')))`,
}

// EnsureSchema creates only the dormant system-slot current, history, command,
// and terminal-outcome tables.
func (a *Authority) EnsureSchema(ctx context.Context) error {
	if a == nil || a.db == nil {
		return ErrConflict
	}
	for _, statement := range schemaStatements {
		if _, err := a.db.ExecContext(ctx, statement); err != nil {
			return err
		}
	}
	return nil
}

// SchemaStatementsForTest returns a defensive copy.
func SchemaStatementsForTest() []string {
	return append([]string(nil), schemaStatements...)
}