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

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, " +
                             "price REAL, " +
                             "dateRange TEXT, " +
                             "isPriceChanged BOOLEAN DEFAULT 0, " +
                             "newPrice REAL DEFAULT 0)"
             );

             PreparedStatement stmt3 = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS savedOptions (" +
                             "userId INTEGER, " +
                             "optionId INTEGER, " +
                             "FOREIGN KEY(userId) REFERENCES users(id), " +
                             "FOREIGN KEY(optionId) REFERENCES options(id), " +
                             "UNIQUE(userId, optionId))"
             )) {
            stmt1.executeUpdate();
            stmt2.executeUpdate();
            stmt3.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); // Store hashed
            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) {
                        // Assume this is a Google login
                        return storedPassword == null;
                    }
                    return password.equals(storedPassword); // Check hashed password
                }
            }
        }
        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()) {
                    // User exists, delete the user and their favourite options
                    deleteStmt.setInt(1, userId);
                    int rowsAffected = deleteStmt.executeUpdate();

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

                    return rowsAffected > 0;
                } else {
                    // User does not exist
                    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, boolean dateFlag) throws SQLException {
        List<Option> options = new ArrayList<>();
        String sql = "SELECT * FROM options WHERE (country LIKE ? OR hotelName LIKE ?)";
        System.out.println(dateQuery);
        if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
            sql += (" AND dateRange = ?");
        } //append date
        if (dateFlag) {   //search only from dates
            sql += "AND dateRange LIKE ?";
        }
        System.out.println("Searching for dest:" + destination + "\n" + sql);
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + destination + "%");
            stmt.setString(2, "%" + destination + "%");
            if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
                stmt.setString(3, dateQuery);
            }
            if (dateFlag) {
                stmt.setString(3, dateQuery + "%");
            }
            // Execute query
            try (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"));
                    options.add(option);
                }
            }
        }
        System.out.println("Found " + options.size());
        return options;
    }

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

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

    public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
        List<Option> savedTrips = new ArrayList<>();
        String sql = "SELECT options.* FROM savedOptions JOIN options ON savedOptions.optionId = options.id WHERE savedOptions.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("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"));
                    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 void saveOptionToDatabase(Option option) {
        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, price, dateRange, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        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.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();
            //Remake the options DB
            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"));
                    return existingOption;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}