
USE budget_app;

CREATE TABLE IF NOT EXISTS imports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  source ENUM('csv','pdf') NOT NULL,
  bank_name VARCHAR(100) NULL,
  original_filename VARCHAR(255) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  row_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE transactions
  ADD COLUMN IF NOT EXISTS import_id INT NULL,
  ADD CONSTRAINT IF NOT EXISTS fk_txn_import FOREIGN KEY (import_id) REFERENCES imports(id) ON DELETE SET NULL;

-- Add a stored fingerprint for de-duplication
ALTER TABLE transactions
  ADD COLUMN IF NOT EXISTS fingerprint CHAR(64) GENERATED ALWAYS AS (
    SHA2(CONCAT(
      user_id,'|', t_date,'|', ROUND(amount,2),'|',
      COALESCE(LOWER(TRIM(description)),'')
    ), 256)
  ) STORED,
  ADD UNIQUE KEY IF NOT EXISTS uniq_txn_fingerprint (user_id, fingerprint);
