USE BeezifiIOM;

ALTER TABLE accounting_sync_log
    ADD COLUMN IF NOT EXISTS order_id INT UNSIGNED NULL AFTER integration_id,
    ADD COLUMN IF NOT EXISTS external_invoice_number VARCHAR(100) NULL AFTER external_invoice_id,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

SET @has_iom_order_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'accounting_sync_log'
      AND COLUMN_NAME = 'iom_order_id'
);
SET @sql := IF(
    @has_iom_order_id > 0,
    'UPDATE accounting_sync_log SET order_id = iom_order_id WHERE order_id IS NULL AND iom_order_id IS NOT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE accounting_sync_log
    MODIFY COLUMN status ENUM('pending', 'success', 'error') DEFAULT 'pending',
    ADD INDEX IF NOT EXISTS idx_order_id (order_id);

ALTER TABLE accounting_sync_log
    ADD UNIQUE KEY IF NOT EXISTS uq_order_integration (integration_id, order_id);

SET @sql := IF(
    @has_iom_order_id > 0,
    'ALTER TABLE accounting_sync_log DROP COLUMN iom_order_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE accounting_sync_log
SET status = 'success'
WHERE status = 'synced';

UPDATE accounting_sync_log
SET synced_at = COALESCE(synced_at, updated_at, created_at)
WHERE status = 'success' AND synced_at IS NULL;

ALTER TABLE exchange_rates
    ADD COLUMN IF NOT EXISTS currency CHAR(3) NULL AFTER base_currency,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER rate;

SET @has_target_currency := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'exchange_rates'
      AND COLUMN_NAME = 'target_currency'
);
SET @sql := IF(
    @has_target_currency > 0,
    'UPDATE exchange_rates SET currency = target_currency WHERE currency IS NULL AND target_currency IS NOT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_fetched_at := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'exchange_rates'
      AND COLUMN_NAME = 'fetched_at'
);
SET @sql := IF(
    @has_fetched_at > 0,
    'UPDATE exchange_rates SET updated_at = fetched_at WHERE updated_at IS NULL AND fetched_at IS NOT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE exchange_rates
    MODIFY COLUMN currency CHAR(3) NOT NULL;

SET @has_uq_pair := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'exchange_rates'
      AND INDEX_NAME = 'uq_pair'
);
SET @sql := IF(
    @has_uq_pair > 0,
    'ALTER TABLE exchange_rates DROP INDEX uq_pair',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE exchange_rates
    ADD UNIQUE KEY IF NOT EXISTS uq_pair (base_currency, currency);

SET @sql := IF(
    @has_target_currency > 0,
    'ALTER TABLE exchange_rates DROP COLUMN target_currency',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_fetched_at > 0,
    'ALTER TABLE exchange_rates DROP COLUMN fetched_at',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE integration_order_map
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER synced_at;

SET @has_synced_at := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'integration_order_map'
      AND COLUMN_NAME = 'synced_at'
);
SET @sql := IF(
    @has_synced_at > 0,
    'UPDATE integration_order_map SET created_at = synced_at WHERE created_at IS NULL AND synced_at IS NOT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE integrations
    MODIFY COLUMN api_key TEXT NULL,
    MODIFY COLUMN webhook_secret TEXT NULL;
