-- ResellBD Step 23: seller storefront profile and product approval workflow.
-- Apply ONLY to the new ResellBD database after migrations 001-014.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS seller_stores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  seller_user_id BIGINT UNSIGNED NOT NULL,
  store_name VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL,
  tagline VARCHAR(255) NULL,
  description TEXT NULL,
  logo_path VARCHAR(500) NULL,
  banner_path VARCHAR(500) NULL,
  contact_phone VARCHAR(32) NULL,
  status ENUM('draft','pending','approved','rejected','suspended') NOT NULL DEFAULT 'draft',
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  review_note 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),
  UNIQUE KEY uq_seller_store_user (seller_user_id),
  UNIQUE KEY uq_seller_store_slug (slug),
  KEY idx_seller_store_status (status,updated_at),
  CONSTRAINT fk_seller_store_user FOREIGN KEY (seller_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_seller_store_reviewer FOREIGN KEY (reviewed_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 product_moderation_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  actor_user_id BIGINT UNSIGNED NOT NULL,
  previous_status VARCHAR(24) NOT NULL,
  new_status VARCHAR(24) NOT NULL,
  note VARCHAR(1000) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_product_moderation_product (product_id,created_at),
  CONSTRAINT fk_product_moderation_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_product_moderation_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;