USE comptaai_maroc;

DROP TABLE IF EXISTS ocr_extractions;

CREATE TABLE IF NOT EXISTS document_extractions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NOT NULL,
  supplier_name VARCHAR(180) NULL,
  customer_name VARCHAR(180) NULL,
  invoice_number VARCHAR(80) NULL,
  invoice_date DATE NULL,
  due_date DATE NULL,
  ht_amount DECIMAL(14,2) NULL,
  tva_amount DECIMAL(14,2) NULL,
  ttc_amount DECIMAL(14,2) NULL,
  vat_rate DECIMAL(5,2) NULL,
  payment_method VARCHAR(80) NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'MAD',
  confidence_score DECIMAL(5,2) NULL,
  raw_ocr_text LONGTEXT NULL,
  ai_json_result JSON NULL,
  status ENUM('draft','needs_review','confirmed') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY document_extractions_document_unique (document_id),
  KEY document_extractions_status_index (status),
  CONSTRAINT document_extractions_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
) ENGINE=InnoDB;