Library management

CREATE TABLE authors (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE books (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    isbn text UNIQUE,
    title text NOT NULL,
    published_year int,
    language text DEFAULT 'en' NOT NULL
);

CREATE TABLE book_authors (
    book_id uuid NOT NULL,
    author_id uuid NOT NULL,
    PRIMARY KEY (book_id, author_id)
);
CREATE INDEX book_authors_author_id_idx ON book_authors (author_id);

CREATE TABLE copies (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    book_id uuid NOT NULL,
    barcode text NOT NULL UNIQUE,
    shelf text,
    lost boolean DEFAULT false NOT NULL
);
CREATE INDEX copies_book_id_idx ON copies (book_id);

CREATE TABLE members (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    card_number text NOT NULL UNIQUE,
    full_name text NOT NULL,
    email text,
    joined_on date DEFAULT current_date NOT NULL
);

CREATE TABLE loans (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    copy_id uuid NOT NULL,
    member_id uuid NOT NULL,
    borrowed_on date DEFAULT current_date NOT NULL,
    due_on date NOT NULL,
    returned_on date
);
CREATE INDEX loans_member_idx ON loans (member_id, returned_on);
CREATE INDEX loans_copy_id_idx ON loans (copy_id);

CREATE TABLE reservations (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    book_id uuid NOT NULL,
    member_id uuid NOT NULL,
    reserved_at timestamptz DEFAULT now() NOT NULL,
    fulfilled boolean DEFAULT false NOT NULL
);
CREATE INDEX reservations_book_id_idx ON reservations (book_id);
CREATE INDEX reservations_member_id_idx ON reservations (member_id);

CREATE TABLE fines (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    loan_id uuid NOT NULL,
    amount numeric(8,2) NOT NULL,
    paid boolean DEFAULT false NOT NULL
);
CREATE INDEX fines_loan_id_idx ON fines (loan_id);

ALTER TABLE book_authors ADD FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE;

ALTER TABLE book_authors ADD FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE CASCADE;

ALTER TABLE copies ADD FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE;

ALTER TABLE loans ADD FOREIGN KEY (copy_id) REFERENCES copies (id);

ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);

ALTER TABLE reservations ADD FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE;

ALTER TABLE reservations ADD FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE;

ALTER TABLE fines ADD FOREIGN KEY (loan_id) REFERENCES loans (id) ON DELETE CASCADE;