-- =====================================================================
-- AI-POWERED SMART FARMING PLATFORM
-- Database Schema | MySQL 5.7+ / 8.0 | cPanel compatible
-- Charset: utf8mb4 (required for Bangla text)
-- All DATETIME values are stored in UTC. Display layer converts to
-- Asia/Dhaka (+06:00).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- 1. USERS, AUTH, OTP
-- =====================================================================

CREATE TABLE users (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mobile              VARCHAR(15)  NOT NULL COMMENT 'Normalized: 8801XXXXXXXXX',
  operator            ENUM('robi','airtel','other') NOT NULL DEFAULT 'other',
  subscriber_id       VARCHAR(191) NULL COMMENT 'bdapps subscriber id / masked msisdn',
  name                VARCHAR(120) NULL,
  district            VARCHAR(80)  NULL,
  role                ENUM('user','reviewer','admin') NOT NULL DEFAULT 'user',
  subscription_status ENUM('none','active','expired','unsubscribed','pending') NOT NULL DEFAULT 'none',
  subscribed_at       DATETIME NULL,
  access_expires_at   DATETIME NULL COMMENT 'Server-side entitlement boundary',
  last_login_at       DATETIME NULL,
  is_blocked          TINYINT(1) NOT NULL DEFAULT 0,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_mobile (mobile),
  KEY idx_users_subscriber (subscriber_id),
  KEY idx_users_sub_status (subscription_status),
  KEY idx_users_expires (access_expires_at),
  KEY idx_users_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE otp_requests (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mobile          VARCHAR(15) NOT NULL,
  otp_hash        CHAR(64)    NOT NULL COMMENT 'SHA-256 of OTP + server pepper. Never store plaintext.',
  purpose         ENUM('subscribe','login','unsubscribe') NOT NULL DEFAULT 'login',
  attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts    TINYINT UNSIGNED NOT NULL DEFAULT 5,
  resend_count    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  ip_address      VARBINARY(16) NULL,
  user_agent_hash CHAR(64) NULL,
  consumed_at     DATETIME NULL,
  expires_at      DATETIME NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_otp_mobile (mobile, purpose, created_at),
  KEY idx_otp_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sessions (
  id            CHAR(64) NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  ip_address    VARBINARY(16) NULL,
  payload       MEDIUMTEXT NOT NULL,
  last_activity DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_sessions_user (user_id),
  KEY idx_sessions_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 2. SUBSCRIPTION, BILLING, REFUND, SUPPORT
-- =====================================================================

CREATE TABLE subscriptions (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id             BIGINT UNSIGNED NOT NULL,
  subscriber_id       VARCHAR(191) NULL,
  plan_code           VARCHAR(40)  NOT NULL DEFAULT 'daily_5_56',
  amount              DECIMAL(8,2) NOT NULL DEFAULT 5.56,
  currency            CHAR(3)      NOT NULL DEFAULT 'BDT',
  tax_note            VARCHAR(60)  NOT NULL DEFAULT 'VAT + SD + SC included',
  status              ENUM('pending','active','expired','failed','refunded','unsubscribed') NOT NULL DEFAULT 'pending',
  started_at          DATETIME NULL,
  expires_at          DATETIME NULL COMMENT 'started_at + 24 hours',
  bdapps_txn_id       VARCHAR(191) NULL,
  bdapps_status_code  VARCHAR(40)  NULL,
  charge_confirmed_at DATETIME NULL,
  idempotency_key     VARCHAR(191) NOT NULL COMMENT 'Guards duplicate charge for the same 24h window',
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sub_idempotency (idempotency_key),
  UNIQUE KEY uq_sub_txn (bdapps_txn_id),
  KEY idx_sub_user (user_id, status),
  KEY idx_sub_expires (expires_at),
  KEY idx_sub_created (created_at),
  CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_events (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_id BIGINT UNSIGNED NULL,
  user_id         BIGINT UNSIGNED NULL,
  event_type      VARCHAR(60) NOT NULL COMMENT 'subscribe_request, otp_verified, bdapps_callback, charge_success, charge_failed, unsubscribe, expiry, reconcile',
  source          ENUM('app','bdapps_callback','bdapps_query','cron','admin') NOT NULL DEFAULT 'app',
  external_event_id VARCHAR(191) NULL COMMENT 'Dedupe key for replayed webhooks',
  http_status     SMALLINT NULL,
  raw_payload     MEDIUMTEXT NULL,
  message         VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_event_external (external_event_id),
  KEY idx_event_sub (subscription_id, created_at),
  KEY idx_event_user (user_id, created_at),
  KEY idx_event_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE refunds (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  amount          DECIMAL(8,2) NOT NULL,
  reason_code     ENUM('duplicate_charge','activation_failed','payment_error','admin_manual','other') NOT NULL,
  reason_note     VARCHAR(255) NULL,
  status          ENUM('queued','processing','completed','rejected','failed') NOT NULL DEFAULT 'queued',
  detected_by     ENUM('auto','admin','user_report') NOT NULL DEFAULT 'auto',
  bdapps_ref      VARCHAR(191) NULL,
  user_notified_at DATETIME NULL,
  processed_at    DATETIME NULL,
  processed_by    BIGINT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_refund_user (user_id, status),
  KEY idx_refund_status (status, created_at),
  CONSTRAINT fk_refund_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_tickets (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NULL,
  mobile        VARCHAR(15) NULL,
  category      ENUM('otp','premium_not_active','double_charge','forgot_number','farm_loading','crop_data','recommendation','other') NOT NULL DEFAULT 'other',
  subject       VARCHAR(180) NOT NULL,
  message       TEXT NOT NULL,
  account_context JSON NULL COMMENT 'Auto-attached: subscription state, last events, device',
  status        ENUM('open','assigned','waiting_user','resolved','closed') NOT NULL DEFAULT 'open',
  priority      ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  assigned_to   BIGINT UNSIGNED NULL,
  resolved_at   DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ticket_status (status, priority, created_at),
  KEY idx_ticket_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 3. FARM / FIELD / CROP
-- =====================================================================

CREATE TABLE farms (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(120) NOT NULL,
  farm_type   ENUM('crop','mixed','horticulture','nursery','other') NOT NULL DEFAULT 'crop',
  district    VARCHAR(80)  NULL,
  upazila     VARCHAR(80)  NULL,
  location_note VARCHAR(255) NULL,
  latitude    DECIMAL(9,6) NULL,
  longitude   DECIMAL(9,6) NULL,
  area_value  DECIMAL(10,2) NULL,
  area_unit   ENUM('decimal','katha','bigha','acre','hectare') NOT NULL DEFAULT 'bigha',
  soil_type   VARCHAR(60)  NULL,
  owner_name  VARCHAR(120) NULL,
  health_score TINYINT UNSIGNED NULL COMMENT 'Computed, 0-100. NULL until enough data.',
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_farm_user (user_id, is_active),
  KEY idx_farm_created (created_at),
  CONSTRAINT fk_farm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fields (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  farm_id        BIGINT UNSIGNED NOT NULL,
  user_id        BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized for ownership checks',
  name           VARCHAR(120) NOT NULL,
  area_value     DECIMAL(10,2) NULL,
  area_unit      ENUM('decimal','katha','bigha','acre','hectare') NOT NULL DEFAULT 'bigha',
  location_note  VARCHAR(255) NULL,
  soil_type      VARCHAR(60) NULL,
  irrigation_type ENUM('rainfed','deep_tubewell','shallow_tubewell','canal','pond','sprinkler','drip','other') NULL,
  field_status   ENUM('fallow','prepared','planted','growing','harvest_ready','harvested') NOT NULL DEFAULT 'fallow',
  health_score   TINYINT UNSIGNED NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_field_farm (farm_id, field_status),
  KEY idx_field_user (user_id),
  CONSTRAINT fk_field_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reference/knowledge catalogue of crops (Section 27). Reviewed content.
CREATE TABLE crop_catalog (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug               VARCHAR(80) NOT NULL,
  name_en            VARCHAR(120) NOT NULL,
  name_bn            VARCHAR(120) NOT NULL,
  category           ENUM('cereal','vegetable','fruit','pulse','oilseed','spice','fibre','other') NOT NULL,
  overview           TEXT NULL,
  suitable_season    VARCHAR(120) NULL COMMENT 'Rabi / Kharif-1 / Kharif-2',
  soil_requirements  TEXT NULL,
  water_requirements TEXT NULL,
  fertilizer_guidance TEXT NULL,
  harvest_info       TEXT NULL,
  storage_tips       TEXT NULL,
  source_reference   VARCHAR(255) NULL COMMENT 'DAE / BARI / BRRI / other verified source',
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  reviewed_by        BIGINT UNSIGNED NULL,
  reviewed_at        DATETIME NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_crop_slug (slug),
  KEY idx_cropcat_status (content_status, category),
  KEY idx_cropcat_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A crop actually planted by a user.
CREATE TABLE crops (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id             BIGINT UNSIGNED NOT NULL,
  farm_id             BIGINT UNSIGNED NOT NULL,
  field_id            BIGINT UNSIGNED NOT NULL,
  crop_catalog_id     BIGINT UNSIGNED NULL,
  crop_name           VARCHAR(120) NOT NULL,
  variety             VARCHAR(120) NULL,
  planting_date       DATE NULL,
  expected_harvest_date DATE NULL,
  current_stage       ENUM('seed','germination','vegetative','flowering','fruiting','maturity','harvest') NOT NULL DEFAULT 'seed',
  health_status       ENUM('unknown','poor','fair','good','excellent') NOT NULL DEFAULT 'unknown',
  notes               TEXT NULL,
  is_active           TINYINT(1) NOT NULL DEFAULT 1,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_crop_field (field_id, is_active),
  KEY idx_crop_farm (farm_id, is_active),
  KEY idx_crop_user (user_id),
  KEY idx_crop_stage (current_stage),
  CONSTRAINT fk_crop_field FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crop_cycles (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  crop_id       BIGINT UNSIGNED NOT NULL,
  field_id      BIGINT UNSIGNED NOT NULL,
  season        VARCHAR(60) NULL,
  started_on    DATE NULL,
  ended_on      DATE NULL,
  status        ENUM('running','completed','abandoned') NOT NULL DEFAULT 'running',
  outcome_note  TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cycle_crop (crop_id, status),
  CONSTRAINT fk_cycle_crop FOREIGN KEY (crop_id) REFERENCES crops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crop_health (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  crop_id       BIGINT UNSIGNED NOT NULL,
  recorded_by   ENUM('user','ai_estimate','admin') NOT NULL DEFAULT 'user',
  health_score  TINYINT UNSIGNED NULL,
  health_status ENUM('unknown','poor','fair','good','excellent') NOT NULL DEFAULT 'unknown',
  observation   TEXT NULL,
  photo_path    VARCHAR(255) NULL,
  recorded_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_chealth_crop (crop_id, recorded_at),
  CONSTRAINT fk_chealth_crop FOREIGN KEY (crop_id) REFERENCES crops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crop_updates (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  crop_id     BIGINT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  update_type ENUM('stage_change','note','photo','irrigation','fertilizer','inspection','other') NOT NULL,
  old_value   VARCHAR(120) NULL,
  new_value   VARCHAR(120) NULL,
  note        TEXT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cupdate_crop (crop_id, created_at),
  CONSTRAINT fk_cupdate_crop FOREIGN KEY (crop_id) REFERENCES crops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 4. PEST & DISEASE
-- =====================================================================

CREATE TABLE pests (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug               VARCHAR(80) NOT NULL,
  name_en            VARCHAR(140) NOT NULL,
  name_bn            VARCHAR(140) NOT NULL,
  category           ENUM('rice','vegetable','fruit','insect','storage','other') NOT NULL DEFAULT 'other',
  affected_crops     VARCHAR(255) NULL,
  symptoms           TEXT NULL,
  possible_causes    TEXT NULL,
  prevention         TEXT NULL,
  recommended_action TEXT NULL,
  typical_severity   ENUM('low','medium','high','critical') NULL,
  source_reference   VARCHAR(255) NULL,
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  reviewed_by        BIGINT UNSIGNED NULL,
  reviewed_at        DATETIME NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pest_slug (slug),
  KEY idx_pest_status (content_status, category),
  KEY idx_pest_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE diseases (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug               VARCHAR(80) NOT NULL,
  name_en            VARCHAR(140) NOT NULL,
  name_bn            VARCHAR(140) NOT NULL,
  pathogen_type      ENUM('fungal','bacterial','viral','nematode','deficiency','unknown') NOT NULL DEFAULT 'unknown',
  affected_crops     VARCHAR(255) NULL,
  symptoms           TEXT NULL,
  possible_causes    TEXT NULL,
  prevention         TEXT NULL,
  recommended_action TEXT NULL,
  typical_severity   ENUM('low','medium','high','critical') NULL,
  source_reference   VARCHAR(255) NULL,
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  reviewed_by        BIGINT UNSIGNED NULL,
  reviewed_at        DATETIME NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_disease_slug (slug),
  KEY idx_disease_status (content_status, pathogen_type),
  KEY idx_disease_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pest_reports (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  farm_id      BIGINT UNSIGNED NOT NULL,
  field_id     BIGINT UNSIGNED NOT NULL,
  crop_id      BIGINT UNSIGNED NULL,
  pest_id      BIGINT UNSIGNED NULL COMMENT 'NULL when user reports an unknown pest',
  severity     ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
  symptoms     TEXT NULL,
  photo_path   VARCHAR(255) NULL,
  observed_on  DATE NOT NULL,
  status       ENUM('submitted','ai_analyzed','reviewed','resolved') NOT NULL DEFAULT 'submitted',
  notes        TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pestrep_user (user_id, created_at),
  KEY idx_pestrep_field (field_id, created_at),
  KEY idx_pestrep_crop (crop_id),
  KEY idx_pestrep_pest (pest_id),
  KEY idx_pestrep_severity (severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE disease_reports (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  farm_id      BIGINT UNSIGNED NOT NULL,
  field_id     BIGINT UNSIGNED NOT NULL,
  crop_id      BIGINT UNSIGNED NULL,
  disease_id   BIGINT UNSIGNED NULL,
  severity     ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
  symptoms     TEXT NULL,
  photo_path   VARCHAR(255) NULL,
  observed_on  DATE NOT NULL,
  status       ENUM('submitted','ai_analyzed','reviewed','resolved') NOT NULL DEFAULT 'submitted',
  notes        TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_disrep_user (user_id, created_at),
  KEY idx_disrep_field (field_id, created_at),
  KEY idx_disrep_crop (crop_id),
  KEY idx_disrep_disease (disease_id),
  KEY idx_disrep_severity (severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 5. SOIL
-- =====================================================================

CREATE TABLE soil_records (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  farm_id       BIGINT UNSIGNED NOT NULL,
  field_id      BIGINT UNSIGNED NOT NULL,
  soil_type     VARCHAR(60) NULL,
  soil_moisture DECIMAL(5,2) NULL COMMENT 'Percent. NULL = Not Available, never invented.',
  data_source   ENUM('manual','api','sensor','ai_estimate') NOT NULL DEFAULT 'manual',
  note          TEXT NULL,
  recorded_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_soilrec_field (field_id, recorded_at),
  KEY idx_soilrec_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE soil_tests (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  field_id        BIGINT UNSIGNED NOT NULL,
  tested_on       DATE NULL,
  lab_name        VARCHAR(140) NULL,
  ph              DECIMAL(4,2) NULL,
  nitrogen_level  ENUM('very_low','low','medium','good','high') NULL,
  phosphorus_level ENUM('very_low','low','medium','good','high') NULL,
  potassium_level ENUM('very_low','low','medium','good','high') NULL,
  organic_matter  DECIMAL(5,2) NULL,
  data_source     ENUM('manual','lab_report','sensor') NOT NULL DEFAULT 'manual',
  report_path     VARCHAR(255) NULL,
  notes           TEXT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_soiltest_field (field_id, tested_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 6. IRRIGATION
-- =====================================================================

CREATE TABLE irrigation_records (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  field_id      BIGINT UNSIGNED NOT NULL,
  crop_id       BIGINT UNSIGNED NULL,
  irrigated_at  DATETIME NOT NULL,
  method        ENUM('flood','furrow','sprinkler','drip','manual','other') NULL,
  duration_min  SMALLINT UNSIGNED NULL,
  note          TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_irr_field (field_id, irrigated_at),
  KEY idx_irr_crop (crop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE irrigation_recommendations (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  field_id        BIGINT UNSIGNED NOT NULL,
  crop_id         BIGINT UNSIGNED NULL,
  status          ENUM('normal','needs_water','overwater_risk','rain_expected','monitoring') NOT NULL,
  moisture_used   DECIMAL(5,2) NULL,
  moisture_source ENUM('manual','api','sensor','ai_estimate','unavailable') NOT NULL DEFAULT 'unavailable',
  recommendation_bn TEXT NULL,
  priority        ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  next_suggested_at DATETIME NULL,
  is_ai_generated TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_irrrec_field (field_id, created_at),
  KEY idx_irrrec_status (status, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 7. WEATHER
-- =====================================================================

CREATE TABLE weather_records (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  farm_id        BIGINT UNSIGNED NULL,
  district       VARCHAR(80) NULL,
  latitude       DECIMAL(9,6) NULL,
  longitude      DECIMAL(9,6) NULL,
  observed_at    DATETIME NOT NULL,
  temperature_c  DECIMAL(5,2) NULL,
  humidity_pct   DECIMAL(5,2) NULL,
  rainfall_mm    DECIMAL(6,2) NULL,
  wind_kph       DECIMAL(6,2) NULL,
  condition_code VARCHAR(60) NULL,
  is_forecast    TINYINT(1) NOT NULL DEFAULT 0,
  provider       VARCHAR(60) NOT NULL COMMENT 'Never store fabricated data. Provider must be real.',
  fetched_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_weather_farm (farm_id, observed_at),
  KEY idx_weather_loc (district, observed_at),
  KEY idx_weather_forecast (is_forecast, observed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE weather_alerts (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  farm_id     BIGINT UNSIGNED NULL,
  district    VARCHAR(80) NULL,
  alert_type  ENUM('heavy_rain','high_temperature','low_temperature','storm_risk','excess_humidity','dry_condition') NOT NULL,
  severity    ENUM('info','warning','severe') NOT NULL DEFAULT 'info',
  message_bn  VARCHAR(500) NOT NULL,
  valid_from  DATETIME NOT NULL,
  valid_to    DATETIME NOT NULL,
  provider    VARCHAR(60) NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_walert_farm (farm_id, valid_to),
  KEY idx_walert_type (alert_type, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 8. SEED & FERTILIZER
-- =====================================================================

CREATE TABLE seed_catalog (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  crop_catalog_id    BIGINT UNSIGNED NULL,
  variety_name       VARCHAR(140) NOT NULL,
  suitable_season    VARCHAR(120) NULL,
  suitable_soil      VARCHAR(180) NULL,
  growth_period_days SMALLINT UNSIGNED NULL,
  water_need         ENUM('low','medium','high') NULL,
  basic_requirements TEXT NULL,
  risk_considerations TEXT NULL,
  source_reference   VARCHAR(255) NULL,
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_seedcat_crop (crop_catalog_id, content_status),
  KEY idx_seedcat_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fertilizer_catalog (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name_en            VARCHAR(140) NOT NULL,
  name_bn            VARCHAR(140) NOT NULL,
  category           ENUM('nitrogen','phosphate','potash','compound','organic','micronutrient','other') NOT NULL,
  general_guidance   TEXT NULL,
  application_timing TEXT NULL,
  safety_notes       TEXT NULL COMMENT 'Required. Never store exact dosage without an agronomic source.',
  source_reference   VARCHAR(255) NULL,
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fertcat_status (content_status, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE seed_recommendations (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id          BIGINT UNSIGNED NOT NULL,
  field_id         BIGINT UNSIGNED NULL,
  input_payload    JSON NOT NULL COMMENT 'location, area, soil, season, water availability, goal',
  output_payload   JSON NULL,
  is_ai_generated  TINYINT(1) NOT NULL DEFAULT 1,
  model_used       VARCHAR(80) NULL,
  prompt_version   VARCHAR(40) NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_seedrec_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fertilizer_recommendations (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id          BIGINT UNSIGNED NOT NULL,
  field_id         BIGINT UNSIGNED NULL,
  crop_id          BIGINT UNSIGNED NULL,
  input_payload    JSON NOT NULL,
  output_payload   JSON NULL,
  is_ai_generated  TINYINT(1) NOT NULL DEFAULT 1,
  model_used       VARCHAR(80) NULL,
  prompt_version   VARCHAR(40) NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fertrec_user (user_id, created_at),
  KEY idx_fertrec_crop (crop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 9. ADVISORY & MARKET
-- =====================================================================

CREATE TABLE agricultural_advisories (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title              VARCHAR(200) NOT NULL,
  category           ENUM('seasonal','crop_management','pest','disease','irrigation','soil','fertilizer','seed','harvest','storage','market','weather') NOT NULL,
  season             VARCHAR(60) NULL,
  target_crops       VARCHAR(255) NULL,
  summary            VARCHAR(500) NULL,
  body               MEDIUMTEXT NOT NULL,
  priority           ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  source_reference   VARCHAR(255) NULL,
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  is_published       TINYINT(1) NOT NULL DEFAULT 0,
  published_at       DATETIME NULL,
  reviewed_by        BIGINT UNSIGNED NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_adv_status (content_status, is_published),
  KEY idx_adv_category (category, season),
  KEY idx_adv_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE market_price_sources (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(140) NOT NULL,
  source_type   ENUM('api','scrape','manual_admin') NOT NULL,
  endpoint      VARCHAR(255) NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  last_success_at DATETIME NULL,
  last_error    VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE market_prices (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_id     BIGINT UNSIGNED NOT NULL,
  crop_name     VARCHAR(140) NOT NULL,
  crop_catalog_id BIGINT UNSIGNED NULL,
  market_name   VARCHAR(140) NOT NULL,
  district      VARCHAR(80) NULL,
  price_min     DECIMAL(10,2) NULL,
  price_max     DECIMAL(10,2) NULL,
  unit          VARCHAR(30) NOT NULL DEFAULT 'kg',
  price_date    DATE NOT NULL,
  fetched_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Always shown to users as the data timestamp',
  PRIMARY KEY (id),
  UNIQUE KEY uq_price_row (source_id, crop_name, market_name, price_date, unit),
  KEY idx_price_crop (crop_name, price_date),
  KEY idx_price_date (price_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 10. CALENDAR & TASKS
-- =====================================================================

CREATE TABLE farm_tasks (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  farm_id      BIGINT UNSIGNED NULL,
  field_id     BIGINT UNSIGNED NULL,
  crop_id      BIGINT UNSIGNED NULL,
  title        VARCHAR(200) NOT NULL,
  task_type    ENUM('planting','irrigation','fertilizer','pest_monitoring','disease_monitoring','field_inspection','harvest','market','other') NOT NULL DEFAULT 'other',
  due_date     DATE NULL,
  priority     ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status       ENUM('pending','in_progress','completed','skipped') NOT NULL DEFAULT 'pending',
  source       ENUM('user','ai_plan','system') NOT NULL DEFAULT 'user',
  notes        TEXT NULL,
  completed_at DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_task_user_due (user_id, due_date, status),
  KEY idx_task_farm (farm_id, status),
  KEY idx_task_crop (crop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE farm_calendar (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  farm_id     BIGINT UNSIGNED NULL,
  field_id    BIGINT UNSIGNED NULL,
  crop_id     BIGINT UNSIGNED NULL,
  event_type  ENUM('planting','irrigation','fertilizer','pest_monitoring','disease_monitoring','inspection','harvest','market_plan','other') NOT NULL,
  title       VARCHAR(200) NOT NULL,
  event_date  DATE NOT NULL,
  is_reminder TINYINT(1) NOT NULL DEFAULT 1,
  note        TEXT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cal_user_date (user_id, event_date),
  KEY idx_cal_farm (farm_id, event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 11. AI LAYER
-- =====================================================================

CREATE TABLE ai_conversations (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  farm_id       BIGINT UNSIGNED NULL,
  title         VARCHAR(200) NULL,
  context_type  ENUM('assistant','knowledge_coach','crop_health','pest_disease','soil','irrigation') NOT NULL DEFAULT 'assistant',
  last_message_at DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_conv_user (user_id, last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_messages (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  role            ENUM('user','assistant','system') NOT NULL,
  content         MEDIUMTEXT NOT NULL,
  model_used      VARCHAR(80) NULL,
  tier            ENUM('high','mid','low') NULL,
  input_tokens    INT UNSIGNED NULL,
  output_tokens   INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_msg_conv (conversation_id, created_at),
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES ai_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_recommendations (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  farm_id         BIGINT UNSIGNED NULL,
  field_id        BIGINT UNSIGNED NULL,
  crop_id         BIGINT UNSIGNED NULL,
  analyzer        ENUM('crop_health','pest_disease','soil','irrigation','seed','fertilizer','farm_planner','risk','general') NOT NULL,
  priority        ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  possible_problem VARCHAR(255) NULL,
  confidence      ENUM('low','moderate','high') NULL COMMENT 'Qualitative only. Never presented as a diagnosis.',
  payload         JSON NOT NULL COMMENT 'causes, actions, prevention, follow-up',
  data_basis      JSON NULL COMMENT 'Which inputs were known_data / user_input / external / ai_estimate',
  model_used      VARCHAR(80) NULL,
  prompt_version  VARCHAR(40) NULL,
  is_dismissed    TINYINT(1) NOT NULL DEFAULT 0,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_airec_user (user_id, created_at),
  KEY idx_airec_field (field_id, analyzer),
  KEY idx_airec_priority (priority, is_dismissed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_usage_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  feature       VARCHAR(60) NOT NULL,
  tier          ENUM('high','mid','low') NOT NULL,
  model_used    VARCHAR(80) NULL,
  input_tokens  INT UNSIGNED NULL,
  output_tokens INT UNSIGNED NULL,
  cost_estimate DECIMAL(10,6) NULL,
  status        ENUM('success','invalid_output','timeout','provider_error','rate_limited','blocked_quota') NOT NULL,
  latency_ms    INT UNSIGNED NULL,
  window_date   DATE NOT NULL COMMENT 'Quota bucket, aligned to entitlement window',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_aiusage_quota (user_id, feature, window_date),
  KEY idx_aiusage_created (created_at),
  KEY idx_aiusage_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 12. RISK / ERROR DNA
-- =====================================================================

CREATE TABLE user_errors (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  farm_id      BIGINT UNSIGNED NULL,
  field_id     BIGINT UNSIGNED NULL,
  error_code   ENUM('over_irrigation','under_irrigation','repeated_pest','late_fertilizer','missed_inspection','low_soil_moisture','high_disease_risk','crop_stress','delayed_harvest') NOT NULL,
  detail       VARCHAR(255) NULL,
  detected_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_uerr_user (user_id, error_code, detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE farm_risk_patterns (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id        BIGINT UNSIGNED NOT NULL,
  farm_id        BIGINT UNSIGNED NULL,
  pattern_code   VARCHAR(60) NOT NULL,
  occurrences    INT UNSIGNED NOT NULL DEFAULT 0,
  risk_level     ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
  first_seen_at  DATETIME NULL,
  last_seen_at   DATETIME NULL,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_risk (user_id, farm_id, pattern_code),
  KEY idx_risk_level (risk_level, occurrences)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 13. KNOWLEDGE / LIBRARY / CHALLENGES
-- =====================================================================

CREATE TABLE knowledge_resources (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title              VARCHAR(200) NOT NULL,
  slug               VARCHAR(200) NOT NULL,
  category           ENUM('crop_management','soil','irrigation','pest','disease','fertilizer','seed','harvest','storage','weather','market','farm_management') NOT NULL,
  summary            VARCHAR(500) NULL,
  body               MEDIUMTEXT NOT NULL,
  difficulty         ENUM('beginner','intermediate','advanced') NOT NULL DEFAULT 'beginner',
  source_reference   VARCHAR(255) NULL,
  license_note       VARCHAR(180) NULL COMMENT 'Original / public domain / licensed',
  access_level       ENUM('free','premium') NOT NULL DEFAULT 'free',
  content_status     ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  rejection_reason   VARCHAR(255) NULL,
  generation_batch_id VARCHAR(64) NULL,
  reviewed_by        BIGINT UNSIGNED NULL,
  published_at       DATETIME NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_kres_slug (slug),
  KEY idx_kres_status (content_status, access_level),
  KEY idx_kres_category (category, difficulty),
  KEY idx_kres_batch (generation_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE library_resources (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title          VARCHAR(200) NOT NULL,
  resource_type  ENUM('article','pdf','video','infographic','link') NOT NULL,
  category       VARCHAR(80) NULL,
  file_path      VARCHAR(255) NULL,
  external_url   VARCHAR(255) NULL,
  license_note   VARCHAR(180) NULL,
  access_level   ENUM('free','premium') NOT NULL DEFAULT 'free',
  content_status ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  generation_batch_id VARCHAR(64) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_lib_status (content_status, access_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daily_challenges (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  challenge_date DATE NOT NULL,
  challenge_type ENUM('farm_health_check','soil_check','crop_monitoring','pest_question','knowledge_question') NOT NULL,
  title          VARCHAR(200) NOT NULL,
  payload        JSON NULL,
  content_status ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'pending_review',
  generation_batch_id VARCHAR(64) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_challenge (challenge_date, challenge_type),
  KEY idx_challenge_status (content_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_challenges (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  challenge_id BIGINT UNSIGNED NOT NULL,
  status       ENUM('pending','completed','skipped') NOT NULL DEFAULT 'pending',
  completed_at DATETIME NULL,
  streak_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_challenge (user_id, challenge_id),
  KEY idx_uchal_user (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 14. NOTIFICATIONS, ANALYTICS, HARVEST
-- =====================================================================

CREATE TABLE notifications (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  type         VARCHAR(60) NOT NULL COMMENT 'subscription_success, expiry_reminder, task_reminder, irrigation, weather_alert, pest_alert, disease_alert, harvest, market_update, advisory, ai_ready, refund_processed, ticket_update',
  title        VARCHAR(200) NOT NULL,
  body         VARCHAR(500) NOT NULL,
  link_url     VARCHAR(255) NULL,
  is_read      TINYINT(1) NOT NULL DEFAULT 0,
  read_at      DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notif_user (user_id, is_read, created_at),
  KEY idx_notif_type (type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE harvest_records (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id        BIGINT UNSIGNED NOT NULL,
  farm_id        BIGINT UNSIGNED NOT NULL,
  field_id       BIGINT UNSIGNED NOT NULL,
  crop_id        BIGINT UNSIGNED NOT NULL,
  harvested_on   DATE NOT NULL,
  estimated_yield DECIMAL(12,2) NULL COMMENT 'NULL unless user-entered or source-backed. Never fabricated.',
  actual_yield   DECIMAL(12,2) NULL,
  yield_unit     VARCHAR(30) NULL DEFAULT 'kg',
  quality_note   VARCHAR(255) NULL,
  sold_price     DECIMAL(12,2) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_harvest_user (user_id, harvested_on),
  KEY idx_harvest_crop (crop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE farm_analytics (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id            BIGINT UNSIGNED NOT NULL,
  farm_id            BIGINT UNSIGNED NULL,
  snapshot_date      DATE NOT NULL,
  farm_health        TINYINT UNSIGNED NULL,
  crop_health        TINYINT UNSIGNED NULL,
  soil_health        TINYINT UNSIGNED NULL,
  irrigation_status  ENUM('good','attention','critical','unknown') NOT NULL DEFAULT 'unknown',
  pest_risk          ENUM('low','medium','high','unknown') NOT NULL DEFAULT 'unknown',
  weather_risk       ENUM('low','medium','high','unknown') NOT NULL DEFAULT 'unknown',
  tasks_completed    INT UNSIGNED NOT NULL DEFAULT 0,
  tasks_total        INT UNSIGNED NOT NULL DEFAULT 0,
  monitoring_events  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_analytics (user_id, farm_id, snapshot_date),
  KEY idx_analytics_date (snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 15. AUDIT
-- =====================================================================

CREATE TABLE audit_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_id    BIGINT UNSIGNED NULL,
  actor_role  ENUM('user','reviewer','admin','system') NOT NULL DEFAULT 'system',
  action      VARCHAR(80) NOT NULL,
  entity_type VARCHAR(60) NULL,
  entity_id   BIGINT UNSIGNED NULL,
  ip_address  VARBINARY(16) NULL,
  meta        JSON NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_actor (actor_id, created_at),
  KEY idx_audit_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;