CREATE TABLE `def_screen` (
`ScreenCode` varchar(80) NOT NULL DEFAULT '',
`Package` varchar(40) NOT NULL DEFAULT 'BASE',
`Workspace` varchar(40) NOT NULL DEFAULT '',
`ScreenSlug` varchar(80) NOT NULL DEFAULT '',
`Title` varchar(120) NOT NULL DEFAULT '',
`TemplateCode` varchar(80) NOT NULL DEFAULT 'AppShellTemplate',
`TargetType` varchar(40) NOT NULL DEFAULT 'report',
`TargetCode` varchar(120) NOT NULL DEFAULT '',
`TargetParamsJson` longtext DEFAULT NULL,
`AclLevel` int NOT NULL DEFAULT 10,
`AclGroups` varchar(255) NOT NULL DEFAULT '',
`FeatureFlag` varchar(80) NOT NULL DEFAULT '',
`MenuRank` int NOT NULL DEFAULT 100,
`IsActive` tinyint(1) NOT NULL DEFAULT 1,
`CreateDate` timestamp NULL DEFAULT current_timestamp(),
`UpdateDate` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`ScreenCode`,`Package`),
KEY `idx_def_screen_lookup` (`Package`,`Workspace`,`ScreenSlug`,`IsActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_route` (
`RouteCode` varchar(80) NOT NULL DEFAULT '',
`Package` varchar(40) NOT NULL DEFAULT 'BASE',
`ScreenCode` varchar(80) NOT NULL DEFAULT '',
`PathPattern` varchar(255) NOT NULL DEFAULT '',
`HttpMethod` varchar(10) NOT NULL DEFAULT 'GET',
`IsCanonical` tinyint(1) NOT NULL DEFAULT 0,
`ParamBindingsJson` longtext DEFAULT NULL,
`ConstraintsJson` longtext DEFAULT NULL,
`RedirectStatus` int DEFAULT NULL,
`IsActive` tinyint(1) NOT NULL DEFAULT 1,
`CreateDate` timestamp NULL DEFAULT current_timestamp(),
`UpdateDate` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`RouteCode`,`Package`),
KEY `idx_def_route_lookup` (`Package`,`ScreenCode`,`HttpMethod`,`IsCanonical`,`IsActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT
s.ScreenCode,
s.Package,
s.Workspace,
s.ScreenSlug,
s.Title,
s.TemplateCode,
s.TargetType,
s.TargetCode,
COALESCE(s.TargetParamsJson, '{{}}') AS TargetParamsJson,
s.AclLevel,
COALESCE(s.AclGroups, '') AS AclGroups,
COALESCE(s.FeatureFlag, '') AS FeatureFlag,
s.MenuRank,
COALESCE(r.RouteCode, '') AS RouteCode,
COALESCE(r.PathPattern, '') AS PathPattern,
COALESCE(r.HttpMethod, '{method_norm}') AS HttpMethod,
COALESCE(r.IsCanonical, 1) AS IsCanonical,
COALESCE(r.ParamBindingsJson, '{{}}') AS ParamBindingsJson,
COALESCE(r.ConstraintsJson, '{{}}') AS ConstraintsJson,
COALESCE(r.RedirectStatus, 0) AS RedirectStatus
FROM def_screen s
LEFT JOIN def_route r
ON r.ScreenCode = s.ScreenCode
AND r.Package = s.Package
AND r.HttpMethod = '{method_norm}'
AND r.IsActive = 1
AND (r.IsCanonical = 1 OR r.IsCanonical IS NULL)
WHERE s.IsActive = 1
AND LOWER(s.Workspace) = '{workspace_norm}'
AND LOWER(s.ScreenSlug) = '{screen_norm}'
AND s.Package IN ({in_clause})
ORDER BY
CASE s.Package
WHEN '{package_name}' THEN 1
WHEN '{archetype}' THEN 2
WHEN 'SYSTEM' THEN 3
WHEN 'ALL' THEN 4
WHEN 'BASE' THEN 5
ELSE 99
END,
CASE WHEN COALESCE(r.IsCanonical, 1) = 1 THEN 0 ELSE 1 END
LIMIT 1