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.util.ArrayList;
import java.util.List;
import java.util.Optional;

import library.db.DB;
import library.domain.Member;

/**
 * All SQL for MEMBERS.
 */
public class MemberDao {

	private static final String COLUMNS = "member_id, username, password_hash, role";

	// GET /members (librarians only)
	public List<Member> findAll() throws SQLException {
		String sql = "SELECT " + COLUMNS + " FROM MEMBERS ORDER BY username";
		List<Member> members = new ArrayList<>();
		try (Connection conn = DB.getConnection();
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				members.add(map(rs));
			}
		}
		return members;
	}

	// GET /members/{id}; also used after register to re-read the stored row
	public Optional<Member> findById(int memberId) throws SQLException {
		String sql = "SELECT " + COLUMNS + " FROM MEMBERS WHERE member_id = ?";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, memberId);
			try (ResultSet rs = ps.executeQuery()) {
				return rs.next() ? Optional.of(map(rs)) : Optional.empty();
			}
		}
	}

	// POST /login: fetch the stored hash by username
	public Optional<Member> findByUsername(String username) throws SQLException {
		String sql = "SELECT " + COLUMNS + " FROM MEMBERS WHERE username = ?";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setString(1, username);
			try (ResultSet rs = ps.executeQuery()) {
				return rs.next() ? Optional.of(map(rs)) : Optional.empty();
			}
		}
	}

	// POST /members (register) → returns the new member_id.
	// The caller hashes the password; this method never sees plaintext.
	public int insert(String username, String passwordHash, String role) throws SQLException {
		String sql = "INSERT INTO MEMBERS (username, password_hash, role) VALUES (?, ?, ?)";
		try (Connection conn = DB.getConnection();
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
			ps.setString(1, username);
			ps.setString(2, passwordHash);
			ps.setString(3, role);
			ps.executeUpdate();
			try (ResultSet keys = ps.getGeneratedKeys()) {
				keys.next();
				return keys.getInt(1);
			}
		}
	}

	// DELETE /members/{id} → false if no such member.
	// LOANS.member_id and SESSIONS.member_id are ON DELETE CASCADE,
	// so the member's loans and sessions go too.
	public boolean delete(int memberId) throws SQLException {
		String sql = "DELETE FROM MEMBERS WHERE member_id = ?";
		try (Connection conn = DB.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setInt(1, memberId);
			return ps.executeUpdate() == 1;
		}
	}

	private static Member map(ResultSet rs) throws SQLException {
		return new Member(rs.getInt("member_id"), rs.getString("username"), rs.getString("password_hash"),
				rs.getString("role"));
	}
}