package catalogpublication

import (
	"context"

	"github.com/pingcap-inc/nexuscrm-controlplane/internal/catalogassignmentidentity"
	"github.com/pingcap-inc/nexuscrm-controlplane/internal/catalognamespaceauthority"
	"github.com/pingcap-inc/nexuscrm-controlplane/internal/catalogwriter"
)

var schemaStatements = []string{
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationAdmissionFence (
FenceKey VARCHAR(64) PRIMARY KEY, FenceGeneration DECIMAL(20,0) NOT NULL,
CreatedDate DATETIME(6) NOT NULL, LastModifiedDate DATETIME(6) NOT NULL,
CONSTRAINT chk_catalog_publication_admission_fence_key CHECK (
FenceKey = 'CATALOG_PUBLICATION_VERSION'))`,
	`INSERT IGNORE INTO _System_CatalogPublicationAdmissionFence
(FenceKey, FenceGeneration, CreatedDate, LastModifiedDate)
VALUES ('CATALOG_PUBLICATION_VERSION', 0, CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogCandidateSource (
OrgId VARCHAR(255) NOT NULL, WriterCommandId CHAR(36) NOT NULL,
RequestDigest VARCHAR(71) NOT NULL, PreparationOutcomeDigest VARCHAR(71) NOT NULL,
RuntimeAudience VARCHAR(255) NOT NULL,
CatalogRevision DECIMAL(20,0) NOT NULL, CatalogSnapshotDigest VARCHAR(71) NOT NULL,
SchemaGeneration DECIMAL(20,0) NOT NULL, EngineGeneration DECIMAL(20,0) NOT NULL,
RequiredBundleKeySetVersion VARCHAR(64) NOT NULL, RequiredBundleKeySetDigest VARCHAR(71) NOT NULL,
RequiredBundleKeySetCanonicalBytes LONGBLOB NOT NULL,
ExpectedPriorPresent BOOLEAN NOT NULL, ExpectedPriorDigest VARCHAR(71) NULL,
ExpectedPriorCatalogRevision DECIMAL(20,0) NOT NULL,
SourceAppSelectionSetVersion VARCHAR(64) NOT NULL,
SourceAppSelectionSetCanonicalBytes LONGBLOB NOT NULL,
WriterReservationGeneration DECIMAL(20,0) NOT NULL,
SourceRelation VARCHAR(40) NOT NULL, State VARCHAR(32) NOT NULL,
FailureOutcomeDigest VARCHAR(71) NULL,
AdoptedPublicationRequestDigest VARCHAR(71) NULL,
AdoptedCandidatePublicationDigest VARCHAR(71) NULL,
CatalogPublicationOutcomeDigest VARCHAR(71) NULL,
CreatedDate DATETIME(6) NOT NULL, LastModifiedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, WriterCommandId),
INDEX idx_catalog_candidate_source_writer_command (WriterCommandId),
CONSTRAINT chk_catalog_candidate_source_prior CHECK (
(ExpectedPriorPresent = FALSE AND ExpectedPriorDigest IS NULL) OR
(ExpectedPriorPresent = TRUE AND ExpectedPriorDigest IS NOT NULL)),
CONSTRAINT chk_catalog_candidate_source_relation CHECK (
SourceRelation IN ('CATALOG_SUCCESSOR','EXACT_CURRENT_CATALOG_REVALIDATION')),
CONSTRAINT chk_catalog_candidate_source_state CHECK (
State IN ('PREPARED','PUBLICATION_COMPLETED','TERMINATED','STALE')),
CONSTRAINT chk_catalog_candidate_source_terminal CHECK (
(State = 'PREPARED'
AND FailureOutcomeDigest IS NULL
AND AdoptedPublicationRequestDigest IS NULL
AND AdoptedCandidatePublicationDigest IS NULL
AND CatalogPublicationOutcomeDigest IS NULL) OR
(State = 'PUBLICATION_COMPLETED'
AND FailureOutcomeDigest IS NULL
AND AdoptedPublicationRequestDigest IS NOT NULL
AND AdoptedCandidatePublicationDigest IS NOT NULL
AND CatalogPublicationOutcomeDigest IS NOT NULL) OR
(State IN ('TERMINATED','STALE')
AND FailureOutcomeDigest IS NOT NULL
AND AdoptedPublicationRequestDigest IS NULL
AND AdoptedCandidatePublicationDigest IS NULL
AND CatalogPublicationOutcomeDigest IS NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogCandidateSourceRequiredKey (
OrgId VARCHAR(255) NOT NULL, WriterCommandId CHAR(36) NOT NULL,
KeyOrdinal DECIMAL(20,0) NOT NULL, SlotOwnerKind VARCHAR(8) NOT NULL,
SlotOwnerNamespace VARCHAR(128) NOT NULL, LocalComponentKey VARCHAR(71) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, WriterCommandId, KeyOrdinal),
UNIQUE KEY ux_catalog_candidate_source_required_key
(OrgId, WriterCommandId, SlotOwnerKind, SlotOwnerNamespace, LocalComponentKey))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogCandidateSourceAppSelection (
OrgId VARCHAR(255) NOT NULL, WriterCommandId CHAR(36) NOT NULL,
SelectionOrdinal DECIMAL(20,0) NOT NULL,
AppId VARCHAR(128) NOT NULL, AppInstallationId CHAR(36) NOT NULL,
AppReleaseDigest VARCHAR(71) NOT NULL, SelectionKind VARCHAR(16) NOT NULL,
AppLifecycleCandidateCommandId CHAR(36) NULL, CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, WriterCommandId, SelectionOrdinal),
UNIQUE KEY ux_catalog_candidate_source_app (OrgId, WriterCommandId, AppId),
UNIQUE KEY ux_catalog_candidate_source_installation
(OrgId, WriterCommandId, AppInstallationId),
CONSTRAINT chk_catalog_candidate_source_app_kind CHECK (
(SelectionKind = 'ACTIVE' AND AppLifecycleCandidateCommandId IS NULL) OR
(SelectionKind = 'CANDIDATE' AND AppLifecycleCandidateCommandId IS NOT NULL
AND AppLifecycleCandidateCommandId = WriterCommandId)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogCandidateSourceFailureOutcome (
OrgId VARCHAR(255) NOT NULL, WriterCommandId CHAR(36) NOT NULL,
WriterReservationGeneration DECIMAL(20,0) NOT NULL,
SourceRequestDigest VARCHAR(71) NOT NULL,
ExpectedPriorPresent BOOLEAN NOT NULL, ExpectedPriorDigest VARCHAR(71) NULL,
ExpectedPriorCatalogRevision DECIMAL(20,0) NOT NULL,
FailureClass VARCHAR(32) NOT NULL, FailureCode VARCHAR(64) NOT NULL,
FailureDetailDigest VARCHAR(71) NOT NULL, RetryDisposition VARCHAR(40) NOT NULL,
SourceTerminalState VARCHAR(16) NOT NULL, FailureOutcomeDigest VARCHAR(71) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, WriterCommandId, WriterReservationGeneration),
UNIQUE KEY ux_catalog_candidate_source_failure_digest (OrgId, FailureOutcomeDigest),
CONSTRAINT chk_catalog_candidate_source_failure_prior CHECK (
(ExpectedPriorPresent = FALSE AND ExpectedPriorDigest IS NULL) OR
(ExpectedPriorPresent = TRUE AND ExpectedPriorDigest IS NOT NULL)),
CONSTRAINT chk_catalog_candidate_source_failure_class CHECK (
FailureClass IN ('SEMANTIC_INVALIDITY','SOURCE_INVALIDITY')),
CONSTRAINT chk_catalog_candidate_source_failure_retry CHECK (
RetryDisposition IN ('RETRYABLE_WITH_NEW_OUTER_COMMAND','TERMINAL')),
CONSTRAINT chk_catalog_candidate_source_failure_state CHECK (
SourceTerminalState IN ('TERMINATED','STALE')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogWriterGenerationTerminalHistory (
OrgId VARCHAR(255) NOT NULL, WriterCommandId CHAR(36) NOT NULL,
WriterReservationGeneration DECIMAL(20,0) NOT NULL,
TerminalAction VARCHAR(16) NOT NULL, TerminalOutcomeDigest VARCHAR(71) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, WriterCommandId, WriterReservationGeneration),
UNIQUE KEY ux_catalog_writer_generation_terminal_outcome
(OrgId, TerminalOutcomeDigest),
CONSTRAINT chk_catalog_writer_generation_terminal_action CHECK (
TerminalAction IN ('COMMITTED','RELEASED')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublication (
PublicationDigest VARCHAR(71) PRIMARY KEY,
BindingVersion VARCHAR(64) NOT NULL,
OrgId VARCHAR(255) NOT NULL,
CatalogRevision DECIMAL(20,0) NOT NULL, CatalogSnapshotDigest VARCHAR(71) NOT NULL,
SchemaGeneration DECIMAL(20,0) NOT NULL, EngineGeneration DECIMAL(20,0) NOT NULL,
LayoutValidationViewDigest VARCHAR(71) NOT NULL,
LayoutBundleOwnerVerificationReceiptSetDigest VARCHAR(71) NULL,
LayoutActivationSetDigest VARCHAR(71) NOT NULL,
SecurityEpoch DECIMAL(20,0) NOT NULL, LayoutRoutingSecurityViewDigest VARCHAR(71) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
INDEX idx_catalog_publication_org_lineage (OrgId, CatalogRevision, SecurityEpoch))`,
	`ALTER TABLE _System_CatalogPublication
ADD COLUMN IF NOT EXISTS BindingVersion VARCHAR(64) NULL AFTER PublicationDigest`,
	`ALTER TABLE _System_CatalogPublication
ADD COLUMN IF NOT EXISTS LayoutBundleOwnerVerificationReceiptSetDigest VARCHAR(71) NULL
AFTER LayoutValidationViewDigest`,
	`ALTER TABLE _System_CatalogPublication
ALTER COLUMN BindingVersion DROP DEFAULT`,
	`UPDATE _System_CatalogPublication
SET BindingVersion = '` + legacyPublicationBindingVersion + `'
WHERE BindingVersion IS NULL
AND LayoutBundleOwnerVerificationReceiptSetDigest IS NULL`,
	`ALTER TABLE _System_CatalogPublication
MODIFY COLUMN BindingVersion VARCHAR(64) NOT NULL`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationCommand (
OrgId VARCHAR(255) NOT NULL, CommandId CHAR(36) NOT NULL, RequestDigest VARCHAR(71) NOT NULL,
RuntimeAudience VARCHAR(255) NOT NULL, CandidatePublicationDigest VARCHAR(71) NOT NULL,
ExpectedPriorPresent BOOLEAN NOT NULL, ExpectedPriorDigest VARCHAR(71) NULL,
State VARCHAR(16) NOT NULL, CreatedDate DATETIME(6) NOT NULL, LastModifiedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, CommandId),
CONSTRAINT chk_catalog_publication_command_prior CHECK (
(ExpectedPriorPresent = FALSE AND ExpectedPriorDigest IS NULL) OR
(ExpectedPriorPresent = TRUE AND ExpectedPriorDigest IS NOT NULL)),
CONSTRAINT chk_catalog_publication_command_state CHECK (State IN ('RESERVED','COMPLETED')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationOutcome (
OrgId VARCHAR(255) NOT NULL, CommandId CHAR(36) NOT NULL, CandidatePublicationDigest VARCHAR(71) NOT NULL,
Result VARCHAR(32) NOT NULL,
ObservedPriorPresent BOOLEAN NOT NULL, ObservedPriorDigest VARCHAR(71) NULL,
ResultingActivePresent BOOLEAN NOT NULL, ResultingActiveDigest VARCHAR(71) NULL,
OutcomeDigest VARCHAR(71) NOT NULL, CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, CommandId), UNIQUE KEY ux_catalog_publication_outcome_digest (OrgId, OutcomeDigest),
CONSTRAINT chk_catalog_publication_outcome_result CHECK (
Result IN ('SELECTED','UNCHANGED','EXPECTED_PRIOR_MISMATCH')),
CONSTRAINT chk_catalog_publication_outcome_observed CHECK (
(ObservedPriorPresent = FALSE AND ObservedPriorDigest IS NULL) OR
(ObservedPriorPresent = TRUE AND ObservedPriorDigest IS NOT NULL)),
CONSTRAINT chk_catalog_publication_outcome_active CHECK (
(ResultingActivePresent = FALSE AND ResultingActiveDigest IS NULL) OR
(ResultingActivePresent = TRUE AND ResultingActiveDigest IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationHistory (
OrgId VARCHAR(255) NOT NULL, SequenceNumber DECIMAL(20,0) NOT NULL,
CommandId CHAR(36) NOT NULL, CandidatePublicationDigest VARCHAR(71) NOT NULL,
Result VARCHAR(32) NOT NULL,
ObservedPriorPresent BOOLEAN NOT NULL, ObservedPriorDigest VARCHAR(71) NULL,
ResultingActivePresent BOOLEAN NOT NULL, ResultingActiveDigest VARCHAR(71) NULL,
OutcomeDigest VARCHAR(71) NOT NULL, CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, SequenceNumber), UNIQUE KEY ux_catalog_publication_history_command (OrgId, CommandId),
UNIQUE KEY ux_catalog_publication_history_outcome (OrgId, OutcomeDigest),
CONSTRAINT chk_catalog_publication_history_result CHECK (
Result IN ('SELECTED','UNCHANGED','EXPECTED_PRIOR_MISMATCH')),
CONSTRAINT chk_catalog_publication_history_observed CHECK (
(ObservedPriorPresent = FALSE AND ObservedPriorDigest IS NULL) OR
(ObservedPriorPresent = TRUE AND ObservedPriorDigest IS NOT NULL)),
CONSTRAINT chk_catalog_publication_history_active CHECK (
(ResultingActivePresent = FALSE AND ResultingActiveDigest IS NULL) OR
(ResultingActivePresent = TRUE AND ResultingActiveDigest IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationActive (
OrgId VARCHAR(255) PRIMARY KEY, ActivePublicationPresent BOOLEAN NOT NULL,
ActivePublicationDigest VARCHAR(71) NULL, SelectedByCommandId CHAR(36) NULL,
HistorySequence DECIMAL(20,0) NOT NULL,
CreatedDate DATETIME(6) NOT NULL, LastModifiedDate DATETIME(6) NOT NULL,
CONSTRAINT chk_catalog_publication_active_reference CHECK (
(ActivePublicationPresent = FALSE AND ActivePublicationDigest IS NULL AND SelectedByCommandId IS NULL) OR
(ActivePublicationPresent = TRUE AND ActivePublicationDigest IS NOT NULL AND SelectedByCommandId IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogPublicationCandidate (
OrgId VARCHAR(255) PRIMARY KEY, CommandId CHAR(36) NOT NULL,
CandidatePublicationDigest VARCHAR(71) NOT NULL, CreatedDate DATETIME(6) NOT NULL,
LastModifiedDate DATETIME(6) NOT NULL,
UNIQUE KEY ux_catalog_publication_candidate_command (OrgId, CommandId))`,
}

// EnsureSchema creates the shared Catalog writer row, admission state, and
// non-serving publication authority tables. It is not called by a production
// startup path.
func (a *Authority) EnsureSchema(ctx context.Context) error {
	if err := ensureTiDBCheckConstraintsEnabled(ctx, a.db); err != nil {
		return err
	}
	if err := catalogwriter.EnsureSchema(ctx, a.db); err != nil {
		return err
	}
	if err := catalogassignmentidentity.EnsureSchema(ctx, a.db); err != nil {
		return err
	}
	if err := catalognamespaceauthority.EnsureSchema(ctx, a.db); err != nil {
		return err
	}
	for _, statement := range schemaStatements {
		if _, err := a.db.ExecContext(ctx, statement); err != nil {
			return err
		}
	}
	return nil
}

// SchemaStatementsForTest returns a defensive copy including the shared
// org-writer and immutable assignment-view schemas provisioned before
// publication state.
func SchemaStatementsForTest() []string {
	result := catalogwriter.SchemaStatementsForTest()
	result = append(result, catalogassignmentidentity.SchemaStatementsForTest()...)
	result = append(result, catalognamespaceauthority.SchemaStatementsForTest()...)
	return append(result, schemaStatements...)
}