-- =========================================================
-- 025_purchase_requests.sql
-- Sprint 9: purchase request stage 1
-- =========================================================

CREATE TABLE IF NOT EXISTS purchase_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(40) NOT NULL,
  request_date DATE NOT NULL,
  supplier_customer_id BIGINT UNSIGNED DEFAULT NULL,
  status ENUM('draft','sent') NOT NULL DEFAULT 'draft',
  note TEXT DEFAULT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  sent_at DATETIME DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_purchase_requests_location_code (location_id, code),
  KEY idx_purchase_requests_location_date (location_id, request_date),
  KEY idx_purchase_requests_supplier (supplier_customer_id),
  CONSTRAINT fk_purchase_requests_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_requests_supplier FOREIGN KEY (supplier_customer_id) REFERENCES customers(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_request_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_request_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  sku_snapshot VARCHAR(80) DEFAULT NULL,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  warehouse_group_name_snapshot VARCHAR(120) DEFAULT NULL,
  item_note VARCHAR(255) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_request_items_request (purchase_request_id),
  KEY idx_purchase_request_items_product (product_id),
  CONSTRAINT fk_purchase_request_items_request FOREIGN KEY (purchase_request_id) REFERENCES purchase_requests(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_request_items_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;