import org.json.simple.JSONObject;

import java.sql.*;
import java.util.ArrayList;


public class DB {
    private static Connection conn1, conn2;
    private static Statement statmt;
    private static ResultSet resSet;

    public static void DB() throws ClassNotFoundException, SQLException
    {
        Class.forName("org.sqlite.JDBC");
        conn1 = DriverManager.getConnection("jdbc:sqlite:conv.s3db");
        conn2 = DriverManager.getConnection("jdbc:sqlite:msgs.s3db");

    }

    public static void createDB() throws ClassNotFoundException, SQLException
    {
        statmt = conn1.createStatement();
        statmt.execute("CREATE TABLE if not exists 'conversations' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "'surname name' text, 'status' text, 'date' text);");
        statmt.execute("CREATE TABLE if not exists 'messages' ('id of message' INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "'id of conversation' INT, 'surname name' text, 'message' text);");
    }

    public static void writeConv(String name, String status, String date) throws SQLException
    {
        statmt.execute("INSERT INTO 'conversations' ('surname name', 'status', 'date') VALUES ('" + name
                + "', '" + status + "', '" + date + "'); ");

    }

    public static void writeMsg(String name, String date, String msg, int conversationId) throws SQLException {
        statmt.execute("INSERT INTO 'messages' ('id of conversation', 'surname name', 'message') VALUES ('" + conversationId
                    + "', '" + name + "', '" + msg + "'); ");

    }

    public static String readConvInfo(int conversationId) throws SQLException {
        resSet.beforeFirst();
        resSet = statmt.executeQuery("SELECT COUNT (*) FROM conversations").beforeFirst();
        System.out.println(resSet.getInt(1));

        resSet.first();
        JSONObject obj = new JSONObject();
        while (resSet.next()) {
            obj.put("surname name", resSet.getString("surname name"));
            obj.put("status", resSet.getString("status"));
            obj.put("date", resSet.getString("date"));
            obj.put("id", resSet.getInt("id"));
        }
        return obj.toJSONString();
    }

    public static String readMsg(int msgId) throws SQLException {
        resSet = statmt.executeQuery("SELECT * FROM messages WHERE 'id of message' LIKE '" + msgId + "'");
        JSONObject obj = new JSONObject();
        obj.put("message", resSet.getString("message"));
        return obj.toJSONString();
    }

    public static String readCnvr(int conversationId) throws SQLException {
        resSet = statmt.executeQuery("SELECT * FROM messages WHERE 'id of conversation' LIKE '" + conversationId + "'");
        ArrayList<String> msgs = new ArrayList<>();
        while (resSet.next()) {
            msgs.add(resSet.getString("message"));
        }
        JSONObject obj = new JSONObject();
        obj.put("message", msgs);
        return obj.toJSONString();
    }
}
/*
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class conn {
    public static Connection conn;
    public static Statement statmt;
    public static ResultSet resSet;

    // --------ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ--------
    public static void Conn() throws ClassNotFoundException, SQLException
    {
        conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:TEST1.s3db");

        System.out.println("База Подключена!");
    }

    // --------Создание таблицы--------
    public static void CreateDB() throws ClassNotFoundException, SQLException
    {
        statmt = conn.createStatement();
        statmt.execute("CREATE TABLE if not exists 'users' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' text, 'phone' INT);");

        System.out.println("Таблица создана или уже существует.");
    }

    // --------Заполнение таблицы--------
    public static void WriteDB() throws SQLException
    {
        statmt.execute("INSERT INTO 'users' ('name', 'phone') VALUES ('Petya', 125453); ");
        statmt.execute("INSERT INTO 'users' ('name', 'phone') VALUES ('Vasya', 321789); ");
        statmt.execute("INSERT INTO 'users' ('name', 'phone') VALUES ('Masha', 456123); ");

        System.out.println("Таблица заполнена");
    }

    // -------- Вывод таблицы--------
    public static void ReadDB() throws ClassNotFoundException, SQLException
    {
        resSet = statmt.executeQuery("SELECT * FROM users");

        while(resSet.next())
        {
            int id = resSet.getInt("id");
            String  name = resSet.getString("name");
            String  phone = resSet.getString("phone");
            System.out.println( "ID = " + id );
            System.out.println( "name = " + name );
            System.out.println( "phone = " + phone );
            System.out.println();
        }

        System.out.println("Таблица выведена");
    }

    // --------Закрытие--------
    public static void CloseDB() throws ClassNotFoundException, SQLException
    {
        conn.close();
        statmt.close();
        resSet.close();

        System.out.println("Соединения закрыты");
    }

}
*/