-- StockFlow migration 005: optional budget/accounting dimensions for the
-- opening-debt report (Chương, Loại, Khoản, Mục in the BÁO CÁO CÔNG NỢ ĐẦU NĂM form).
-- Run after migration 004.

-- Run this file after selecting the target database.  Do not hard-code the
-- cPanel-prefixed database name here.

DELIMITER //

DROP PROCEDURE IF EXISTS add_debt_opening_dimensions //
CREATE PROCEDURE add_debt_opening_dimensions()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='debt_opening_balances' AND COLUMN_NAME='budget_chapter') THEN
    ALTER TABLE debt_opening_balances ADD COLUMN budget_chapter VARCHAR(20) NULL AFTER category;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='debt_opening_balances' AND COLUMN_NAME='budget_type') THEN
    ALTER TABLE debt_opening_balances ADD COLUMN budget_type VARCHAR(20) NULL AFTER budget_chapter;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='debt_opening_balances' AND COLUMN_NAME='budget_section') THEN
    ALTER TABLE debt_opening_balances ADD COLUMN budget_section VARCHAR(20) NULL AFTER budget_type;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='debt_opening_balances' AND COLUMN_NAME='budget_item') THEN
    ALTER TABLE debt_opening_balances ADD COLUMN budget_item VARCHAR(20) NULL AFTER budget_section;
  END IF;
END //

DELIMITER ;
CALL add_debt_opening_dimensions();
DROP PROCEDURE add_debt_opening_dimensions;