-- ---------------------------------------------------------------------------
-- Migration: adds a Suppliers directory — the mirror image of the existing
-- Customers directory, so a regular feed/expense supplier doesn't have to be
-- retyped every time. Never referenced by a foreign key from feed_purchases
-- or expenses (both keep the supplier/payee name as plain text), matching
-- customers/sales exactly.
-- ---------------------------------------------------------------------------
-- schema.sql already carries this for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-suppliers.sql`).
-- CREATE TABLE IF NOT EXISTS is naturally idempotent — safe to re-run.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS suppliers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  name          VARCHAR(160)  NOT NULL,
  phone         VARCHAR(40)   NULL,
  address       VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_suppliers_farm (farm_id),
  CONSTRAINT fk_suppliers_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;