-- ResellBD Step 18: reseller catalog, pricing snapshots and customer order requests.
-- Apply only to the NEW ResellBD database after migrations 001-009.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS reseller_catalog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reseller_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NOT NULL,
  selling_price_paisa BIGINT UNSIGNED NOT NULL,
  cost_snapshot_paisa BIGINT UNSIGNED NOT NULL,
  margin_percent DECIMAL(7,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  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_reseller_catalog_variant (reseller_id,variant_id),
  KEY idx_reseller_catalog_active (reseller_id,active), KEY idx_reseller_catalog_product (product_id),
  CONSTRAINT fk_reseller_catalog_reseller FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reseller_catalog_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reseller_catalog_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT chk_reseller_catalog_margin CHECK (margin_percent >= 0 AND margin_percent <= 500)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS reseller_customer_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_code VARCHAR(32) NOT NULL,
  reseller_id BIGINT UNSIGNED NOT NULL,
  customer_name VARCHAR(160) NOT NULL,
  customer_phone VARCHAR(30) NOT NULL,
  district VARCHAR(100) NOT NULL,
  delivery_address VARCHAR(500) NOT NULL,
  customer_note VARCHAR(1000) NULL,
  subtotal_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  delivery_fee_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  total_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('new','confirmed','cancelled','fulfilled') NOT NULL DEFAULT 'new',
  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_reseller_customer_order_code (order_code),
  KEY idx_reseller_customer_orders_owner (reseller_id,status,created_at), KEY idx_reseller_customer_orders_phone (customer_phone),
  CONSTRAINT fk_reseller_customer_orders_reseller FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS reseller_customer_order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reseller_order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(240) NOT NULL,
  variant_name_snapshot VARCHAR(190) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  cost_snapshot_paisa BIGINT UNSIGNED NOT NULL,
  selling_price_paisa BIGINT UNSIGNED NOT NULL,
  line_total_paisa BIGINT UNSIGNED NOT NULL,
  estimated_profit_paisa BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id), KEY idx_reseller_order_items_order (reseller_order_id),
  CONSTRAINT fk_reseller_order_items_order FOREIGN KEY (reseller_order_id) REFERENCES reseller_customer_orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_reseller_order_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_reseller_order_items_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS reseller_storefronts (
  reseller_id BIGINT UNSIGNED NOT NULL,
  store_token CHAR(32) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (reseller_id), UNIQUE KEY uq_reseller_store_token (store_token),
  CONSTRAINT fk_reseller_storefront_owner FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;