-- ===========================================================
--  Scrap Module Verification Script (v3.0.0+)
--  File: llx_scrap_verify.sql
--  Purpose:
--      Ensures table `llx_scrap` exists and matches
--      the expected production structure as of version 3.0.0+.
--      Safe to execute multiple times.
-- ===========================================================

-- ===========================================================
--  1️⃣  Create table if it does not exist
-- ===========================================================
CREATE TABLE IF NOT EXISTS llx_scrap (
  rowid INT AUTO_INCREMENT PRIMARY KEY,
  fk_product INT NOT NULL,
  fk_warehouse INT NOT NULL,
  qty DOUBLE NOT NULL,
  cost DOUBLE NOT NULL DEFAULT 0,
  reason VARCHAR(255),
  scrap_date DATETIME NOT NULL,
  fk_user_scrap INT NULL,
  entity INT DEFAULT 1,
  fk_mo INT DEFAULT NULL,

  INDEX idx_scrap_entity (entity),
  INDEX idx_scrap_product (fk_product),
  INDEX idx_scrap_warehouse (fk_warehouse),
  INDEX idx_scrap_user_scrap (fk_user_scrap),
  INDEX idx_scrap_mo (fk_mo),

  CONSTRAINT fk_scrap_product FOREIGN KEY (fk_product) REFERENCES llx_product(rowid),
  CONSTRAINT fk_scrap_warehouse FOREIGN KEY (fk_warehouse) REFERENCES llx_entrepot(rowid),
  CONSTRAINT fk_scrap_user_scrap FOREIGN KEY (fk_user_scrap) REFERENCES llx_user(rowid),
  CONSTRAINT fk_scrap_mo FOREIGN KEY (fk_mo) REFERENCES llx_mrp_mo(rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ===========================================================
--  2️⃣  COLUMN CHECKS
--  Adds missing columns or fixes type mismatches
-- ===========================================================
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS fk_product INT NOT NULL AFTER rowid;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS fk_warehouse INT NOT NULL AFTER fk_product;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS qty DOUBLE NOT NULL AFTER fk_warehouse;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS cost DOUBLE NOT NULL DEFAULT 0 AFTER qty;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS reason VARCHAR(255) AFTER cost;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS scrap_date DATETIME NOT NULL AFTER reason;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS fk_user_scrap INT NULL AFTER scrap_date;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS entity INT DEFAULT 1 AFTER fk_user_scrap;
ALTER TABLE llx_scrap ADD COLUMN IF NOT EXISTS fk_mo INT DEFAULT NULL AFTER entity;

-- Normalize column types if they already exist but differ
ALTER TABLE llx_scrap
  MODIFY qty DOUBLE NOT NULL,
  MODIFY cost DOUBLE NOT NULL DEFAULT 0,
  MODIFY entity INT DEFAULT 1;

-- ===========================================================
--  3️⃣  INDEX CHECKS
--  Ensure all required indexes exist
-- ===========================================================
ALTER TABLE llx_scrap ADD INDEX IF NOT EXISTS idx_scrap_entity (entity);
ALTER TABLE llx_scrap ADD INDEX IF NOT EXISTS idx_scrap_product (fk_product);
ALTER TABLE llx_scrap ADD INDEX IF NOT EXISTS idx_scrap_warehouse (fk_warehouse);
ALTER TABLE llx_scrap ADD INDEX IF NOT EXISTS idx_scrap_user_scrap (fk_user_scrap);
ALTER TABLE llx_scrap ADD INDEX IF NOT EXISTS idx_scrap_mo (fk_mo);

-- ===========================================================
--  4️⃣  CONSTRAINT CHECKS
--  Recreate foreign keys if missing
-- ===========================================================
ALTER TABLE llx_scrap
  ADD CONSTRAINT fk_scrap_product
  FOREIGN KEY (fk_product) REFERENCES llx_product(rowid)
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE llx_scrap
  ADD CONSTRAINT fk_scrap_warehouse
  FOREIGN KEY (fk_warehouse) REFERENCES llx_entrepot(rowid)
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE llx_scrap
  ADD CONSTRAINT fk_scrap_user_scrap
  FOREIGN KEY (fk_user_scrap) REFERENCES llx_user(rowid)
  ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE llx_scrap
  ADD CONSTRAINT fk_scrap_mo
  FOREIGN KEY (fk_mo) REFERENCES llx_mrp_mo(rowid)
  ON DELETE SET NULL ON UPDATE CASCADE;

-- ===========================================================
--  5️⃣  ENTITY NORMALIZATION
--  Set default entity = 1 if missing
-- ===========================================================
UPDATE llx_scrap SET entity = 1 WHERE entity IS NULL;

-- ===========================================================
--  6️⃣  STRUCTURE VERIFICATION SUMMARY
-- ===========================================================
SELECT 
  '✅ llx_scrap structure verified and updated — Scrap Module v3.0.0+' AS verification_result,
  COUNT(*) AS total_records,
  SUM(qty) AS total_qty,
  SUM(cost * qty) AS total_value
FROM llx_scrap;
