-- ─── TABLES ───────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS authors (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(255) NOT NULL,
    bio       TEXT,
    born_date DATE
);

CREATE TABLE IF NOT EXISTS books (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    author_id   INT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    published   DATE,
    genre       VARCHAR(100),
    description TEXT
);

-- ─── SEED DATA ────────────────────────────────────────────

INSERT INTO authors (name, bio, born_date) VALUES
(
    'George Orwell',
    'English novelist and essayist, known for his sharp criticism of totalitarianism.',
    '1903-06-25'
),
(
    'J.R.R. Tolkien',
    'English author and philologist, creator of Middle-earth.',
    '1892-01-03'
),
(
    'Frank Herbert',
    'American science fiction author best known for the Dune series.',
    '1920-10-08'
),
(
    'Ursula K. Le Guin',
    'American author of speculative fiction, known for exploring gender and society.',
    '1929-10-21'
);

INSERT INTO books (title, author_id, published, genre, description) VALUES
(
    'Nineteen Eighty-Four',
    1,
    '1949-06-08',
    'Dystopian Fiction',
    'A chilling portrayal of a totalitarian society ruled by Big Brother.'
),
(
    'Animal Farm',
    1,
    '1945-08-17',
    'Political Satire',
    'A satirical allegory of the Russian Revolution told through farm animals.'
),
(
    'The Hobbit',
    2,
    '1937-09-21',
    'Fantasy',
    'Bilbo Baggins is swept into an epic quest to reclaim the Lonely Mountain.'
),
(
    'The Fellowship of the Ring',
    2,
    '1954-07-29',
    'Fantasy',
    'The first part of the Lord of the Rings trilogy, following Frodo and the One Ring.'
),
(
    'The Two Towers',
    2,
    '1954-11-11',
    'Fantasy',
    'The fellowship is broken and the war for Middle-earth begins.'
),
(
    'Dune',
    3,
    '1965-08-01',
    'Science Fiction',
    'Epic tale of politics, religion and survival on the desert planet Arrakis.'
),
(
    'Dune Messiah',
    3,
    '1969-07-01',
    'Science Fiction',
    'Paul Atreides faces the consequences of his rise to power.'
),
(
    'The Left Hand of Darkness',
    4,
    '1969-03-01',
    'Science Fiction',
    'An envoy visits a planet where the inhabitants have no fixed gender.'
),
(
    'The Dispossessed',
    4,
    '1974-05-01',
    'Science Fiction',
    'A physicist travels between two worlds with opposing political systems.'
);