-- ============================================================================
-- AgriFlow — MySQL database schema
-- Covers: auth/orgs/team, fields & crops, livestock & health, disease
-- predictions, weather, financials & forecasting, inventory & equipment,
-- reports, compliance/audit log.
--
-- Import with:  mysql -u youruser -p yourdatabase < schema.sql
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- Organizations & users (multi-org support, matches the org switcher screen)
-- ----------------------------------------------------------------------------

CREATE TABLE organizations (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(150) NOT NULL,
  initials      VARCHAR(4)   NOT NULL,
  color_hex     VARCHAR(7)   NOT NULL DEFAULT '#c67139',
  logo_path     VARCHAR(255) NULL,
  address       VARCHAR(255) NULL,
  theme_key     VARCHAR(20)  NOT NULL DEFAULT 'harvest' COMMENT 'harvest | slate | field — see THEME_PRESETS in js/nav.js',
  enabled_modules TEXT NULL COMMENT 'JSON array of module keys the org has chosen; NULL = all modules enabled',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name         VARCHAR(150) NOT NULL,
  email             VARCHAR(190) NOT NULL UNIQUE,
  password_hash     VARCHAR(255) NOT NULL,
  mfa_enabled       TINYINT(1) NOT NULL DEFAULT 0,
  mfa_secret        VARCHAR(64) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at     DATETIME NULL
) ENGINE=InnoDB;

-- Links a user to one or more organizations, with a role in each
CREATE TABLE organization_members (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  role            ENUM('owner','admin','manager','worker','viewer') NOT NULL DEFAULT 'worker',
  invited_at      DATETIME NULL,
  joined_at       DATETIME NULL,
  status          ENUM('active','invited','suspended') NOT NULL DEFAULT 'invited',
  UNIQUE KEY uq_member (organization_id, user_id),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE password_resets (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  token_hash  VARCHAR(255) NOT NULL,
  expires_at  DATETIME NOT NULL,
  used_at     DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE mfa_backup_codes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  code_hash   VARCHAR(255) NOT NULL,
  used_at     DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sessions (
  id          VARCHAR(64) PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  organization_id INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Fields & crops
-- ----------------------------------------------------------------------------

CREATE TABLE fields (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  name            VARCHAR(150) NOT NULL,
  acreage         DECIMAL(10,2) NULL,
  soil_type       VARCHAR(100) NULL,
  latitude        DECIMAL(10,7) NULL,
  longitude       DECIMAL(10,7) NULL,
  notes           TEXT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE crops (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  field_id        INT UNSIGNED NOT NULL,
  crop_name       VARCHAR(120) NOT NULL,
  variety         VARCHAR(120) NULL,
  planted_on      DATE NULL,
  expected_harvest DATE NULL,
  status          ENUM('planned','planted','growing','harvested','failed') NOT NULL DEFAULT 'planned',
  yield_estimate  DECIMAL(10,2) NULL,
  yield_unit      VARCHAR(20) NULL,
  FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Livestock, health & disease prediction
-- ----------------------------------------------------------------------------

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;

CREATE TABLE livestock (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  tag_id          VARCHAR(50) NOT NULL,
  species         VARCHAR(80) NOT NULL,
  breed           VARCHAR(80) NULL,
  current_farm_id INT UNSIGNED NULL,
  arrived_at      DATE NULL,
  birth_date      DATE NULL,
  weight_kg       DECIMAL(8,2) NULL,
  status          ENUM('active','sold','deceased') NOT NULL DEFAULT 'active',
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (current_farm_id) REFERENCES farm_locations(id) ON DELETE SET NULL,
  UNIQUE KEY uq_tag (organization_id, tag_id)
) ENGINE=InnoDB;

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 TABLE livestock_health_records (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  livestock_id    INT UNSIGNED NOT NULL,
  recorded_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  temperature_c   DECIMAL(4,1) NULL,
  weight_kg       DECIMAL(8,2) NULL,
  condition_score DECIMAL(3,1) NULL,
  notes           TEXT NULL,
  recorded_by     INT UNSIGNED NULL,
  FOREIGN KEY (livestock_id) REFERENCES livestock(id) ON DELETE CASCADE,
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE disease_predictions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  livestock_id    INT UNSIGNED NULL,
  field_id        INT UNSIGNED NULL,
  predicted_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  disease_name    VARCHAR(150) NOT NULL,
  confidence_pct  DECIMAL(5,2) NOT NULL,
  risk_level      ENUM('low','moderate','high','critical') NOT NULL DEFAULT 'low',
  recommendation  TEXT NULL,
  resolved        TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (livestock_id) REFERENCES livestock(id) ON DELETE CASCADE,
  FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Weather
-- ----------------------------------------------------------------------------

CREATE TABLE weather_recommendations (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  field_id        INT UNSIGNED NULL,
  forecast_date   DATE NOT NULL,
  temp_high_c     DECIMAL(4,1) NULL,
  temp_low_c      DECIMAL(4,1) NULL,
  precipitation_mm DECIMAL(6,2) NULL,
  `condition`     VARCHAR(80) NULL,
  recommendation  TEXT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Financials & forecasting
-- ----------------------------------------------------------------------------

CREATE TABLE financial_transactions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  category        VARCHAR(100) NOT NULL,
  description     VARCHAR(255) NULL,
  amount          DECIMAL(12,2) NOT NULL,
  type            ENUM('income','expense') NOT NULL,
  related_field_id INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (related_field_id) REFERENCES fields(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE financial_forecasts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  forecast_month  DATE NOT NULL COMMENT 'first day of the forecast month',
  projected_income DECIMAL(12,2) NULL,
  projected_expense DECIMAL(12,2) NULL,
  confidence_pct  DECIMAL(5,2) NULL,
  notes           TEXT NULL,
  UNIQUE KEY uq_org_month (organization_id, forecast_month),
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Inventory & equipment
-- ----------------------------------------------------------------------------

CREATE TABLE inventory_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  item_name       VARCHAR(150) NOT NULL,
  category        VARCHAR(100) NULL,
  quantity        DECIMAL(10,2) NOT NULL DEFAULT 0,
  unit            VARCHAR(20) NULL,
  reorder_level   DECIMAL(10,2) NULL,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE equipment (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  name            VARCHAR(150) NOT NULL,
  type            VARCHAR(100) NULL,
  status          ENUM('idle','in_use','maintenance','retired') NOT NULL DEFAULT 'idle',
  latitude        DECIMAL(10,7) NULL,
  longitude       DECIMAL(10,7) NULL,
  last_serviced_at DATE NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Reports & compliance / audit log
-- ----------------------------------------------------------------------------

CREATE TABLE reports (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  title           VARCHAR(200) NOT NULL,
  report_type     VARCHAR(80) NOT NULL,
  generated_by    INT UNSIGNED NULL,
  generated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  file_path       VARCHAR(255) NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE compliance_audit_log (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id INT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NULL,
  action          VARCHAR(150) NOT NULL,
  entity_type     VARCHAR(80) NULL,
  entity_id       INT UNSIGNED NULL,
  details         TEXT NULL,
  ip_address      VARCHAR(45) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Helpful indexes for common lookups/filtering
CREATE INDEX idx_crops_field ON crops(field_id);
CREATE INDEX idx_health_livestock ON livestock_health_records(livestock_id);
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);
CREATE INDEX idx_transactions_org_date ON financial_transactions(organization_id, transaction_date);
CREATE INDEX idx_audit_org_date ON compliance_audit_log(organization_id, created_at);

SET FOREIGN_KEY_CHECKS = 1;