MyGUILore.java

import java.awt.*;
import java.sql.*;
import java.util.Arrays;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;

public class MyGUILore extends JFrame {
    private Connection connection;
    private DefaultTableModel tableModel;
    private JTable table;

    // sets up the GUI and connects to MySQL
    public MyGUILore() {
        setTitle("PersonalityMatch GUI");
        setSize(1200, 700);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);

        // Attempt DB connection right away
        connectToDatabase();

        // Create top panel with buttons
        JPanel controlPanel = new JPanel(new FlowLayout());

        // Basic operations
        JButton btnCreate    = new JButton("Create Tables");
        JButton btnPopulate  = new JButton("Populate Tables");
        JButton btnQuery     = new JButton("Simple Query");
        JButton btnDrop      = new JButton("Drop Tables");

        // CRUD
        JButton btnDelete    = new JButton("Delete Record");
        JButton btnUpdate    = new JButton("Update Record");

        // Advanced queries
        JButton btnUserNetwork     = new JButton("User Network");
        JButton btnLocationReport  = new JButton("Location Report");
        JButton btnMatchRank       = new JButton("Match Rank");
        JButton btnPersonalityStats= new JButton("Personality Stats");
        JButton btnSharedInterests = new JButton("Shared Interests");

        // Add action listeners
        btnCreate.addActionListener(e -> createAllTables());
        btnPopulate.addActionListener(e -> populateAllTables());
        btnDrop.addActionListener(e -> dropAllTables());
        btnQuery.addActionListener(e -> queryTables("SELECT * FROM User"));

        btnDelete.addActionListener(e -> deleteUserRecord());
        btnUpdate.addActionListener(e -> updateUserRecord());

        btnUserNetwork.addActionListener(e -> runUserNetworkReport());
        btnLocationReport.addActionListener(e -> runLocationReport());
        btnMatchRank.addActionListener(e -> runMatchRankReport());
        btnPersonalityStats.addActionListener(e -> runPersonalityStatsReport());
        btnSharedInterests.addActionListener(e -> runSharedInterestsReport());

        // Add buttons to panel
        for (JButton b : Arrays.asList(
                btnCreate, btnPopulate, btnQuery, btnDrop,
                btnDelete, btnUpdate,
                btnUserNetwork, btnLocationReport, btnMatchRank, btnPersonalityStats, btnSharedInterests
        )) {
            controlPanel.add(b);
        }

        // Table model & table for displaying results
        tableModel = new DefaultTableModel();
        table = new JTable(tableModel);
        JScrollPane scrollPane = new JScrollPane(table);

        // Layout
        setLayout(new BorderLayout());
        add(controlPanel, BorderLayout.NORTH);
        add(scrollPane, BorderLayout.CENTER);
    }

    private void connectToDatabase() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            String URL = "jdbc:mysql://localhost:3306/personalitymatch";
            String USER = "root";
            String PASSWORD = "IloveTopik889**";
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("DB Connection successful.");
        } catch (ClassNotFoundException | SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Database Connection Error: " + ex.getMessage());
        }
    }

    
    private void createAllTables() {
        // This big multiline string has all your CREATE statements
        String createSQL = """
            CREATE TABLE IF NOT EXISTS User (
                UserID INT PRIMARY KEY,
                Name VARCHAR(50) NOT NULL,
                Email VARCHAR(100) UNIQUE NOT NULL,
                PhoneNumber VARCHAR(15) UNIQUE NOT NULL,
                DOB DATE,
                Username VARCHAR(50) UNIQUE,
                Location VARCHAR(100),
                Gender VARCHAR(10)
            );

            CREATE TABLE IF NOT EXISTS PersonalityTypes (
                PersonalityID INT PRIMARY KEY,
                PersonalityName VARCHAR(50) NOT NULL,
                PersonalityDescription VARCHAR(255)
            );

            CREATE TABLE IF NOT EXISTS PersonalityProfile (
                ProfileID INT PRIMARY KEY,
                UserID INT NOT NULL,
                PersonalityID INT NOT NULL,
                CompatibilityScore FLOAT NOT NULL DEFAULT 0.0,
                CHECK (CompatibilityScore >= 0.0 AND CompatibilityScore <= 100.0),
                FOREIGN KEY (UserID) REFERENCES User(UserID),
                FOREIGN KEY (PersonalityID) REFERENCES PersonalityTypes(PersonalityID)
            );

            CREATE TABLE IF NOT EXISTS Matches (
                MatchID INT PRIMARY KEY,
                UserID1 INT NOT NULL,
                UserID2 INT NOT NULL,
                DateMatched DATE,
                CompatibilityScore FLOAT NOT NULL,
                FOREIGN KEY (UserID1) REFERENCES User(UserID),
                FOREIGN KEY (UserID2) REFERENCES User(UserID)
            );

            CREATE TABLE IF NOT EXISTS InteractionHistory (
                InteractionID INT PRIMARY KEY,
                MatchID INT NOT NULL,
                Message TEXT,
                InteractionDate DATETIME,
                FOREIGN KEY (MatchID) REFERENCES Matches(MatchID)
            );

            CREATE TABLE IF NOT EXISTS UserPreferences (
                PreferenceID INT PRIMARY KEY,
                UserID INT NOT NULL,
                PreferenceType VARCHAR(50),
                PreferenceValue VARCHAR(100),
                FOREIGN KEY (UserID) REFERENCES User(UserID)
            );

            CREATE TABLE IF NOT EXISTS Interests (
                InterestID INT PRIMARY KEY,
                InterestName VARCHAR(50) NOT NULL,
                InterestDescription VARCHAR(255)
            );

            CREATE TABLE IF NOT EXISTS UserInterests (
                InterestID INT NOT NULL,
                UserID INT NOT NULL,
                PRIMARY KEY (InterestID, UserID),
                FOREIGN KEY (InterestID) REFERENCES Interests(InterestID),
                FOREIGN KEY (UserID) REFERENCES User(UserID)
            );

            CREATE TABLE IF NOT EXISTS ActivityLog (
                LogID INT PRIMARY KEY,
                UserID INT NOT NULL,
                ActivityType VARCHAR(50),
                ActivityTimeStamp DATETIME,
                FOREIGN KEY (UserID) REFERENCES User(UserID)
            );

            CREATE TABLE IF NOT EXISTS Compatibility (
                CompatibilityID INT PRIMARY KEY,
                UserID1 INT NOT NULL,
                UserID2 INT NOT NULL,
                CompatibilityScore FLOAT NOT NULL,
                FOREIGN KEY (UserID1) REFERENCES User(UserID),
                FOREIGN KEY (UserID2) REFERENCES User(UserID)
            );
            """;

        try (Statement stmt = connection.createStatement()) {
            // Split on semicolons so we can run each CREATE statement separately
            for (String singleStmt : createSQL.split(";")) {
                String trimmed = singleStmt.trim();
                if (!trimmed.isEmpty()) {
                    stmt.execute(trimmed);
                }
            }
            JOptionPane.showMessageDialog(this, "All real tables created (if they didn't exist).");
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Error creating real tables: " + ex.getMessage());
        }
    }


    private void populateAllTables() {
        // Put all your insert statements into this big string:
        String insertSQL = """
            INSERT IGNORE INTO User (UserID, Name, Email, PhoneNumber, DOB, Username, Location, Gender)
            VALUES
            (1, 'Aidan Lin', 'linx2628@mylaurier.ca', '2263950488', '2004-11-23', 'Sushi4aidan', 'Waterloo, Canada', 'Male'),
            (2, 'Michelle Chala', 'Chal1836@mylaurier.ca', '1231902389', '2004-07-11', 'doobie', 'Waterloo, Canada', 'Female');

            INSERT IGNORE INTO PersonalityTypes (PersonalityID, PersonalityName, PersonalityDescription)
            VALUES
            (0,'Lazy','Loves doing nothing but napping and staying at home'),
            (1,'Normal','Just your average everyday person'),
            (2,'Peppy','A go-getter facing everyday with a smile'),
            (3,'Jock','Sporty, in-shape, and loves to be out'),
            (4,'Cranky','Might not be the happiest around, but still loving'),
            (5,'Snooty','Prickly like a rose, but beautiful on the inside'),
            (6,'Sisterly','Looks after everybody with their undying love'),
            (7,'Smug','Shows off whenever they get an opportunity'),
            (8,'Open','Their soul is a gallery ready for others to see'),
            (9,'Conscientious','Always follows the rules, does what is right'),
            (10,'Extroverted','The more the merrier! They love being around others'),
            (11,'Introverted','Would rather be on their own, crowds are scary!'),
            (12,'Neurodivergent','On the spectrum definitely!');

            INSERT IGNORE INTO Compatibility (CompatibilityID, UserID1, UserID2, CompatibilityScore)
            VALUES
            (1,1,2,85.5);

            INSERT IGNORE INTO PersonalityProfile (ProfileID, UserID, PersonalityID, CompatibilityScore)
            VALUES
            (0,1,0,85.5),
            (1,2,6,85.5);

            INSERT IGNORE INTO Matches (MatchID, UserID1, UserID2, DateMatched, CompatibilityScore)
            VALUES
            (1,1,2,'2025-02-07',85.5);

            INSERT IGNORE INTO InteractionHistory (InteractionID, MatchID, Message, InteractionDate)
            VALUES
            (0,1,'hey girl - Sushi4aidan','2025-02-07 14:30:00'),
            (1,1,'hey king - doobie','2025-02-07 14:45:00');

            INSERT IGNORE INTO UserPreferences (PreferenceID, UserID, PreferenceType, PreferenceValue)
            VALUES
            (1,1,'Sexuality','Gay'),
            (2,2,'Sexuality','Straight'),
            (3,2,'Location','Waterloo'),
            (4,2,'Screen Mode','Dark');

            INSERT IGNORE INTO Interests (InterestID, InterestName, InterestDescription)
            VALUES
            (0,'Sports','Watching, playing sports'),
            (1,'Make-up','Applying, experimenting, using make-up products'),
            (2,'Video Games','Playing, discussing, and learning about video games'),
            (3,'Movies','Watching and discussing movies'),
            (4,'Fashion','Dressing up, shopping for clothes, discussing fashion trends'),
            (6,'Music','Listening to, discussing music, and attending concerts');

            INSERT IGNORE INTO UserInterests (InterestID, UserID)
            VALUES
            (2,1),
            (6,1),
            (2,2),
            (6,2);

            INSERT IGNORE INTO ActivityLog (LogID, UserID, ActivityType, ActivityTimeStamp)
            VALUES
            (0,2,'Sent Message','2025-02-07 14:30:00'),
            (1,1,'Sent Message','2025-02-07 14:45:00');
            """;

        try (Statement stmt = connection.createStatement()) {
            for (String singleStmt : insertSQL.split(";")) {
                String trimmed = singleStmt.trim();
                if (!trimmed.isEmpty()) {
                    stmt.executeUpdate(trimmed);
                }
            }
            JOptionPane.showMessageDialog(this, "All real tables populated with dummy data.");
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Error populating real tables: " + ex.getMessage());
        }
    }

    
    private void dropAllTables() {
        // We drop them in an order that respects FKs. Or we can just do them all and rely on no referencing if any.
        String dropSQL = """
            DROP TABLE IF EXISTS Compatibility;
            DROP TABLE IF EXISTS ActivityLog;
            DROP TABLE IF EXISTS UserInterests;
            DROP TABLE IF EXISTS Interests;
            DROP TABLE IF EXISTS UserPreferences;
            DROP TABLE IF EXISTS InteractionHistory;
            DROP TABLE IF EXISTS Matches;
            DROP TABLE IF EXISTS PersonalityProfile;
            DROP TABLE IF EXISTS PersonalityTypes;
            DROP TABLE IF EXISTS User;
            """;
        try (Statement stmt = connection.createStatement()) {
            for (String singleStmt : dropSQL.split(";")) {
                String trimmed = singleStmt.trim();
                if (!trimmed.isEmpty()) {
                    stmt.execute(trimmed);
                }
            }
            JOptionPane.showMessageDialog(this, "All project tables dropped.");
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Error dropping real tables: " + ex.getMessage());
        }
    }

   
    private void queryTables(String sql) {
        if (connection == null) {
            JOptionPane.showMessageDialog(this, "No DB connection!");
            return;
        }
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Clear  data
            tableModel.setRowCount(0);
            tableModel.setColumnCount(0);

            // Create columns 
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            for (int i = 1; i <= colCount; i++) {
                tableModel.addColumn(meta.getColumnLabel(i));
            }

            // Add rows
            while (rs.next()) {
                Object[] rowData = new Object[colCount];
                for (int i = 1; i <= colCount; i++) {
                    rowData[i - 1] = rs.getObject(i);
                }
                tableModel.addRow(rowData);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(this, "Query Error: " + ex.getMessage());
        }
    }

    // CRUD: Delete a record from User table
    private void deleteUserRecord() {
        String input = JOptionPane.showInputDialog(this,
            "Enter the 'UserID' of the record to delete from the 'User' table:");
        if (input == null) return; 

        try {
            int userId = Integer.parseInt(input);
            String sql = "DELETE FROM User WHERE UserID=" + userId;
            try (Statement stmt = connection.createStatement()) {
                int rows = stmt.executeUpdate(sql);
                JOptionPane.showMessageDialog(this, rows + " record(s) deleted from 'User'.");
            }
        } catch (NumberFormatException nfe) {
            JOptionPane.showMessageDialog(this, "Invalid UserID format.");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "Delete Error: " + ex.getMessage());
        }
    }

   
    private void updateUserRecord() {
        String input = JOptionPane.showInputDialog(this,
            "Enter the 'UserID' of the record to update in the 'User' table:");
        if (input == null) return;

        try {
            int userId = Integer.parseInt(input);

            // prompt for new location
            String newLocation = JOptionPane.showInputDialog(this,
                "Enter a new Location for UserID " + userId + ":");
            if (newLocation == null) return;

            String sql = "UPDATE User SET Location='" + newLocation + "' WHERE UserID=" + userId;
            try (Statement stmt = connection.createStatement()) {
                int rows = stmt.executeUpdate(sql);
                JOptionPane.showMessageDialog(this, rows + " record(s) updated in 'User'.");
            }
        } catch (NumberFormatException nfe) {
            JOptionPane.showMessageDialog(this, "Invalid UserID format.");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "Update Error: " + ex.getMessage());
        }
    }


    private void runUserNetworkReport() {
        String sql = """
            WITH RECURSIVE UserNetwork AS (
                SELECT UserID AS Origin, UserID AS ConnectedUser, CAST(UserID AS CHAR(100)) AS path
                FROM User
                WHERE UserID = 1
                UNION ALL
                SELECT un.Origin,
                       CASE WHEN m.UserID1 = un.ConnectedUser THEN m.UserID2 ELSE m.UserID1 END AS ConnectedUser,
                       CONCAT(un.path, ',', CASE WHEN m.UserID1 = un.ConnectedUser THEN m.UserID2 ELSE m.UserID1 END) AS path
                FROM Matches m
                JOIN UserNetwork un
                  ON (m.UserID1 = un.ConnectedUser OR m.UserID2 = un.ConnectedUser)
                WHERE FIND_IN_SET(
                          CASE WHEN m.UserID1 = un.ConnectedUser THEN m.UserID2 ELSE m.UserID1 END,
                          un.path
                      ) = 0
            )
            SELECT DISTINCT ConnectedUser
            FROM UserNetwork
            WHERE ConnectedUser <> 1
            """;
        queryTables(sql);
    }

    private void runLocationReport() {
        String sql = """
            SELECT
                u.Location,
                AVG(m.CompatibilityScore) AS AvgCompatibilityScore,
                COUNT(*) AS TotalMatches
            FROM User u
            JOIN Matches m
                ON u.UserID = m.UserID1 OR u.UserID = m.UserID2
            GROUP BY u.Location
            ORDER BY AvgCompatibilityScore DESC
            """;
        queryTables(sql);
    }

    private void runMatchRankReport() {
        String sql = """
            SELECT
                u.UserID,
                u.Name,
                COUNT(m.MatchID) AS TotalMatches,
                RANK() OVER (ORDER BY COUNT(m.MatchID) DESC) AS MatchRank
            FROM User u
            LEFT JOIN Matches m
                ON u.UserID = m.UserID1 OR u.UserID = m.UserID2
            GROUP BY u.UserID, u.Name
            ORDER BY MatchRank
            """;
        queryTables(sql);
    }

    private void runPersonalityStatsReport() {
        String sql = """
            SELECT
                pt.PersonalityName,
                MIN(pp.CompatibilityScore) AS MinScore,
                MAX(pp.CompatibilityScore) AS MaxScore,
                AVG(pp.CompatibilityScore) AS AvgScore,
                STD(pp.CompatibilityScore) AS StdDevScore
            FROM PersonalityTypes pt
            JOIN PersonalityProfile pp
                ON pt.PersonalityID = pp.PersonalityID
            GROUP BY pt.PersonalityName
            ORDER BY AvgScore DESC
            """;
        queryTables(sql);
    }

    private void runSharedInterestsReport() {
        String sql = """
            SELECT DISTINCT
                u.UserID,
                u.Name,
                i.InterestName
            FROM User u
            JOIN UserInterests ui
                ON u.UserID = ui.UserID
            JOIN Interests i
                ON ui.InterestID = i.InterestID
            WHERE i.InterestID IN (
                SELECT ui2.InterestID
                FROM UserInterests ui2
                WHERE ui2.UserID = 1
            )
              AND u.UserID <> 1
              AND u.UserID IN (
                  SELECT CASE WHEN m.UserID1 = 1 THEN m.UserID2 ELSE m.UserID1 END
                  FROM Matches m
                  WHERE (m.UserID1 = 1 OR m.UserID2 = 1)
                    AND m.CompatibilityScore > 80
              )
            ORDER BY u.Name, i.InterestName
            """;
        queryTables(sql);
    }

    // run the GUI
    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> {
            MyGUILore app = new MyGUILore();
            app.setVisible(true);
        });
    }
}