import java.io.IOException;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DatabaseUtil {

    private static final String DB_URL = "jdbc:sqlite:globe_guru.db";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }
    public static void initializeDatabase() throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement stmt1 = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS users (" +
                             "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                             "username TEXT NOT NULL UNIQUE, " +
                             "email TEXT NOT NULL UNIQUE, " +
                             "password TEXT, " +
                             "isAdmin BOOLEAN NOT NULL DEFAULT FALSE)"
             );
             PreparedStatement stmt2 = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS options (" +
                             "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                             "link TEXT, " +
                             "imgSrc TEXT, " +
                             "hotelName TEXT, " +
                             "country TEXT, " +
                             "dateRange TEXT, " +
                             "numberOfPeople INTEGER, " +
                             "isPriceChanged BOOLEAN DEFAULT 0, " +
                             "newPrice REAL DEFAULT 0)"
             );
             PreparedStatement stmt3 = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS savedOptions (" +
                             "userId INTEGER," +
                             "detailId INTEGER," +
                             "FOREIGN KEY(userId) REFERENCES users(id)," +
                             "FOREIGN KEY(detailId) REFERENCES optionDetails(id)," +
                             "UNIQUE(userId, detailId))"
             );
             PreparedStatement stmt4 = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS optionDetails (" +
                             "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                             "optionId INTEGER NOT NULL, " +
                             "type TEXT, " +
                             "board TEXT, " +
                             "amenities TEXT, " +
                             "price REAL," +
                             "FOREIGN KEY(optionId) REFERENCES options(id))"
             )) {

            stmt1.executeUpdate();
            stmt2.executeUpdate();
            stmt3.executeUpdate();
            stmt4.executeUpdate();
        }
    }

    public static boolean registerUser(String username, String email, String password) throws SQLException {
        String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, username);
            stmt.setString(2, email);
            stmt.setString(3, password);
            return stmt.executeUpdate() > 0;
        }
    }

    public static boolean authenticateUser(String email, String password) throws SQLException {
        String sql = "SELECT password FROM users WHERE email = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, email);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    String storedPassword = rs.getString("password");
                    if (password == null) {
                        // Google login
                        return storedPassword == null;
                    }
                    return password.equals(storedPassword);
                }
            }
        }
        return false;
    }

    public static boolean deleteUser(int userId) throws SQLException {
        String selectSql = "SELECT userId FROM users WHERE userId = ?";
        String deleteSql = "DELETE FROM users WHERE userId = ?";
        String deleteFavoritesSql = "DELETE FROM savedOptions WHERE userId = ?";

        try (Connection conn = getConnection();
             PreparedStatement selectStmt = conn.prepareStatement(selectSql);
             PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
             PreparedStatement deleteFavoritesStmt = conn.prepareStatement(deleteFavoritesSql)) {

            selectStmt.setInt(1, userId);
            try (ResultSet rs = selectStmt.executeQuery()) {
                if (rs.next()) {
                    deleteStmt.setInt(1, userId);
                    int rowsAffected = deleteStmt.executeUpdate();

                    deleteFavoritesStmt.setInt(1, userId);
                    deleteFavoritesStmt.executeUpdate();

                    return rowsAffected > 0;
                } else {
                    return false;
                }
            }
        }
    }

    public static boolean userExists(String email) throws SQLException {
        String query = "SELECT COUNT(*) FROM users WHERE email = ?";
        try (Connection connection = getConnection();
             PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, email);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1) > 0;
            }
        }
        return false;
    }

    public static boolean isAdmin(String email) throws SQLException {
        String selectSql = "SELECT isAdmin FROM users WHERE email = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(selectSql)) {
            stmt.setString(1, email);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBoolean("isAdmin");
                }
            }
        }
        return false;
    }

    public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
        List<Option> options = new ArrayList<>();
        String sql = "SELECT o.*, od.id AS detail_id, od.type, od.board, od.amenities, od.price AS detail_price " +
                "FROM options o " +
                "LEFT JOIN optionDetails od ON o.id = od.optionId " +
                "WHERE (o.country LIKE ? OR o.hotelName LIKE ?)";

        if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
            sql += " AND o.dateRange = ?";
        }
        if (dateFlag) {
            sql += " AND o.dateRange LIKE ?";
        }
        if (numPeople != 0) {
            sql += " AND o.numberOfPeople = ?";
        }

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + destination + "%");
            stmt.setString(2, "%" + destination + "%");
            int paramIndex = 3;
            if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
                stmt.setString(paramIndex++, dateQuery);
            }
            if (dateFlag) {
                stmt.setString(paramIndex++, dateQuery + "%");
            }
            if (numPeople != 0) {
                stmt.setInt(paramIndex, numPeople);
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Option option = new Option();
                    option.setId(rs.getInt("detail_id"));
                    option.setLink(rs.getString("link"));
                    option.setImgSrc(rs.getString("imgSrc"));
                    option.setHotelName(rs.getString("hotelName"));
                    option.setCountry(rs.getString("country"));
                    option.setDateRange(rs.getString("dateRange"));
                    option.setNumPeople(rs.getInt("numberOfPeople"));
                    option.setType(rs.getString("type"));
                    option.setBoard(rs.getString("board"));
                    option.setAmenities(rs.getString("amenities"));
                    option.setPrice(rs.getFloat("detail_price"));
                    options.add(option);
                }
            }
        }

        System.out.println("Found " + options.size() + " options");
        return options;
    }


    public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
        String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, detailId);
            return stmt.executeUpdate() > 0;
        }
    }



    public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
        String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
        try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, detailId);
            return stmt.executeUpdate() > 0;
        }
    }


    public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
        List<Option> savedTrips = new ArrayList<>();
        String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
                "JOIN optionDetails od ON so.detailId = od.id " +
                "JOIN options o ON od.optionId = o.id " +
                "WHERE so.userId = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Option option = new Option();
                    option.setId(rs.getInt("detail_id"));
                    option.setLink(rs.getString("link"));
                    option.setImgSrc(rs.getString("imgSrc"));
                    option.setHotelName(rs.getString("hotelName"));
                    option.setCountry(rs.getString("country"));
                    option.setDateRange(rs.getString("dateRange"));
                    option.setNumPeople(rs.getInt("numberOfPeople"));
                    option.setType(rs.getString("type"));
                    option.setBoard(rs.getString("board"));
                    option.setAmenities(rs.getString("amenities"));
                    option.setPrice(rs.getFloat("price"));
                    savedTrips.add(option);
                }
            }
        }
        return savedTrips;
    }



    public static int getUserIdByEmail(String email) throws SQLException {
        String sql = "SELECT id FROM users WHERE email = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, email);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("id");
                } else {
                    throw new SQLException("User not found");
                }
            }
        }
    }

    public static int getCurrentOptionsCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS optionsCount FROM options";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt("optionsCount");
            } else {
                return 0;
            }
        }
    }

    public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
        LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
        if (lastUpdateTime == null) {
            return 0;
        }
        String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("changedOptionsCount");
                } else {
                    return 0;
                }
            }
        }
    }

    public static int saveOptionToDatabase(Option option) {
        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        int id = 0;
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, option.getLink());
            stmt.setString(2, option.getImgSrc());
            stmt.setString(3, option.getHotelName());
            stmt.setString(4, option.getCountry());
            stmt.setString(5, option.getDateRange());
            stmt.setInt(6,option.getNumPeople());
            stmt.setBoolean(7, option.isPriceChanged());
            stmt.setFloat(8, option.getNewPrice());
            stmt.executeUpdate();
            try(ResultSet genKey = stmt.getGeneratedKeys()){
                if(genKey.next()){
                    id = genKey.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    public static void saveOptionDetails(int id, String type, String board, String amenity, float price) {
        String sql = "INSERT INTO optionDetails (optionId, type, board, amenities, price) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.setString(2, type);
            stmt.setString(3, board);
            stmt.setString(4, amenity);
            stmt.setFloat(5, price);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void dropOptions() throws SQLException {
        String sql = "DROP TABLE options";
        try (
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)){
            stmt.executeUpdate();
            initializeDatabase();

        }
    }
    public static void updateOptionInDatabase(Option option) {
        String sql = "UPDATE options SET link = ?, imgSrc = ?, hotelName = ?, country = ?, price = ?, dateRange = ?, isPriceChanged = ?, newPrice = ? WHERE id = ?";
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, option.getLink());
            stmt.setString(2, option.getImgSrc());
            stmt.setString(3, option.getHotelName());
            stmt.setString(4, option.getCountry());
            stmt.setFloat(5, option.getPrice());
            stmt.setString(6, option.getDateRange());
            stmt.setBoolean(7, option.isPriceChanged());
            stmt.setFloat(8, option.getNewPrice());
            stmt.setInt(9, option.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Option findOption(Option option) {
        String sql = "SELECT * FROM options WHERE id = ?";
        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, option.getId());
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Option existingOption = new Option();
                    existingOption.setId(rs.getInt("id"));
                    existingOption.setLink(rs.getString("link"));
                    existingOption.setImgSrc(rs.getString("imgSrc"));
                    existingOption.setHotelName(rs.getString("hotelName"));
                    existingOption.setCountry(rs.getString("country"));
                    existingOption.setPrice(rs.getFloat("price"));
                    existingOption.setDateRange(rs.getString("dateRange"));
                    existingOption.setPriceChanged(rs.getBoolean("isPriceChanged"));
                    existingOption.setNewPrice(rs.getInt("newPrice"));
                    existingOption.setNumPeople(rs.getInt("numberOfPeople"));
                    return existingOption;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static List<Option> fetchAllOptions() {
        List<Option> options = new ArrayList<>();
        String sql = "SELECT * FROM options";

        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Option option = new Option();
                option.setId(rs.getInt("id"));
                option.setLink(rs.getString("link"));
                option.setImgSrc(rs.getString("imgSrc"));
                option.setHotelName(rs.getString("hotelName"));
                option.setCountry(rs.getString("country"));
                option.setPrice(rs.getFloat("price"));
                option.setDateRange(rs.getString("dateRange"));
                option.setPriceChanged(rs.getBoolean("isPriceChanged"));
                option.setNewPrice(rs.getInt("newPrice"));
                option.setNumPeople(rs.getInt("numberOfPeople"));
                options.add(option);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return options;
    }

}