-- Migration 007: vendor_aliases table
-- Allows known alternate names / abbreviations for vendors to be curated
-- so the ingest pipeline can resolve them without fuzzy matching.

CREATE TABLE IF NOT EXISTS vendor_aliases (
    alias_id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vendor_id  INT          NOT NULL,
    alias_name VARCHAR(255) NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_alias_name (alias_name),
    CONSTRAINT fk_va_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed: common short names seen in AP mailbox vs full names in vendors table
-- Add more rows here as mismatches are discovered in the Review Inbox.
-- INSERT INTO vendor_aliases (vendor_id, alias_name)
-- SELECT id, 'Carroll Fuel' FROM vendors WHERE vendor = 'CARRFU' LIMIT 1;
