-- =====================================================================
-- The six schema changes `php artisan migrate` still owes production_testing,
-- written as plain DDL so migrate never has to run.
--
--   Target   : production_testing
--   Written  : 2026-08-26
--   Server   : verified against MySQL 8.4.11
--   Verified : every column and index below was read from production_testing
--              itself on 2026-08-26, not assumed from dev
--
-- ---------------------------------------------------------------------
-- WHY THIS FILE EXISTS
-- ---------------------------------------------------------------------
-- `migrations` is empty here, so `php artisan migrate` would replay all 36
-- migrations starting at create_users_table. Everything the step tree owed
-- has already landed EXCEPT the five migrations below (six objects), which
-- `two db/step/zz_finalize/10_seed_migrations.sql` deliberately withholds
-- from its 28-row seed because they do real work the step tree cannot.
--
--   2026_08_24_100000  create diagnosticemailtest              -> §1
--   2026_08_26_100000  widen SpecialCondition to varchar(500)  -> §2
--   2026_08_01_100000  two quotation list indexes              -> §3
--   2026_08_22_100000  quotation won-month index               -> §4
--   2026_08_22_100100  quotationassignment won-lookup index    -> §5
--
-- §6 is NOT a migration and NOT a schema change. It is the server-wide
-- sql_mode this application now assumes. It lives here because this is the
-- file an operator runs on a freshly restored database, and getting it
-- wrong is invisible until the first over-long INSERT. Read its own header
-- before running it -- it is the only statement here that reaches outside
-- the current database.
--
-- The other three migrations that file withholds are already satisfied here
-- and need nothing: sampleorderdetail.IsStockReturnable exists, the eleven
-- visitplanstatus rows are present, and the notification table exists.
--
-- ---------------------------------------------------------------------
-- ⚠️ THERE IS NO TRANSACTION IN THIS FILE, AND THAT IS NOT AN OVERSIGHT
-- ---------------------------------------------------------------------
-- Every statement here is DDL. MySQL implicitly COMMITs before and after
-- each one, so START TRANSACTION would be theatre: a failure at §4 does not
-- roll back §1-§3. Each section is therefore written to be independently
-- re-runnable -- run the file again after fixing whatever failed and the
-- sections that already landed turn into no-ops.
--
-- ---------------------------------------------------------------------
-- WHY THIS DOES NOT TOUCH `migrations`
-- ---------------------------------------------------------------------
-- Recording these five rows while the other 31 stay unrecorded does not make
-- `php artisan migrate` usable -- it would still replay create_users_table.
-- Half a ledger is worse than none: it reads as a promise the database
-- cannot keep. If you ever DO want them recorded, that is:
--
--   INSERT INTO `migrations` (`migration`, `batch`) VALUES
--     ('2026_08_01_100000_add_quotation_list_indexes', 1), ... ;
--
-- but do it as part of seeding all 36, not as a side effect of this file.
--
-- ---------------------------------------------------------------------
-- IDEMPOTENCE
-- ---------------------------------------------------------------------
-- MySQL has no `ADD INDEX IF NOT EXISTS` (that is MariaDB), so §2-§5 use the
-- PREPARE/EXECUTE guard: read information_schema, build either the real DDL
-- or `DO 0`, run that. It mirrors what each migration's own up() does.
-- =====================================================================

-- Fail fast rather than hang the application. ALTER takes a brief metadata
-- lock at each end; if a long transaction holds the table, the ALTER waits
-- and EVERY new query queues behind it. Ten seconds, then give up.
SET SESSION lock_wait_timeout = 10;

-- =====================================================================
-- §1  diagnosticemailtest -- CREATE TABLE
-- ---------------------------------------------------------------------
-- One row per diagnostic test send; the store behind Settings > Diagnostics
-- > Email Queue. Shape copied from `notification`: PascalCase columns, PK
-- `ID`, soft delete via `IsDeleted`, audit via `Tanggal` + `UserIDInput`,
-- no created_at/updated_at, no foreign keys.
--
-- ⚠️ latin1 ON PURPOSE. Every table in this schema is latin1_swedish_ci
-- while the PDO connection is utf8mb4. utf8mb4 here would buy nothing and
-- would raise "Illegal mix of collations" the first time `UserID` is joined
-- against a latin1 lookup. Both text columns are handled in the app:
-- `Recipient` is user input guarded by App\Rules\FitsColumnCharset, and
-- `Error` is exception text transliterated to CP1252 by
-- DiagnosticEmailTest::latin1Error.
-- =====================================================================

CREATE TABLE IF NOT EXISTS `diagnosticemailtest` (
    `ID`          int          NOT NULL AUTO_INCREMENT,
    `IsDeleted`   int          NOT NULL DEFAULT 0,
    `UserID`      int          NOT NULL DEFAULT 0,
    `Recipient`   varchar(190) NOT NULL,
    `Mode`        varchar(10)  NOT NULL,
    `Status`      varchar(10)  NOT NULL DEFAULT 'pending',
    `Error`       text         NULL,
    `SentAt`      datetime     NULL,
    `Tanggal`     datetime     NOT NULL,
    `UserIDInput` int          NOT NULL DEFAULT 0,
    PRIMARY KEY (`ID`),
    KEY `idx_diagnosticemailtest_recent` (`IsDeleted`, `ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- =====================================================================
-- §2  proposedcreditceilinghistory.SpecialCondition -- varchar(100) -> (500)
-- ---------------------------------------------------------------------
-- THE ONE ON THIS LIST THAT FAILS SILENTLY IN PRODUCTION.
--
-- The history table is a snapshot: CreditCeilingWorkflow::transition() saves
-- the proposedcreditceiling row and copies every column into a history
-- record inside the SAME transaction. A narrower history column therefore
-- cannot merely truncate -- under STRICT it raises 1406 and rolls back the
-- whole stage transition. The status does not move and the stage email is
-- never sent, while ApprovalCeoActionRequest validated at max:500 quite
-- correctly, because it validates the PARENT column. 501 characters in a
-- field the form does not even write to.
--
-- Read from production_testing 2026-08-26:
--   proposedcreditceiling.SpecialCondition        varchar(500) latin1  NOT NULL
--   proposedcreditceilinghistory.SpecialCondition varchar(100) latin1  NOT NULL   <-- narrow
--
-- Widening is lossless: the column carries no index (checked against
-- information_schema.STATISTICS) and no row is rewritten in a way that can
-- fail. Non-additive in the letter of ATURAN ABSOLUT #2, additive in effect
-- -- the column only gains capacity.
--
-- ⚠️ CHARSET PINNED EXPLICITLY. A bare `varchar(500)` inherits the table
-- default; once any table here is converted to utf8mb4 that would silently
-- retype the column and break JOINs against latin1 ("Illegal mix of
-- collations" -- a runtime 500).
-- =====================================================================

SET @ddl := IF(
    (SELECT `CHARACTER_MAXIMUM_LENGTH`
       FROM `information_schema`.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE()
        AND `TABLE_NAME`   = 'proposedcreditceilinghistory'
        AND `COLUMN_NAME`  = 'SpecialCondition') >= 500,
    'DO 0',
    'ALTER TABLE `proposedcreditceilinghistory`
       MODIFY COLUMN `SpecialCondition`
       varchar(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- §3  quotation -- two list indexes, created INVISIBLE
-- ---------------------------------------------------------------------
-- `quotation` carries 19 secondary indexes and every one is a single-column
-- FK index minted by a constraint. `IsDeleted` and `Tanggal` are not indexed
-- at all and there is no composite on the table. Measured on a 10k-row
-- committed + ANALYZEd copy:
--
--   (IsDeleted, ID)          paginator COUNT becomes a covering range scan
--                            (~40 KB) instead of a clustered scan (~2.6 MB).
--                            Pays on EVERY request.
--   (IsDeleted, Tanggal, ID) date-range filter and sort=tanggal stop filesorting.
--
-- ⚠️ BOTH ARE CREATED INVISIBLE, exactly as the migration does on MySQL 8.0+
-- (dev confirms: IS_VISIBLE = NO for both). The optimizer ignores an
-- invisible index entirely, so creating it carries zero risk of a plan
-- regression on an existing hot path. AN INVISIBLE INDEX NOBODY FLIPS DOES
-- NOTHING -- prove the numbers first:
--
--   SET SESSION optimizer_switch = 'use_invisible_indexes=on';   -- session var, not DDL
--   -- ... EXPLAIN your list query ...
--
-- then flip them for real:
--
--   ALTER TABLE `quotation` ALTER INDEX `idx_quotation_isdeleted_id` VISIBLE;
--   ALTER TABLE `quotation` ALTER INDEX `idx_quotation_isdeleted_tanggal_id` VISIBLE;
--
-- ALGORITHM=INPLACE, LOCK=NONE is pinned so MySQL ERRORS if it would have to
-- fall back to a full table copy, rather than silently rewriting a
-- production-sized table underneath you.
-- =====================================================================

SET @ddl := IF(
    (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE()
        AND `TABLE_NAME`   = 'quotation'
        AND `INDEX_NAME`   = 'idx_quotation_isdeleted_id') > 0,
    'DO 0',
    'ALTER TABLE `quotation`
       ADD INDEX `idx_quotation_isdeleted_id` (`IsDeleted`, `ID`)'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ddl := IF(
    (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE()
        AND `TABLE_NAME`   = 'quotation'
        AND `INDEX_NAME`   = 'idx_quotation_isdeleted_tanggal_id') > 0,
    'DO 0',
    'ALTER TABLE `quotation`
       ADD INDEX `idx_quotation_isdeleted_tanggal_id` (`IsDeleted`, `Tanggal`, `ID`)'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- §4  quotation -- won-month index, VISIBLE
-- ---------------------------------------------------------------------
-- For the two dashboard money widgets (KPI money tile + Omset 12 Bulan),
-- which run on every dashboard load for every user and scan the same slice:
--
--   WHERE quotation.IsDeleted = 0
--     AND quotation.QuotationStatusID IN (11, 12, 13)
--
-- COLUMN ORDER: status first (equality, an IN list of three), date second.
-- `IsDeleted` is deliberately NOT leading -- it is an equality too, but it is
-- 0 for ~98% of the table, so leading with it buys nothing while pushing the
-- selective column behind a near-constant. MySQL applies it as a row filter.
--
-- ⚠️ DO NOT OVERSELL THIS INDEX. The widgets' date bound lands on an
-- expression over a subquery (the WON month is
-- COALESCE(MIN(quotationassignment.Tanggal), quotation.QuotationDate),
-- projected inside a derived table and filtered outside it), so no index can
-- serve it. Measured on dev, the optimizer picks the pre-existing FK index
-- `quotation_QuotationStatusID` for the money queries and leaves this one
-- alone. It is kept because the design spec keeps it and it is additive and
-- harmless -- measure before claiming it is what makes the board fast.
--
-- VISIBLE, unlike §3: the query it serves was brand new in the same commit,
-- so there is no existing plan for it to regress. Dev confirms IS_VISIBLE=YES.
-- =====================================================================

SET @ddl := IF(
    (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE()
        AND `TABLE_NAME`   = 'quotation'
        AND `INDEX_NAME`   = 'idx_quotation_status_quotationdate') > 0,
    'DO 0',
    'ALTER TABLE `quotation`
       ADD INDEX `idx_quotation_status_quotationdate` (`QuotationStatusID`, `QuotationDate`),
       ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- §5  quotationassignment -- won-lookup index, VISIBLE
-- ---------------------------------------------------------------------
-- The other half of the dashboard money move. Every money figure asks WHEN a
-- quotation became won, and the answer is a correlated subquery evaluated
-- once per candidate header, on every dashboard load, for every user:
--
--   SELECT MIN(qa.Tanggal) FROM quotationassignment qa
--    WHERE qa.QuotationID = quotation.ID
--      AND qa.IsDeleted = 0
--      AND qa.QuotationStatusID IN (11, 12, 13)
--
-- This table already carries PRIMARY plus one FK index per foreign key
-- (qta_fk_QuotationID, qta_fk_QuotationStatusID, qta_fk_UserID) -- confirmed
-- again on production_testing 2026-08-26. MySQL uses one index per table
-- reference, so two single-column indexes do not compose: without a
-- composite the optimizer picks qta_fk_QuotationID and filters the rest row
-- by row, and a busy quotation is not one row.
--
-- COLUMN ORDER: QuotationID first (the correlation, and the most selective
-- predicate), QuotationStatusID second so the three-value IN list becomes a
-- range read inside that one header's slice rather than a filter over all of
-- its history. `IsDeleted` and `Tanggal` are deliberately excluded -- adding
-- them would make the index cover the whole subquery, but that is a new
-- measurement and a new decision, not a silent widening of this one.
-- =====================================================================

SET @ddl := IF(
    (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE()
        AND `TABLE_NAME`   = 'quotationassignment'
        AND `INDEX_NAME`   = 'idx_qta_quotation_status') > 0,
    'DO 0',
    'ALTER TABLE `quotationassignment`
       ADD INDEX `idx_qta_quotation_status` (`QuotationID`, `QuotationStatusID`),
       ALGORITHM=INPLACE, LOCK=NONE'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =====================================================================
-- §6  sql_mode -- drop STRICT_TRANS_TABLES, SERVER-WIDE
-- ---------------------------------------------------------------------
-- ⚠️ THIS IS THE ONLY STATEMENT IN THIS FILE THAT IS NOT SCOPED TO ONE
-- DATABASE. SET PERSIST writes /var/lib/mysql/mysqld-auto.cnf and applies
-- to EVERY database on this server and to EVERY application that connects
-- to it -- this app, legacy CC, and anything else sharing the instance.
--
-- WHY. `config/database.php` already removed STRICT_TRANS_TABLES from the
-- app's own connection via the `modes` array, so the app truncates rather
-- than raising 1406. Legacy CC never sets sql_mode at all -- dbhandler.php
-- sets only ATTR_ERRMODE and ATTR_DEFAULT_FETCH_MODE -- so it INHERITS the
-- server's. Without this statement the two halves of the same system
-- disagree about identical input: the app truncates, legacy throws 1406 /
-- 1364. This is what makes them agree.
--
-- WHAT IT COSTS, measured against this schema on 2026-08-26:
--   * 1855 NOT NULL columns without a default silently take 0 or ''
--     when omitted -- error 1364 becomes a warning;
--   * 1672 numeric columns silently take 0 on non-numeric input --
--     error 1366 becomes a warning;
--   * out-of-range numbers clamp; invalid dates become '0000-00-00';
--   * ERROR_FOR_DIVISION_BY_ZERO only raises in strict mode, so x/0
--     yields NULL rather than an error.
--
-- FOREIGN KEYS ARE UNAFFECTED. Verified by experiment 2026-08-27 with
-- these exact modes: an explicit 0, a non-numeric value coerced to 0, and
-- an omitted NOT NULL FK column all still fail with 1452; NULL into a
-- nullable FK succeeds. FK enforcement answers to foreign_key_checks, not
-- to sql_mode. Two consequences worth knowing:
--   * the ERROR GETS WORSE, not better -- strict mode said "1366 Incorrect
--     integer value" and named the column and the bad value; now the value
--     is silently coerced to 0 and the failure surfaces as "1452 foreign
--     key constraint fails", which names only the constraint;
--   * the 76 ID-named columns that carry NO FK constraint are now
--     completely unguarded: bad input becomes 0, no 1366, no 1452, saved.
--
-- NO_ZERO_IN_DATE and NO_ZERO_DATE are kept here even though the app's own
-- connection drops them (decided 2026-06-11: legacy writes zero dates and
-- they must stay writable through the APP). They stay at server level so
-- nothing else on this instance loses them by accident; the app overrides
-- what it needs on its own connection.
--
-- PRIVILEGE. SET PERSIST needs SYSTEM_VARIABLES_ADMIN and PERSIST_RO_
-- VARIABLES_ADMIN. Run it as an administrative account, not as the
-- application user.
--
-- IF YOU CANNOT PERSIST -- a managed instance that forbids it -- put the
-- same value in the server's my.cnf under [mysqld] and restart:
--     sql_mode = "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
--
-- TO REVERT: SET PERSIST sql_mode = DEFAULT;  (or RESET PERSIST sql_mode)
-- =====================================================================
-- ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
SET PERSIST sql_mode = 'NO_ENGINE_SUBSTITUTION';

-- SET PERSIST changes the GLOBAL value and records it, but it does NOT
-- touch sessions that are already open -- including this one. Existing
-- connections keep the old mode until they reconnect, so restart php-fpm /
-- Apache (or let the pool recycle) before concluding it did not work.
SELECT @@GLOBAL.sql_mode;

-- =====================================================================
-- VERIFY (run after the file)
-- =====================================================================
--   -- §1: expect one row, 10 columns
--   SELECT COUNT(*) AS cols FROM information_schema.COLUMNS
--    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'diagnosticemailtest';
--
--   -- §2: expect 500
--   SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS
--    WHERE TABLE_SCHEMA = DATABASE()
--      AND TABLE_NAME = 'proposedcreditceilinghistory'
--      AND COLUMN_NAME = 'SpecialCondition';
--
--   -- §3-§5: expect 4 rows, with IS_VISIBLE exactly as shown
--   SELECT TABLE_NAME, INDEX_NAME,
--          GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols,
--          IS_VISIBLE
--     FROM information_schema.STATISTICS
--    WHERE TABLE_SCHEMA = DATABASE()
--      AND INDEX_NAME IN ('idx_quotation_isdeleted_id',
--                         'idx_quotation_isdeleted_tanggal_id',
--                         'idx_quotation_status_quotationdate',
--                         'idx_qta_quotation_status')
--    GROUP BY TABLE_NAME, INDEX_NAME, IS_VISIBLE
--    ORDER BY TABLE_NAME, INDEX_NAME;
--
--   -- expected:
--   --   quotation            idx_quotation_isdeleted_id          IsDeleted,ID                    NO
--   --   quotation            idx_quotation_isdeleted_tanggal_id  IsDeleted,Tanggal,ID            NO
--   --   quotation            idx_quotation_status_quotationdate  QuotationStatusID,QuotationDate YES
--   --   quotationassignment  idx_qta_quotation_status            QuotationID,QuotationStatusID   YES
--
--   -- §6: expect exactly
--   --   ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,
--   --   ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
--   SELECT @@GLOBAL.sql_mode;
--
--   -- and that it will survive a restart -- expect one row:
--   SELECT VARIABLE_NAME, VARIABLE_VALUE
--     FROM performance_schema.persisted_variables
--    WHERE VARIABLE_NAME = 'sql_mode';
-- =====================================================================
--
-- STATUS ON production_testing (read 2026-08-27): §6 is ALREADY APPLIED.
-- @@GLOBAL.sql_mode matches the value above and the setting is recorded in
-- performance_schema.persisted_variables, with mysqld-auto.cnf written the
-- same day. Re-running §6 there is a harmless no-op.
-- =====================================================================