CREATE TABLE IF NOT EXISTS `def_scorecard` (
`Package` CHAR(255) NULL DEFAULT NULL COLLATE {collation},
`Module` VARCHAR(255) DEFAULT NULL,
`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';
CREATE TABLE `def_scorecard_context` (
`Guid` varchar(50) NOT NULL,
`Scorecard` varchar(50) NOT NULL,
`Package` varchar(255) NOT NULL DEFAULT '',
`Module` varchar(255) DEFAULT NULL,
`Context` varchar(50) DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
`DoVerify` char(1) DEFAULT 'N',
`PrimaryTable` varchar(100) DEFAULT NULL,
`PrimaryTableGuidName` varchar(100) DEFAULT NULL,
`PrimaryTableSim` varchar(100) DEFAULT NULL,
`VerifyScoreName` varchar(100) DEFAULT NULL,
`SelectScorecard` varchar(100) DEFAULT NULL,
`ScoreOutputName` varchar(100) DEFAULT NULL,
`RaciSimulation` text DEFAULT NULL,
`RaciProduction` text DEFAULT NULL,
`RaciOutcome` text DEFAULT NULL,
`RaciDevelopment` text DEFAULT NULL,
PRIMARY KEY (`Guid`),
KEY `ScorecardContext` (`Scorecard`, `Active`),
KEY `idx_scorecard_active` (`Scorecard`, `Active`),
KEY `idx_scorecard_doverify` (`Scorecard`, `DoVerify`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_scorecard_history` (
`Guid` VARCHAR(64) NOT NULL,
`ScorecardName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255) DEFAULT 'ObjScorecard',
`Version` VARCHAR(50) NOT NULL,
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`VersionDate` DATETIME DEFAULT NULL,
`NodeCount` INT DEFAULT 0,
`ChangedBy` VARCHAR(255) DEFAULT NULL,
`ChangeType` VARCHAR(50) DEFAULT 'IMPORT',
`ChangeNotes` TEXT DEFAULT NULL,
`CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ScorecardName`, `Package`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};