package dao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import dto.Message;
import dto.MyThread;
import dto.User;

public class UserDAO {
    private Connection con;

    public UserDAO(){
        this.con = new BDConnection().getConnection();
        if (this.con == null) System.out.println("Connection nulle");
    }

    public void createUser(String username, String password){
        try{
            PreparedStatement stmt = this.con.prepareStatement("INSERT INTO userAccount (username, password) VALUES (?,?)");
            stmt.setString(1, username);
            stmt.setString(2, password);

            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<User> getAllUsers(){
        ArrayList<User> users = new ArrayList<>();
        try{
            Statement stmt = this.con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM userAccount;");

            while(rs.next()){
                int userId = rs.getInt(1);
                String username = rs.getString(2);
                String pwd = rs.getString(3);

                User nUser = new User(userId, username, pwd);
                users.add(nUser);
            }
        } catch (SQLException sqle) {
            sqle.getStackTrace();
        }
        
        return users;
    }

    public boolean isDatabased(User user) {
        boolean isIn = false;

        try {
            PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE userid=?");
            stmt.setInt(1, user.getId());

            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                isIn = true;
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return isIn;
    }


    public User getUserByLogs(String username, String password){
        User user = new User(0,null,null);

        try{
            PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE username=? AND password=?");
            stmt.setString(1, username);
            stmt.setString(2, password);

            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                int userId = rs.getInt(1);
                String login = rs.getString(2);
                String pwd = rs.getString(3);

                user = new User(userId,login,pwd);
            }

        } catch (SQLException sqle) {
            sqle.getStackTrace();
        }
        return user;
    }

    public User getUserById(int id){
        User user = new User(0,null,null);

        try{
            PreparedStatement stmt = this.con.prepareStatement("SELECT * FROM userAccount WHERE id=?");
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();

            if(rs.next()){
                int userId = rs.getInt(1);
                String username = rs.getString(2);
                String pwd = rs.getString(3);

                user = new User(userId,username,pwd);
            }
        } catch (SQLException sqle) {
            sqle.getStackTrace();
        }

        return user;
    }

    public void addUser(User nUser){
        try{
            PreparedStatement stmt = this.con.prepareStatement("INSERT INTO userAccount (username, password) VALUES (?,?)");
            stmt.setString(1, nUser.getUserName());
            stmt.setString(2, nUser.getPwd());

            stmt.executeUpdate();

        } catch (SQLException sqle) {
            sqle.getStackTrace();
        }
    }

    public void removeUser(User nUser){
        try{
            PreparedStatement stmt = this.con.prepareStatement("DELETE FROM userAccount WHERE userID=?");
            stmt.setInt(1, nUser.getId());

            stmt.executeUpdate();

        } catch (SQLException sqle) {
            sqle.getStackTrace();
        }
    }

    // S'abonner à un thread
    public void followThread(User user, Thread thread) throws SQLException {
        PreparedStatement ps = this.con.prepareStatement("INSERT INTO follow VALUES(?, ?, ?)");
        try {
            ps.setString(1, ""+user.getId());
            ps.setString(2, ""+thread.getId());
            ps.setString(3, LocalDate.now().toString());
        }
        catch (SQLException sqle) {
            sqle.getStackTrace();
        }
    }

    // Poster un message dans un thread
    public void postMessage(User user, Thread thread, String message) throws SQLException {
        PreparedStatement ps = this.con.prepareStatement("INSERT INTO msg (userID_msg, threadID, msg) VALUES(?, ?, ?)");
        try {
            ps.setInt(1, user.getId());
            ps.setInt(2, (int) thread.getId());
            ps.setString(3, message);
        }
        catch (SQLException sqle) {
            sqle.getStackTrace();
        }
    }

    // Réagir à un message
    public void reactToMsg(User user, Message msg) throws SQLException {
        PreparedStatement ps = this.con.prepareStatement("INSERT INTO reactions VALUES(?, ?, ?)");
        try {
            ps.setInt(1, user.getId());
            ps.setInt(2, msg.getMsgId());
            ps.setString(3, "true");
        }
        catch (SQLException sqle) {
            sqle.getStackTrace();
        }
    }

    // Récupérer les thread auxquels un utilisateur est abonné
    public List<MyThread> getThreadsByUser(User user) throws SQLException {
        List<MyThread> threads = new ArrayList<>();
        PreparedStatement ps = this.con.prepareStatement("SELECT threadID_follow FROM follow WHERE userID_follow = ?");
        try {
            ps.setInt(1, user.getId());
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int threadId = rs.getInt(1);
                String threadname = rs.getString(2);
                threads.add(new MyThread(threadId, threadname));
            }
        }
        catch (SQLException sqle) {
            sqle.getStackTrace();
        }
        return threads;
    }

    // Récupérer les messages de chaque thread à partir du plus récent
    public List<Message> getMostRecentMessages(User user) throws SQLException {
        List<Message> messages = new ArrayList<>();
        PreparedStatement ps = this.con.prepareStatement("""
        SELECT m.msgID, m.userID_msg, m.threadID, m.msg, m.posted_at FROM msg AS m 
        INNER JOIN thread AS t ON m.threadid = t.threadid 
        WHERE m.threadID IN (
            SELECT f.threadid_follow FROM follow AS f 
            INNER JOIN thread AS t 
            ON t.threadid = f.threadid_follow 
            INNER JOIN userAccount AS u 
            ON u.userid = f.userid_follow 
            WHERE u.userID = ?)
        ORDER BY m.posted_at DESC;
        """);
        try {
            System.out.println("user id : " + user.getId());
            ps.setInt(1, user.getId());
            System.out.println("dans le try");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                System.out.println("dans le while");
                int msgID = rs.getInt(1);
                int sender = rs.getInt(2);
                int threadID = rs.getInt(3);
                String content = rs.getString(4);
                Date creationDate = rs.getDate(5);
                messages.add(new Message(msgID, sender, threadID, content, creationDate.toLocalDate()));
            }
        }
        catch (SQLException sqle) {
            System.out.println("erreur");
            sqle.getStackTrace();
        }
        System.out.println("test");
        return messages;
    }
}