BEGIN;

CREATE TABLE IF NOT EXISTS legal_entities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  display_name text NOT NULL,
  legal_name text,
  entity_type text NOT NULL DEFAULT 'OWNER_OPERATED_FARM',
  registration_number_ciphertext text,
  tax_number_ciphertext text,
  public_description text,
  status text NOT NULL DEFAULT 'DRAFT',
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS project_ownership_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  legal_entity_id uuid REFERENCES legal_entities(id) ON DELETE RESTRICT,
  owner_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
  ownership_role text NOT NULL DEFAULT 'OWNER',
  ownership_percentage numeric CHECK (ownership_percentage IS NULL OR (ownership_percentage >= 0 AND ownership_percentage <= 100)),
  evidence_document_id uuid REFERENCES project_documents(id) ON DELETE RESTRICT,
  verification_status text NOT NULL DEFAULT 'PENDING_REVIEW',
  verified_by uuid REFERENCES users(id),
  verified_at timestamptz,
  public_visible boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_owner_reference CHECK (legal_entity_id IS NOT NULL OR owner_user_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_project_ownership_project ON project_ownership_records(project_id, verification_status);

CREATE TABLE IF NOT EXISTS license_records (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  legal_entity_id uuid REFERENCES legal_entities(id) ON DELETE CASCADE,
  license_type text NOT NULL,
  document_number_ciphertext text,
  issuing_authority text,
  issue_date date,
  expiry_date date,
  evidence_document_id uuid REFERENCES project_documents(id) ON DELETE RESTRICT,
  status text NOT NULL DEFAULT 'PENDING_REVIEW',
  verified_by uuid REFERENCES users(id),
  verified_at timestamptz,
  public_visible boolean NOT NULL DEFAULT false,
  public_label text,
  notes text,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_license_subject CHECK (project_id IS NOT NULL OR legal_entity_id IS NOT NULL),
  CONSTRAINT ck_license_dates CHECK (expiry_date IS NULL OR issue_date IS NULL OR expiry_date >= issue_date)
);
CREATE INDEX IF NOT EXISTS idx_license_records_project ON license_records(project_id, status);
CREATE INDEX IF NOT EXISTS idx_license_records_expiry ON license_records(expiry_date) WHERE expiry_date IS NOT NULL;

CREATE TABLE IF NOT EXISTS license_review_history (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  license_record_id uuid NOT NULL REFERENCES license_records(id) ON DELETE CASCADE,
  from_status text,
  to_status text NOT NULL,
  notes text,
  reviewed_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

COMMIT;