-- Create receipt_archives for OpenAI receipt recognition archive
-- Database: kartulirest_xyz (or current app DB)

CREATE TABLE IF NOT EXISTS `receipt_archives` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(32) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'pending',
  `original_filename` varchar(255) NOT NULL,
  `disk` varchar(255) NOT NULL DEFAULT 'public',
  `storage_path` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `file_size` bigint unsigned DEFAULT NULL,
  `found` tinyint(1) DEFAULT NULL,
  `recognized_title` varchar(255) DEFAULT NULL,
  `recognized_amount` decimal(12,2) DEFAULT NULL,
  `recognized_currency` varchar(8) DEFAULT NULL,
  `recognized_entry_date` date DEFAULT NULL,
  `recognized_document_date` date DEFAULT NULL,
  `recognized_merchant` varchar(255) DEFAULT NULL,
  `recognized_invoice_number` varchar(255) DEFAULT NULL,
  `recognized_vat_amount` decimal(12,2) DEFAULT NULL,
  `confidence` decimal(4,2) DEFAULT NULL,
  `notes` text,
  `line_items` json DEFAULT NULL,
  `keywords` json DEFAULT NULL,
  `raw_response` json DEFAULT NULL,
  `error_message` text,
  `uploaded_by` bigint unsigned DEFAULT NULL,
  `financial_entry_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `receipt_archives_type_status_index` (`type`, `status`),
  KEY `receipt_archives_created_at_index` (`created_at`),
  KEY `receipt_archives_uploaded_by_foreign` (`uploaded_by`),
  KEY `receipt_archives_financial_entry_id_foreign` (`financial_entry_id`),
  CONSTRAINT `receipt_archives_uploaded_by_foreign`
    FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `receipt_archives_financial_entry_id_foreign`
    FOREIGN KEY (`financial_entry_id`) REFERENCES `financial_entries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mark migration as ran (Laravel migrations table), skip if you run via artisan later
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_07_26_180000_create_receipt_archives_table', IFNULL(MAX(`batch`), 0) + 1
FROM `migrations`
WHERE NOT EXISTS (
  SELECT 1 FROM `migrations`
  WHERE `migration` = '2026_07_26_180000_create_receipt_archives_table'
);