-- ResellBD Step 19: supplier feed staging, stock sync snapshots and order forwarding queue.
-- Apply only to the NEW ResellBD database after migrations 001-010.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_product_feeds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  supplier_id BIGINT UNSIGNED NOT NULL,
  feed_name VARCHAR(190) NOT NULL,
  feed_url VARCHAR(1000) NOT NULL,
  feed_format ENUM('json','csv') NOT NULL DEFAULT 'json',
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  last_synced_at DATETIME NULL,
  last_status ENUM('never','success','warning','error') NOT NULL DEFAULT 'never',
  last_message VARCHAR(1000) 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_supplier_feed_enabled(supplier_id,enabled),
  CONSTRAINT fk_supplier_feed_owner FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS supplier_feed_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  feed_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,
  stock_qty INT UNSIGNED NOT NULL DEFAULT 0,
  content_hash CHAR(64) NOT NULL,
  sync_status ENUM('new','changed','unchanged','unavailable','review') NOT NULL DEFAULT 'review',
  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_supplier_feed_external(feed_id,external_key),
  KEY idx_supplier_feed_items_status(feed_id,sync_status), KEY idx_supplier_feed_items_seen(feed_id,last_seen_at),
  CONSTRAINT fk_supplier_feed_items_feed FOREIGN KEY(feed_id) REFERENCES supplier_product_feeds(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS supplier_order_forwardings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reseller_order_id BIGINT UNSIGNED NOT NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  forwarding_code VARCHAR(40) NOT NULL,
  status ENUM('queued','sent_manually','accepted','processing','shipped','delivered','rejected','cancelled') NOT NULL DEFAULT 'queued',
  supplier_reference VARCHAR(190) NULL,
  note VARCHAR(1000) NULL,
  forwarded_by BIGINT UNSIGNED NULL,
  forwarded_at DATETIME NULL,
  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_supplier_forward_code(forwarding_code),
  KEY idx_supplier_forward_order(reseller_order_id), KEY idx_supplier_forward_supplier_status(supplier_id,status,created_at),
  CONSTRAINT fk_supplier_forward_order FOREIGN KEY(reseller_order_id) REFERENCES reseller_customer_orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_supplier_forward_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_supplier_forward_actor FOREIGN KEY(forwarded_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;