package library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import library.db.DB;
import library.domain.Loan;

/**
 * All SQL for LOANS.
 *
 * The ...ForMember / ...ByMember methods scope by the current member's id (from
 * the session, never from the request): ownership checks.
 */
public class LoanDao {

	private static final String COLUMNS = "loan_id, book_id, member_id, checked_out_on, due_on, returned_on";

	// GET /loans for a librarian: every loan
	public List<Loan> findAll() {
		String sql = "SELECT " + COLUMNS + " FROM LOANS ORDER BY checked_out_on DESC";
		List<Loan> loans = new ArrayList<>();
		try (Connection conn = DB.getConnection();
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				loans.add(map(rs));
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return loans;
	}

	// GET /loans for a member: only their own loans
	public List<Loan> findByMember(int memberId) {
		String sql = "SELECT " + COLUMNS + " FROM LOANS WHERE member_id = ? " + "ORDER BY checked_out_on DESC";
		List<Loan> loans = new ArrayList<>();
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, memberId);
			try (ResultSet rs = ps.executeQuery()) {
				while (rs.next()) {
					loans.add(map(rs));
				}
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return loans;
	}

	// A member's current loans (not yet returned), due soonest first
	public List<Loan> findCurrentByMember(int memberId) {
		String sql = "SELECT " + COLUMNS + " FROM LOANS "
				+ "WHERE member_id = ? AND returned_on IS NULL ORDER BY due_on";
		List<Loan> loans = new ArrayList<>();
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, memberId);
			try (ResultSet rs = ps.executeQuery()) {
				while (rs.next()) {
					loans.add(map(rs));
				}
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return loans;
	}

	// GET /loans/{id} for a librarian: any loan
	public Optional<Loan> findById(int loanId) {
		String sql = "SELECT " + COLUMNS + " FROM LOANS WHERE loan_id = ?";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, loanId);
			try (ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					return Optional.of(map(rs));
				}
				return Optional.empty();
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// GET /loans/{id} for a member: empty unless the loan is theirs (→ 404, not
	// 403)
	public Optional<Loan> findByIdForMember(int loanId, int memberId) {
		String sql = "SELECT " + COLUMNS + " FROM LOANS WHERE loan_id = ? AND member_id = ?";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, loanId);
			ps.setInt(2, memberId);
			try (ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					return Optional.of(map(rs));
				}
				return Optional.empty();
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// POST /loans (part of checkout) → returns the new loan_id.
	// returned_on is left out, so it starts as NULL.
	public int insert(Loan loan) {
		String sql = "INSERT INTO LOANS (book_id, member_id, checked_out_on, due_on) " + "VALUES (?, ?, ?, ?)";
		try (Connection conn = DB.getConnection();
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
			ps.setInt(1, loan.bookId());
			ps.setInt(2, loan.memberId());
			ps.setObject(3, loan.checkedOutOn());
			ps.setObject(4, loan.dueOn());
			ps.executeUpdate();
			try (ResultSet keys = ps.getGeneratedKeys()) {
				keys.next();
				return keys.getInt(1);
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// Return a book (part of the return operation) → false if there is no such
	// loan or it was already returned. The IS NULL condition makes a second
	// return of the same loan change nothing.
	public boolean markReturned(int loanId, LocalDate returnedOn) {
		String sql = "UPDATE LOANS SET returned_on = ? WHERE loan_id = ? AND returned_on IS NULL";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setObject(1, returnedOn);
			ps.setInt(2, loanId);
			return ps.executeUpdate() == 1;
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	private static Loan map(ResultSet rs) throws SQLException {
		return new Loan(rs.getInt("loan_id"), rs.getInt("book_id"), rs.getInt("member_id"),
				rs.getObject("checked_out_on", LocalDate.class), rs.getObject("due_on", LocalDate.class),
				rs.getObject("returned_on", LocalDate.class)); // null while still out
	}
}