Эцсийн төсөл

-- Extension-уудыг идэвхжүүлэх
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Гишүүд
CREATE TABLE members (
    id         UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
    name       TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    joined_at  TIMESTAMPTZ DEFAULT NOW(),
    active     BOOLEAN DEFAULT true
);

-- Номнууд
CREATE TABLE books (
    id             UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
    title          TEXT NOT NULL,
    author         TEXT NOT NULL,
    isbn           TEXT UNIQUE,
    total_copies   INT NOT NULL DEFAULT 1,
    available_copies INT NOT NULL DEFAULT 1,
    search_vector  TSVECTOR,
    CONSTRAINT positive_copies CHECK (available_copies >= 0)
);

-- Зээлдүүлэлтийн бүртгэл
CREATE TABLE loans (
    id          UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
    member_id   UUID NOT NULL REFERENCES members(id),
    book_id     UUID NOT NULL REFERENCES books(id),
    loaned_at   TIMESTAMPTZ DEFAULT NOW(),
    due_date    TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '14 days',
    returned_at TIMESTAMPTZ,
    CONSTRAINT one_active_loan UNIQUE (member_id, book_id, returned_at)
);

-- Индексүүд
CREATE INDEX idx_books_search ON books USING GIN (search_vector);
CREATE INDEX idx_books_trgm   ON books USING GIN (title gin_trgm_ops);
CREATE INDEX idx_loans_member ON loans (member_id);
CREATE INDEX idx_loans_active ON loans (returned_at) WHERE returned_at IS NULL;

-- Ном хайлтын vector-г автоматаар шинэчлэх trigger
CREATE OR REPLACE FUNCTION update_book_search()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.search_vector :=
        SETWEIGHT(TO_TSVECTOR('russian', COALESCE(NEW.title, '')),  'A') ||
        SETWEIGHT(TO_TSVECTOR('russian', COALESCE(NEW.author, '')), 'B');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_book_search
    BEFORE INSERT OR UPDATE ON books
    FOR EACH ROW EXECUTE FUNCTION update_book_search();

-- Гишүүний статистик view
CREATE VIEW member_stats AS
SELECT
    m.id,
    m.name,
    m.email,
    COUNT(l.id)                              AS нийт_зээл,
    COUNT(l.id) FILTER (WHERE l.returned_at IS NULL) AS одоогийн_зээл,
    COUNT(l.id) FILTER (WHERE l.due_date < NOW()
                          AND l.returned_at IS NULL)  AS хугацаа_хэтэрсэн,
    RANK() OVER (ORDER BY COUNT(l.id) DESC)  AS идэвхийн_зэрэглэл
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name, m.email;

// library-api.js
import { pool } from './db.js';

// Ном зээлдүүлэх — transaction ашиглана
export async function loanBook(memberId, bookId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Ном байгаа эсэх, боломжтой хувь байгаа эсэхийг шалгах
    const book = await client.query(
      'SELECT id, title, available_copies FROM books WHERE id = $1 FOR UPDATE',
      [bookId]
    );

    if (!book.rows[0]) throw new Error('Ном олдсонгүй');
    if (book.rows[0].available_copies < 1) {
      throw new Error('Одоогоор боломжтой хувь байхгүй байна');
    }

    // Боломжтой хувийг хасах
    await client.query(
      'UPDATE books SET available_copies = available_copies - 1 WHERE id = $1',
      [bookId]
    );

    // Зээлдүүлэлт бүртгэх
    const loan = await client.query(
      `INSERT INTO loans (member_id, book_id)
       VALUES ($1, $2)
       RETURNING id, loaned_at, due_date`,
      [memberId, bookId]
    );

    await client.query('COMMIT');
    return { loan: loan.rows[0], book: book.rows[0].title };

  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Ном буцаах
export async function returnBook(loanId) {
  const result = await pool.query(
    `UPDATE loans
     SET returned_at = NOW()
     WHERE id = $1 AND returned_at IS NULL
     RETURNING book_id`,
    [loanId]
  );

  if (!result.rows[0]) throw new Error('Зээлдүүлэлт олдсонгүй');

  // Буцаасан тул хувийг нэмэх
  await pool.query(
    'UPDATE books SET available_copies = available_copies + 1 WHERE id = $1',
    [result.rows[0].book_id]
  );

  return { success: true };
}

// Full-text search
export async function searchBooks(query, limit = 10) {
  const result = await pool.query(
    `SELECT
        id, title, author, available_copies,
        TS_RANK(search_vector, TO_TSQUERY('russian', $1)) AS оноо
     FROM books
     WHERE search_vector @@ TO_TSQUERY('russian', $1)
        OR title % $2
     ORDER BY оноо DESC, title
     LIMIT $3`,
    [query.replace(/\s+/g, ' & '), query, limit]
  );
  return result.rows;
}

-- 1. Хугацаа хэтэрсэн зээлийг автоматаар илрүүлэх materialized view
CREATE MATERIALIZED VIEW overdue_loans AS
SELECT l.id, m.name, m.email, b.title, l.due_date,
       NOW() - l.due_date AS хэтэрсэн_хугацаа
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN books   b ON l.book_id   = b.id
WHERE l.returned_at IS NULL AND l.due_date < NOW()
WITH DATA;

-- 2. Хамгийн их зээлдсэн номуудын жагсаалт
SELECT b.title, b.author, COUNT(l.id) AS зээлдсэн_тоо
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title, b.author
ORDER BY зээлдсэн_тоо DESC
LIMIT 10;

-- 3. Гишүүний дэлгэрэнгүй статистик
SELECT * FROM member_stats ORDER BY нийт_зээл DESC;