-- =====================================================================
-- 45_generalaffairs.sql
-- General Affairs
--
-- GENERATED by _gen/pack.py from ../step/. DO NOT EDIT -- edit the source
-- there and re-pack, or the sha256 in MANIFEST.md stops matching.
--
-- 4 table(s), 16 ledger step(s).
--
-- ⚠ IMPORT ORDER: 01_system.sql, then all seven 1x masters, then this file. Order among
--    transactions does not matter. Re-importing costs nothing.
--
-- ⚠ STOP ON THE FIRST ERROR. Do not use `mysql --force`, and do not tick any
--    "continue on error" box. Each step records itself in `_migration_ledger`
--    immediately after its statement runs, WITHOUT checking that the statement
--    succeeded -- that is inherited from step/, where run.sh guarantees a stop by
--    running mysql without --force. Continue past an error here and a step that
--    failed is marked applied: 99_finalize.sql will then seed `migrations`, and
--    `php artisan migrate` will skip the work still owed. Both default clients
--    stop on error; the danger is turning that off.
--
-- ⚠ phpMyAdmin: UNTICK "Allow the interruption of an import".
--    If it fires, the import resumes on a NEW CONNECTION: SQL_MODE reverts to the server
--    default and every @variable dies, mid-file. Two things then break. NO_ZERO_DATE is
--    back, so any ALTER that rebuilds a table holding '0000-00-00' fails with ERROR 1292.
--    And @step / @todo / @sql are NULL, so the guard around the next payload stops meaning
--    anything. The SET SQL_MODE before every unit below limits the damage when someone
--    forgets the checkbox; it does not replace unticking it.
--
--    (STRICT_TRANS_TABLES is forced for a third case -- a value too big for its new column
--    is CLAMPED silently rather than refused. Measured on the tree as it stands, no column
--    narrows: all 674 MODIFY COLUMNs widen or are no-ops. It stays forced because the next
--    regeneration is under no obligation to keep that true.)
--
-- ⚠ Take a full backup first. The `20_cleanup` steps change data and nothing here
--    reverses them. `../audit_bukanmain_before_migrate.sql` only reads.
-- =====================================================================

-- Menus this file serves (10 links):
--   /general-affairs/approval-ga
--   /general-affairs/create
--   /general-affairs/create-all
--   /general-affairs/finance-dp
--   /general-affairs/finance-payment
--   /general-affairs/ga-completion
--   /general-affairs/ga-processing
--   /general-affairs/my-request
--   /general-affairs/review-ga
--   /general-affairs/view-all

-- --------------------------------------------------------------- prologue
-- run.sh sources step/_lib/session.sql for you. phpMyAdmin will not, so it is inlined.
             AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
        @@SQL_MODE,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- Assert all four, not just the first. STRICT_TRANS_TABLES missing means out-of-range
-- values are clamped silently; NO_ZERO_DATE still present means any ALTER that rebuilds a
-- legacy table re-validates its '0000-00-00' rows and dies with ERROR 1292. The second is
-- the one that actually stops the migration, and it was not being checked.
SELECT IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%'
      AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
      AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
      AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
          'sql_mode OK - proceeding',
          (SELECT 'ABORT: sql_mode is wrong. Wanted STRICT_TRANS_TABLES + NO_AUTO_VALUE_ON_ZERO,'
           UNION ALL
           SELECT 'and NO_ZERO_DATE / NO_ZERO_IN_DATE OFF. The SET above should have done it.')
       ) AS preflight_sql_mode;

SET SESSION lock_wait_timeout = 60;
SET SESSION innodb_lock_wait_timeout = 60;

-- The ledger, copied verbatim from step/_lib/ledger.sql -- not restated, so the two
-- paths cannot disagree about its definition.
-- ---------------------------------------------------------------------
-- _lib/ledger.sql  --  the only thing here that is not in the source file
-- One row per applied step. This is the record that decides what a re-run skips,
-- what --status reports, and -- through the app-side gate -- which modules the
-- application will serve. It lives in the database on purpose: a file on one
-- operator's laptop drifts the moment someone runs a step from another machine.
--
-- Deliberately NOT in the project's PascalCase convention. It is an ops table, never
-- touched by Eloquent, and it should be visibly not-an-app-table.
--
-- `checksum` is the SHA-256 of the step's SQL payload -- the verbatim statements,
-- not the file -- so it cannot change when a comment above them is reworded.
-- _lib/steps.tsv carries the whole-file hashes for tamper detection.
--
-- ⚠ `step` IS ascii, AND THAT IS LOAD-BEARING. Every guard compares it against a
-- user variable, and a user variable carries the CONNECTION collation. Give this
-- column utf8mb4_unicode_ci and the very first step dies with ERROR 1267,
-- "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and
-- (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='" -- measured, that is exactly
-- how the first end-to-end run failed. ascii is a repertoire subset of latin1 and
-- of utf8mb4, so the server coerces it to whatever the connection is using,
-- whatever that turns out to be on the day. Step names are [a-z0-9_/] by
-- construction, so nothing is lost.
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `_migration_ledger` (
  `step`        varchar(191) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `applied_at`  datetime     NOT NULL,
  `checksum`    char(64)     NOT NULL,
  `duration_ms` int          NOT NULL,
  PRIMARY KEY (`step`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------------- preflight
-- 00_system/01_system.sql MUST already be applied. Every doc says so; until now nothing
-- enforced it, and the violation was silent for most of the tree: only three files break
-- without it (30_quotation and 40_budgetandtarget hit ERROR 1146 on a table 01_system
-- creates, and 99_finalize needs `migrations`). The other twenty import cleanly, record
-- their ledger rows, and leave a database that is half-migrated with nothing to say so.
--
-- 13 steps: the ALTER DATABASE plus the 12 CREATE TABLEs.
SET @sys_done = (SELECT COUNT(*) FROM `_migration_ledger` WHERE `step` LIKE '\_system/%');
SET @pf_sys = IF(@sys_done >= 13,
    'SELECT ''preflight: 00_system is applied'' AS preflight_system',
    'SELECT `ABORT: import 00_system/01_system.sql first. It runs the ALTER DATABASE and creates the 12 new tables that everything here assumes exist.`');
PREPARE _pf FROM @pf_sys; EXECUTE _pf; DEALLOCATE PREPARE _pf;

-- --------------------------------------------------------------- preflight
-- WAS THIS DATABASE MIGRATED FROM A DIFFERENT VERSION OF THIS FILE?
--
-- `_migration_ledger.checksum` is the SHA-256 of each step's payload. It is written 632
-- times across the tree and, until this block existed, read never -- so a corrected payload
-- re-issued under the same step name was skipped forever on any database that had recorded
-- the old one, silently and permanently.
SELECT COUNT(*) INTO @ck_bad FROM `_migration_ledger`
 WHERE `step` IN ('vehicleservicerequest/10_alter', 'vehicleservicerequest/20_cleanup', 'vehicleservicerequest/30_assert', 'vehicleservicerequest/40_fk', 'vehicleservicerequestassignment/10_alter', 'vehicleservicerequestassignment/20_cleanup', 'vehicleservicerequestassignment/30_assert', 'vehicleservicerequestassignment/40_fk', 'vehicleservicerequestdetails/10_alter', 'vehicleservicerequestdetails/20_cleanup', 'vehicleservicerequestdetails/30_assert', 'vehicleservicerequestdetails/40_fk', 'vehicleservicerequestdetailsassigntment/10_alter', 'vehicleservicerequestdetailsassigntment/20_cleanup', 'vehicleservicerequestdetailsassigntment/30_assert', 'vehicleservicerequestdetailsassigntment/40_fk')
   AND CONCAT(`step`, ':', `checksum`) NOT IN ('vehicleservicerequest/10_alter:7db26c64506a1605557c88ef6796919e9b004c1ac14669f8444de3e5d239fb3b', 'vehicleservicerequest/20_cleanup:fb416089683b045b72cda7fba40ff628655ef641c1dc2a207f58704fe7a6c218', 'vehicleservicerequest/30_assert:1ab4ff4acf6935331652dbe9846c5560e52de277b7909b7eacf77b41c7424d29', 'vehicleservicerequest/40_fk:70b8c35674eb6d2ec6ff13ffb1dd34162137494f4a233f9ec6c358fd8c2d9233', 'vehicleservicerequestassignment/10_alter:47135fd4e5b7dccba93f3af3491d1e2bc34a7f3b3239487b3e30d318082b72e6', 'vehicleservicerequestassignment/20_cleanup:e2f09cefc0f97ec06c7037868355c258aa501d129077ec76044a67e9695c4c42', 'vehicleservicerequestassignment/30_assert:4d2c29839b0a1ab45864c9350a9ba5ffdfd99d8eac9ff17f194a5bdb7a00a0ba', 'vehicleservicerequestassignment/40_fk:2a31ff83c1589769bcbb4faa3b5472a53c368db7ed3d60b290dd2ea9bd199694', 'vehicleservicerequestdetails/10_alter:0d81375d84e6e5abc5e47c048c4b379587b1322e64d43f393454a7caae6bfde0', 'vehicleservicerequestdetails/20_cleanup:47cd72048a6bf51cddefb596a8758d63c8f92269fac9691acdac0ecae9301bad', 'vehicleservicerequestdetails/30_assert:72865010958e5ee3cdcd0df4d15f621541036fef9dc6020263cd3591efdbc449', 'vehicleservicerequestdetails/40_fk:60a7a898075b80c4668fc4e549f267670843b01da41c74eb911d6a3d47950f8a', 'vehicleservicerequestdetailsassigntment/10_alter:d942c17acee95084b8a8b5ca9b2f3504d9acca599df58185f7fd9eb468f6b4a7', 'vehicleservicerequestdetailsassigntment/20_cleanup:7f0174bcb97708be9ae289869181456107f044e053f3e7a859ab7fc37754ad84', 'vehicleservicerequestdetailsassigntment/30_assert:5e27bb2d6743df3e15eaf118842df7e06d80979853d3ef1b1cfa06250ec609c9', 'vehicleservicerequestdetailsassigntment/40_fk:3ef75f453ae871ae0176699df79dc0872bdcb4699f0e24341ec6719a3d9a0ff1');
SET @pf_ck = IF(@ck_bad = 0,
    'SELECT ''preflight: ledger matches this file'' AS preflight_checksum',
    CONCAT('SELECT `ABORT: ', @ck_bad, ' step(s) here were applied from a DIFFERENT version of this file. Their payloads changed. Compare before re-importing; do not delete ledger rows blindly.`'));
PREPARE _pf FROM @pf_ck; EXECUTE _pf; DEALLOCATE PREPARE _pf;

-- ---------------------------------------------------------------------
-- unit: vehicleservicerequest    (transaction)
-- ---------------------------------------------------------------------
             AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
        @@SQL_MODE,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- vehicleservicerequest/10_alter  --  source section A
-- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation
-- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this
-- table ONCE. The original migration split these across five sections and rebuilt some
-- tables four times; that is where its 12 hours went. Do not split this back up.
--
-- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps
-- every ADD COLUMN ... AFTER positioned exactly as before.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequest/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequest`
  MODIFY COLUMN `VehicleServiceRequestStatusID` int DEFAULT NULL,
  MODIFY COLUMN `UserIDInput` int DEFAULT NULL,
  MODIFY COLUMN `VehicleID` int DEFAULT NULL,
  MODIFY COLUMN `UserIDOwner` int DEFAULT NULL,
  ADD INDEX `vehicleservicerequest_UserIDInput` (`UserIDInput`),
  ADD INDEX `vehicleservicerequest_UserIDOwner` (`UserIDOwner`),
  ADD INDEX `vehicleservicerequest_VehicleID` (`VehicleID`),
  ADD INDEX `VehicleServiceRequestStatusID` (`VehicleServiceRequestStatusID`,`UserIDInput`,`VehicleID`,`UserIDOwner`)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '7db26c64506a1605557c88ef6796919e9b004c1ac14669f8444de3e5d239fb3b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequest/20_cleanup  --  source section B (= the original sections 5 and 6, merged)
-- 0-sentinels AND orphaned references -> NULL, one pass per table.
--     section 5:  WHERE `c` = 0
--     section 6:  WHERE `c` IS NOT NULL AND parent.ID IS NULL
--     merged   :  WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL)
-- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows.
--
-- DATA CHANGE, and this script cannot reverse it. Run the audit first.
-- In its own transaction, with the ledger row inside it: either the cleanup and the
-- record of it both land, or neither does.
-- ---------------------------------------------------------------------

START TRANSACTION;

SET @step = 'vehicleservicerequest/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `vehicleservicerequest` c
  LEFT JOIN `users` p0 ON c.`UserIDInput` = p0.`ID`
  LEFT JOIN `users` p1 ON c.`UserIDOwner` = p1.`ID`
  LEFT JOIN `vehicle` p2 ON c.`VehicleID` = p2.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p3 ON c.`VehicleServiceRequestStatusID` = p3.`ID`
   SET
       c.`UserIDInput` = IF((c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserIDInput`),
       c.`UserIDOwner` = IF((c.`UserIDOwner` IS NOT NULL AND (c.`UserIDOwner` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserIDOwner`),
       c.`VehicleID` = IF((c.`VehicleID` IS NOT NULL AND (c.`VehicleID` = 0 OR p2.`ID` IS NULL)), NULL, c.`VehicleID`),
       c.`VehicleServiceRequestStatusID` = IF((c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p3.`ID` IS NULL)), NULL, c.`VehicleServiceRequestStatusID`)
 WHERE  (c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p0.`ID` IS NULL))
    OR (c.`UserIDOwner` IS NOT NULL AND (c.`UserIDOwner` = 0 OR p1.`ID` IS NULL))
    OR (c.`VehicleID` IS NOT NULL AND (c.`VehicleID` = 0 OR p2.`ID` IS NULL))
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p3.`ID` IS NULL))', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), 'fb416089683b045b72cda7fba40ff628655ef641c1dc2a207f58704fe7a6c218', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- vehicleservicerequest/30_assert  --  source section 7b
-- Orphan assertion over this table's 4 foreign-key relationships.
--
-- This is what pays for 40_fk being fast. The original created the constraints with
-- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it
-- with a full table copy per constraint. This buys the same protection with one indexed
-- pass, and 40_fk then adds the constraints as metadata only.
--
-- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the
-- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it.
--
-- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would
-- have hit as ERROR 1452 inside section 8, but nothing has been created yet.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequest/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `vehicleservicerequest` c
  LEFT JOIN `users` p0 ON c.`UserIDInput` = p0.`ID`
  LEFT JOIN `users` p1 ON c.`UserIDOwner` = p1.`ID`
  LEFT JOIN `vehicle` p2 ON c.`VehicleID` = p2.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p3 ON c.`VehicleServiceRequestStatusID` = p3.`ID`
 WHERE  (c.`UserIDInput` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`UserIDOwner` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`VehicleID` IS NOT NULL AND p2.`ID` IS NULL)
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND p3.`ID` IS NULL);
SET @orphans = @orphans + @n;

-- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs.
SELECT IF(@orphans = 0,
          'vehicleservicerequest: orphan check OK - 0 rows across 4 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `vehicleservicerequest` still reference a parent that does not exist')
           UNION ALL
           SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.')
       ) AS preflight_orphans;

INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '1ab4ff4acf6935331652dbe9846c5560e52de277b7909b7eacf77b41c7424d29', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequest/40_fk  --  source section 8
-- The constraints, one ALTER for the whole table.
--
-- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With
-- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and
-- re-validates every row, which is where the original migration's 154 table copies
-- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans
-- and refuses to let this run otherwise.
-- ---------------------------------------------------------------------

SET @fk_prev = @@FOREIGN_KEY_CHECKS;

SET @step = 'vehicleservicerequest/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequest`
  ADD CONSTRAINT `vehicleservicerequest_UserIDInput` FOREIGN KEY (`UserIDInput`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequest_UserIDOwner` FOREIGN KEY (`UserIDOwner`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequest_VehicleID` FOREIGN KEY (`VehicleID`) REFERENCES `vehicle` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequest_VehicleServiceRequestStatusID` FOREIGN KEY (`VehicleServiceRequestStatusID`) REFERENCES `vehicleservicerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '70b8c35674eb6d2ec6ff13ffb1dd34162137494f4a233f9ec6c358fd8c2d9233', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;


-- ---------------------------------------------------------------------
-- unit: vehicleservicerequestassignment    (detail, owned by vehicleservicerequest)
-- ---------------------------------------------------------------------
             AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
        @@SQL_MODE,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- vehicleservicerequestassignment/10_alter  --  source section A
-- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation
-- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this
-- table ONCE. The original migration split these across five sections and rebuilt some
-- tables four times; that is where its 12 hours went. Do not split this back up.
--
-- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps
-- every ADD COLUMN ... AFTER positioned exactly as before.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestassignment/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestassignment`
  MODIFY COLUMN `VehicleServiceRequestID` int DEFAULT NULL,
  MODIFY COLUMN `VehicleServiceRequestStatusID` int DEFAULT NULL,
  MODIFY COLUMN `UserID` int DEFAULT NULL,
  ADD INDEX `vehicleservicerequestassignment_UserID` (`UserID`),
  ADD INDEX `vehicleservicerequestassignment_VehicleServiceRequestStatusID` (`VehicleServiceRequestStatusID`),
  ADD INDEX `VehicleServiceRequestID` (`VehicleServiceRequestID`,`VehicleServiceRequestStatusID`,`UserID`)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '47135fd4e5b7dccba93f3af3491d1e2bc34a7f3b3239487b3e30d318082b72e6', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestassignment/20_cleanup  --  source section B (= the original sections 5 and 6, merged)
-- 0-sentinels AND orphaned references -> NULL, one pass per table.
--     section 5:  WHERE `c` = 0
--     section 6:  WHERE `c` IS NOT NULL AND parent.ID IS NULL
--     merged   :  WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL)
-- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows.
--
-- DATA CHANGE, and this script cannot reverse it. Run the audit first.
-- In its own transaction, with the ledger row inside it: either the cleanup and the
-- record of it both land, or neither does.
-- ---------------------------------------------------------------------

START TRANSACTION;

SET @step = 'vehicleservicerequestassignment/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `vehicleservicerequestassignment` c
  LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID`
  LEFT JOIN `vehicleservicerequest` p1 ON c.`VehicleServiceRequestID` = p1.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p2 ON c.`VehicleServiceRequestStatusID` = p2.`ID`
   SET
       c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserID`),
       c.`VehicleServiceRequestID` = IF((c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p1.`ID` IS NULL)), NULL, c.`VehicleServiceRequestID`),
       c.`VehicleServiceRequestStatusID` = IF((c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p2.`ID` IS NULL)), NULL, c.`VehicleServiceRequestStatusID`)
 WHERE  (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL))
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p1.`ID` IS NULL))
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p2.`ID` IS NULL))', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), 'e2f09cefc0f97ec06c7037868355c258aa501d129077ec76044a67e9695c4c42', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- vehicleservicerequestassignment/30_assert  --  source section 7b
-- Orphan assertion over this table's 3 foreign-key relationships.
--
-- This is what pays for 40_fk being fast. The original created the constraints with
-- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it
-- with a full table copy per constraint. This buys the same protection with one indexed
-- pass, and 40_fk then adds the constraints as metadata only.
--
-- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the
-- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it.
--
-- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would
-- have hit as ERROR 1452 inside section 8, but nothing has been created yet.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestassignment/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `vehicleservicerequestassignment` c
  LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID`
  LEFT JOIN `vehicleservicerequest` p1 ON c.`VehicleServiceRequestID` = p1.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p2 ON c.`VehicleServiceRequestStatusID` = p2.`ID`
 WHERE  (c.`UserID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND p2.`ID` IS NULL);
SET @orphans = @orphans + @n;

-- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs.
SELECT IF(@orphans = 0,
          'vehicleservicerequestassignment: orphan check OK - 0 rows across 3 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `vehicleservicerequestassignment` still reference a parent that does not exist')
           UNION ALL
           SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.')
       ) AS preflight_orphans;

INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '4d2c29839b0a1ab45864c9350a9ba5ffdfd99d8eac9ff17f194a5bdb7a00a0ba', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestassignment/40_fk  --  source section 8
-- The constraints, one ALTER for the whole table.
--
-- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With
-- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and
-- re-validates every row, which is where the original migration's 154 table copies
-- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans
-- and refuses to let this run otherwise.
-- ---------------------------------------------------------------------

SET @fk_prev = @@FOREIGN_KEY_CHECKS;

SET @step = 'vehicleservicerequestassignment/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestassignment`
  ADD CONSTRAINT `vehicleservicerequestassignment_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestassignment_VehicleServiceRequestID` FOREIGN KEY (`VehicleServiceRequestID`) REFERENCES `vehicleservicerequest` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestassignment_VehicleServiceRequestStatusID` FOREIGN KEY (`VehicleServiceRequestStatusID`) REFERENCES `vehicleservicerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '2a31ff83c1589769bcbb4faa3b5472a53c368db7ed3d60b290dd2ea9bd199694', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;


-- ---------------------------------------------------------------------
-- unit: vehicleservicerequestdetails    (detail, owned by vehicleservicerequest)
-- ---------------------------------------------------------------------
             AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
        @@SQL_MODE,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetails/10_alter  --  source section A
-- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation
-- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this
-- table ONCE. The original migration split these across five sections and rebuilt some
-- tables four times; that is where its 12 hours went. Do not split this back up.
--
-- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps
-- every ADD COLUMN ... AFTER positioned exactly as before.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestdetails/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestdetails`
  MODIFY COLUMN `VehicleServiceRequestID` int DEFAULT NULL,
  MODIFY COLUMN `VehicleServiceTypeID` int DEFAULT NULL,
  MODIFY COLUMN `VehicleServiceRequestDetailStatus` int DEFAULT NULL,
  ADD INDEX `vehicleservicerequestdetails_VehicleServiceRequestDetailStatus` (`VehicleServiceRequestDetailStatus`),
  ADD INDEX `vehicleservicerequestdetails_VehicleServiceTypeID` (`VehicleServiceTypeID`),
  ADD INDEX `VehicleServiceRequestID` (`VehicleServiceRequestID`,`VehicleServiceTypeID`,`VehicleServiceRequestDetailStatus`)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '0d81375d84e6e5abc5e47c048c4b379587b1322e64d43f393454a7caae6bfde0', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetails/20_cleanup  --  source section B (= the original sections 5 and 6, merged)
-- 0-sentinels AND orphaned references -> NULL, one pass per table.
--     section 5:  WHERE `c` = 0
--     section 6:  WHERE `c` IS NOT NULL AND parent.ID IS NULL
--     merged   :  WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL)
-- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows.
--
-- DATA CHANGE, and this script cannot reverse it. Run the audit first.
-- In its own transaction, with the ledger row inside it: either the cleanup and the
-- record of it both land, or neither does.
-- ---------------------------------------------------------------------

START TRANSACTION;

SET @step = 'vehicleservicerequestdetails/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `vehicleservicerequestdetails` c
  LEFT JOIN `vehicleservicerequeststatus` p0 ON c.`VehicleServiceRequestDetailStatus` = p0.`ID`
  LEFT JOIN `vehicleservicerequest` p1 ON c.`VehicleServiceRequestID` = p1.`ID`
  LEFT JOIN `vehicleservicetype` p2 ON c.`VehicleServiceTypeID` = p2.`ID`
   SET
       c.`VehicleServiceRequestDetailStatus` = IF((c.`VehicleServiceRequestDetailStatus` IS NOT NULL AND (c.`VehicleServiceRequestDetailStatus` = 0 OR p0.`ID` IS NULL)), NULL, c.`VehicleServiceRequestDetailStatus`),
       c.`VehicleServiceRequestID` = IF((c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p1.`ID` IS NULL)), NULL, c.`VehicleServiceRequestID`),
       c.`VehicleServiceTypeID` = IF((c.`VehicleServiceTypeID` IS NOT NULL AND (c.`VehicleServiceTypeID` = 0 OR p2.`ID` IS NULL)), NULL, c.`VehicleServiceTypeID`)
 WHERE  (c.`VehicleServiceRequestDetailStatus` IS NOT NULL AND (c.`VehicleServiceRequestDetailStatus` = 0 OR p0.`ID` IS NULL))
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p1.`ID` IS NULL))
    OR (c.`VehicleServiceTypeID` IS NOT NULL AND (c.`VehicleServiceTypeID` = 0 OR p2.`ID` IS NULL))', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '47cd72048a6bf51cddefb596a8758d63c8f92269fac9691acdac0ecae9301bad', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetails/30_assert  --  source section 7b
-- Orphan assertion over this table's 3 foreign-key relationships.
--
-- This is what pays for 40_fk being fast. The original created the constraints with
-- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it
-- with a full table copy per constraint. This buys the same protection with one indexed
-- pass, and 40_fk then adds the constraints as metadata only.
--
-- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the
-- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it.
--
-- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would
-- have hit as ERROR 1452 inside section 8, but nothing has been created yet.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestdetails/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `vehicleservicerequestdetails` c
  LEFT JOIN `vehicleservicerequeststatus` p0 ON c.`VehicleServiceRequestDetailStatus` = p0.`ID`
  LEFT JOIN `vehicleservicerequest` p1 ON c.`VehicleServiceRequestID` = p1.`ID`
  LEFT JOIN `vehicleservicetype` p2 ON c.`VehicleServiceTypeID` = p2.`ID`
 WHERE  (c.`VehicleServiceRequestDetailStatus` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`VehicleServiceTypeID` IS NOT NULL AND p2.`ID` IS NULL);
SET @orphans = @orphans + @n;

-- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs.
SELECT IF(@orphans = 0,
          'vehicleservicerequestdetails: orphan check OK - 0 rows across 3 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `vehicleservicerequestdetails` still reference a parent that does not exist')
           UNION ALL
           SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.')
       ) AS preflight_orphans;

INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '72865010958e5ee3cdcd0df4d15f621541036fef9dc6020263cd3591efdbc449', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetails/40_fk  --  source section 8
-- The constraints, one ALTER for the whole table.
--
-- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With
-- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and
-- re-validates every row, which is where the original migration's 154 table copies
-- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans
-- and refuses to let this run otherwise.
-- ---------------------------------------------------------------------

SET @fk_prev = @@FOREIGN_KEY_CHECKS;

SET @step = 'vehicleservicerequestdetails/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestdetails`
  ADD CONSTRAINT `vehicleservicerequestdetails_VehicleServiceRequestDetailStatus` FOREIGN KEY (`VehicleServiceRequestDetailStatus`) REFERENCES `vehicleservicerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestdetails_VehicleServiceRequestID` FOREIGN KEY (`VehicleServiceRequestID`) REFERENCES `vehicleservicerequest` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestdetails_VehicleServiceTypeID` FOREIGN KEY (`VehicleServiceTypeID`) REFERENCES `vehicleservicetype` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '60a7a898075b80c4668fc4e549f267670843b01da41c74eb911d6a3d47950f8a', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;


-- ---------------------------------------------------------------------
-- unit: vehicleservicerequestdetailsassigntment    (detail, owned by vehicleservicerequest)
-- ---------------------------------------------------------------------
             AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%'
             AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%',
        @@SQL_MODE,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetailsassigntment/10_alter  --  source section A
-- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation
-- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this
-- table ONCE. The original migration split these across five sections and rebuilt some
-- tables four times; that is where its 12 hours went. Do not split this back up.
--
-- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps
-- every ADD COLUMN ... AFTER positioned exactly as before.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestdetailsassigntment/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestdetailsassigntment`
  MODIFY COLUMN `VehicleServiceRequestID` int DEFAULT NULL,
  MODIFY COLUMN `VehicleServiceRequestDetailID` int DEFAULT NULL,
  MODIFY COLUMN `VehicleServiceRequestStatusID` int DEFAULT NULL,
  MODIFY COLUMN `UserID` int DEFAULT NULL,
  ADD INDEX `vehicleservicerequestdetailsassigntment_UserID` (`UserID`),
  ADD INDEX `vehicleservicerequestdetailsassigntment_VehSerReqDetailID` (`VehicleServiceRequestDetailID`),
  ADD INDEX `vehicleservicerequestdetailsassigntment_VehSerReqStatusID` (`VehicleServiceRequestStatusID`),
  ADD INDEX `VehicleServiceRequestID` (`VehicleServiceRequestID`,`VehicleServiceRequestDetailID`,`VehicleServiceRequestStatusID`,`UserID`)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), 'd942c17acee95084b8a8b5ca9b2f3504d9acca599df58185f7fd9eb468f6b4a7', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetailsassigntment/20_cleanup  --  source section B (= the original sections 5 and 6, merged)
-- 0-sentinels AND orphaned references -> NULL, one pass per table.
--     section 5:  WHERE `c` = 0
--     section 6:  WHERE `c` IS NOT NULL AND parent.ID IS NULL
--     merged   :  WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL)
-- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows.
--
-- DATA CHANGE, and this script cannot reverse it. Run the audit first.
-- In its own transaction, with the ledger row inside it: either the cleanup and the
-- record of it both land, or neither does.
-- ---------------------------------------------------------------------

START TRANSACTION;

SET @step = 'vehicleservicerequestdetailsassigntment/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `vehicleservicerequestdetailsassigntment` c
  LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID`
  LEFT JOIN `vehicleservicerequestdetails` p1 ON c.`VehicleServiceRequestDetailID` = p1.`ID`
  LEFT JOIN `vehicleservicerequest` p2 ON c.`VehicleServiceRequestID` = p2.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p3 ON c.`VehicleServiceRequestStatusID` = p3.`ID`
   SET
       c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserID`),
       c.`VehicleServiceRequestDetailID` = IF((c.`VehicleServiceRequestDetailID` IS NOT NULL AND (c.`VehicleServiceRequestDetailID` = 0 OR p1.`ID` IS NULL)), NULL, c.`VehicleServiceRequestDetailID`),
       c.`VehicleServiceRequestID` = IF((c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p2.`ID` IS NULL)), NULL, c.`VehicleServiceRequestID`),
       c.`VehicleServiceRequestStatusID` = IF((c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p3.`ID` IS NULL)), NULL, c.`VehicleServiceRequestStatusID`)
 WHERE  (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL))
    OR (c.`VehicleServiceRequestDetailID` IS NOT NULL AND (c.`VehicleServiceRequestDetailID` = 0 OR p1.`ID` IS NULL))
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND (c.`VehicleServiceRequestID` = 0 OR p2.`ID` IS NULL))
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND (c.`VehicleServiceRequestStatusID` = 0 OR p3.`ID` IS NULL))', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '7f0174bcb97708be9ae289869181456107f044e053f3e7a859ab7fc37754ad84', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetailsassigntment/30_assert  --  source section 7b
-- Orphan assertion over this table's 4 foreign-key relationships.
--
-- This is what pays for 40_fk being fast. The original created the constraints with
-- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it
-- with a full table copy per constraint. This buys the same protection with one indexed
-- pass, and 40_fk then adds the constraints as metadata only.
--
-- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the
-- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it.
--
-- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would
-- have hit as ERROR 1452 inside section 8, but nothing has been created yet.
-- ---------------------------------------------------------------------

SET @step = 'vehicleservicerequestdetailsassigntment/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `vehicleservicerequestdetailsassigntment` c
  LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID`
  LEFT JOIN `vehicleservicerequestdetails` p1 ON c.`VehicleServiceRequestDetailID` = p1.`ID`
  LEFT JOIN `vehicleservicerequeststatus` p2 ON c.`VehicleServiceRequestStatusID` = p2.`ID`
  LEFT JOIN `vehicleservicerequest` p3 ON c.`VehicleServiceRequestID` = p3.`ID`
 WHERE  (c.`UserID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`VehicleServiceRequestDetailID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`VehicleServiceRequestStatusID` IS NOT NULL AND p2.`ID` IS NULL)
    OR (c.`VehicleServiceRequestID` IS NOT NULL AND p3.`ID` IS NULL);
SET @orphans = @orphans + @n;

-- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs.
SELECT IF(@orphans = 0,
          'vehicleservicerequestdetailsassigntment: orphan check OK - 0 rows across 4 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `vehicleservicerequestdetailsassigntment` still reference a parent that does not exist')
           UNION ALL
           SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.')
       ) AS preflight_orphans;

INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '5e27bb2d6743df3e15eaf118842df7e06d80979853d3ef1b1cfa06250ec609c9', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- vehicleservicerequestdetailsassigntment/40_fk  --  source section 8
-- The constraints, one ALTER for the whole table.
--
-- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With
-- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and
-- re-validates every row, which is where the original migration's 154 table copies
-- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans
-- and refuses to let this run otherwise.
-- ---------------------------------------------------------------------

SET @fk_prev = @@FOREIGN_KEY_CHECKS;

SET @step = 'vehicleservicerequestdetailsassigntment/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `vehicleservicerequestdetailsassigntment`
  ADD CONSTRAINT `vehicleservicerequestdetailsassigntment_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestdetailsassigntment_VehSerReqDetailID` FOREIGN KEY (`VehicleServiceRequestDetailID`) REFERENCES `vehicleservicerequestdetails` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestdetailsassigntment_VehSerReqStatusID` FOREIGN KEY (`VehicleServiceRequestStatusID`) REFERENCES `vehicleservicerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `vehicleservicerequestdetailsassigntment_VehicleServiceRequestID` FOREIGN KEY (`VehicleServiceRequestID`) REFERENCES `vehicleservicerequest` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '3ef75f453ae871ae0176699df79dc0872bdcb4699f0e24341ec6719a3d9a0ff1', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;


-- --------------------------------------------------------------- postflight
-- DID THE WORK ACTUALLY LAND?
--
-- Every step records itself in `_migration_ledger` immediately after its statement runs,
-- WITHOUT checking that the statement succeeded. That is inherited from step/ unchanged,
-- and there it is safe because run.sh runs mysql without --force, so the first error stops
-- everything. Imported by hand, the client decides -- and "continue on error" would leave
-- failed steps marked applied.
--
-- So this file checks its own work against information_schema before it finishes. It is
-- the only thing here that is NOT copied from step/. It reads; it changes nothing.

-- postflight-set: fk
SET @want_fk = 14;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_fk, @miss_fk
  FROM (SELECT 'vehicleservicerequest.vehicleservicerequest_UserIDInput' AS n UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_UserIDOwner' UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_VehicleID' UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_VehicleServiceRequestStatusID' UNION ALL SELECT 'vehicleservicerequestassignment.vehicleservicerequestassignment_UserID' UNION ALL SELECT 'vehicleservicerequestassignment.vehicleservicerequestassignment_VehicleServiceRequestID' UNION ALL SELECT 'vehicleservicerequestassignment.vehicleservicerequestassignment_VehicleServiceRequestStatusID' UNION ALL SELECT 'vehicleservicerequestdetails.vehicleservicerequestdetails_VehicleServiceRequestDetailStatus' UNION ALL SELECT 'vehicleservicerequestdetails.vehicleservicerequestdetails_VehicleServiceRequestID' UNION ALL SELECT 'vehicleservicerequestdetails.vehicleservicerequestdetails_VehicleServiceTypeID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_UserID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_VehSerReqDetailID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_VehSerReqStatusID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_VehicleServiceRequestID') _w
 WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', CONSTRAINT_NAME) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_TYPE = 'FOREIGN KEY');
SET @have_fk = @want_fk - @missn_fk;
SET @pf_fk = IF(@missn_fk = 0,
    CONCAT('SELECT ''postflight foreign keys: ', @have_fk, '/', @want_fk, ' present'' AS postflight_fk'),
    CONCAT('SELECT `ABORT postflight: MISSING foreign keys: ', @miss_fk,
           ' -- ', @missn_fk, ' of ', @want_fk, ' absent. A step is recorded in',
           ' _migration_ledger but did not take effect.`'));
PREPARE _pf FROM @pf_fk; EXECUTE _pf; DEALLOCATE PREPARE _pf;

-- postflight-set: idx
SET @want_idx = 14;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_idx, @miss_idx
  FROM (SELECT 'vehicleservicerequest.VehicleServiceRequestStatusID' AS n UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_UserIDInput' UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_UserIDOwner' UNION ALL SELECT 'vehicleservicerequest.vehicleservicerequest_VehicleID' UNION ALL SELECT 'vehicleservicerequestassignment.VehicleServiceRequestID' UNION ALL SELECT 'vehicleservicerequestassignment.vehicleservicerequestassignment_UserID' UNION ALL SELECT 'vehicleservicerequestassignment.vehicleservicerequestassignment_VehicleServiceRequestStatusID' UNION ALL SELECT 'vehicleservicerequestdetails.VehicleServiceRequestID' UNION ALL SELECT 'vehicleservicerequestdetails.vehicleservicerequestdetails_VehicleServiceRequestDetailStatus' UNION ALL SELECT 'vehicleservicerequestdetails.vehicleservicerequestdetails_VehicleServiceTypeID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.VehicleServiceRequestID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_UserID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_VehSerReqDetailID' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.vehicleservicerequestdetailsassigntment_VehSerReqStatusID') _w
 WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', INDEX_NAME) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE());
SET @have_idx = @want_idx - @missn_idx;
SET @pf_idx = IF(@missn_idx = 0,
    CONCAT('SELECT ''postflight indexes: ', @have_idx, '/', @want_idx, ' present'' AS postflight_idx'),
    CONCAT('SELECT `ABORT postflight: MISSING indexes: ', @miss_idx,
           ' -- ', @missn_idx, ' of ', @want_idx, ' absent. A step is recorded in',
           ' _migration_ledger but did not take effect.`'));
PREPARE _pf FROM @pf_idx; EXECUTE _pf; DEALLOCATE PREPARE _pf;

-- postflight-set: typ
SET @want_typ = 14;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_typ, @miss_typ
  FROM (SELECT 'vehicleservicerequest.UserIDInput.int' AS n UNION ALL SELECT 'vehicleservicerequest.UserIDOwner.int' UNION ALL SELECT 'vehicleservicerequest.VehicleID.int' UNION ALL SELECT 'vehicleservicerequest.VehicleServiceRequestStatusID.int' UNION ALL SELECT 'vehicleservicerequestassignment.UserID.int' UNION ALL SELECT 'vehicleservicerequestassignment.VehicleServiceRequestID.int' UNION ALL SELECT 'vehicleservicerequestassignment.VehicleServiceRequestStatusID.int' UNION ALL SELECT 'vehicleservicerequestdetails.VehicleServiceRequestDetailStatus.int' UNION ALL SELECT 'vehicleservicerequestdetails.VehicleServiceRequestID.int' UNION ALL SELECT 'vehicleservicerequestdetails.VehicleServiceTypeID.int' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.UserID.int' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.VehicleServiceRequestDetailID.int' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.VehicleServiceRequestID.int' UNION ALL SELECT 'vehicleservicerequestdetailsassigntment.VehicleServiceRequestStatusID.int') _w
 WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME, '.', DATA_TYPE) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE());
SET @have_typ = @want_typ - @missn_typ;
SET @pf_typ = IF(@missn_typ = 0,
    CONCAT('SELECT ''postflight retyped columns: ', @have_typ, '/', @want_typ, ' present'' AS postflight_typ'),
    CONCAT('SELECT `ABORT postflight: MISSING retyped columns: ', @miss_typ,
           ' -- ', @missn_typ, ' of ', @want_typ, ' absent. A step is recorded in',
           ' _migration_ledger but did not take effect.`'));
PREPARE _pf FROM @pf_typ; EXECUTE _pf; DEALLOCATE PREPARE _pf;

-- --------------------------------------------------------------- restore
-- IFNULL on all four: if phpMyAdmin resumed this import on a fresh connection the
-- @OLD_ variables are gone, and a bare SET from NULL is ERROR 1231.
--
-- The CAST is on the two timeouts and NOWHERE ELSE, on purpose. An INTEGER system
-- variable rejects IFNULL()'s result type outright (ERROR 1232, every time, not only
-- when NULL). FOREIGN_KEY_CHECKS is a boolean and SQL_MODE is a set; both accept a
-- string result, so they need no CAST. Do not "fix" the asymmetry, and do not copy
-- line 3's shape onto a new integer variable.
SET SESSION lock_wait_timeout = CAST(IFNULL(@OLD_LOCK_WAIT, 86400) AS UNSIGNED);
SET SESSION innodb_lock_wait_timeout = CAST(IFNULL(@OLD_INNODB_LOCK_WAIT, 50) AS UNSIGNED);
