-- =====================================================================
-- 99_finalize.sql
-- seeds `migrations`, then verifies -- refuses to run until every step is applied
--
-- 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.
--
-- 0 table(s), 1 ledger step(s).
--
-- ⚠ IMPORT ORDER: LAST. Every other file -- including all of 90_no_menu/ -- must be in
--    first; this file checks and aborts if not.
--
-- ⚠ 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.
-- =====================================================================

-- --------------------------------------------------------------- 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 ('_finalize/10_seed_migrations')
   AND CONCAT(`step`, ':', `checksum`) NOT IN ('_finalize/10_seed_migrations:3a4dcc6f43217cde0f3a739d5fa13b1fa94820868ed00acc3ceb3f7ce6798186');
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: _finalize    (system)
-- ---------------------------------------------------------------------
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'));
-- ---------------------------------------------------------------------
-- _finalize/10_seed_migrations  --  source section 9
-- Seeds `migrations` with 28 rows so `php artisan migrate` afterwards is a short, guarded
-- pass instead of a replay of everything.
--
-- IN THIS MODEL THAT SEED IS A TRAP, WHICH IS WHY IT REFUSES TO RUN EARLY.
-- Seeding all 28 rows claims every app migration is applied. While only some units are
-- done that claim is false, and a later `php artisan migrate` would skip work still owed
-- to the tables that have not been migrated. So: this aborts unless every step is recorded.
--
-- Consequence, stated plainly: YOU CANNOT RUN `php artisan migrate` ON A PARTIALLY
-- MIGRATED DATABASE. The application still runs on the migrated modules without it,
-- because 00_system has already created the 8 tables the framework needs.
--
-- FOUR migrations are deliberately withheld from the list, each because it does real work
-- this tree cannot -- the IsStockReturnable backfill, the visitplanstatus lookup seed, the
-- two Quotation list indexes, and the notification table definition. After this, run
-- `php artisan migrate --pretend` and then `php artisan migrate`.
--
-- NEVER run `php artisan migrate:rollback` on production: everything is at batch 1, so one
-- stray rollback attempts 28 down() methods.
--
-- THE COMPLETENESS CHECK IS PART OF THE GUARD, not just the message above it. @todo is
-- ANDed with it, so on a client that continues past the ERROR 1242 the seed becomes DO 0
-- instead of running. It then records itself in the ledger having done nothing, and the
-- postflight in import/zz_finalize/99_finalize.sql fails on `migrations` holding 0 rows
-- instead of 28 -- loud and recoverable, where seeding a half-migrated database is
-- neither. To recover: DELETE the _finalize/10_seed_migrations row and re-import.
-- ---------------------------------------------------------------------

SET @applied = (SELECT COUNT(*) FROM `_migration_ledger` WHERE `step` NOT LIKE '\_finalize/%');
SELECT IF(@applied >= 631,
          CONCAT('all ', @applied, ' steps recorded - seeding `migrations`'),
          (SELECT CONCAT('ABORT: only ', @applied, ' of 631 steps are recorded')
           UNION ALL
           SELECT 'Seeding `migrations` now would tell artisan the untouched tables are done.')
       ) AS preflight_complete;

SET @step = '_finalize/10_seed_migrations';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step) AND (@applied >= 631);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'INSERT INTO `migrations` (`migration`, `batch`) VALUES
  (''0001_01_01_000000_create_users_table'', 1),
  (''0001_01_01_000001_create_cache_table'', 1),
  (''0001_01_01_000002_create_jobs_table'', 1),
  (''2026_05_19_100000_add_password_reset_columns_to_users_table'', 1),
  (''2026_05_28_074227_add_icon_to_menu_table'', 1),
  (''2026_05_29_025236_rename_users_columns_to_pascalcase_and_widen_password'', 1),
  (''2026_06_29_120000_drop_mismatched_company_product_pivot_fks'', 1),
  (''2026_07_02_134941_add_auto_increment_to_visitplan_ids'', 1),
  (''2026_07_09_120000_fix_companyprojectdetailcomp_productidcomp_fk_to_barang'', 1),
  (''2026_07_22_120000_drop_lying_complain_department_fks'', 1),
  (''2026_07_22_130000_drop_department_fks_on_complainitem_tables'', 1),
  (''2026_07_23_120000_add_auto_increment_to_vehicleservicerequest_ids'', 1),
  (''2026_07_24_090000_add_auto_increment_to_vehicletype_id'', 1),
  (''2026_07_24_090100_add_auto_increment_to_vehiclebrand_id'', 1),
  (''2026_07_24_090200_add_auto_increment_to_vehiclecolor_id'', 1),
  (''2026_07_24_090300_add_auto_increment_to_vehiclefueltype_id'', 1),
  (''2026_07_24_090400_add_auto_increment_to_vehiclebrandtype_id'', 1),
  (''2026_07_24_090500_add_auto_increment_to_vehicleassignment_id'', 1),
  (''2026_07_24_120000_repoint_company_product_pivot_fks_to_master_tables'', 1),
  (''2026_08_06_120000_repoint_project_detail_fks_to_companyprojectdetailcc'', 1),
  (''2026_08_13_100000_widen_remark_columns_to_text'', 1),
  (''2026_08_13_100100_restructure_country_to_int_pk_with_code'', 1),
  (''2026_08_13_100200_repoint_salesrebateinvoice_status_fks_to_detailstatus'', 1),
  (''2026_08_13_100300_add_legacy_only_columns_to_match_production'', 1),
  (''2026_08_13_100400_widen_decimals_to_match_legacy_precision'', 1),
  (''2026_08_13_100500_relax_depname_to_match_legacy'', 1),
  (''2026_08_14_100000_drop_wrong_paymentterm_fks'', 1),
  (''2026_08_14_100100_narrow_quotation_decimals_to_match_legacy'', 1)', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), '3a4dcc6f43217cde0f3a739d5fa13b1fa94820868ed00acc3ceb3f7ce6798186', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- zz_finalize/20_verify.sql  --  read-only, not a step
-- Prints what the tree produced. Records nothing, changes nothing, and is not in the
-- ledger, so it can be run at any point -- including on a half-migrated database.
-- ---------------------------------------------------------------------

SELECT COUNT(*) AS steps_recorded FROM `_migration_ledger`;
SELECT `step`, `applied_at`, `duration_ms`
  FROM `_migration_ledger`
 ORDER BY `applied_at` DESC
 LIMIT 10;
SELECT COUNT(*) AS foreign_keys
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = DATABASE();
SELECT COUNT(*) AS tables_in_schema
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE';
SELECT COUNT(*) AS seeded_migrations FROM `migrations`;
SELECT @@SQL_MODE AS sql_mode_in_effect, @@FOREIGN_KEY_CHECKS AS fk_checks;

-- --------------------------------------------------------------- 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.

SET @want_seed = 28;
SET @have_seed = (SELECT COUNT(*) FROM (SELECT 1 FROM `migrations`) _p);
SET @pf_seed = IF(@have_seed = @want_seed,
    CONCAT('SELECT ''postflight seeded migrations rows: ', @have_seed, '/', @want_seed, ' present'' AS postflight_seed'),
    CONCAT('SELECT `ABORT postflight: seeded migrations rows is ', @have_seed, ', expected ', @want_seed,
           '. The step is recorded in _migration_ledger but did not take effect.`'));
PREPARE _pf FROM @pf_seed; 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);