BEGIN;
CREATE TABLE categories (id BIGSERIAL PRIMARY KEY,name VARCHAR(100) UNIQUE NOT NULL,slug VARCHAR(120) UNIQUE NOT NULL);
CREATE TABLE books (id BIGSERIAL PRIMARY KEY,title VARCHAR(255) NOT NULL,author VARCHAR(200) NOT NULL,isbn VARCHAR(20) UNIQUE,category_id BIGINT REFERENCES categories(id),publisher VARCHAR(150),published_year SMALLINT,description TEXT,cover_url TEXT,active BOOLEAN NOT NULL DEFAULT TRUE,created_at TIMESTAMP NOT NULL DEFAULT NOW(),updated_at TIMESTAMP NOT NULL DEFAULT NOW());
CREATE TYPE copy_status AS ENUM ('available','reserved','loaned','maintenance','lost');
CREATE TABLE copies (id BIGSERIAL PRIMARY KEY,book_id BIGINT NOT NULL REFERENCES books(id),inventory_code VARCHAR(50) UNIQUE NOT NULL,status copy_status NOT NULL DEFAULT 'available',location VARCHAR(100),created_at TIMESTAMP NOT NULL DEFAULT NOW());
CREATE TYPE user_role AS ENUM ('admin','librarian');
CREATE TABLE loans (id BIGSERIAL PRIMARY KEY,student_id BIGINT NOT NULL REFERENCES students(id),copy_id BIGINT NOT NULL REFERENCES copies(id),loaned_at TIMESTAMP NOT NULL DEFAULT NOW(),due_at DATE NOT NULL,returned_at TIMESTAMP,notes TEXT,created_by BIGINT REFERENCES users(id),CHECK(due_at>=loaned_at::date));
CREATE TYPE reservation_status AS ENUM ('pending','fulfilled','cancelled','expired');
CREATE TABLE reservations (id BIGSERIAL PRIMARY KEY,student_id BIGINT NOT NULL REFERENCES students(id),copy_id BIGINT NOT NULL REFERENCES copies(id),requested_at TIMESTAMP NOT NULL DEFAULT NOW(),loan_start_date DATE NOT NULL,loan_days SMALLINT NOT NULL CHECK(loan_days IN (7,14,21)),due_at DATE NOT NULL,pickup_deadline DATE NOT NULL,status reservation_status NOT NULL DEFAULT 'pending',notes TEXT,fulfilled_loan_id BIGINT REFERENCES loans(id),updated_at TIMESTAMP NOT NULL DEFAULT NOW(),CHECK(due_at>loan_start_date),CHECK(pickup_deadline>=loan_start_date));
CREATE UNIQUE INDEX one_pending_reservation_per_copy ON reservations(copy_id) WHERE status='pending';
CREATE INDEX reservations_student_status_idx ON reservations(student_id,status,requested_at DESC);
CREATE INDEX reservations_status_pickup_idx ON reservations(status,pickup_deadline);
CREATE UNIQUE INDEX one_active_loan_per_copy ON loans(copy_id) WHERE returned_at IS NULL;
CREATE INDEX books_search_idx ON books USING GIN (to_tsvector('spanish',title||' '||author));
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX books_catalog_search_trgm_idx ON books USING GIN (normalize_catalog_text(COALESCE(title,'')||' '||COALESCE(author,'')||' '||COALESCE(isbn,'')||' '||COALESCE(publisher,'')||' '||COALESCE(description,'')) gin_trgm_ops) WHERE active=true;
CREATE INDEX books_active_category_idx ON books(category_id) WHERE active=true;
INSERT INTO categories(name,slug) VALUES ('Ciencias','ciencias'),('Tecnología','tecnologia'),('Humanidades','humanidades'),('Derecho','derecho'),('Economía','economia');
COMMIT;
