package com.crystalstore.pro.database; import com.crystalstore.pro.CrystalStorePro; import com.crystalstore.pro.model.*; import com.google.gson.Gson; import com.google.gson.reflect.TypeToken; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import java.io.File; import java.sql.*; import java.util.*; import java.util.concurrent.CompletableFuture; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.logging.Level; public class DatabaseManager { private final CrystalStorePro plugin; private HikariDataSource dataSource; private final ExecutorService executor; private final Gson gson = new Gson(); private final String dbType; public DatabaseManager(CrystalStorePro plugin) { this.plugin = plugin; this.executor = Executors.newFixedThreadPool(4, r -> { Thread t = new Thread(r, "CrystalStore-DB"); t.setDaemon(true); return t; }); this.dbType = plugin.getConfigManager().getDatabaseType(); } public void init() { HikariConfig hikari = new HikariConfig(); hikari.setPoolName("CrystalStore-Pool"); if (dbType.equals("SQLITE")) { File dbFile = new File(plugin.getDataFolder(), "data.db"); plugin.getDataFolder().mkdirs(); hikari.setJdbcUrl("jdbc:sqlite:" + dbFile.getAbsolutePath()); hikari.setDriverClassName("org.sqlite.JDBC"); hikari.setMaximumPoolSize(1); } else { String host = plugin.getConfigManager().getDatabaseHost(); int port = plugin.getConfigManager().getDatabasePort(); String name = plugin.getConfigManager().getDatabaseName(); String user = plugin.getConfigManager().getDatabaseUsername(); String pass = plugin.getConfigManager().getDatabasePassword(); int poolSize = plugin.getConfigManager().getDatabasePoolSize(); String scheme = dbType.equals("POSTGRESQL") ? "postgresql" : "mysql"; hikari.setJdbcUrl("jdbc:" + scheme + "://" + host + ":" + port + "/" + name + "?useSSL=false&allowPublicKeyRetrieval=true&characterEncoding=utf8"); hikari.setUsername(user); hikari.setPassword(pass); hikari.setMaximumPoolSize(poolSize); hikari.setConnectionTimeout(plugin.getConfigManager().getDatabaseConnectionTimeout()); } hikari.addDataSourceProperty("cachePrepStmts", "true"); hikari.addDataSourceProperty("prepStmtCacheSize", "250"); hikari.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); dataSource = new HikariDataSource(hikari); createTables(); plugin.getLogger().info("Database connected (" + dbType + ")."); } public void close() { if (dataSource != null && !dataSource.isClosed()) { dataSource.close(); } executor.shutdown(); } private Connection getConnection() throws SQLException { return dataSource.getConnection(); } private void createTables() { String[] sqls = { "CREATE TABLE IF NOT EXISTS cs_products (" + "id VARCHAR(36) PRIMARY KEY," + "name VARCHAR(255) NOT NULL," + "description TEXT," + "lore TEXT," + "price DOUBLE NOT NULL DEFAULT 0," + "discounted_price DOUBLE DEFAULT 0," + "category VARCHAR(50) NOT NULL," + "icon_material VARCHAR(100)," + "icon_base64 TEXT," + "commands TEXT," + "permissions TEXT," + "stock INTEGER DEFAULT -1," + "featured TINYINT(1) DEFAULT 0," + "active TINYINT(1) DEFAULT 1," + "created_at BIGINT," + "total_purchases INTEGER DEFAULT 0," + "total_views INTEGER DEFAULT 0," + "sale_end_time BIGINT DEFAULT 0," + "custom_data TEXT" + ")", "CREATE TABLE IF NOT EXISTS cs_orders (" + "id VARCHAR(36) PRIMARY KEY," + "player_id VARCHAR(36) NOT NULL," + "player_name VARCHAR(16)," + "items TEXT NOT NULL," + "amount DOUBLE NOT NULL," + "original_amount DOUBLE NOT NULL," + "currency VARCHAR(10) DEFAULT 'INR'," + "status VARCHAR(20) NOT NULL," + "gateway_order_id VARCHAR(255)," + "payment_id VARCHAR(255)," + "gateway_type VARCHAR(20)," + "coupon_code VARCHAR(50)," + "affiliate_code VARCHAR(50)," + "created_at BIGINT," + "paid_at BIGINT DEFAULT 0," + "is_gift TINYINT(1) DEFAULT 0," + "gift_recipient VARCHAR(36)," + "gift_recipient_name VARCHAR(16)" + ")", "CREATE TABLE IF NOT EXISTS cs_pending_rewards (" + "id VARCHAR(36) PRIMARY KEY," + "order_id VARCHAR(36) NOT NULL," + "player_id VARCHAR(36) NOT NULL," + "player_name VARCHAR(16)," + "product_name VARCHAR(255)," + "commands TEXT NOT NULL," + "created_at BIGINT" + ")", "CREATE TABLE IF NOT EXISTS cs_coupons (" + "code VARCHAR(50) PRIMARY KEY," + "type VARCHAR(20) NOT NULL," + "amount DOUBLE NOT NULL," + "usage_limit INTEGER DEFAULT -1," + "current_usage INTEGER DEFAULT 0," + "expires_at BIGINT DEFAULT -1," + "product_ids TEXT," + "active TINYINT(1) DEFAULT 1," + "created_by VARCHAR(36)," + "created_at BIGINT" + ")", "CREATE TABLE IF NOT EXISTS cs_coupon_usage (" + "id VARCHAR(36) PRIMARY KEY," + "code VARCHAR(50) NOT NULL," + "player_id VARCHAR(36) NOT NULL," + "used_at BIGINT" + ")", "CREATE TABLE IF NOT EXISTS cs_wishlist (" + "player_id VARCHAR(36) NOT NULL," + "product_id VARCHAR(36) NOT NULL," + "added_at BIGINT," + "PRIMARY KEY (player_id, product_id)" + ")", "CREATE TABLE IF NOT EXISTS cs_reviews (" + "id VARCHAR(36) PRIMARY KEY," + "player_id VARCHAR(36) NOT NULL," + "player_name VARCHAR(16)," + "product_id VARCHAR(36) NOT NULL," + "rating INTEGER NOT NULL," + "comment TEXT," + "created_at BIGINT," + "pinned TINYINT(1) DEFAULT 0" + ")", "CREATE TABLE IF NOT EXISTS cs_affiliates (" + "code VARCHAR(50) PRIMARY KEY," + "owner_id VARCHAR(36) NOT NULL," + "owner_name VARCHAR(16)," + "total_sales INTEGER DEFAULT 0," + "total_revenue DOUBLE DEFAULT 0," + "created_at BIGINT," + "active TINYINT(1) DEFAULT 1" + ")", "CREATE TABLE IF NOT EXISTS cs_affiliate_usage (" + "id VARCHAR(36) PRIMARY KEY," + "code VARCHAR(50) NOT NULL," + "player_id VARCHAR(36) NOT NULL," + "order_id VARCHAR(36) NOT NULL," + "revenue DOUBLE DEFAULT 0," + "used_at BIGINT" + ")", "CREATE TABLE IF NOT EXISTS cs_branding (" + "server_id VARCHAR(36) PRIMARY KEY," + "store_name VARCHAR(255)," + "store_description TEXT," + "tagline VARCHAR(255)," + "icon_material VARCHAR(100)," + "banner_text TEXT," + "primary_color VARCHAR(10)," + "secondary_color VARCHAR(10)," + "footer_text TEXT," + "discord_webhook TEXT," + "updated_at BIGINT" + ")", "CREATE TABLE IF NOT EXISTS cs_analytics_daily (" + "date VARCHAR(10) NOT NULL," + "revenue DOUBLE DEFAULT 0," + "orders INTEGER DEFAULT 0," + "unique_buyers INTEGER DEFAULT 0," + "PRIMARY KEY (date)" + ")" }; try (Connection conn = getConnection(); Statement stmt = conn.createStatement()) { for (String sql : sqls) { stmt.execute(sql); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Failed to create database tables", e); } } // ========================= PRODUCTS ========================= public CompletableFuture> getProducts() { return CompletableFuture.supplyAsync(() -> { List products = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_products WHERE active = 1"); ResultSet rs = stmt.executeQuery()) { while (rs.next()) products.add(mapProduct(rs)); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error loading products", e); } return products; }, executor); } public CompletableFuture saveProduct(Product p) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_products " + "(id,name,description,lore,price,discounted_price,category,icon_material,icon_base64," + "commands,permissions,stock,featured,active,created_at,total_purchases,total_views,sale_end_time,custom_data) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; if (dbType.equals("MYSQL") || dbType.equals("MARIADB") || dbType.equals("POSTGRESQL")) { sql = "INSERT INTO cs_products " + "(id,name,description,lore,price,discounted_price,category,icon_material,icon_base64," + "commands,permissions,stock,featured,active,created_at,total_purchases,total_views,sale_end_time,custom_data) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),lore=VALUES(lore)," + "price=VALUES(price),discounted_price=VALUES(discounted_price),category=VALUES(category)," + "icon_material=VALUES(icon_material),icon_base64=VALUES(icon_base64),commands=VALUES(commands)," + "permissions=VALUES(permissions),stock=VALUES(stock),featured=VALUES(featured),active=VALUES(active)," + "sale_end_time=VALUES(sale_end_time),custom_data=VALUES(custom_data)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, p.getId()); stmt.setString(2, p.getName()); stmt.setString(3, p.getDescription()); stmt.setString(4, gson.toJson(p.getLore())); stmt.setDouble(5, p.getPrice()); stmt.setDouble(6, p.getDiscountedPrice()); stmt.setString(7, p.getCategory().name()); stmt.setString(8, p.getIconMaterial()); stmt.setString(9, p.getIconBase64()); stmt.setString(10, gson.toJson(p.getCommands())); stmt.setString(11, gson.toJson(p.getPermissions())); stmt.setInt(12, p.getStock()); stmt.setInt(13, p.isFeatured() ? 1 : 0); stmt.setInt(14, p.isActive() ? 1 : 0); stmt.setLong(15, p.getCreatedAt()); stmt.setInt(16, p.getTotalPurchases()); stmt.setInt(17, p.getTotalViews()); stmt.setLong(18, p.getSaleEndTime()); stmt.setString(19, p.getCustomData()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving product: " + p.getId(), e); } }, executor); } public CompletableFuture deleteProduct(String id) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("UPDATE cs_products SET active = 0 WHERE id = ?")) { stmt.setString(1, id); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error deleting product: " + id, e); } }, executor); } public CompletableFuture incrementProductViews(String id) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "UPDATE cs_products SET total_views = total_views + 1 WHERE id = ?")) { stmt.setString(1, id); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error incrementing product views", e); } }, executor); } public CompletableFuture incrementProductPurchases(String id, int quantity) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "UPDATE cs_products SET total_purchases = total_purchases + ?, stock = CASE WHEN stock > 0 THEN stock - ? ELSE stock END WHERE id = ?")) { stmt.setInt(1, quantity); stmt.setInt(2, quantity); stmt.setString(3, id); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error incrementing product purchases", e); } }, executor); } // ========================= ORDERS ========================= public CompletableFuture saveOrder(Order o) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_orders " + "(id,player_id,player_name,items,amount,original_amount,currency,status,gateway_order_id," + "payment_id,gateway_type,coupon_code,affiliate_code,created_at,paid_at,is_gift,gift_recipient,gift_recipient_name) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; if (!dbType.equals("SQLITE")) { sql = "INSERT INTO cs_orders " + "(id,player_id,player_name,items,amount,original_amount,currency,status,gateway_order_id," + "payment_id,gateway_type,coupon_code,affiliate_code,created_at,paid_at,is_gift,gift_recipient,gift_recipient_name) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE status=VALUES(status),gateway_order_id=VALUES(gateway_order_id)," + "payment_id=VALUES(payment_id),paid_at=VALUES(paid_at)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, o.getId()); stmt.setString(2, o.getPlayerId().toString()); stmt.setString(3, o.getPlayerName()); stmt.setString(4, gson.toJson(o.getItems())); stmt.setDouble(5, o.getAmount()); stmt.setDouble(6, o.getOriginalAmount()); stmt.setString(7, o.getCurrency()); stmt.setString(8, o.getStatus().name()); stmt.setString(9, o.getGatewayOrderId()); stmt.setString(10, o.getPaymentId()); stmt.setString(11, o.getGatewayType() != null ? o.getGatewayType().name() : null); stmt.setString(12, o.getCouponCode()); stmt.setString(13, o.getAffiliateCode()); stmt.setLong(14, o.getCreatedAt()); stmt.setLong(15, o.getPaidAt()); stmt.setInt(16, o.isGift() ? 1 : 0); stmt.setString(17, o.getGiftRecipient() != null ? o.getGiftRecipient().toString() : null); stmt.setString(18, o.getGiftRecipientName()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving order: " + o.getId(), e); } }, executor); } public CompletableFuture getOrderByGatewayOrderId(String gatewayOrderId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM cs_orders WHERE gateway_order_id = ?")) { stmt.setString(1, gatewayOrderId); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapOrder(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error fetching order by gateway id", e); } return null; }, executor); } public CompletableFuture getOrderById(String orderId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_orders WHERE id = ?")) { stmt.setString(1, orderId); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapOrder(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error fetching order by id", e); } return null; }, executor); } public CompletableFuture> getPlayerOrders(UUID playerId) { return CompletableFuture.supplyAsync(() -> { List orders = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM cs_orders WHERE player_id = ? ORDER BY created_at DESC LIMIT 50")) { stmt.setString(1, playerId.toString()); try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) orders.add(mapOrder(rs)); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error fetching player orders", e); } return orders; }, executor); } public CompletableFuture hasPlayerPurchased(UUID playerId, String productId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT COUNT(*) FROM cs_orders WHERE player_id = ? AND items LIKE ? AND status = 'DELIVERED'")) { stmt.setString(1, playerId.toString()); stmt.setString(2, "%" + productId + "%"); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return rs.getInt(1) > 0; } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error checking purchase", e); } return false; }, executor); } // ========================= PENDING REWARDS ========================= public CompletableFuture savePendingReward(PendingReward r) { return CompletableFuture.runAsync(() -> { String sql = "INSERT INTO cs_pending_rewards (id,order_id,player_id,player_name,product_name,commands,created_at) VALUES (?,?,?,?,?,?,?)"; try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, r.getId()); stmt.setString(2, r.getOrderId()); stmt.setString(3, r.getPlayerId().toString()); stmt.setString(4, r.getPlayerName()); stmt.setString(5, r.getProductName()); stmt.setString(6, gson.toJson(r.getCommands())); stmt.setLong(7, r.getCreatedAt()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving pending reward", e); } }, executor); } public CompletableFuture> getPendingRewards(UUID playerId) { return CompletableFuture.supplyAsync(() -> { List rewards = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM cs_pending_rewards WHERE player_id = ? ORDER BY created_at ASC")) { stmt.setString(1, playerId.toString()); try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) rewards.add(mapPendingReward(rs)); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error fetching pending rewards", e); } return rewards; }, executor); } public CompletableFuture deletePendingReward(String id) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("DELETE FROM cs_pending_rewards WHERE id = ?")) { stmt.setString(1, id); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error deleting pending reward", e); } }, executor); } // ========================= COUPONS ========================= public CompletableFuture getCoupon(String code) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_coupons WHERE code = ?")) { stmt.setString(1, code.toUpperCase()); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapCoupon(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching coupon", e); } return null; }, executor); } public CompletableFuture> getAllCoupons() { return CompletableFuture.supplyAsync(() -> { List coupons = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_coupons ORDER BY created_at DESC"); ResultSet rs = stmt.executeQuery()) { while (rs.next()) coupons.add(mapCoupon(rs)); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching coupons", e); } return coupons; }, executor); } public CompletableFuture saveCoupon(Coupon c) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_coupons (code,type,amount,usage_limit,current_usage,expires_at,product_ids,active,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,?)"; if (!dbType.equals("SQLITE")) { sql = "INSERT INTO cs_coupons (code,type,amount,usage_limit,current_usage,expires_at,product_ids,active,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE type=VALUES(type),amount=VALUES(amount),usage_limit=VALUES(usage_limit)," + "current_usage=VALUES(current_usage),expires_at=VALUES(expires_at),active=VALUES(active)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, c.getCode().toUpperCase()); stmt.setString(2, c.getType().name()); stmt.setDouble(3, c.getAmount()); stmt.setInt(4, c.getUsageLimit()); stmt.setInt(5, c.getCurrentUsage()); stmt.setLong(6, c.getExpiresAt()); stmt.setString(7, gson.toJson(c.getProductIds())); stmt.setInt(8, c.isActive() ? 1 : 0); stmt.setString(9, c.getCreatedBy()); stmt.setLong(10, c.getCreatedAt()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving coupon", e); } }, executor); } public CompletableFuture hasCouponBeenUsedBy(String code, UUID playerId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT COUNT(*) FROM cs_coupon_usage WHERE code = ? AND player_id = ?")) { stmt.setString(1, code.toUpperCase()); stmt.setString(2, playerId.toString()); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return rs.getInt(1) > 0; } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error checking coupon usage", e); } return false; }, executor); } public CompletableFuture recordCouponUsage(String code, UUID playerId) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection()) { try (PreparedStatement stmt = conn.prepareStatement( "INSERT INTO cs_coupon_usage (id,code,player_id,used_at) VALUES (?,?,?,?)")) { stmt.setString(1, UUID.randomUUID().toString()); stmt.setString(2, code.toUpperCase()); stmt.setString(3, playerId.toString()); stmt.setLong(4, System.currentTimeMillis()); stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement( "UPDATE cs_coupons SET current_usage = current_usage + 1 WHERE code = ?")) { stmt.setString(1, code.toUpperCase()); stmt.executeUpdate(); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error recording coupon usage", e); } }, executor); } // ========================= WISHLIST ========================= public CompletableFuture> getWishlist(UUID playerId) { return CompletableFuture.supplyAsync(() -> { List ids = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT product_id FROM cs_wishlist WHERE player_id = ? ORDER BY added_at DESC")) { stmt.setString(1, playerId.toString()); try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) ids.add(rs.getString("product_id")); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching wishlist", e); } return ids; }, executor); } public CompletableFuture addToWishlist(UUID playerId, String productId) { return CompletableFuture.runAsync(() -> { String sql = dbType.equals("SQLITE") ? "INSERT OR IGNORE INTO cs_wishlist (player_id,product_id,added_at) VALUES (?,?,?)" : "INSERT IGNORE INTO cs_wishlist (player_id,product_id,added_at) VALUES (?,?,?)"; try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, playerId.toString()); stmt.setString(2, productId); stmt.setLong(3, System.currentTimeMillis()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error adding to wishlist", e); } }, executor); } public CompletableFuture removeFromWishlist(UUID playerId, String productId) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "DELETE FROM cs_wishlist WHERE player_id = ? AND product_id = ?")) { stmt.setString(1, playerId.toString()); stmt.setString(2, productId); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error removing from wishlist", e); } }, executor); } // ========================= REVIEWS ========================= public CompletableFuture> getReviews(String productId) { return CompletableFuture.supplyAsync(() -> { List reviews = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM cs_reviews WHERE product_id = ? ORDER BY pinned DESC, created_at DESC")) { stmt.setString(1, productId); try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) reviews.add(mapReview(rs)); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching reviews", e); } return reviews; }, executor); } public CompletableFuture hasReviewed(UUID playerId, String productId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT COUNT(*) FROM cs_reviews WHERE player_id = ? AND product_id = ?")) { stmt.setString(1, playerId.toString()); stmt.setString(2, productId); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return rs.getInt(1) > 0; } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error checking review existence", e); } return false; }, executor); } public CompletableFuture saveReview(Review r) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_reviews (id,player_id,player_name,product_id,rating,comment,created_at,pinned) VALUES (?,?,?,?,?,?,?,?)"; if (!dbType.equals("SQLITE")) { sql = "INSERT INTO cs_reviews (id,player_id,player_name,product_id,rating,comment,created_at,pinned) VALUES (?,?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE rating=VALUES(rating),comment=VALUES(comment)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, r.getId()); stmt.setString(2, r.getPlayerId().toString()); stmt.setString(3, r.getPlayerName()); stmt.setString(4, r.getProductId()); stmt.setInt(5, r.getRating()); stmt.setString(6, r.getComment()); stmt.setLong(7, r.getCreatedAt()); stmt.setInt(8, r.isPinned() ? 1 : 0); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving review", e); } }, executor); } public CompletableFuture deleteReview(String reviewId) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("DELETE FROM cs_reviews WHERE id = ?")) { stmt.setString(1, reviewId); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error deleting review", e); } }, executor); } // ========================= AFFILIATES ========================= public CompletableFuture getAffiliate(String code) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_affiliates WHERE code = ?")) { stmt.setString(1, code.toUpperCase()); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapAffiliate(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching affiliate", e); } return null; }, executor); } public CompletableFuture getAffiliateByOwner(UUID ownerId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_affiliates WHERE owner_id = ?")) { stmt.setString(1, ownerId.toString()); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapAffiliate(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching affiliate by owner", e); } return null; }, executor); } public CompletableFuture saveAffiliate(AffiliateCode a) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_affiliates (code,owner_id,owner_name,total_sales,total_revenue,created_at,active) VALUES (?,?,?,?,?,?,?)"; if (!dbType.equals("SQLITE")) { sql = "INSERT INTO cs_affiliates (code,owner_id,owner_name,total_sales,total_revenue,created_at,active) VALUES (?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE total_sales=VALUES(total_sales),total_revenue=VALUES(total_revenue),active=VALUES(active)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, a.getCode().toUpperCase()); stmt.setString(2, a.getOwnerId().toString()); stmt.setString(3, a.getOwnerName()); stmt.setInt(4, a.getTotalSales()); stmt.setDouble(5, a.getTotalRevenue()); stmt.setLong(6, a.getCreatedAt()); stmt.setInt(7, a.isActive() ? 1 : 0); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving affiliate", e); } }, executor); } public CompletableFuture recordAffiliateUsage(String code, UUID playerId, String orderId, double revenue) { return CompletableFuture.runAsync(() -> { try (Connection conn = getConnection()) { try (PreparedStatement stmt = conn.prepareStatement( "INSERT INTO cs_affiliate_usage (id,code,player_id,order_id,revenue,used_at) VALUES (?,?,?,?,?,?)")) { stmt.setString(1, UUID.randomUUID().toString()); stmt.setString(2, code.toUpperCase()); stmt.setString(3, playerId.toString()); stmt.setString(4, orderId); stmt.setDouble(5, revenue); stmt.setLong(6, System.currentTimeMillis()); stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement( "UPDATE cs_affiliates SET total_sales = total_sales + 1, total_revenue = total_revenue + ? WHERE code = ?")) { stmt.setDouble(1, revenue); stmt.setString(2, code.toUpperCase()); stmt.executeUpdate(); } } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error recording affiliate usage", e); } }, executor); } // ========================= BRANDING ========================= public CompletableFuture getBranding(String serverId) { return CompletableFuture.supplyAsync(() -> { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM cs_branding WHERE server_id = ?")) { stmt.setString(1, serverId); try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) return mapBranding(rs); } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching branding", e); } return BrandingConfig.builder().build(); }, executor); } public CompletableFuture saveBranding(String serverId, BrandingConfig b) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO cs_branding (server_id,store_name,store_description,tagline,icon_material,banner_text,primary_color,secondary_color,footer_text,discord_webhook,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)"; if (!dbType.equals("SQLITE")) { sql = "INSERT INTO cs_branding (server_id,store_name,store_description,tagline,icon_material,banner_text,primary_color,secondary_color,footer_text,discord_webhook,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE store_name=VALUES(store_name),store_description=VALUES(store_description)," + "tagline=VALUES(tagline),icon_material=VALUES(icon_material),banner_text=VALUES(banner_text)," + "primary_color=VALUES(primary_color),secondary_color=VALUES(secondary_color)," + "footer_text=VALUES(footer_text),discord_webhook=VALUES(discord_webhook),updated_at=VALUES(updated_at)"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, serverId); stmt.setString(2, b.getStoreName()); stmt.setString(3, b.getStoreDescription()); stmt.setString(4, b.getTagline()); stmt.setString(5, b.getIconMaterial()); stmt.setString(6, b.getBannerText()); stmt.setString(7, b.getPrimaryColor()); stmt.setString(8, b.getSecondaryColor()); stmt.setString(9, b.getFooterText()); stmt.setString(10, b.getDiscordWebhook()); stmt.setLong(11, System.currentTimeMillis()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.SEVERE, "Error saving branding", e); } }, executor); } // ========================= ANALYTICS ========================= public CompletableFuture recordSale(double amount, UUID buyerId) { return CompletableFuture.runAsync(() -> { String date = new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date()); String upsert; if (dbType.equals("SQLITE")) { upsert = "INSERT INTO cs_analytics_daily (date,revenue,orders,unique_buyers) VALUES (?,?,1,1) " + "ON CONFLICT(date) DO UPDATE SET revenue=revenue+?,orders=orders+1"; } else { upsert = "INSERT INTO cs_analytics_daily (date,revenue,orders,unique_buyers) VALUES (?,?,1,1) " + "ON DUPLICATE KEY UPDATE revenue=revenue+?,orders=orders+1"; } try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(upsert)) { stmt.setString(1, date); stmt.setDouble(2, amount); stmt.setDouble(3, amount); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error recording analytics", e); } }, executor); } public CompletableFuture> getAnalytics(int days) { return CompletableFuture.supplyAsync(() -> { List data = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM cs_analytics_daily ORDER BY date DESC LIMIT ?")) { stmt.setInt(1, days); try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) { data.add(new AnalyticsData( rs.getString("date"), rs.getDouble("revenue"), rs.getInt("orders"), rs.getInt("unique_buyers") )); } } } catch (SQLException e) { plugin.getLogger().log(Level.WARNING, "Error fetching analytics", e); } return data; }, executor); } // ========================= MAPPERS ========================= @SuppressWarnings("unchecked") private Product mapProduct(ResultSet rs) throws SQLException { Product p = new Product(); p.setId(rs.getString("id")); p.setName(rs.getString("name")); p.setDescription(rs.getString("description")); p.setPrice(rs.getDouble("price")); p.setDiscountedPrice(rs.getDouble("discounted_price")); p.setCategory(ProductCategory.fromString(rs.getString("category"))); p.setIconMaterial(rs.getString("icon_material")); p.setIconBase64(rs.getString("icon_base64")); p.setStock(rs.getInt("stock")); p.setFeatured(rs.getInt("featured") == 1); p.setActive(rs.getInt("active") == 1); p.setCreatedAt(rs.getLong("created_at")); p.setTotalPurchases(rs.getInt("total_purchases")); p.setTotalViews(rs.getInt("total_views")); p.setSaleEndTime(rs.getLong("sale_end_time")); p.setCustomData(rs.getString("custom_data")); String loreJson = rs.getString("lore"); if (loreJson != null) { p.setLore(gson.fromJson(loreJson, new TypeToken>(){}.getType())); } String cmdJson = rs.getString("commands"); if (cmdJson != null) { p.setCommands(gson.fromJson(cmdJson, new TypeToken>(){}.getType())); } String permJson = rs.getString("permissions"); if (permJson != null) { p.setPermissions(gson.fromJson(permJson, new TypeToken>(){}.getType())); } return p; } @SuppressWarnings("unchecked") private Order mapOrder(ResultSet rs) throws SQLException { Order o = new Order(); o.setId(rs.getString("id")); o.setPlayerId(UUID.fromString(rs.getString("player_id"))); o.setPlayerName(rs.getString("player_name")); o.setAmount(rs.getDouble("amount")); o.setOriginalAmount(rs.getDouble("original_amount")); o.setCurrency(rs.getString("currency")); o.setStatus(OrderStatus.fromString(rs.getString("status"))); o.setGatewayOrderId(rs.getString("gateway_order_id")); o.setPaymentId(rs.getString("payment_id")); String gt = rs.getString("gateway_type"); if (gt != null) o.setGatewayType(GatewayType.fromString(gt)); o.setCouponCode(rs.getString("coupon_code")); o.setAffiliateCode(rs.getString("affiliate_code")); o.setCreatedAt(rs.getLong("created_at")); o.setPaidAt(rs.getLong("paid_at")); o.setGift(rs.getInt("is_gift") == 1); String gifRec = rs.getString("gift_recipient"); if (gifRec != null) o.setGiftRecipient(UUID.fromString(gifRec)); o.setGiftRecipientName(rs.getString("gift_recipient_name")); String itemsJson = rs.getString("items"); if (itemsJson != null) { o.setItems(gson.fromJson(itemsJson, new TypeToken>(){}.getType())); } return o; } @SuppressWarnings("unchecked") private PendingReward mapPendingReward(ResultSet rs) throws SQLException { PendingReward r = new PendingReward(); r.setId(rs.getString("id")); r.setOrderId(rs.getString("order_id")); r.setPlayerId(UUID.fromString(rs.getString("player_id"))); r.setPlayerName(rs.getString("player_name")); r.setProductName(rs.getString("product_name")); r.setCreatedAt(rs.getLong("created_at")); String cmdsJson = rs.getString("commands"); if (cmdsJson != null) { r.setCommands(gson.fromJson(cmdsJson, new TypeToken>(){}.getType())); } return r; } @SuppressWarnings("unchecked") private Coupon mapCoupon(ResultSet rs) throws SQLException { Coupon c = new Coupon(); c.setCode(rs.getString("code")); c.setType(CouponType.fromString(rs.getString("type"))); c.setAmount(rs.getDouble("amount")); c.setUsageLimit(rs.getInt("usage_limit")); c.setCurrentUsage(rs.getInt("current_usage")); c.setExpiresAt(rs.getLong("expires_at")); c.setActive(rs.getInt("active") == 1); c.setCreatedBy(rs.getString("created_by")); c.setCreatedAt(rs.getLong("created_at")); String pidsJson = rs.getString("product_ids"); if (pidsJson != null) { c.setProductIds(gson.fromJson(pidsJson, new TypeToken>(){}.getType())); } return c; } private Review mapReview(ResultSet rs) throws SQLException { return new Review( rs.getString("id"), UUID.fromString(rs.getString("player_id")), rs.getString("player_name"), rs.getString("product_id"), rs.getInt("rating"), rs.getString("comment"), rs.getLong("created_at"), rs.getInt("pinned") == 1 ); } private AffiliateCode mapAffiliate(ResultSet rs) throws SQLException { return new AffiliateCode( rs.getString("code"), UUID.fromString(rs.getString("owner_id")), rs.getString("owner_name"), rs.getInt("total_sales"), rs.getDouble("total_revenue"), rs.getLong("created_at"), rs.getInt("active") == 1 ); } private BrandingConfig mapBranding(ResultSet rs) throws SQLException { return BrandingConfig.builder() .storeName(rs.getString("store_name")) .storeDescription(rs.getString("store_description")) .tagline(rs.getString("tagline")) .iconMaterial(rs.getString("icon_material")) .bannerText(rs.getString("banner_text")) .primaryColor(rs.getString("primary_color")) .secondaryColor(rs.getString("secondary_color")) .footerText(rs.getString("footer_text")) .discordWebhook(rs.getString("discord_webhook")) .updatedAt(rs.getLong("updated_at")) .build(); } }