pinakes/migrations/postgres/V12__book_management.sql

-- V12: Book Management Schema (PostgreSQL)
-- Adds comprehensive book metadata tracking, authors, and identifiers

-- Book metadata (supplements media_items for EPUB/PDF/MOBI)
CREATE TABLE book_metadata (
    media_id UUID PRIMARY KEY REFERENCES media_items(id) ON DELETE CASCADE,
    isbn TEXT,
    isbn13 TEXT,              -- Normalized ISBN-13 for lookups
    publisher TEXT,
    language TEXT,            -- ISO 639-1 code
    page_count INTEGER,
    publication_date DATE,
    series_name TEXT,
    series_index DOUBLE PRECISION,  -- Supports 1.5, etc.
    format TEXT,              -- 'epub', 'pdf', 'mobi', 'azw3'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_book_isbn13 ON book_metadata(isbn13);
CREATE INDEX idx_book_series ON book_metadata(series_name, series_index);
CREATE INDEX idx_book_publisher ON book_metadata(publisher);
CREATE INDEX idx_book_language ON book_metadata(language);

-- Multiple authors per book (many-to-many)
CREATE TABLE book_authors (
    media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
    author_name TEXT NOT NULL,
    author_sort TEXT,         -- "Last, First" for sorting
    role TEXT NOT NULL DEFAULT 'author', -- author, translator, editor, illustrator
    position INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (media_id, author_name, role)
);

CREATE INDEX idx_book_authors_name ON book_authors(author_name);
CREATE INDEX idx_book_authors_sort ON book_authors(author_sort);

-- Multiple identifiers (ISBN variants, ASIN, DOI, etc.)
CREATE TABLE book_identifiers (
    media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
    identifier_type TEXT NOT NULL,  -- isbn, isbn13, asin, doi, lccn, oclc
    identifier_value TEXT NOT NULL,
    PRIMARY KEY (media_id, identifier_type, identifier_value)
);

CREATE INDEX idx_book_identifiers ON book_identifiers(identifier_type, identifier_value);

-- Trigger to update updated_at on book_metadata changes
CREATE OR REPLACE FUNCTION update_book_metadata_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_book_metadata_timestamp
    BEFORE UPDATE ON book_metadata
    FOR EACH ROW
    EXECUTE FUNCTION update_book_metadata_timestamp();