-- Phase 11 — Receipt Capture
-- Run AFTER 010_purchase_orders.sql
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS receipts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED  NOT NULL,
  vendor_name   VARCHAR(255)  DEFAULT NULL,
  vendor_id     INT UNSIGNED  DEFAULT NULL,
  amount        DECIMAL(15,2) DEFAULT NULL,
  receipt_date  DATE          DEFAULT NULL,
  category      VARCHAR(100)  DEFAULT NULL,
  description   TEXT          DEFAULT NULL,
  filename      VARCHAR(255)  DEFAULT NULL,
  original_name VARCHAR(255)  DEFAULT NULL,
  mime_type     VARCHAR(100)  DEFAULT NULL,
  file_size     INT UNSIGNED  DEFAULT NULL,
  status        ENUM('pending','processed','linked') DEFAULT 'pending',
  account_id    INT UNSIGNED  DEFAULT NULL,
  bill_id       INT UNSIGNED  DEFAULT NULL,
  uploaded_by   INT UNSIGNED  DEFAULT NULL,
  is_deleted    TINYINT(1)    DEFAULT 0,
  created_at    DATETIME      DEFAULT NOW(),
  updated_at    DATETIME      ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  INDEX idx_status (tenant_id, status),
  CONSTRAINT fk_rcpt_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
