-- ResellBD Step 26: AI feed ingestion and validation queue only.
-- Apply only to the NEW ResellBD database after migrations 001-017.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS ai_sources (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(190) NOT NULL,
 source_type ENUM('json','csv') NOT NULL DEFAULT 'json',
 feed_url VARCHAR(1000) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'BDT',
 last_status ENUM('never','success','warning','error') NOT NULL DEFAULT 'never',
 last_message VARCHAR(1000) NULL,
 last_imported_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_ai_sources_enabled(enabled),
 CONSTRAINT fk_ai_source_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS ai_product_queue (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 source_id BIGINT UNSIGNED NOT NULL,
 external_key VARCHAR(190) NOT NULL,
 source_url VARCHAR(1000) NULL,
 title VARCHAR(240) NOT NULL,
 description TEXT NULL,
 image_url VARCHAR(1000) NULL,
 source_price_paisa BIGINT UNSIGNED NOT NULL,
 suggested_price_paisa BIGINT UNSIGNED NULL,
 stock_qty INT UNSIGNED NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'BDT',
 status ENUM('pending','validated','rejected','error') NOT NULL DEFAULT 'pending',
 validation_message VARCHAR(1000) NULL,
 content_hash CHAR(64) NOT NULL,
 last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_ai_queue_source_external(source_id,external_key),
 KEY idx_ai_queue_status_updated(status,updated_at), KEY idx_ai_queue_source(source_id,last_seen_at),
 CONSTRAINT fk_ai_queue_source FOREIGN KEY(source_id) REFERENCES ai_sources(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS ai_feed_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 source_id BIGINT UNSIGNED NOT NULL,
 started_by BIGINT UNSIGNED NULL,
 status ENUM('success','warning','error') NOT NULL,
 received_count INT UNSIGNED NOT NULL DEFAULT 0,
 accepted_count INT UNSIGNED NOT NULL DEFAULT 0,
 rejected_count INT UNSIGNED NOT NULL DEFAULT 0,
 message VARCHAR(1000) NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 finished_at DATETIME NULL,
 PRIMARY KEY(id), KEY idx_ai_feed_runs_source(source_id,started_at),
 CONSTRAINT fk_ai_feed_run_source FOREIGN KEY(source_id) REFERENCES ai_sources(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_ai_feed_run_actor FOREIGN KEY(started_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;