78 lines
3.6 KiB
SQL
78 lines
3.6 KiB
SQL
-- Permanent integrity contract for SmartPakker.
|
|
-- Legacy packages remain available to administrators as needs_review, while only
|
|
-- verified complete_offer packages may be used in the carpenter field flow.
|
|
|
|
ALTER TABLE material_packages
|
|
ADD COLUMN IF NOT EXISTS validation_status varchar(30) NOT NULL DEFAULT 'needs_review' AFTER package_type,
|
|
ADD COLUMN IF NOT EXISTS validation_notes text DEFAULT NULL AFTER validation_status,
|
|
ADD COLUMN IF NOT EXISTS validated_at datetime DEFAULT NULL AFTER validation_notes,
|
|
ADD COLUMN IF NOT EXISTS version int(11) NOT NULL DEFAULT 1 AFTER validated_at;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_material_packages_field_catalog
|
|
ON material_packages (is_active, package_type, validation_status);
|
|
|
|
CREATE TABLE IF NOT EXISTS smart_package_components (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
parent_package_id int(11) NOT NULL,
|
|
component_package_id int(11) NOT NULL,
|
|
is_required tinyint(1) NOT NULL DEFAULT 1,
|
|
component_order int(11) NOT NULL DEFAULT 0,
|
|
conditions_json longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
|
|
created_at timestamp NOT NULL DEFAULT current_timestamp(),
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_smart_package_component (parent_package_id, component_package_id),
|
|
KEY idx_smart_package_component_child (component_package_id),
|
|
CONSTRAINT fk_smart_package_component_parent FOREIGN KEY (parent_package_id)
|
|
REFERENCES material_packages (id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_smart_package_component_child FOREIGN KEY (component_package_id)
|
|
REFERENCES material_packages (id) ON DELETE RESTRICT,
|
|
CONSTRAINT chk_smart_package_component_conditions CHECK (
|
|
conditions_json IS NULL OR json_valid(conditions_json)
|
|
)
|
|
);
|
|
|
|
-- Excel's Udlejning sheet is a service/rental catalogue, not building materials.
|
|
UPDATE material_packages
|
|
SET package_type = 'rental_service',
|
|
validation_status = 'verified',
|
|
validation_notes = 'Klassificeret som udlejning/ydelse; materialekobling er ikke påkrævet.',
|
|
validated_at = COALESCE(validated_at, NOW())
|
|
WHERE is_active = 1
|
|
AND (
|
|
LOWER(COALESCE(excel_source_sheet, '')) IN ('udlejning', 'byggeplads')
|
|
OR LOWER(COALESCE(package_type, '')) IN ('rental', 'equipment_rental')
|
|
);
|
|
|
|
-- No legacy craft package is trusted merely because it existed before this rule.
|
|
UPDATE material_packages
|
|
SET package_type = CASE
|
|
WHEN LOWER(COALESCE(package_type, '')) IN ('complete_offer', 'component') THEN package_type
|
|
ELSE 'component'
|
|
END,
|
|
validation_status = 'needs_review',
|
|
validation_notes = COALESCE(validation_notes,
|
|
'Legacy-pakke: kræver faglig gennemgang og materialedatabasekobling før feltbrug.'),
|
|
validated_at = NULL
|
|
WHERE is_active = 1
|
|
AND COALESCE(package_type, '') <> 'rental_service'
|
|
AND validation_status <> 'verified';
|
|
|
|
-- Quarantine unsafe legacy craft packages instead of guessing a product match.
|
|
-- This is reversible (is_active=1) and the pre-migration backup preserves the
|
|
-- exact source rows. A replacement must pass the verification endpoint.
|
|
UPDATE material_packages mp
|
|
LEFT JOIN (
|
|
SELECT package_id,
|
|
COUNT(*) AS material_lines,
|
|
SUM(material_id IS NULL) AS unlinked_material_lines
|
|
FROM package_materials
|
|
GROUP BY package_id
|
|
) audit ON audit.package_id = mp.id
|
|
SET mp.is_active = 0,
|
|
mp.validation_status = 'blocked',
|
|
mp.validation_notes = 'Arkiveret af integritetskontrol: mindst én materialelinje mangler sikker kobling til materialedatabasen.',
|
|
mp.validated_at = NULL
|
|
WHERE mp.is_active = 1
|
|
AND mp.package_type = 'component'
|
|
AND (COALESCE(audit.material_lines, 0) = 0 OR COALESCE(audit.unlinked_material_lines, 0) > 0);
|