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;
import library.errors.ConflictException;

/**
 * All SQL for MEMBERS.
 *
 * No routes use members yet, but the DAO follows the same pattern as BookDao:
 * no throws SQLException, every SQLException translated.
 */
public class MemberDao {

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

	// GET /members (librarians only)
	public List<Member> findAll() {
		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));
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return members;
	}

	// GET /members/{id}; also used after register to re-read the stored row
	public Optional<Member> findById(int memberId) {
		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()) {
				if (rs.next()) {
					return Optional.of(map(rs));
				}
				return Optional.empty();
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// POST /login: fetch the stored hash by username
	public Optional<Member> findByUsername(String username) {
		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()) {
				if (rs.next()) {
					return Optional.of(map(rs));
				}
				return Optional.empty();
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// 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) {
		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);
			}
		} catch (SQLException e) {
			if (e.getErrorCode() == 1062) {
				throw new ConflictException("Username " + username + " is already taken", e);
			}
			throw SqlErrors.translate(e);
		}
	}

	// 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) {
		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;
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	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"));
	}
}