-- =====================================================================
-- 46_netsuite.sql
-- NetSuite invoice detail
--
-- 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.
--
-- 1 table(s), 2 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 (3 links):
--   /netsuite/print-sj
--   /netsuite/stock-barang
--   /netsuite/stock-barang-logistik

-- --------------------------------------------------------------- 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 ('netsuiteinvoicedetail/05_charset', 'netsuiteinvoicedetail/10_alter')
   AND CONCAT(`step`, ':', `checksum`) NOT IN ('netsuiteinvoicedetail/05_charset:be8a5e97c73535216874f4c016c8c86954fb2c8fde694731c84fcc30874555b5', 'netsuiteinvoicedetail/10_alter:e65882e0c93bf1b89231a11b65fe278f3404833b49b2b986c514f1b78f1507d6');
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: netsuiteinvoicedetail    (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'));
-- ---------------------------------------------------------------------
-- netsuiteinvoicedetail/05_charset  --  source section 1
-- Table default charset. Governs columns added LATER; it does not touch existing
-- columns. The columns that actually change charset are in 10_alter.
-- ---------------------------------------------------------------------

SET @step = 'netsuiteinvoicedetail/05_charset';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `netsuiteinvoicedetail` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci', 'DO 0');
PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step;
INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`)
     VALUES (@step, NOW(), 'be8a5e97c73535216874f4c016c8c86954fb2c8fde694731c84fcc30874555b5', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000)
ON DUPLICATE KEY UPDATE `step` = `step`;
-- ---------------------------------------------------------------------
-- netsuiteinvoicedetail/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 = 'netsuiteinvoicedetail/10_alter';
SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step);
SET @t0 = NOW(3);
SET @sql = IF(@todo, 'ALTER TABLE `netsuiteinvoicedetail`
  MODIFY COLUMN `Quarter` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `CompanyID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `CompanyName` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `CompanyDivision` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `SalesCustomer` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `SalesTransaction` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `ItemVendor` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `ItemBrand` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `ItemCode` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `ItemName` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `PersenProfit` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  MODIFY COLUMN `Profitability` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT 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(), 'e65882e0c93bf1b89231a11b65fe278f3404833b49b2b986c514f1b78f1507d6', 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: typ
SET @want_typ = 12;
SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '')
  INTO @missn_typ, @miss_typ
  FROM (SELECT 'netsuiteinvoicedetail.CompanyDivision.varchar' AS n UNION ALL SELECT 'netsuiteinvoicedetail.CompanyID.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.CompanyName.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.ItemBrand.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.ItemCode.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.ItemName.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.ItemVendor.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.PersenProfit.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.Profitability.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.Quarter.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.SalesCustomer.varchar' UNION ALL SELECT 'netsuiteinvoicedetail.SalesTransaction.varchar') _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);
