-- =====================================================================
-- 40_budgetandtarget.sql
-- Budget and Target
--
-- 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), 12 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 (14 links):
--   /budget-and-target-periodes
--   /budget-and-targets
--   /budget-and-targets/all
--   /budget-and-targets/approval-mm
--   /budget-and-targets/approval-pm
--   /budget-and-targets/approval-sm
--   /budget-and-targets/cancel-mm
--   /budget-and-targets/cancel-pm
--   /budget-and-targets/cancel-sm
--   /budget-and-targets/create
--   /budget-and-targets/head-dept
--   /budget-and-targets/mm
--   /budget-and-targets/pm
--   /budget-and-targets/sm

-- --------------------------------------------------------------- prologue
-- run.sh sources step/_lib/session.sql for you. phpMyAdmin will not, so it is inlined.
SET @OLD_SQL_MODE = @@SQL_MODE;
SET SQL_MODE = 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,
        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 @OLD_FK = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 1;
SET @OLD_LOCK_WAIT = @@SESSION.lock_wait_timeout;
SET @OLD_INNODB_LOCK_WAIT = @@SESSION.innodb_lock_wait_timeout;
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 ('budgetandtarget/10_alter', 'budgetandtarget/20_cleanup', 'budgetandtarget/30_assert', 'budgetandtarget/40_fk', 'budgetandtargetachievements/30_assert', 'budgetandtargetachievements/40_fk', 'budgetandtargetassignment/10_alter', 'budgetandtargetassignment/20_cleanup', 'budgetandtargetassignment/30_assert', 'budgetandtargetassignment/40_fk', 'budgetandtargetreal/30_assert', 'budgetandtargetreal/40_fk')
   AND CONCAT(`step`, ':', `checksum`) NOT IN ('budgetandtarget/10_alter:f8a8bea6325d48a390a9ee150e86270bff537d61bc53353e33d9ff53acf09fe9', 'budgetandtarget/20_cleanup:061188a7a46bb49be4d8b574c0a116e76bc74164e231f9435f1cb1167c08da21', 'budgetandtarget/30_assert:67ccb5c3e0319ee329b0025557699b01cc873e4aaa31b27995e236dc3539dfaa', 'budgetandtarget/40_fk:c96eb11ebc0cc599504f3cc406a7596f81e59186eb6f0311c4bd2e0b7603ac12', 'budgetandtargetachievements/30_assert:c6c0d4d84d3fb2a0505d8ab4a11cdbc674fa5d6931dbff72454775cfd25dd521', 'budgetandtargetachievements/40_fk:201ec6c452dda613013d0e02d6efae40b8ca6e0097859f94d09f57dadd079aae', 'budgetandtargetassignment/10_alter:5f5a72a0cc6f1dc6d8a240a2d6802c7c4e6ef48ea9833ebeb9e3394cef50590b', 'budgetandtargetassignment/20_cleanup:5cd80d8c7a96202a7081c6a80f9843664b88088fb0bc35018fc31c1491f099f6', 'budgetandtargetassignment/30_assert:73bf6d73eee3e0c3d45c2d888d106e1d6ee282f86d92bf7ad8ec32227292bd40', 'budgetandtargetassignment/40_fk:1289b0672640aed6c2dbe80e85a09a5c23b9b83c040a04706544ceb39b635486', 'budgetandtargetreal/30_assert:ff1d4ceb74b6b33978b1d256edbe67f585fd3e7b54de37bc3b0749f51a70c6ce', 'budgetandtargetreal/40_fk:7b46baaeb081164063c74d8ba97c7c02107d945d4948811e359a5859692fc1fc');
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: budgetandtarget    (transaction)
-- ---------------------------------------------------------------------
SET SQL_MODE = 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,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- budgetandtarget/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 = 'budgetandtarget/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtarget`
  MODIFY COLUMN `UnitPricePM` decimal(25,2) NOT NULL AFTER `QtyTargetMM`,
  MODIFY COLUMN `UnitPriceSM` decimal(25,2) NOT NULL AFTER `UnitPricePM`,
  MODIFY COLUMN `UnitPriceMM` decimal(25,2) NOT NULL AFTER `UnitPriceSM`,
  MODIFY COLUMN `ValueBudgetUSDPM` decimal(25,2) NOT NULL AFTER `UnitPriceMM`,
  MODIFY COLUMN `ValueBudgetUSDSM` decimal(25,2) NOT NULL AFTER `ValueBudgetUSDPM`,
  MODIFY COLUMN `ValueBudgetUSDMM` decimal(25,2) NOT NULL AFTER `ValueBudgetUSDSM`,
  MODIFY COLUMN `ValueTargetUSDPM` decimal(25,2) NOT NULL AFTER `ValueBudgetUSDMM`,
  MODIFY COLUMN `ValueTargetUSDSM` decimal(25,2) NOT NULL AFTER `ValueTargetUSDPM`,
  MODIFY COLUMN `ValueTargetUSDMM` decimal(25,2) NOT NULL AFTER `ValueTargetUSDSM`,
  MODIFY COLUMN `BudgetAndTargetStatusID` int DEFAULT NULL,
  MODIFY COLUMN `BudgetAndTargetPeriodeID` int DEFAULT NULL,
  MODIFY COLUMN `UserIDInput` int DEFAULT NULL,
  MODIFY COLUMN `UserIDSales` int DEFAULT NULL,
  MODIFY COLUMN `PrincipalID` int DEFAULT NULL,
  MODIFY COLUMN `DivisionID` int DEFAULT NULL,
  MODIFY COLUMN `CompanyID` int DEFAULT NULL,
  ADD INDEX `budgetandtarget_BudgetAndTargetPeriodeID` (`BudgetAndTargetPeriodeID`),
  ADD INDEX `budgetandtarget_CompanyID` (`CompanyID`),
  ADD INDEX `budgetandtarget_DivisionID` (`DivisionID`),
  ADD INDEX `budgetandtarget_UserIDInput` (`UserIDInput`),
  ADD INDEX `budgetandtarget_UserIDSales` (`UserIDSales`)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), 'f8a8bea6325d48a390a9ee150e86270bff537d61bc53353e33d9ff53acf09fe9', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtarget/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 = 'budgetandtarget/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `budgetandtarget` c
  LEFT JOIN `budgetandtargetperiode` p0 ON c.`BudgetAndTargetPeriodeID` = p0.`ID`
  LEFT JOIN `budgetandtargetstatus` p1 ON c.`BudgetAndTargetStatusID` = p1.`ID`
  LEFT JOIN `company` p2 ON c.`CompanyID` = p2.`ID`
  LEFT JOIN `division` p3 ON c.`DivisionID` = p3.`ID`
  LEFT JOIN `principal` p4 ON c.`PrincipalID` = p4.`ID`
  LEFT JOIN `users` p5 ON c.`UserIDInput` = p5.`ID`
  LEFT JOIN `users` p6 ON c.`UserIDSales` = p6.`ID`
   SET
       c.`BudgetAndTargetPeriodeID` = IF((c.`BudgetAndTargetPeriodeID` IS NOT NULL AND (c.`BudgetAndTargetPeriodeID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BudgetAndTargetPeriodeID`),
       c.`BudgetAndTargetStatusID` = IF((c.`BudgetAndTargetStatusID` IS NOT NULL AND (c.`BudgetAndTargetStatusID` = 0 OR p1.`ID` IS NULL)), NULL, c.`BudgetAndTargetStatusID`),
       c.`CompanyID` = IF((c.`CompanyID` IS NOT NULL AND (c.`CompanyID` = 0 OR p2.`ID` IS NULL)), NULL, c.`CompanyID`),
       c.`DivisionID` = IF((c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p3.`ID` IS NULL)), NULL, c.`DivisionID`),
       c.`PrincipalID` = IF((c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p4.`ID` IS NULL)), NULL, c.`PrincipalID`),
       c.`UserIDInput` = IF((c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p5.`ID` IS NULL)), NULL, c.`UserIDInput`),
       c.`UserIDSales` = IF((c.`UserIDSales` IS NOT NULL AND (c.`UserIDSales` = 0 OR p6.`ID` IS NULL)), NULL, c.`UserIDSales`)
 WHERE  (c.`BudgetAndTargetPeriodeID` IS NOT NULL AND (c.`BudgetAndTargetPeriodeID` = 0 OR p0.`ID` IS NULL))
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND (c.`BudgetAndTargetStatusID` = 0 OR p1.`ID` IS NULL))
    OR (c.`CompanyID` IS NOT NULL AND (c.`CompanyID` = 0 OR p2.`ID` IS NULL))
    OR (c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p3.`ID` IS NULL))
    OR (c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p4.`ID` IS NULL))
    OR (c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p5.`ID` IS NULL))
    OR (c.`UserIDSales` IS NOT NULL AND (c.`UserIDSales` = 0 OR p6.`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(), '061188a7a46bb49be4d8b574c0a116e76bc74164e231f9435f1cb1167c08da21', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- budgetandtarget/30_assert  --  source section 7b
-- Orphan assertion over this table's 7 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 = 'budgetandtarget/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `budgetandtarget` c
  LEFT JOIN `budgetandtargetperiode` p0 ON c.`BudgetAndTargetPeriodeID` = p0.`ID`
  LEFT JOIN `budgetandtargetstatus` p1 ON c.`BudgetAndTargetStatusID` = p1.`ID`
  LEFT JOIN `company` p2 ON c.`CompanyID` = p2.`ID`
  LEFT JOIN `division` p3 ON c.`DivisionID` = p3.`ID`
  LEFT JOIN `principal` p4 ON c.`PrincipalID` = p4.`ID`
  LEFT JOIN `users` p5 ON c.`UserIDInput` = p5.`ID`
  LEFT JOIN `users` p6 ON c.`UserIDSales` = p6.`ID`
 WHERE  (c.`BudgetAndTargetPeriodeID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`CompanyID` IS NOT NULL AND p2.`ID` IS NULL)
    OR (c.`DivisionID` IS NOT NULL AND p3.`ID` IS NULL)
    OR (c.`PrincipalID` IS NOT NULL AND p4.`ID` IS NULL)
    OR (c.`UserIDInput` IS NOT NULL AND p5.`ID` IS NULL)
    OR (c.`UserIDSales` IS NOT NULL AND p6.`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,
          'budgetandtarget: orphan check OK - 0 rows across 7 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `budgetandtarget` 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(), '67ccb5c3e0319ee329b0025557699b01cc873e4aaa31b27995e236dc3539dfaa', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtarget/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 FOREIGN_KEY_CHECKS = 0;

SET @step = 'budgetandtarget/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtarget`
  ADD CONSTRAINT `budgetandtarget_BudgetAndTargetPeriodeID` FOREIGN KEY (`BudgetAndTargetPeriodeID`) REFERENCES `budgetandtargetperiode` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_BudgetAndTargetStatusID` FOREIGN KEY (`BudgetAndTargetStatusID`) REFERENCES `budgetandtargetstatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_CompanyID` FOREIGN KEY (`CompanyID`) REFERENCES `company` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_DivisionID` FOREIGN KEY (`DivisionID`) REFERENCES `division` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_PrincipalID` FOREIGN KEY (`PrincipalID`) REFERENCES `principal` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_UserIDInput` FOREIGN KEY (`UserIDInput`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtarget_UserIDSales` FOREIGN KEY (`UserIDSales`) REFERENCES `users` (`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(), 'c96eb11ebc0cc599504f3cc406a7596f81e59186eb6f0311c4bd2e0b7603ac12', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1);

-- ---------------------------------------------------------------------
-- unit: budgetandtargetachievements    (detail, owned by budgetandtarget)
-- ---------------------------------------------------------------------
SET SQL_MODE = 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,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- budgetandtargetachievements/30_assert  --  source section 7b
-- Orphan assertion over this table's 7 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 = 'budgetandtargetachievements/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `budgetandtargetachievements` c
  LEFT JOIN `barang` p0 ON c.`BarangID` = p0.`ID`
  LEFT JOIN `budgetandtargetperiode` p1 ON c.`BudgetAndTargetPeriodeID` = p1.`ID`
  LEFT JOIN `budgetandtargetstatus` p2 ON c.`BudgetAndTargetStatusID` = p2.`ID`
  LEFT JOIN `company` p3 ON c.`CompanyID` = p3.`ID`
  LEFT JOIN `division` p4 ON c.`DivisionID` = p4.`ID`
  LEFT JOIN `users` p5 ON c.`UserIDInput` = p5.`ID`
  LEFT JOIN `users` p6 ON c.`UserIDSales` = p6.`ID`
 WHERE  (c.`BarangID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`BudgetAndTargetPeriodeID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND p2.`ID` IS NULL)
    OR (c.`CompanyID` IS NOT NULL AND p3.`ID` IS NULL)
    OR (c.`DivisionID` IS NOT NULL AND p4.`ID` IS NULL)
    OR (c.`UserIDInput` IS NOT NULL AND p5.`ID` IS NULL)
    OR (c.`UserIDSales` IS NOT NULL AND p6.`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,
          'budgetandtargetachievements: orphan check OK - 0 rows across 7 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `budgetandtargetachievements` 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(), 'c6c0d4d84d3fb2a0505d8ab4a11cdbc674fa5d6931dbff72454775cfd25dd521', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtargetachievements/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 FOREIGN_KEY_CHECKS = 0;

SET @step = 'budgetandtargetachievements/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtargetachievements`
  ADD CONSTRAINT `budgetandtargetachievements_BarangID` FOREIGN KEY (`BarangID`) REFERENCES `barang` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_BudgetAndTargetPeriodeID` FOREIGN KEY (`BudgetAndTargetPeriodeID`) REFERENCES `budgetandtargetperiode` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_BudgetAndTargetStatusID` FOREIGN KEY (`BudgetAndTargetStatusID`) REFERENCES `budgetandtargetstatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_CompanyID` FOREIGN KEY (`CompanyID`) REFERENCES `company` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_DivisionID` FOREIGN KEY (`DivisionID`) REFERENCES `division` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_UserIDInput` FOREIGN KEY (`UserIDInput`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetachievements_UserIDSales` FOREIGN KEY (`UserIDSales`) REFERENCES `users` (`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(), '201ec6c452dda613013d0e02d6efae40b8ca6e0097859f94d09f57dadd079aae', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1);

-- ---------------------------------------------------------------------
-- unit: budgetandtargetassignment    (detail, owned by budgetandtarget)
-- ---------------------------------------------------------------------
SET SQL_MODE = 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,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- budgetandtargetassignment/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 = 'budgetandtargetassignment/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtargetassignment`
  MODIFY COLUMN `BudgetAndTargetID` int DEFAULT NULL,
  MODIFY COLUMN `BudgetAndTargetStatusID` int DEFAULT NULL,
  MODIFY COLUMN `UserID` int DEFAULT NULL,
  ADD INDEX `budgetandtargetassignment_BudgetAndTargetStatusID` (`BudgetAndTargetStatusID`),
  ADD INDEX `budgetandtargetassignment_UserID` (`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(), '5f5a72a0cc6f1dc6d8a240a2d6802c7c4e6ef48ea9833ebeb9e3394cef50590b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtargetassignment/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 = 'budgetandtargetassignment/20_cleanup';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'UPDATE `budgetandtargetassignment` c
  LEFT JOIN `budgetandtarget` p0 ON c.`BudgetAndTargetID` = p0.`ID`
  LEFT JOIN `budgetandtargetstatus` p1 ON c.`BudgetAndTargetStatusID` = p1.`ID`
  LEFT JOIN `users` p2 ON c.`UserID` = p2.`ID`
   SET
       c.`BudgetAndTargetID` = IF((c.`BudgetAndTargetID` IS NOT NULL AND (c.`BudgetAndTargetID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BudgetAndTargetID`),
       c.`BudgetAndTargetStatusID` = IF((c.`BudgetAndTargetStatusID` IS NOT NULL AND (c.`BudgetAndTargetStatusID` = 0 OR p1.`ID` IS NULL)), NULL, c.`BudgetAndTargetStatusID`),
       c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p2.`ID` IS NULL)), NULL, c.`UserID`)
 WHERE  (c.`BudgetAndTargetID` IS NOT NULL AND (c.`BudgetAndTargetID` = 0 OR p0.`ID` IS NULL))
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND (c.`BudgetAndTargetStatusID` = 0 OR p1.`ID` IS NULL))
    OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 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(), '5cd80d8c7a96202a7081c6a80f9843664b88088fb0bc35018fc31c1491f099f6', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

COMMIT;
-- ---------------------------------------------------------------------
-- budgetandtargetassignment/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 = 'budgetandtargetassignment/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `budgetandtargetassignment` c
  LEFT JOIN `budgetandtarget` p0 ON c.`BudgetAndTargetID` = p0.`ID`
  LEFT JOIN `budgetandtargetstatus` p1 ON c.`BudgetAndTargetStatusID` = p1.`ID`
  LEFT JOIN `users` p2 ON c.`UserID` = p2.`ID`
 WHERE  (c.`BudgetAndTargetID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`UserID` 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,
          'budgetandtargetassignment: orphan check OK - 0 rows across 3 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `budgetandtargetassignment` 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(), '73bf6d73eee3e0c3d45c2d888d106e1d6ee282f86d92bf7ad8ec32227292bd40', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtargetassignment/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 FOREIGN_KEY_CHECKS = 0;

SET @step = 'budgetandtargetassignment/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtargetassignment`
  ADD CONSTRAINT `budgetandtargetassignment_BudgetAndTargetID` FOREIGN KEY (`BudgetAndTargetID`) REFERENCES `budgetandtarget` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetassignment_BudgetAndTargetStatusID` FOREIGN KEY (`BudgetAndTargetStatusID`) REFERENCES `budgetandtargetstatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetassignment_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`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(), '1289b0672640aed6c2dbe80e85a09a5c23b9b83c040a04706544ceb39b635486', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1);

-- ---------------------------------------------------------------------
-- unit: budgetandtargetreal    (detail, owned by budgetandtarget)
-- ---------------------------------------------------------------------
SET SQL_MODE = 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,
        CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''),
               ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES'));
-- ---------------------------------------------------------------------
-- budgetandtargetreal/30_assert  --  source section 7b
-- Orphan assertion over this table's 7 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 = 'budgetandtargetreal/30_assert';
SET @t0 = NOW(3);
SET @orphans = 0;
SELECT COUNT(*) INTO @n FROM `budgetandtargetreal` c
  LEFT JOIN `budgetandtargetperiode` p0 ON c.`BudgetAndTargetPeriodeID` = p0.`ID`
  LEFT JOIN `budgetandtargetstatus` p1 ON c.`BudgetAndTargetStatusID` = p1.`ID`
  LEFT JOIN `company` p2 ON c.`CompanyID` = p2.`ID`
  LEFT JOIN `division` p3 ON c.`DivisionID` = p3.`ID`
  LEFT JOIN `principal` p4 ON c.`PrincipalID` = p4.`ID`
  LEFT JOIN `users` p5 ON c.`UserIDInput` = p5.`ID`
  LEFT JOIN `users` p6 ON c.`UserIDSales` = p6.`ID`
 WHERE  (c.`BudgetAndTargetPeriodeID` IS NOT NULL AND p0.`ID` IS NULL)
    OR (c.`BudgetAndTargetStatusID` IS NOT NULL AND p1.`ID` IS NULL)
    OR (c.`CompanyID` IS NOT NULL AND p2.`ID` IS NULL)
    OR (c.`DivisionID` IS NOT NULL AND p3.`ID` IS NULL)
    OR (c.`PrincipalID` IS NOT NULL AND p4.`ID` IS NULL)
    OR (c.`UserIDInput` IS NOT NULL AND p5.`ID` IS NULL)
    OR (c.`UserIDSales` IS NOT NULL AND p6.`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,
          'budgetandtargetreal: orphan check OK - 0 rows across 7 relationships',
          (SELECT CONCAT('ABORT: ', @orphans, ' rows in `budgetandtargetreal` 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(), 'ff1d4ceb74b6b33978b1d256edbe67f585fd3e7b54de37bc3b0749f51a70c6ce', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- budgetandtargetreal/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 FOREIGN_KEY_CHECKS = 0;

SET @step = 'budgetandtargetreal/40_fk';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `budgetandtargetreal`
  ADD CONSTRAINT `budgetandtargetreal_BudgetAndTargetPeriodeID` FOREIGN KEY (`BudgetAndTargetPeriodeID`) REFERENCES `budgetandtargetperiode` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_BudgetAndTargetStatusID` FOREIGN KEY (`BudgetAndTargetStatusID`) REFERENCES `budgetandtargetstatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_CompanyID` FOREIGN KEY (`CompanyID`) REFERENCES `company` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_DivisionID` FOREIGN KEY (`DivisionID`) REFERENCES `division` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_PrincipalID` FOREIGN KEY (`PrincipalID`) REFERENCES `principal` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_UserIDInput` FOREIGN KEY (`UserIDInput`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `budgetandtargetreal_UserIDSales` FOREIGN KEY (`UserIDSales`) REFERENCES `users` (`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(), '7b46baaeb081164063c74d8ba97c7c02107d945d4948811e359a5859692fc1fc', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;

SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1);

-- --------------------------------------------------------------- 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 = 24;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_fk, @miss_fk
  FROM (SELECT 'budgetandtarget.budgetandtarget_BudgetAndTargetPeriodeID' AS n UNION ALL SELECT 'budgetandtarget.budgetandtarget_BudgetAndTargetStatusID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_CompanyID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_DivisionID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_PrincipalID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_UserIDInput' UNION ALL SELECT 'budgetandtarget.budgetandtarget_UserIDSales' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_BarangID' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_BudgetAndTargetPeriodeID' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_BudgetAndTargetStatusID' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_CompanyID' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_DivisionID' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_UserIDInput' UNION ALL SELECT 'budgetandtargetachievements.budgetandtargetachievements_UserIDSales' UNION ALL SELECT 'budgetandtargetassignment.budgetandtargetassignment_BudgetAndTargetID' UNION ALL SELECT 'budgetandtargetassignment.budgetandtargetassignment_BudgetAndTargetStatusID' UNION ALL SELECT 'budgetandtargetassignment.budgetandtargetassignment_UserID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_BudgetAndTargetPeriodeID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_BudgetAndTargetStatusID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_CompanyID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_DivisionID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_PrincipalID' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_UserIDInput' UNION ALL SELECT 'budgetandtargetreal.budgetandtargetreal_UserIDSales') _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 = 7;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_idx, @miss_idx
  FROM (SELECT 'budgetandtarget.budgetandtarget_BudgetAndTargetPeriodeID' AS n UNION ALL SELECT 'budgetandtarget.budgetandtarget_CompanyID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_DivisionID' UNION ALL SELECT 'budgetandtarget.budgetandtarget_UserIDInput' UNION ALL SELECT 'budgetandtarget.budgetandtarget_UserIDSales' UNION ALL SELECT 'budgetandtargetassignment.budgetandtargetassignment_BudgetAndTargetStatusID' UNION ALL SELECT 'budgetandtargetassignment.budgetandtargetassignment_UserID') _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 = 19;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_typ, @miss_typ
  FROM (SELECT 'budgetandtarget.BudgetAndTargetPeriodeID.int' AS n UNION ALL SELECT 'budgetandtarget.BudgetAndTargetStatusID.int' UNION ALL SELECT 'budgetandtarget.CompanyID.int' UNION ALL SELECT 'budgetandtarget.DivisionID.int' UNION ALL SELECT 'budgetandtarget.PrincipalID.int' UNION ALL SELECT 'budgetandtarget.UnitPriceMM.decimal' UNION ALL SELECT 'budgetandtarget.UnitPricePM.decimal' UNION ALL SELECT 'budgetandtarget.UnitPriceSM.decimal' UNION ALL SELECT 'budgetandtarget.UserIDInput.int' UNION ALL SELECT 'budgetandtarget.UserIDSales.int' UNION ALL SELECT 'budgetandtarget.ValueBudgetUSDMM.decimal' UNION ALL SELECT 'budgetandtarget.ValueBudgetUSDPM.decimal' UNION ALL SELECT 'budgetandtarget.ValueBudgetUSDSM.decimal' UNION ALL SELECT 'budgetandtarget.ValueTargetUSDMM.decimal' UNION ALL SELECT 'budgetandtarget.ValueTargetUSDPM.decimal' UNION ALL SELECT 'budgetandtarget.ValueTargetUSDSM.decimal' UNION ALL SELECT 'budgetandtargetassignment.BudgetAndTargetID.int' UNION ALL SELECT 'budgetandtargetassignment.BudgetAndTargetStatusID.int' UNION ALL SELECT 'budgetandtargetassignment.UserID.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);
SET FOREIGN_KEY_CHECKS = IFNULL(@OLD_FK, 1);
SET SQL_MODE = IFNULL(@OLD_SQL_MODE, @@SQL_MODE);