-- migrations/20260608000000_init.sql

CREATE TABLE IF NOT EXISTS papers (
    bibcode TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    authors TEXT,          -- JSON array of author names
    year TEXT,
    pub TEXT,              -- journal / publisher
    keywords TEXT,         -- JSON array of keywords
    abstract TEXT,
    doi TEXT,
    arxiv_id TEXT,
    citation_count INTEGER DEFAULT 0,
    reference_count INTEGER DEFAULT 0,
    pdf_path TEXT,
    html_path TEXT,
    markdown_path TEXT,
    translation_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS citations_references (
    source_bibcode TEXT NOT NULL,
    target_bibcode TEXT NOT NULL,
    PRIMARY KEY (source_bibcode, target_bibcode)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_papers_doi ON papers(doi);
CREATE INDEX IF NOT EXISTS idx_papers_arxiv_id ON papers(arxiv_id);
CREATE INDEX IF NOT EXISTS idx_citations_ref_source ON citations_references(source_bibcode);
CREATE INDEX IF NOT EXISTS idx_citations_ref_target ON citations_references(target_bibcode);