BEGIN;

CREATE TABLE IF NOT EXISTS project_milestones (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title text NOT NULL,
  description text,
  status text NOT NULL DEFAULT 'PLANNED',
  progress_percent integer NOT NULL DEFAULT 0 CHECK (progress_percent BETWEEN 0 AND 100),
  planned_start date,
  planned_end date,
  actual_start date,
  actual_end date,
  responsible_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  evidence_document_id uuid REFERENCES project_documents(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_project_milestones_project ON project_milestones(project_id, status);

CREATE TABLE IF NOT EXISTS farm_daily_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  farm_id uuid REFERENCES farms(id) ON DELETE CASCADE,
  farm_unit_id uuid REFERENCES farm_units(id) ON DELETE CASCADE,
  poultry_batch_id uuid REFERENCES poultry_batches(id) ON DELETE SET NULL,
  report_date date NOT NULL,
  opening_poultry integer CHECK (opening_poultry IS NULL OR opening_poultry >= 0),
  additions integer NOT NULL DEFAULT 0 CHECK (additions >= 0),
  mortality integer NOT NULL DEFAULT 0 CHECK (mortality >= 0),
  closing_poultry integer CHECK (closing_poultry IS NULL OR closing_poultry >= 0),
  eggs_total integer NOT NULL DEFAULT 0 CHECK (eggs_total >= 0),
  good_eggs integer NOT NULL DEFAULT 0 CHECK (good_eggs >= 0),
  broken_eggs integer NOT NULL DEFAULT 0 CHECK (broken_eggs >= 0),
  feed_consumption numeric CHECK (feed_consumption IS NULL OR feed_consumption >= 0),
  feed_unit text,
  water_consumption numeric CHECK (water_consumption IS NULL OR water_consumption >= 0),
  water_unit text,
  medication text,
  notes text,
  photos jsonb NOT NULL DEFAULT '[]'::jsonb,
  employee_id uuid REFERENCES users(id) ON DELETE SET NULL,
  source text NOT NULL DEFAULT 'MANUAL',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_daily_report_closing CHECK (
    opening_poultry IS NULL OR closing_poultry IS NULL OR closing_poultry = opening_poultry + additions - mortality
  ),
  CONSTRAINT ck_daily_report_eggs CHECK (good_eggs + broken_eggs <= eggs_total)
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_farm_daily_report_scope ON farm_daily_reports(project_id, COALESCE(farm_unit_id, '00000000-0000-0000-0000-000000000000'::uuid), report_date);
CREATE INDEX IF NOT EXISTS idx_farm_daily_reports_project_date ON farm_daily_reports(project_id, report_date DESC);

CREATE TABLE IF NOT EXISTS veterinary_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  farm_unit_id uuid REFERENCES farm_units(id) ON DELETE SET NULL,
  poultry_batch_id uuid REFERENCES poultry_batches(id) ON DELETE SET NULL,
  record_type text NOT NULL,
  occurred_on date NOT NULL,
  diagnosis text,
  treatment text,
  medication text,
  dosage text,
  veterinarian_name text,
  withdrawal_until date,
  attachment_document_id uuid REFERENCES project_documents(id) ON DELETE SET NULL,
  notes text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_veterinary_project_date ON veterinary_records(project_id, occurred_on DESC);

CREATE TABLE IF NOT EXISTS vaccination_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  poultry_batch_id uuid REFERENCES poultry_batches(id) ON DELETE SET NULL,
  vaccine_name text NOT NULL,
  administered_on date NOT NULL,
  next_due_on date,
  lot_number text,
  dose text,
  administered_by text,
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_vaccine_dates CHECK (next_due_on IS NULL OR next_due_on >= administered_on)
);
CREATE INDEX IF NOT EXISTS idx_vaccination_due ON vaccination_records(project_id, next_due_on) WHERE next_due_on IS NOT NULL;

CREATE TABLE IF NOT EXISTS farm_tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  farm_unit_id uuid REFERENCES farm_units(id) ON DELETE SET NULL,
  title text NOT NULL,
  category text NOT NULL DEFAULT 'GENERAL',
  status text NOT NULL DEFAULT 'OPEN',
  priority text NOT NULL DEFAULT 'NORMAL',
  due_at timestamptz,
  assigned_to uuid REFERENCES users(id) ON DELETE SET NULL,
  completed_at timestamptz,
  notes text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_farm_tasks_due ON farm_tasks(project_id, status, due_at);

CREATE TABLE IF NOT EXISTS farm_equipment (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  farm_unit_id uuid REFERENCES farm_units(id) ON DELETE SET NULL,
  name text NOT NULL,
  serial_number_ciphertext text,
  status text NOT NULL DEFAULT 'ACTIVE',
  acquired_on date,
  next_maintenance_on date,
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_equipment_maintenance ON farm_equipment(project_id, next_maintenance_on) WHERE next_maintenance_on IS NOT NULL;

CREATE TABLE IF NOT EXISTS maintenance_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  equipment_id uuid NOT NULL REFERENCES farm_equipment(id) ON DELETE CASCADE,
  maintenance_type text NOT NULL,
  occurred_on date NOT NULL,
  cost numeric CHECK (cost IS NULL OR cost >= 0),
  currency char(3) NOT NULL DEFAULT 'IQD',
  vendor text,
  notes text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS report_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  requested_by uuid REFERENCES users(id) ON DELETE SET NULL,
  report_type text NOT NULL,
  period_start date,
  period_end date,
  format text NOT NULL DEFAULT 'PDF',
  status text NOT NULL DEFAULT 'QUEUED',
  output_object_key text,
  error_code text,
  created_at timestamptz NOT NULL DEFAULT now(),
  started_at timestamptz,
  completed_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_report_jobs_status ON report_jobs(status, created_at);

CREATE TABLE IF NOT EXISTS notification_preferences (
  user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  in_app_enabled boolean NOT NULL DEFAULT true,
  email_enabled boolean NOT NULL DEFAULT true,
  sms_enabled boolean NOT NULL DEFAULT false,
  push_enabled boolean NOT NULL DEFAULT false,
  quiet_hours jsonb NOT NULL DEFAULT '{}'::jsonb,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS notification_deliveries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  notification_id uuid NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,
  channel text NOT NULL,
  provider text,
  status text NOT NULL DEFAULT 'PENDING',
  provider_reference text,
  attempted_at timestamptz,
  delivered_at timestamptz,
  error_code text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_notification_delivery_status ON notification_deliveries(status, created_at);

CREATE TABLE IF NOT EXISTS two_factor_methods (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  method text NOT NULL,
  secret_ciphertext text,
  destination_masked text,
  enabled boolean NOT NULL DEFAULT false,
  verified_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, method)
);

CREATE TABLE IF NOT EXISTS security_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  event_type text NOT NULL,
  severity text NOT NULL DEFAULT 'INFO',
  ip inet,
  device text,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  resolved_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_security_events_recent ON security_events(severity, created_at DESC);

CREATE TABLE IF NOT EXISTS ai_operational_insights (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  insight_type text NOT NULL,
  severity text NOT NULL DEFAULT 'INFO',
  title text NOT NULL,
  summary text NOT NULL,
  evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
  model_provider text NOT NULL DEFAULT 'RULE_ENGINE',
  status text NOT NULL DEFAULT 'OPEN',
  generated_at timestamptz NOT NULL DEFAULT now(),
  acknowledged_by uuid REFERENCES users(id) ON DELETE SET NULL,
  acknowledged_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_ai_insights_project_time ON ai_operational_insights(project_id, generated_at DESC);

CREATE TABLE IF NOT EXISTS anomaly_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  metric_key text NOT NULL,
  severity text NOT NULL,
  observed_value numeric,
  baseline_value numeric,
  deviation_percent numeric,
  observed_on date NOT NULL,
  source_record_id text,
  status text NOT NULL DEFAULT 'OPEN',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_anomaly_project_date ON anomaly_events(project_id, observed_on DESC);

CREATE TABLE IF NOT EXISTS backup_runs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  backup_type text NOT NULL,
  scope text NOT NULL,
  status text NOT NULL DEFAULT 'RUNNING',
  encrypted boolean NOT NULL DEFAULT true,
  offsite boolean NOT NULL DEFAULT false,
  object_reference text,
  size_bytes bigint,
  checksum text,
  started_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz,
  verified_at timestamptz,
  restore_tested_at timestamptz,
  error_code text
);
CREATE INDEX IF NOT EXISTS idx_backup_runs_recent ON backup_runs(started_at DESC);

CREATE TABLE IF NOT EXISTS system_health_snapshots (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  app_status text NOT NULL,
  database_status text NOT NULL,
  cron_status text NOT NULL,
  storage_status text NOT NULL,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  captured_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_health_snapshots_recent ON system_health_snapshots(captured_at DESC);

CREATE TABLE IF NOT EXISTS integration_connections (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_key text NOT NULL UNIQUE,
  category text NOT NULL,
  status text NOT NULL DEFAULT 'DISCONNECTED',
  config_ciphertext text,
  last_checked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

COMMIT;