-- Gold Jewellery Software - Multi-item Sales, HUID, Wastage, Stone Value
-- Migration 008: supports a proper multi-item sales cart plus three new
-- per-item attributes used in the gold trade:
--   - huid: the BIS Hallmark Unique ID (mandatory on Indian gold jewellery
--     since 2023) — 6-character alphanumeric, optional here since older
--     stock may predate it.
--   - wastage_percent: extra gold-rate-priced weight charged to cover
--     melting/crafting loss, as a % of net weight. Set once when the item
--     enters stock (purchase or manual add) and rarely changes — NOT
--     re-entered per sale.
--   - stone_value: monetary value of set stones/diamonds, separate from
--     gold weight since stones aren't priced by gold rate.
-- purchase_items and sales_items get the same three columns so both are
-- snapshotted at transaction time, same pattern as making_charge_type/
-- hsn_code in migration 002.
--
-- Uses the information_schema-checked pattern from migration 007, not
-- `ADD COLUMN IF NOT EXISTS` — that clause proved unreliable on this
-- deployment's server. Safe to run any number of times.
USE gold_jewellery;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jewellery_items' AND COLUMN_NAME = 'huid');
SET @sql := IF(@exists = 0, 'ALTER TABLE jewellery_items ADD COLUMN huid VARCHAR(6) NULL AFTER barcode', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jewellery_items' AND COLUMN_NAME = 'wastage_percent');
SET @sql := IF(@exists = 0, 'ALTER TABLE jewellery_items ADD COLUMN wastage_percent DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER making_charge', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jewellery_items' AND COLUMN_NAME = 'stone_value');
SET @sql := IF(@exists = 0, 'ALTER TABLE jewellery_items ADD COLUMN stone_value DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER wastage_percent', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'purchase_items' AND COLUMN_NAME = 'huid');
SET @sql := IF(@exists = 0, 'ALTER TABLE purchase_items ADD COLUMN huid VARCHAR(6) NULL AFTER item_code', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'purchase_items' AND COLUMN_NAME = 'wastage_percent');
SET @sql := IF(@exists = 0, 'ALTER TABLE purchase_items ADD COLUMN wastage_percent DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER making_charge_type', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'purchase_items' AND COLUMN_NAME = 'stone_value');
SET @sql := IF(@exists = 0, 'ALTER TABLE purchase_items ADD COLUMN stone_value DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER wastage_percent', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_items' AND COLUMN_NAME = 'huid');
SET @sql := IF(@exists = 0, 'ALTER TABLE sales_items ADD COLUMN huid VARCHAR(6) NULL AFTER item_code', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_items' AND COLUMN_NAME = 'wastage_percent');
SET @sql := IF(@exists = 0, 'ALTER TABLE sales_items ADD COLUMN wastage_percent DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER making_charge_type', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_items' AND COLUMN_NAME = 'wastage_value');
SET @sql := IF(@exists = 0, 'ALTER TABLE sales_items ADD COLUMN wastage_value DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER wastage_percent', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Note: sales_items already has an unused `stone_charge` column from the
-- original schema (migration 001) — that's used for the stone value on a
-- sale line, no new column needed for it.