-- ResellBD Step 28: bounded price suggestions, pause/resume, dry-run and rollback history.
-- Apply only to the NEW ResellBD database after migrations 001-019.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS ai_pricing_settings (
 id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
 paused TINYINT(1) NOT NULL DEFAULT 1,
 min_margin_bps SMALLINT UNSIGNED NOT NULL DEFAULT 2000,
 max_discount_bps SMALLINT UNSIGNED NOT NULL DEFAULT 1500,
 max_price_change_bps SMALLINT UNSIGNED NOT NULL DEFAULT 1000,
 delivery_cost_paisa BIGINT UNSIGNED NOT NULL DEFAULT 8000,
 platform_cost_bps SMALLINT UNSIGNED NOT NULL DEFAULT 200,
 round_to_paisa INT UNSIGNED NOT NULL DEFAULT 500,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_ai_pricing_settings_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT IGNORE INTO ai_pricing_settings(id,paused,min_margin_bps,max_discount_bps,max_price_change_bps,delivery_cost_paisa,platform_cost_bps,round_to_paisa) VALUES(1,1,2000,1500,1000,8000,200,500);
CREATE TABLE IF NOT EXISTS ai_pricing_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_type ENUM('dry_run','apply','rollback') NOT NULL,
 status ENUM('success','partial','error') NOT NULL DEFAULT 'success',
 requested_by BIGINT UNSIGNED NULL,
 item_count INT UNSIGNED NOT NULL DEFAULT 0,
 changed_count INT UNSIGNED NOT NULL DEFAULT 0,
 message VARCHAR(1000) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_ai_pricing_run_actor FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 KEY idx_ai_pricing_runs_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS ai_price_change_log (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 run_id BIGINT UNSIGNED NOT NULL,
 queue_id BIGINT UNSIGNED NOT NULL,
 old_suggested_price_paisa BIGINT UNSIGNED NULL,
 new_suggested_price_paisa BIGINT UNSIGNED NOT NULL,
 source_price_paisa BIGINT UNSIGNED NOT NULL,
 reason VARCHAR(500) NOT NULL,
 rolled_back_at DATETIME NULL,
 rolled_back_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_ai_price_log_run FOREIGN KEY(run_id) REFERENCES ai_pricing_runs(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_ai_price_log_queue FOREIGN KEY(queue_id) REFERENCES ai_product_queue(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_ai_price_log_rollback_actor FOREIGN KEY(rolled_back_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 KEY idx_ai_price_log_queue(queue_id,created_at), KEY idx_ai_price_log_rollback(rolled_back_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;