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, Day 4)
    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 (Day 4)
    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"));
    }
}