-- Track My Money Receipt Scan Module v2
-- MySQL 8+

CREATE TABLE IF NOT EXISTS merchant_directory (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  default_category_id BIGINT NULL,
  default_account_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_merchant_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receipt_scans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NULL,
  uploaded_file_path VARCHAR(500) NOT NULL,
  provider VARCHAR(50) NOT NULL DEFAULT 'gateway',
  provider_request_json JSON NULL,
  provider_response_json JSON NULL,
  merchant_detected VARCHAR(255) NULL,
  merchant_final VARCHAR(255) NULL,
  receipt_datetime_detected DATETIME NULL,
  receipt_datetime_final DATETIME NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'KES',
  subtotal DECIMAL(18,2) NULL,
  tax DECIMAL(18,2) NULL,
  discount DECIMAL(18,2) NULL,
  total DECIMAL(18,2) NULL,
  status ENUM('draft','posted','rejected','error') NOT NULL DEFAULT 'draft',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_receipt_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receipt_scan_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  receipt_scan_id BIGINT UNSIGNED NOT NULL,
  line_index INT NOT NULL,
  name_detected VARCHAR(255) NULL,
  name_final VARCHAR(255) NULL,
  qty DECIMAL(18,3) NULL,
  unit_price DECIMAL(18,2) NULL,
  line_total DECIMAL(18,2) NULL,
  suggested_category_id BIGINT NULL,
  suggested_item_id BIGINT NULL,
  final_category_id BIGINT NULL,
  final_item_id BIGINT NULL,
  confidence_score INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_receipt_items_receipt (receipt_scan_id),
  CONSTRAINT fk_receipt_items_receipt
    FOREIGN KEY (receipt_scan_id) REFERENCES receipt_scans(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add receipt_scan_id to expenses safely (works on older MySQL 8.x)
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'expenses'
    AND COLUMN_NAME = 'receipt_scan_id'
);

SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE expenses ADD COLUMN receipt_scan_id BIGINT NULL',
  'SELECT "receipt_scan_id already exists"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index safely
SET @idx_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'expenses'
    AND INDEX_NAME = 'idx_expenses_receipt_scan_id'
);

SET @sql2 = IF(
  @idx_exists = 0,
  'CREATE INDEX idx_expenses_receipt_scan_id ON expenses(receipt_scan_id)',
  'SELECT "idx_expenses_receipt_scan_id already exists"'
);

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
