package Database;

import lombok.Getter;
import lombok.NonNull;

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

@SuppressWarnings("SqlSourceToSinkFlow")
@Getter
public class MySQLDatabase {
    private final String url;
    private final String user;
    private final String password;
    private final String database;

    /**
     * Constructor for MySQLDatabase
     * @param host Server address
     * @param port Server port
     * @param user Username
     * @param password Password
     * @param database Database name
     */
    @SuppressWarnings("unused")
    public MySQLDatabase(String host, int port, String user, String password, String database){
        url = String.format("jdbc:mysql://%s:%d/", host, port);
        this.user = user;
        this.password = password;
        this.database = database;
        raise_log(String.format("Connecting to %s:%d with user %s and password %s to database %s", host, port, user, password, database));
        CreateDatabase();

    }

    private void CreateDatabase(){
        try(Connection conn = DriverManager.getConnection(url, user, password)){
            int result = conn.createStatement().executeUpdate(String.format("CREATE DATABASE IF NOT EXISTS %s", database));
            if (result>0){
                raise_log(String.format("Database %s created successfully", database));
            } else {
                raise_log(String.format("Database %s already exists", database));
            }
        } catch (Exception e) {
            raise_log(String.format("Error creating database %s, error: %s", database, e.getMessage()));
        }
    }

    private String correcttablename(String tablename){
        return database+"."+tablename;
    }

    public String[] GetTableNames(){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            Statement stmt = conn.createStatement();
            String sql = String.format("SHOW TABLES FROM %s", database);
            ResultSet rs = stmt.executeQuery(sql);
            List<String> result = new ArrayList<>();
            while(rs.next()){
                result.add(rs.getString(1));
            }
            return result.toArray(new String[0]);
        } catch (Exception e) {
            raise_log(String.format("Error getting table names, error: %s", e.getMessage()));
        }
        return new String[0];
    }

    /**
     * Create Table in database
     * Table schema is defined as FisData object
     * @param tablename Table name, usually in format yyyyMMdd
     * @return String value, "success" |  "error"
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public String CreateTable(@NonNull String tablename){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            Statement stmt = conn.createStatement();
            String sql = String.format("CREATE TABLE IF NOT EXISTS %s ("
                    +"id INT PRIMARY KEY AUTO_INCREMENT,"
                    +"AirlineCode CHAR(2),"     // 2-letter IATA code
                    +"FlightNumber CHAR(4),"    // 4-digit flight number
                    +"DA CHAR(1),"                       // Departure or Arrival
                    +"DI CHAR(1),"                       // Domestic or International
                    +"Origin VARCHAR(255),"                 // JSON String of CityDetail object
                    +"Destination VARCHAR(255),"            // JSON String of Array of CityDetail object
                    +"Gate VARCHAR(20),"                     // Gate number
                    +"Terminal VARCHAR(20),"                // Terminal number
                    +"Baggage VARCHAR(20),"                 // Baggage claim number
                    +"CheckinCounter VARCHAR(20),"          // Check-in counter number
                    +"LastUpdated VARCHAR(20),"             // Last updated time, in format yyyy-MM-dd HH:mm:ss
                    +"ScheduledTime VARCHAR(20),"           // Scheduled time, in format yyyy-MM-dd HH:mm:ss
                    +"EstimatedTime VARCHAR(20),"           // Estimated time, in format yyyy-MM-dd HH:mm:ss
                    +"ActualTime VARCHAR(20),"              // Actual time, in format yyyy-MM-dd HH:mm:ss
                    +"GateScheduledTime VARCHAR(20),"       // Gate scheduled time, in format yyyy-MM-dd HH:mm:ss
                    +"GateEstimatedTime VARCHAR(20),"       // Gate estimated time, in format yyyy-MM-dd HH:mm:ss
                    +"GateActualTime VARCHAR(20),"          // Gate actual time, in format yyyy-MM-dd HH:mm:ss
                    +"Weather VARCHAR(20),"                 // Optional value
                    +"TemperatureCelsius VARCHAR(20),"      // Optional value
                    +"Remarks VARCHAR(255),"                 // Optional value
                    +"IsRead CHAR(1)"                       // for AAS to mark if the data is read
                    +")", correcttablename(tablename));

            // result for create table is always 0
            stmt.executeUpdate(sql);
            return "success";
        } catch (Exception e) {
            raise_log(String.format("Error creating table %s, error: %s\n", tablename,e.getMessage()));
            return "error";
        }
    }

    /**
     * Delete table from database
     * @param tablename Table name, usually in format yyyyMMdd
     * @return String value, "success" | "error"
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public String DeleteTable(@NonNull String tablename){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            String sql = String.format("DROP TABLE IF EXISTS %s", correcttablename(tablename));
            Statement stmt = conn.createStatement();
            //System.out.println(sql);
            // result for drop table is always 0
            stmt.executeUpdate(sql);
            return  "success" ;
        } catch (Exception e) {
            raise_log(String.format("Error deleting table %s, error: %s", tablename, e.getMessage()));
            return "error";
        }
    }

    /**
     * Insert data to table
     * @param tablename Table name, usually in format yyyyMMdd
     * @param datas FisData objects to insert
     * @return String value, "success" | "failed" | "error"
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public String InsertData(@NonNull String tablename, @NonNull FisData... datas){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            conn.beginRequest();
            int xx = 0;
            int successcount = 0;
            for(FisData data : datas){
                var stmt = conn.prepareStatement(String.format("INSERT INTO %s (AirlineCode, FlightNumber, DA, DI, Origin, Destination, Gate, Terminal, Baggage, CheckinCounter, LastUpdated, ScheduledTime, EstimatedTime, ActualTime, GateScheduledTime, GateEstimatedTime, GateActualTime, Weather, TemperatureCelsius, Remarks, IsRead) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", correcttablename(tablename)));
                fisdata_to_preparedstatement(data, stmt);
                int _result = stmt.executeUpdate();

                if (_result>0){
                    //raise_log(String.format("Data %d inserted to %s successfully", xx, tablename));
                    successcount++;
                } else {
                    raise_log(String.format("Data %d insertion to %s failed",xx,tablename));
                }
                xx++;
            }
            conn.endRequest();

            raise_log(String.format("Target Insert: %d, Success: %d data inserted to %s", datas.length, successcount, tablename));
            return successcount == datas.length ? "success" : "failed";
        } catch (Exception e) {
            raise_log(String.format("Error inserting data to %s, error: %s", tablename, e.getMessage()));
            return "error";
        }
    }

    /**
     * Common code for InsertData and Update Data, to convert FisData object to PreparedStatement
     * @param data FisData object
     * @param stmt PreparedStatement object
     * @throws SQLException if error occurs
     */
    private void fisdata_to_preparedstatement(FisData data, PreparedStatement stmt) throws SQLException {
        stmt.setString(1, data.AirlineCode);
        stmt.setString(2, data.FlightNumber);
        stmt.setString(3, String.valueOf(data.DA));
        stmt.setString(4, String.valueOf(data.DI));
        stmt.setString(5, data.Origin);
        stmt.setString(6, data.Destination);
        stmt.setString(7, data.Gate);
        stmt.setString(8, data.Terminal);
        stmt.setString(9, data.BaggageClaim);
        stmt.setString(10, data.CheckinCounter);
        stmt.setString(11, FisData.LocalDateTimeToString(data.lastUpdated));
        stmt.setString(12, FisData.LocalDateTimeToString(data.scheduledTime));
        stmt.setString(13, FisData.LocalDateTimeToString(data.estimatedTime));
        stmt.setString(14, FisData.LocalDateTimeToString(data.actualTime));
        stmt.setString(15, FisData.LocalDateTimeToString(data.gateScheduledTime));
        stmt.setString(16, FisData.LocalDateTimeToString(data.gateEstimatedTime));
        stmt.setString(17, FisData.LocalDateTimeToString(data.gateActualTime));
        stmt.setString(18, data.weather);
        stmt.setString(19, String.format("%.1f",data.temperatureC));
        stmt.setString(20, data.Remark);
        stmt.setString(21, data.IsRead);
    }

    /**
     * Update data in table
     * @param tablename Table name, usually in format yyyyMMdd
     * @param index Index of data to update
     * @param data FisData object to update
     * @return String value, "success" | "failed" | "error"
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public String UpdateData(@NonNull String tablename, int index, @NonNull FisData data){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            String sql = String.format("UPDATE %s SET AirlineCode=?, FlightNumber=?, DA=?, DI=?, Origin=?, Destination=?, Gate=?, Terminal=?, Baggage=?, CheckinCounter=?, LastUpdated=?, ScheduledTime=?, EstimatedTime=?, ActualTime=?, GateScheduledTime=?, GateEstimatedTime=?, GateActualTime=?, Weather=?, TemperatureCelsius=?, Remarks=?, IsRead=? WHERE id=?", correcttablename(tablename));
            var stmt = conn.prepareStatement(sql);
            fisdata_to_preparedstatement(data, stmt);
            stmt.setInt(22, index);
            int result = stmt.executeUpdate();
            return result>0 ? "success" : "failed";

        } catch (Exception e) {
            raise_log(String.format("Error updating data in %s, error: %s", tablename, e.getMessage()));
            return "error";
        }
    }

    /**
     * Delete data from table
     * @param tablename Table name, usually in format yyyyMMdd
     * @param index Index of data to delete
     * @return String value, "success" | "failed" | "error"
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public String DeleteData(@NonNull String tablename, int index){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            String sql = String.format("DELETE FROM %s WHERE id=?", correcttablename(tablename));
            var stmt = conn.prepareStatement(sql);
            stmt.setInt(1, index);
            int result = stmt.executeUpdate();
            return result>0 ? "success" : "failed";

        } catch (Exception e) {
            raise_log(String.format("Error deleting data from %s, error: %s", tablename, e.getMessage()));
            return "error";
        }
    }

    @SuppressWarnings({"SqlSourceToSinkFlow","unused"})
    public String ReorderingIndex(@NonNull String tablename){
        try(Connection conn = DriverManager.getConnection(url+database, user,password)){
            conn.beginRequest();
            var stmt = conn.createStatement();
            stmt.execute("SET @count = 0");
            stmt.execute(String.format("UPDATE %s SET id = @count:=@count+1", correcttablename(tablename)));
            stmt.execute(String.format("ALTER TABLE %s AUTO_INCREMENT=1", correcttablename(tablename)));
            conn.endRequest();
            return "success";
        } catch(Exception e){
            raise_log(String.format("Error reordering index in %s, error: %s", tablename, e.getMessage()));
            return "error";
        }
    }

    private boolean validstring(String str){
        return str != null && !str.isEmpty();
    }

    /**
     * Get data from table
     * @param tablename Table name, usually in format yyyyMMdd
     * @param filter_airlinecode if need to filter by AirlineCode, put the value here, if not needed, put null
     * @param filter_flightnumber if need to filter by FlightNumber, put the value here, if not needed, put null
     * @param filter_da if need to filter by DA, put the value here, if not needed, put null
     * @param filter_di if need to filter by DI, put the value here, if not needed, put null
     * @return Array of FisData objects
     */
    @SuppressWarnings({"unused", "SqlSourceToSinkFlow"})
    public FisData[] GetData(@NonNull String tablename, String filter_airlinecode, String filter_flightnumber, String filter_da, String filter_di){
        try(Connection conn = DriverManager.getConnection(url+database, user, password)){
            boolean have_filter = validstring(filter_airlinecode);

            if (validstring(filter_flightnumber)){
                have_filter = true;
            }
            if (validstring(filter_da)){
                have_filter = true;
            }
            if (validstring(filter_di)){
                have_filter = true;
            }


            ResultSet rs;
            if (!have_filter){
                raise_log("No filter provided, returning all data");
                var stmt = conn.createStatement();
                rs = stmt.executeQuery(String.format("SELECT * FROM %s", correcttablename(tablename)));

            } else {
                    StringBuilder sql = new StringBuilder("SELECT * FROM ");
                    sql.append(correcttablename(tablename));
                    sql.append(" WHERE ");
                    if (filter_airlinecode != null && !filter_airlinecode.isEmpty()){
                        sql.append("AirlineCode='");
                        sql.append(filter_airlinecode);
                        sql.append("' AND ");
                    }
                    if (filter_flightnumber != null && !filter_flightnumber.isEmpty()){
                        sql.append("FlightNumber='");
                        sql.append(filter_flightnumber);
                        sql.append("' AND ");
                    }
                    if (filter_da != null && !filter_da.isEmpty()){
                        sql.append("DA='");
                        sql.append(filter_da);
                        sql.append("' AND ");
                    }
                    if (filter_di != null && !filter_di.isEmpty()){
                        sql.append("DI='");
                        sql.append(filter_di);
                        sql.append("' AND ");
                    }
                    sql.delete(sql.length()-5, sql.length());
                    var stmt = conn.createStatement();
                    rs = stmt.executeQuery(sql.toString());
            }

            List<FisData> result = new ArrayList<>();
            while(rs.next()){
                var temp = new FisData();
                temp.id = rs.getInt("id");
                temp.setAirlineCode(rs.getString("AirlineCode"));
                temp.setFlightNumber(rs.getString("FlightNumber"));
                String tempDA = rs.getString("DA");
                if (tempDA != null && !tempDA.isEmpty()){
                    char tempchar = tempDA.charAt(0);
                    if (tempchar == 'D' || tempchar == 'A'){
                        temp.setDA(tempchar);
                    }
                }
                String tempDI = rs.getString("DI");
                if (tempDI != null && !tempDI.isEmpty()){
                    char tempchar = tempDI.charAt(0);
                    if (tempchar == 'D' || tempchar == 'I'){
                        temp.setDI(tempchar);
                    }
                }
                //temp.setOrigin(GsonFormatter.fromJson(rs.getString("Origin"), CityDetail.class));
                //temp.setDestination(GsonFormatter.fromJson(rs.getString("Destination"), CityDetail[].class));
                temp.Origin = (rs.getString("Origin"));
                temp.Destination = (rs.getString("Destination"));
                temp.Gate = (rs.getString("Gate"));
                temp.Terminal = (rs.getString("Terminal"));
                temp.BaggageClaim = (rs.getString("Baggage"));
                temp.CheckinCounter = (rs.getString("CheckinCounter"));
                String tempLU = rs.getString("LastUpdated");
                temp.setLastUpdated(FisData.CorrectTimeFormat(tempLU) ? tempLU : null);
                String tempST = rs.getString("ScheduledTime");
                temp.setScheduledTime(FisData.CorrectTimeFormat(tempST) ? tempST : null);
                String tempET = rs.getString("EstimatedTime");
                temp.setEstimatedTime(FisData.CorrectTimeFormat(tempET) ? tempET : null);
                String tempAT = rs.getString("ActualTime");
                temp.setActualTime(FisData.CorrectTimeFormat(tempAT) ? tempAT : null);
                String tempGST = rs.getString("GateScheduledTime");
                temp.setGateScheduledTime(FisData.CorrectTimeFormat(tempGST) ? tempGST : null);
                String tempGET = rs.getString("GateEstimatedTime");
                temp.setGateEstimatedTime(FisData.CorrectTimeFormat(tempGET) ? tempGET : null);
                String tempGAT = rs.getString("GateActualTime");
                temp.setGateActualTime(FisData.CorrectTimeFormat(tempGAT) ? tempGAT : null);
                temp.weather = (rs.getString("Weather"));
                temp.temperatureC = (Double.parseDouble(rs.getString("TemperatureCelsius")));
                temp.Remark = (rs.getString("Remarks"));
                temp.IsRead = (rs.getString("IsRead"));
                result.add(temp);
            }
            return result.toArray(new FisData[0]);

        } catch (Exception e) {
            raise_log(String.format("Error GetData from %s, error: %s", tablename, e.getMessage()));
        }
        return new FisData[0];
    }

    private void raise_log(String msg){
        System.out.println(msg);
    }
}
