CREATE TABLE IF NOT EXISTS employee_news_links (
  id SERIAL PRIMARY KEY,
  employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  url TEXT,
  summary TEXT,
  published_at TIMESTAMPTZ,
  published_year INTEGER,
  source_hash VARCHAR(64) NOT NULL,
  raw_data JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_employee_news_links_employee_url UNIQUE (employee_id, url),
  CONSTRAINT uq_employee_news_links_employee_source_hash UNIQUE (employee_id, source_hash)
);

CREATE INDEX IF NOT EXISTS ix_employee_news_links_employee_id
  ON employee_news_links (employee_id);

CREATE INDEX IF NOT EXISTS ix_employee_news_links_url
  ON employee_news_links (url);

CREATE INDEX IF NOT EXISTS ix_employee_news_links_published_at
  ON employee_news_links (published_at);

CREATE INDEX IF NOT EXISTS ix_employee_news_links_published_year
  ON employee_news_links (published_year);