CREATE TABLE IF NOT EXISTS `def_scorecard` (
`Package` CHAR(255) NULL DEFAULT NULL COLLATE {collation},
`ScorecardGuid` CHAR(50) NOT NULL COLLATE {collation},
`Scorecard` CHAR(50) NOT NULL COLLATE {collation},
`Variable` CHAR(50) NOT NULL COLLATE {collation},
`AttributeID` INT(11) NOT NULL DEFAULT 0,
`AttributeType` CHAR(50) NULL DEFAULT NULL COLLATE {collation},
`AttributeLower` CHAR(50) NULL DEFAULT NULL COLLATE {collation},
`AttributeHigher` CHAR(50) NULL DEFAULT NULL COLLATE {collation},
`Attribute` CHAR(200) NULL DEFAULT NULL COLLATE {collation},
`Score` DOUBLE NULL DEFAULT NULL,
`Description` CHAR(255) NULL DEFAULT NULL COLLATE {collation},
`Version` CHAR(50) NOT NULL DEFAULT 'v1' COLLATE {collation},
`Active` CHAR(1) NULL DEFAULT 'Y' COLLATE {collation},
`DatasetName` VARCHAR(255) NULL DEFAULT NULL COLLATE {collation},
`ModelName` VARCHAR(255) NULL DEFAULT NULL COLLATE {collation},
`TargetClass` VARCHAR(100) NULL DEFAULT NULL COLLATE {collation},
`WoeValue` DOUBLE NULL DEFAULT NULL,
`CreatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`UpdatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ScorecardGuid`) USING BTREE,
UNIQUE KEY `ScorecardVersion` (`Scorecard`, `Version`, `Variable`, `AttributeID`) USING BTREE,
INDEX `Score` (`Scorecard`) USING BTREE,
INDEX `Variable` (`Variable`) USING BTREE,
INDEX `AttributeID` (`AttributeID`) USING BTREE,
INDEX `DatasetModel` (`DatasetName`, `ModelName`) USING BTREE,
INDEX `PackageScorecardQuery` (`Package`, `Scorecard`, `Variable`) USING BTREE,
INDEX `ScorecardActive` (`Scorecard`, `Active`) USING BTREE,
INDEX `VersionLookup` (`Scorecard`, `Version`) USING BTREE
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE={collation}
COMMENT='Unified scorecard table with versioning support for production and ML use cases';
ALTER TABLE `def_scorecard_context`
ADD INDEX IF NOT EXISTS `idx_scorecard_active` (`Scorecard`, `Active`),
ADD INDEX IF NOT EXISTS `idx_scorecard_doverify` (`Scorecard`, `DoVerify`);