-- ==========================================================
-- Scrap Module Cleanup Script (Final v3.0.0+)
-- Purpose: Verify and fix llx_scrap table structure and foreign keys
-- Compatible with MySQL 8+ / MariaDB 10.6+ / Dolibarr 22+
-- ==========================================================

START TRANSACTION;

-- ✅ 1️⃣ Ensure all required columns exist and have correct types
ALTER TABLE llx_scrap
  MODIFY fk_product INT NOT NULL,
  MODIFY fk_warehouse INT NOT NULL,
  MODIFY qty DOUBLE NOT NULL,
  MODIFY cost DOUBLE NOT NULL DEFAULT 0,
  MODIFY reason VARCHAR(255),
  MODIFY scrap_date DATETIME NOT NULL,
  MODIFY fk_user_scrap INT DEFAULT NULL,
  MODIFY entity INT DEFAULT 1,
  MODIFY fk_mo INT DEFAULT NULL;

-- ✅ 2️⃣ Ensure all required indexes exist (ignore if already exist)
CREATE INDEX IF NOT EXISTS idx_scrap_entity ON llx_scrap (entity);
CREATE INDEX IF NOT EXISTS idx_scrap_product ON llx_scrap (fk_product);
CREATE INDEX IF NOT EXISTS idx_scrap_warehouse ON llx_scrap (fk_warehouse);
CREATE INDEX IF NOT EXISTS idx_scrap_user_scrap ON llx_scrap (fk_user_scrap);
CREATE INDEX IF NOT EXISTS idx_scrap_mo ON llx_scrap (fk_mo);

-- ✅ 3️⃣ Drop any invalid or duplicate foreign keys (optional safety)
ALTER TABLE llx_scrap
  DROP FOREIGN KEY IF EXISTS fk_scrap_product,
  DROP FOREIGN KEY IF EXISTS fk_scrap_warehouse,
  DROP FOREIGN KEY IF EXISTS fk_scrap_user_scrap,
  DROP FOREIGN KEY IF EXISTS fk_scrap_mo;

-- ✅ 4️⃣ Recreate valid foreign key constraints
ALTER TABLE llx_scrap
  ADD CONSTRAINT fk_scrap_product FOREIGN KEY (fk_product) REFERENCES llx_product (rowid),
  ADD CONSTRAINT fk_scrap_warehouse FOREIGN KEY (fk_warehouse) REFERENCES llx_entrepot (rowid),
  ADD CONSTRAINT fk_scrap_user_scrap FOREIGN KEY (fk_user_scrap) REFERENCES llx_user (rowid),
  ADD CONSTRAINT fk_scrap_mo FOREIGN KEY (fk_mo) REFERENCES llx_mrp_mo (rowid);

-- ✅ 5️⃣ Optional data consistency cleanup (remove orphans)
DELETE s FROM llx_scrap s
LEFT JOIN llx_product p ON s.fk_product = p.rowid
WHERE p.rowid IS NULL;

DELETE s FROM llx_scrap s
LEFT JOIN llx_entrepot w ON s.fk_warehouse = w.rowid
WHERE w.rowid IS NULL;

DELETE s FROM llx_scrap s
LEFT JOIN llx_user u ON s.fk_user_scrap = u.rowid
WHERE s.fk_user_scrap IS NOT NULL AND u.rowid IS NULL;

DELETE s FROM llx_scrap s
LEFT JOIN llx_mrp_mo m ON s.fk_mo = m.rowid
WHERE s.fk_mo IS NOT NULL AND m.rowid IS NULL;

-- ✅ 6️⃣ Remove logically invalid data
DELETE FROM llx_scrap
WHERE qty <= 0 OR scrap_date > NOW();

-- ✅ 7️⃣ Optimize and confirm success
OPTIMIZE TABLE llx_scrap;

COMMIT;

SELECT '✅ llx_scrap table structure and data verified successfully — Scrap Module v3.0.0+' AS result;
