-- ------------------------------------------------------------
-- Design Request Core v1
-- Store new design requests, designer queue, admin review workflow.
-- MySQL 5.7 compatible. Does not alter/drop order, stone, or product data.
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS design_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_no VARCHAR(30) NOT NULL UNIQUE,
  store_id INT UNSIGNED NOT NULL,
  requested_by INT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  source_url TEXT NULL,
  reference_image_path VARCHAR(255) NULL,
  request_note TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'submitted',
  priority TINYINT UNSIGNED NOT NULL DEFAULT 5,
  product_type_code VARCHAR(80) NULL,
  matched_product_id INT UNSIGNED NULL,
  designer_user_id INT UNSIGNED NULL,
  designer_note TEXT NULL,
  admin_note TEXT NULL,
  drawing_cost_actual_usd DECIMAL(10,2) NULL,
  sizing_cost_actual_usd DECIMAL(10,2) NULL,
  drawing_charge_total_usd DECIMAL(10,2) NULL,
  sizing_charge_total_usd DECIMAL(10,2) NULL,
  width_mm DECIMAL(10,3) NULL,
  length_mm DECIMAL(10,3) NULL,
  height_mm DECIMAL(10,3) NULL,
  band_width_mm DECIMAL(10,3) NULL,
  gram_10k DECIMAL(10,3) NULL,
  gram_14k DECIMAL(10,3) NULL,
  gram_18k DECIMAL(10,3) NULL,
  stone_slot_note TEXT NULL,
  is_sizing_required TINYINT(1) NOT NULL DEFAULT 0,
  created_product_id INT UNSIGNED NULL,
  rejection_reason TEXT NULL,
  submitted_at DATETIME NULL,
  design_started_at DATETIME NULL,
  design_completed_at DATETIME NULL,
  admin_decision_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_design_requests_store (store_id),
  INDEX idx_design_requests_status (status),
  INDEX idx_design_requests_designer (designer_user_id),
  INDEX idx_design_requests_product_type (product_type_code),
  INDEX idx_design_requests_created (created_at),
  CONSTRAINT fk_design_requests_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_design_requests_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_requests_designer FOREIGN KEY (designer_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_requests_matched_product FOREIGN KEY (matched_product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_requests_created_product FOREIGN KEY (created_product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS design_request_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  design_request_id BIGINT UNSIGNED NOT NULL,
  actor_user_id INT UNSIGNED NULL,
  action_type VARCHAR(80) NOT NULL,
  old_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_design_audit_request (design_request_id),
  INDEX idx_design_audit_actor (actor_user_id),
  CONSTRAINT fk_design_audit_request FOREIGN KEY (design_request_id) REFERENCES design_requests(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_design_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
