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): Day 4's 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() throws SQLException {
        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));
            }
        }
        return loans;
    }

    // GET /loans for a member: only their own loans
    public List<Loan> findByMember(int memberId) throws SQLException {
        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));
                }
            }
        }
        return loans;
    }

    // A member's current loans (not yet returned), due soonest first
    public List<Loan> findCurrentByMember(int memberId) throws SQLException {
        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));
                }
            }
        }
        return loans;
    }

    // GET /loans/{id} for a librarian: any loan
    public Optional<Loan> findById(int loanId) throws SQLException {
        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()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    // GET /loans/{id} for a member: empty unless the loan is theirs (→ 404, not 403)
    public Optional<Loan> findByIdForMember(int loanId, int memberId) throws SQLException {
        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()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    // 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) throws SQLException {
        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);
            }
        }
    }

    // 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) throws SQLException {
        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;
        }
    }

    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
    }
}