CREATE TABLE IF NOT EXISTS employee_publications (
  id SERIAL PRIMARY KEY,
  employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  publication_id VARCHAR(64),
  title TEXT NOT NULL,
  year INTEGER,
  publication_type VARCHAR(64),
  language VARCHAR(16),
  status INTEGER,
  url TEXT,
  doi_url TEXT,
  other_url TEXT,
  document_url TEXT,
  citation_text TEXT,
  annotation JSONB,
  description JSONB,
  authors JSONB,
  raw_data JSONB,
  source_hash VARCHAR(64) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_employee_publications_employee_publication UNIQUE (employee_id, publication_id),
  CONSTRAINT uq_employee_publications_employee_source_hash UNIQUE (employee_id, source_hash)
);

CREATE INDEX IF NOT EXISTS ix_employee_publications_employee_id
  ON employee_publications (employee_id);

CREATE INDEX IF NOT EXISTS ix_employee_publications_publication_id
  ON employee_publications (publication_id);

CREATE INDEX IF NOT EXISTS ix_employee_publications_doi_url
  ON employee_publications (doi_url);

CREATE INDEX IF NOT EXISTS ix_employee_publications_year
  ON employee_publications (year);

CREATE INDEX IF NOT EXISTS ix_employee_publications_publication_type
  ON employee_publications (publication_type);