-- AI-assisted document import module: upload a scanned/photographed document,
-- extract fields via an AI vision API, review, then create the matching record
-- (supplier invoice, quotation, bank deposit, or a receipt reference note).
-- Table is also self-created by DocumentImportService::ensureTable() so this
-- migration is optional/idempotent, matching the ApiIntegrationService/Suppliers pattern.

CREATE TABLE IF NOT EXISTS document_imports (
  import_id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT NULL,
  uploaded_by INT NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(100) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size INT NOT NULL,
  doc_type_hint VARCHAR(30) NOT NULL DEFAULT 'auto',
  detected_doc_type VARCHAR(30) NULL,
  integration_id INT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'uploaded',
  extracted_data LONGTEXT NULL,
  ai_raw_response LONGTEXT NULL,
  error_message VARCHAR(500) NULL,
  target_module VARCHAR(30) NULL,
  target_record_id INT NULL,
  receipt_reference VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_document_imports_status (status),
  KEY idx_document_imports_branch (branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
