CREATE TABLE IF NOT EXISTS countries (
  country TEXT PRIMARY KEY,
  country_label_en TEXT NOT NULL,
  country_label_zh TEXT,
  role TEXT,
  document_count INTEGER NOT NULL DEFAULT 0,
  year_min INTEGER,
  year_max INTEGER,
  avg_stringency REAL,
  avg_ambiguity REAL,
  avg_policy_tendency REAL,
  top_capacity_functions TEXT,
  top_personnel_interfaces TEXT
);

CREATE TABLE IF NOT EXISTS public_documents (
  public_id TEXT PRIMARY KEY,
  source_line TEXT NOT NULL,
  country TEXT NOT NULL,
  title TEXT NOT NULL,
  year INTEGER,
  document_type TEXT,
  source_url TEXT,
  fulltext_tier TEXT,
  abstract TEXT,
  commentary TEXT,
  personnel_interface TEXT,
  lifecycle_stage TEXT,
  capacity_function TEXT,
  governance_intent TEXT,
  actor_category TEXT,
  commentary_zh TEXT,
  commentary_en TEXT,
  compliance_oriented_stringency REAL,
  policy_tendency REAL,
  textual_ambiguity REAL,
  label_confidence REAL,
  release_level TEXT NOT NULL DEFAULT 'public_summary',
  FOREIGN KEY(country) REFERENCES countries(country)
);

CREATE INDEX IF NOT EXISTS idx_public_documents_country ON public_documents(country);
CREATE INDEX IF NOT EXISTS idx_public_documents_year ON public_documents(year);
CREATE INDEX IF NOT EXISTS idx_public_documents_interface ON public_documents(personnel_interface);
CREATE INDEX IF NOT EXISTS idx_public_documents_capacity ON public_documents(capacity_function);

CREATE TABLE IF NOT EXISTS foreign_evidence (
  public_id TEXT PRIMARY KEY,
  country TEXT NOT NULL,
  title TEXT NOT NULL,
  title_zh TEXT,
  policy_domain TEXT,
  document_type TEXT,
  issuing_body TEXT,
  date TEXT,
  source_url TEXT,
  summary_zh TEXT,
  commentary_zh TEXT,
  importance TEXT,
  evidence_use TEXT
);

CREATE INDEX IF NOT EXISTS idx_foreign_evidence_country ON foreign_evidence(country);
CREATE INDEX IF NOT EXISTS idx_foreign_evidence_importance ON foreign_evidence(importance);

CREATE TABLE IF NOT EXISTS codebook (
  name TEXT PRIMARY KEY,
  public_label TEXT NOT NULL,
  meaning TEXT NOT NULL,
  release_note TEXT
);
