CREATE TYPE user_status AS ENUM ('active', 'inactive', 'blocked');
CREATE TYPE membership_status AS ENUM ('active', 'inactive', 'suspended', 'expired');
CREATE TYPE copy_status AS ENUM ('available', 'reserved', 'borrowed', 'lost', 'damaged');
CREATE TYPE payment_method AS ENUM ('cash', 'card', 'online');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');
CREATE TYPE reservation_status AS ENUM ('pending', 'active', 'cancelled', 'expired');
CREATE TYPE loan_status AS ENUM ('borrowed', 'returned', 'overdue', 'lost');
CREATE TYPE fine_status AS ENUM ('unpaid', 'paid', 'waived', 'overdue');
CREATE TYPE notification_status AS ENUM ('pending', 'sent', 'failed', 'read');
CREATE TYPE notification_type AS ENUM ('payment', 'fine', 'reservation', 'event', 'membership', 'general');
CREATE TYPE event_reservation_status AS ENUM ('reserved', 'cancelled', 'attended');
CREATE TYPE plan_type AS ENUM ('monthly', 'semiannual', 'annual');


CREATE TABLE app_user (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(20) NOT NULL,
    email VARCHAR(30) NOT NULL UNIQUE,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15),
    status user_status NOT NULL DEFAULT 'active',
    address VARCHAR(50),
    street VARCHAR(50),
    city VARCHAR(20),
    country VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    CONSTRAINT chk_app_user_email CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_app_user_phone CHECK (phone ~ '^\+389[0-9]{8}$')
);

CREATE TABLE admin (
    user_id INT PRIMARY KEY,
    FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE member (
    user_id INT PRIMARY KEY,
    member_number INT NOT NULL UNIQUE,
    join_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status membership_status NOT NULL DEFAULT 'inactive',
    FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE librarian (
    user_id INT PRIMARY KEY,
    employee_code INT NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES app_user(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE publisher (
    publisher_id SERIAL PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    city VARCHAR(30),
    country VARCHAR(50)
);

CREATE TABLE category (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    description TEXT,
    parent_category_id INT,
    FOREIGN KEY (parent_category_id) REFERENCES category(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE genre (
    genre_id SERIAL PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE author (
    author_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE book (
    barcode VARCHAR(13) PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    publication_year INT,
    language VARCHAR(20),
    pages INT,
    description TEXT,
    publisher_id INT,
    shelf_location VARCHAR(10),
    FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CHECK (barcode ~ '^[0-9]{13}$')
);

CREATE TABLE book_copy (
    copy_id SERIAL PRIMARY KEY,
    barcode VARCHAR(13) NOT NULL,
    status copy_status NOT NULL DEFAULT 'available',
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE book_author (
    barcode VARCHAR(13) NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (barcode, author_id),
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
   FOREIGN KEY (author_id) REFERENCES author(author_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE book_genre (
    barcode VARCHAR(13) NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (barcode, genre_id),
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE category_book (
    category_id INT NOT NULL,
    barcode VARCHAR(13) NOT NULL,
    PRIMARY KEY (category_id, barcode),
    FOREIGN KEY (category_id) REFERENCES category(category_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE membership_plan (
    plan_id SERIAL PRIMARY KEY,
    plan_name plan_type NOT NULL,
    duration_days INT NOT NULL,
    price INT NOT NULL,
    CONSTRAINT chk_duration_match CHECK (
        (plan_name = 'monthly' AND duration_days = 30) OR
        (plan_name = 'semiannual' AND duration_days = 180) OR
        (plan_name = 'annual' AND duration_days = 365)
    ),

    CONSTRAINT chk_price_match CHECK (
        (plan_name = 'monthly' AND price = 300) OR
        (plan_name = 'semiannual' AND price = 1500) OR
        (plan_name = 'annual' AND price = 2500)
    )
);

CREATE TABLE membership (
    membership_id SERIAL PRIMARY KEY,
    member_user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status membership_status NOT NULL DEFAULT 'active',
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES membership_plan(plan_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CHECK (end_date >= start_date)
);

CREATE TABLE payment (
    payment_id SERIAL PRIMARY KEY,
    amount INT NOT NULL,
    payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    method payment_method NOT NULL,
    status payment_status NOT NULL DEFAULT 'pending',
    membership_id INT NOT NULL,
    member_user_id INT NOT NULL,
    FOREIGN KEY (membership_id) REFERENCES membership(membership_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE event (
	event_id serial4 NOT NULL,
	title varchar(50) NOT NULL,
	description text NULL,
	event_date date NOT NULL,
	start_time time NOT NULL,
	end_time time NOT NULL,
	"location" varchar(30) NOT NULL,
	max_seats int4 NOT NULL,
	available_seats int4 NOT NULL,
	created_by int4 NULL,
	CONSTRAINT event_check CHECK ((end_time > start_time)),
	CONSTRAINT event_check1 CHECK (((max_seats > 0) AND (available_seats >= 0) AND (available_seats <= max_seats))),
	CONSTRAINT event_pkey PRIMARY KEY (event_id),
	CONSTRAINT event_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.librarian(user_id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE event_book (
    event_id INT NOT NULL,
    barcode VARCHAR(13) NOT NULL,
    PRIMARY KEY (event_id, barcode),
    FOREIGN KEY (event_id) REFERENCES event(event_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE event_reservation (
    event_reservation_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL,
    member_user_id INT NOT NULL,
    reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
    seats_reserved INT NOT NULL DEFAULT 1,
    status event_reservation_status NOT NULL DEFAULT 'reserved',
    FOREIGN KEY (event_id) REFERENCES event(event_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE CASCADE
);

CREATE TABLE reservation (
    reservation_id SERIAL PRIMARY KEY,
    member_user_id INT NOT NULL,
    barcode VARCHAR(13) NOT NULL,
    reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
    reservation_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expiration_date DATE,
    notified_at TIMESTAMP,
    status reservation_status NOT NULL DEFAULT 'pending',
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CHECK (expiration_date IS NULL OR expiration_date >= reservation_date)
);

CREATE TABLE loan_history (
    loan_id SERIAL PRIMARY KEY,
    copy_id INT NOT NULL,
    librarian_user_id INT NOT NULL,
    member_user_id INT NOT NULL,
    reservation_id INT,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE,
    status loan_status NOT NULL DEFAULT 'borrowed',
    FOREIGN KEY (copy_id) REFERENCES book_copy(copy_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
   FOREIGN KEY (librarian_user_id) REFERENCES librarian(user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CHECK (
        due_date >= borrow_date AND
        (return_date IS NULL OR return_date >= borrow_date)
    )
);

CREATE TABLE fines (
    fine_id SERIAL PRIMARY KEY,
    loan_id INT NOT NULL,
    amount INT NOT NULL,
    payment_due_date DATE NOT NULL,
    status fine_status NOT NULL DEFAULT 'unpaid',
    FOREIGN KEY (loan_id) REFERENCES loan_history(loan_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE book_view_log (
    view_id SERIAL PRIMARY KEY,
    view_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    barcode VARCHAR(13),
    member_user_id INT,
    FOREIGN KEY (barcode) REFERENCES book(barcode)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE search_log (
    search_id SERIAL PRIMARY KEY,
    query_text TEXT NOT NULL,
    search_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    member_user_id INT,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE author_log (
    authorlog_id SERIAL PRIMARY KEY,
    member_user_id INT,
    query_text TEXT NOT NULL,
    log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE authorlog_author (
    authorlog_id INT NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (authorlog_id, author_id),
    FOREIGN KEY (authorlog_id) REFERENCES author_log(authorlog_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES author(author_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE genre_log (
    genrelog_id SERIAL PRIMARY KEY,
    member_user_id INT,
    query_text TEXT NOT NULL,
    log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE genrelog_genre (
    genrelog_id INT NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (genrelog_id, genre_id),
    FOREIGN KEY (genrelog_id) REFERENCES genre_log(genrelog_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE notification (
    notification_id SERIAL PRIMARY KEY,
    member_user_id INT NOT NULL,
    payment_id INT,
    notification_type notification_type NOT NULL,
    title VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP,
    status notification_status NOT NULL DEFAULT 'pending',
    FOREIGN KEY (member_user_id) REFERENCES member(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CHECK (sent_at IS NULL OR sent_at >= created_at)
);

CREATE TABLE fines_notification (
    fine_id INT NOT NULL,
    notification_id INT NOT NULL,
    PRIMARY KEY (fine_id, notification_id),
    FOREIGN KEY (fine_id) REFERENCES fines(fine_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (notification_id) REFERENCES notification(notification_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);