-- ===========================================================================
-- Migration: Document management (DMS)
-- Date: 2026-07-30
--
-- Upload and organise farm documents (licences, permits, contracts, SOPs,
-- receipts, lab results) with categories and expiry tracking. The file bytes
-- live on the cPanel filesystem OUTSIDE the web root (backend/storage/documents,
-- per farm) and are served through an authenticated download endpoint — only
-- this metadata row is in the database. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS documents (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  title       VARCHAR(160)  NOT NULL,
  category    VARCHAR(60)   NULL,
  doc_no      VARCHAR(80)   NULL,            -- licence/permit/reference number
  issuer      VARCHAR(120)  NULL,
  issued_on   DATE          NULL,
  expires_on  DATE          NULL,            -- drives expiry alerts
  file_name   VARCHAR(200)  NOT NULL,        -- original filename (for download)
  stored_name VARCHAR(200)  NOT NULL,        -- name on disk (farmId/<random>.ext)
  mime        VARCHAR(120)  NULL,
  size_bytes  INT UNSIGNED  NOT NULL DEFAULT 0,
  uploaded_by VARCHAR(120)  NULL,
  notes       VARCHAR(500)  NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_doc_farm (farm_id),
  KEY idx_doc_expiry (farm_id, expires_on),
  CONSTRAINT fk_doc_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;