package catalogassignmentidentity

import (
	"context"
	"database/sql"
	"errors"
)

var schemaStatements = []string{
	`CREATE TABLE IF NOT EXISTS _System_CatalogAssignmentIdentityView (
OrgId VARBINARY(255) NOT NULL, ViewDigest VARBINARY(71) NOT NULL,
CatalogRevision DECIMAL(20,0) NOT NULL, CatalogSnapshotDigest VARBINARY(71) NOT NULL,
SchemaGeneration DECIMAL(20,0) NOT NULL, EngineGeneration DECIMAL(20,0) NOT NULL,
CanonicalizationVersion VARBINARY(64) NOT NULL, KeyGrammarVersion VARBINARY(64) NOT NULL,
ObjectKeyNormalizationVersion VARBINARY(64) NOT NULL,
CanonicalBytes LONGBLOB NOT NULL, CanonicalByteLength DECIMAL(20,0) NOT NULL,
ObjectCount DECIMAL(20,0) NOT NULL, RecordTypeCount DECIMAL(20,0) NOT NULL,
ProductAppCount DECIMAL(20,0) NOT NULL, NavigationAppCount DECIMAL(20,0) NOT NULL,
CreatedDate DATETIME(6) NOT NULL,
PRIMARY KEY (OrgId, ViewDigest),
CONSTRAINT chk_catalog_assignment_identity_source CHECK (
OCTET_LENGTH(OrgId) > 0 AND OCTET_LENGTH(ViewDigest) = 71
AND CatalogRevision > 0 AND OCTET_LENGTH(CatalogSnapshotDigest) = 71
AND SchemaGeneration > 0 AND EngineGeneration > 0),
CONSTRAINT chk_catalog_assignment_identity_versions CHECK (
CanonicalizationVersion = 'nexuscrm.catalog-assignment-identity-view-canonical/v1'
AND KeyGrammarVersion = 'nexuscrm.catalog-assignment-identity-key/v1'
AND ObjectKeyNormalizationVersion = 'nexuscrm.catalog-object-key-normalization/v1'),
CONSTRAINT chk_catalog_assignment_identity_counts CHECK (
ObjectCount >= 0 AND ObjectCount <= 100000
AND RecordTypeCount >= 0 AND RecordTypeCount <= 100000
AND ProductAppCount >= 0 AND ProductAppCount <= 100000
AND NavigationAppCount >= 0 AND NavigationAppCount <= 100000),
CONSTRAINT chk_catalog_assignment_identity_bytes CHECK (
CanonicalByteLength > 0 AND CanonicalByteLength <= 67108864
AND CanonicalByteLength = OCTET_LENGTH(CanonicalBytes)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogAssignmentIdentityViewObject (
OrgId VARBINARY(255) NOT NULL, ViewDigest VARBINARY(71) NOT NULL,
EntryOrdinal DECIMAL(20,0) NOT NULL, NormalizedAPIName VARBINARY(64) NOT NULL,
ResolvedObjectId VARBINARY(255) NOT NULL, State VARBINARY(16) NOT NULL,
PRIMARY KEY (OrgId, ViewDigest, EntryOrdinal),
UNIQUE KEY ux_catalog_assignment_identity_object_key
(OrgId, ViewDigest, NormalizedAPIName),
UNIQUE KEY ux_catalog_assignment_identity_object_target
(OrgId, ViewDigest, ResolvedObjectId),
CONSTRAINT chk_catalog_assignment_identity_object_shape CHECK (
OCTET_LENGTH(OrgId) > 0 AND OCTET_LENGTH(ViewDigest) = 71
AND EntryOrdinal >= 0 AND EntryOrdinal < 100000
AND OCTET_LENGTH(NormalizedAPIName) > 0
AND OCTET_LENGTH(ResolvedObjectId) > 0),
CONSTRAINT chk_catalog_assignment_identity_object_state CHECK (
State IN ('ACTIVE','TOMBSTONE')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogAssignmentIdentityViewRecordType (
OrgId VARBINARY(255) NOT NULL, ViewDigest VARBINARY(71) NOT NULL,
EntryOrdinal DECIMAL(20,0) NOT NULL, ObjectNormalizedAPIName VARBINARY(64) NOT NULL,
NamespaceId VARBINARY(128) NOT NULL, LocalKey VARBINARY(128) NOT NULL,
ResolvedRecordTypeId VARBINARY(255) NOT NULL, State VARBINARY(16) NOT NULL,
PRIMARY KEY (OrgId, ViewDigest, EntryOrdinal),
UNIQUE KEY ux_catalog_assignment_identity_record_type_key
(OrgId, ViewDigest, ObjectNormalizedAPIName, NamespaceId, LocalKey),
UNIQUE KEY ux_catalog_assignment_identity_record_type_target
(OrgId, ViewDigest, ResolvedRecordTypeId),
CONSTRAINT chk_catalog_assignment_identity_record_type_shape CHECK (
OCTET_LENGTH(OrgId) > 0 AND OCTET_LENGTH(ViewDigest) = 71
AND EntryOrdinal >= 0 AND EntryOrdinal < 100000
AND OCTET_LENGTH(ObjectNormalizedAPIName) > 0
AND OCTET_LENGTH(NamespaceId) > 0 AND OCTET_LENGTH(LocalKey) > 0
AND OCTET_LENGTH(ResolvedRecordTypeId) > 0),
CONSTRAINT chk_catalog_assignment_identity_record_type_state CHECK (
State IN ('ACTIVE','TOMBSTONE')))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogAssignmentIdentityViewProductApp (
OrgId VARBINARY(255) NOT NULL, ViewDigest VARBINARY(71) NOT NULL,
EntryOrdinal DECIMAL(20,0) NOT NULL, AppId VARBINARY(128) NOT NULL,
State VARBINARY(16) NOT NULL, ResolvedTargetKind TINYINT UNSIGNED NOT NULL,
ResolvedAppInstallationId VARBINARY(36) NULL,
PRIMARY KEY (OrgId, ViewDigest, EntryOrdinal),
UNIQUE KEY ux_catalog_assignment_identity_product_app_key
(OrgId, ViewDigest, AppId),
UNIQUE KEY ux_catalog_assignment_identity_product_app_target
(OrgId, ViewDigest, ResolvedAppInstallationId),
CONSTRAINT chk_catalog_assignment_identity_product_app_state CHECK (
State IN ('ACTIVE','TOMBSTONE')),
CONSTRAINT chk_catalog_assignment_identity_product_app_shape CHECK (
OCTET_LENGTH(OrgId) > 0 AND OCTET_LENGTH(ViewDigest) = 71
AND EntryOrdinal >= 0 AND EntryOrdinal < 100000
AND OCTET_LENGTH(AppId) > 0
AND (ResolvedAppInstallationId IS NULL
OR OCTET_LENGTH(ResolvedAppInstallationId) = 36)),
CONSTRAINT chk_catalog_assignment_identity_product_app_target CHECK (
(ResolvedTargetKind = 0 AND State = 'TOMBSTONE'
AND ResolvedAppInstallationId IS NULL)
OR (ResolvedTargetKind = 1 AND ResolvedAppInstallationId IS NOT NULL)))`,
	`CREATE TABLE IF NOT EXISTS _System_CatalogAssignmentIdentityViewNavigationApp (
OrgId VARBINARY(255) NOT NULL, ViewDigest VARBINARY(71) NOT NULL,
EntryOrdinal DECIMAL(20,0) NOT NULL, NamespaceId VARBINARY(128) NOT NULL,
LocalKey VARBINARY(128) NOT NULL, ResolvedAppConfigId VARBINARY(255) NOT NULL,
State VARBINARY(16) NOT NULL,
PRIMARY KEY (OrgId, ViewDigest, EntryOrdinal),
UNIQUE KEY ux_catalog_assignment_identity_navigation_app_key
(OrgId, ViewDigest, NamespaceId, LocalKey),
UNIQUE KEY ux_catalog_assignment_identity_navigation_app_target
(OrgId, ViewDigest, ResolvedAppConfigId),
CONSTRAINT chk_catalog_assignment_identity_navigation_app_shape CHECK (
OCTET_LENGTH(OrgId) > 0 AND OCTET_LENGTH(ViewDigest) = 71
AND EntryOrdinal >= 0 AND EntryOrdinal < 100000
AND OCTET_LENGTH(NamespaceId) > 0
AND OCTET_LENGTH(LocalKey) > 0 AND OCTET_LENGTH(ResolvedAppConfigId) > 0),
CONSTRAINT chk_catalog_assignment_identity_navigation_app_state CHECK (
State IN ('ACTIVE','TOMBSTONE')))`,
}

// EnsureSchema creates only the immutable assignment-view artifact tables.
func EnsureSchema(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return errors.New("catalog assignment identity store: database is required")
	}
	for _, statement := range schemaStatements {
		if _, err := db.ExecContext(ctx, statement); err != nil {
			return err
		}
	}
	return nil
}

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