-- Equity Coffee - EUDR database structure (Stage 2 / manual-first, integration-ready)
-- Philosophy: evidence-based, audit-friendly, no automation required.
-- Safe defaults: DOES NOT assume your shipments/lots schema. Uses nullable references.

-- -----------------------------
-- eudr_case
-- -----------------------------
-- One case typically maps to one shipment (or a group if needed).
CREATE TABLE IF NOT EXISTS eudr_case (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_ref VARCHAR(64) NULL,

  -- Integration references (fill these with your real IDs later)
  shipment_id BIGINT NULL,
  lot_id BIGINT NULL,
  contract_id BIGINT NULL,

  commodity VARCHAR(32) NOT NULL DEFAULT 'coffee',
  origin_country VARCHAR(64) NULL,
  origin_region VARCHAR(128) NULL,
  farm_name VARCHAR(128) NULL,

  status ENUM('draft','in_review','ready','issued','blocked') NOT NULL DEFAULT 'draft',
  readiness_score TINYINT UNSIGNED NOT NULL DEFAULT 0,

  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_case_ref (case_ref),
  KEY idx_shipment (shipment_id),
  KEY idx_lot (lot_id),
  KEY idx_status (status),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------
-- eudr_requirement
-- -----------------------------
-- Defines what "complete" means. You can expand later.
CREATE TABLE IF NOT EXISTS eudr_requirement (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL,
  description TEXT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_code (code),
  KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed a minimal, practical checklist
INSERT INTO eudr_requirement (code,label,description,is_required,is_active,sort_order)
SELECT * FROM (
  SELECT 'GEOLOCATION','Geolocation evidence','Coordinates or polygon for producing plot(s) / origin area',1,1,10 UNION ALL
  SELECT 'LAND_USE','Land-use declaration','Supplier declaration / supporting documents',1,1,20 UNION ALL
  SELECT 'TRACEABILITY','Traceability chain','Lot → shipment → contract linkage and identifiers',1,1,30 UNION ALL
  SELECT 'SUPPLIER_KYC','Supplier identity','Supplier / exporter identity documentation',1,1,40 UNION ALL
  SELECT 'RISK_ASSESSMENT','Risk assessment note','Internal assessment note (manual)',0,1,50
) s
WHERE NOT EXISTS (SELECT 1 FROM eudr_requirement r WHERE r.code = s.`code`);

-- -----------------------------
-- eudr_evidence
-- -----------------------------
-- Evidence items collected against a case.
CREATE TABLE IF NOT EXISTS eudr_evidence (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,

  requirement_code VARCHAR(64) NOT NULL,
  evidence_type ENUM('file','url','text','geo_point','geo_polygon','declaration') NOT NULL DEFAULT 'text',

  -- Storage pointers (use ONE depending on evidence_type)
  file_path VARCHAR(255) NULL,
  url VARCHAR(255) NULL,
  text_value MEDIUMTEXT NULL,

  -- Geolocation support
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  polygon_geojson MEDIUMTEXT NULL,

  provided_by BIGINT NULL,
  reviewed_by BIGINT NULL,
  review_status ENUM('pending','accepted','rejected') NOT NULL DEFAULT 'pending',
  review_notes TEXT NULL,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_case (case_id),
  KEY idx_req (requirement_code),
  KEY idx_review (review_status),
  CONSTRAINT fk_eudr_evidence_case
    FOREIGN KEY (case_id) REFERENCES eudr_case(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------
-- eudr_declaration
-- -----------------------------
-- Optional structured declarations (manual-first).
CREATE TABLE IF NOT EXISTS eudr_declaration (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  declarant_name VARCHAR(128) NULL,
  declarant_role VARCHAR(128) NULL,
  declarant_org VARCHAR(128) NULL,
  statement MEDIUMTEXT NOT NULL,
  signed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_case (case_id),
  CONSTRAINT fk_eudr_decl_case
    FOREIGN KEY (case_id) REFERENCES eudr_case(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------
-- eudr_dossier
-- -----------------------------
-- A snapshot of a case at issuance time.
CREATE TABLE IF NOT EXISTS eudr_dossier (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  version INT NOT NULL DEFAULT 1,
  status ENUM('draft','issued','revoked') NOT NULL DEFAULT 'draft',

  -- Immutable snapshot payloads
  snapshot_json LONGTEXT NOT NULL,
  pdf_path VARCHAR(255) NULL,
  issued_by BIGINT NULL,
  issued_at DATETIME NULL,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_case_version (case_id, version),
  KEY idx_case (case_id),
  KEY idx_status (status),
  CONSTRAINT fk_eudr_dossier_case
    FOREIGN KEY (case_id) REFERENCES eudr_case(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------
-- eudr_event
-- -----------------------------
-- Lightweight audit trail specific to EUDR (separate from your global audit table).
CREATE TABLE IF NOT EXISTS eudr_event (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_id BIGINT UNSIGNED NOT NULL,
  actor_id BIGINT NULL,
  action VARCHAR(64) NOT NULL,
  details TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_case (case_id),
  KEY idx_action (action),
  CONSTRAINT fk_eudr_event_case
    FOREIGN KEY (case_id) REFERENCES eudr_case(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;