-- Gold Jewellery Software - Fix silently-skipped columns
-- Migration 007: several earlier migrations used
-- `ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...`, which needs MySQL
-- 8.0.29+ (or a fairly recent MariaDB) to work reliably — on some servers,
-- or when run through certain SQL clients that continue past
-- per-statement errors, that clause can silently fail for one statement
-- while the rest of the same script still succeeds. This migration
-- doesn't assume which ones were affected — it checks each column via
-- information_schema (which works on every MySQL/MariaDB version) and
-- only adds it if it's genuinely still missing. Safe to run any number
-- of times.
USE gold_jewellery;

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

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

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

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

-- order_items.making_charge_type
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'making_charge_type');
SET @sql := IF(@exists = 0, 'ALTER TABLE order_items ADD COLUMN making_charge_type ENUM(''AMOUNT'',''PER_GRAM'',''PERCENT'') NOT NULL DEFAULT ''AMOUNT'' AFTER making_charge', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

-- If username was just added by this migration (rather than back in 004),
-- it won't have the backfill or unique index from 004 yet — apply both,
-- guarded the same way.
UPDATE users SET username = SUBSTRING_INDEX(email, '@', 1) WHERE username IS NULL OR username = '';

SET @exists := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'uq_users_username');
SET @sql := IF(@exists = 0, 'ALTER TABLE users ADD UNIQUE KEY uq_users_username (username)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;