-- AgriFlow — schema part 7: multi-farm livestock tracking + dip records.

CREATE TABLE farm_locations (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  name            VARCHAR(150) NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE livestock
  ADD COLUMN current_farm_id INT UNSIGNED NULL AFTER breed,
  ADD COLUMN arrived_at DATE NULL AFTER current_farm_id,
  ADD FOREIGN KEY (current_farm_id) REFERENCES farm_locations(id) ON DELETE SET NULL;

CREATE TABLE livestock_movements (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  livestock_id    INT UNSIGNED NOT NULL,
  farm_location_id INT UNSIGNED NOT NULL,
  arrived_at      DATE NOT NULL,
  departed_at     DATE NULL COMMENT 'NULL = animal is still at this farm',
  notes           VARCHAR(255) NULL,
  FOREIGN KEY (livestock_id) REFERENCES livestock(id) ON DELETE CASCADE,
  FOREIGN KEY (farm_location_id) REFERENCES farm_locations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE livestock_dip_records (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  livestock_id    INT UNSIGNED NOT NULL,
  dipped_at       DATE NOT NULL,
  next_due_at     DATE NULL,
  notes           VARCHAR(255) NULL,
  recorded_by     INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (livestock_id) REFERENCES livestock(id) ON DELETE CASCADE,
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE INDEX idx_dip_due ON livestock_dip_records(livestock_id, next_due_at);
CREATE INDEX idx_movements_livestock ON livestock_movements(livestock_id, departed_at);