ISN

CREATE TABLE products (
    tenant_id uuid NOT NULL,
    id integer NOT NULL,
    title text,
    isbn isbn,          -- For books
    ean13 ean13,       -- For general products
    upc upc,           -- For North American products
    PRIMARY KEY(tenant_id, id)
);

CREATE TABLE publications (
    tenant_id uuid NOT NULL,
    id integer NOT NULL,
    title text,
    issn issn,         -- For magazines/journals
    ismn ismn,         -- For music publications
    PRIMARY KEY(tenant_id, id)
);

-- Insert sample book data
INSERT INTO products (tenant_id, id, title, isbn) VALUES
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Sample Book 1', '978-0-7475-3269-9'),  -- ISBN-13
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Sample Book 2', '0-7475-3269-9');      -- ISBN-10

-- Insert sample product data
INSERT INTO products (tenant_id, id, title, ean13) VALUES
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Sample Product 1', '4006381333931'),
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 4, 'Sample Product 2', '0012345678905');

-- Insert sample publication data
INSERT INTO publications (tenant_id, id, title, issn, ismn) VALUES
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Science Journal', '0317-8471', null),
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Music Score', null, 'M-2306-7118-7');

-- ISBN validation and formatting
SELECT
    '978-0-7475-3269-9'::isbn as valid_isbn,  -- Works
    '978-0-7475-3269-X'::isbn as invalid_isbn; -- Fails check digit

-- Format conversion (ISBN-10 to ISBN-13)
SELECT
    isbn13('0-7475-3269-9'::isbn) as isbn13,
    isbn('978-0-7475-3269-9'::isbn) as isbn10;

-- EAN13/UPC validation
SELECT
    '4006381333931'::ean13 as valid_ean,      -- Works
    '4006381333932'::ean13 as invalid_ean;    -- Fails check digit

-- Look up a book by ISBN
SELECT title, isbn
FROM products
WHERE isbn = '978-0-7475-3269-9'::isbn;

-- Find books in a specific ISBN publisher range
SELECT title, isbn
FROM products
WHERE
    isbn IS NOT NULL
    AND isbn13(isbn)::text LIKE '978-0-7475-%';

-- Group books by publisher prefix
SELECT
    substring(isbn13(isbn)::text, 1, 8) as publisher_prefix,
    count(*) as book_count
FROM products
WHERE isbn IS NOT NULL
GROUP BY publisher_prefix;