SET @schema_name := DATABASE();

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'source_type'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN source_type VARCHAR(20) NOT NULL DEFAULT 'MANUAL' AFTER location_id"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'source_ref_id'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN source_ref_id BIGINT UNSIGNED NULL AFTER source_type"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'order_id'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN order_id BIGINT UNSIGNED NULL AFTER source_ref_id"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'expense_category'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN expense_category VARCHAR(50) NULL AFTER order_id"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'reference_label'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN reference_label VARCHAR(190) NULL AFTER expense_category"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND COLUMN_NAME = 'is_active'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER description"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE expenses
SET source_type = 'MANUAL'
WHERE COALESCE(source_type, '') = '';

UPDATE expenses
SET is_active = 1
WHERE is_active IS NULL;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND INDEX_NAME = 'uq_expenses_pos_shipping'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD UNIQUE KEY uq_expenses_pos_shipping (source_type, expense_category, order_id)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @schema_name
      AND TABLE_NAME = 'expenses'
      AND INDEX_NAME = 'idx_expenses_location_date_active'
  ),
  'SELECT 1',
  "ALTER TABLE expenses ADD KEY idx_expenses_location_date_active (location_id, expense_date, is_active)"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;