package control;

import java.io.IOException;
import java.net.InetAddress;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import model.DBConnection;
import org.apache.commons.net.ntp.NTPUDPClient;
import org.apache.commons.net.ntp.TimeInfo;

/**
 * This class provides the user with the basic CRUD operations.
 *
 * @author Daniele Cuomo, Luca Gaudino, Mattia Formisano
 */
public class DataController{
    public static DBConnection db;

    private static Double FIXED_COST = 1.50, SHIPPING_COST = 5.0;

    public static final String INJUNCTIONQUERY = "SELECT idInjunction, Amount, Customer FROM injunction JOIN customer WHERE Customer = FC %s";
    public static final String EVADERQUERY = "SELECT name, surname, FC FROM customer C JOIN expiredbill B WHERE idInjunction IS NULL AND (C.FC = B.customer) %s GROUP BY FC";

    /**
     * Establishes connection with the database and updates it if necessary
     */
    public static void init(){
        db = DBConnection.getInstance();

        DataController.generateBills();
        DataController.updateExpiryBills();
    }

    /**
     * Asks the database to update the injunction composed by the operator
     *
     * @param bills the sequence of bills identifiers
     * @param id    the primary injunction key
     */
    public static void updateInjunction(String bills, Float amount, Object id) {
        try {
            update("injunction SET ExpiredBills = ?, Amount = ? WHERE idInjunction = ?", bills, amount, id);
        } catch (SQLException e) {
            ViewController.printErr(e);
            return;
        }
        ViewController.successPopup();
    }

    /**
     * Asks the database to delete the injunction selected by the operator
     *
     * @param id    the primary injunction key
     */
    public static void deleteInjunction(Object id) {
        try {
            delete("injunction WHERE idInjunction = ?", id);
        } catch (SQLException e) {
            ViewController.printErr(e);
        }
        ViewController.successPopup();
    }

    /**
     * Asks the database to create the injunction composed by the operator
     *
     * @param bills     the sequence of bills identifiers
     * @param cust      the primary customer key
     * @param amount    value obtained through bills
     */
    public static void createInjunction(String bills, Object cust, Float amount) {
        try {
            insert("injunction(amount, customer, expiredbills) VALUES( ?, ?, ? )", amount, cust, bills);
        } catch (SQLException e) {
            ViewController.printErr(e);
            return;
        }
        ViewController.successPopup();
    }

    /**
     * Generate bills every quarter
     */
    public static void generateBills(){
        try{
            int request = 0;
            ResultSet answer = query("call lgaudino_ingdb.checkCreateBill();");
            if( answer.first() )
                request = answer.getInt(1);
            if(request == 0){ return; }

            ResultSet customers = query("SELECT FC FROM customer");
            String fc;
            while( customers.next() ){
                fc = customers.getString(1);
                LocalDate today = getToday();
                insert("unpaidbill(Amount, ReleaseDate, ExpiryDate, Customer) VALUES( ?, ?, ? )",
                    getAmount(), today.toString(), today.plusMonths(2).toString(), fc);
            }
        }
        catch(SQLException e){ ViewController.printErr(e); }
    }

    /**
     * Move the expired bills in the appropriate table
     */
    public static void updateExpiryBills(){
        try{
            ResultSet rs = query("SELECT * FROM unpaidbill WHERE ExpiryDate <= " + getToday().toString());
            while( rs.next() ){
                insert("expiredbill(Amount, Issue, Expiry, Customer) VALUES(?, ?, ?, ?)",
                        rs.getFloat(2), rs.getDate(3), rs.getDate(4), rs.getString(5) );
                delete("unpaidbill WHERE idUnpaidBill = ?", rs.getInt(1));
            }
        }
        catch(SQLException e){ ViewController.printErr(e); }
    }

    /**
     * Returns a pseudo random number that simulates the costs of a water bill
     *
     * @return average consumption * fixed cost + shipping costs
     */
    private static String getAmount(){
        Double tmp = 20 + Math.random()%40;
        tmp = tmp*FIXED_COST + SHIPPING_COST;
        return String.valueOf(tmp);
    }

    /**
     * Asks the network the current day or alternatively asks the system
     *
     * @return current day
     */
    private static LocalDate getToday(){
        try{
            String TIME_SERVER = "time-a.nist.gov";
            NTPUDPClient timeClient = new NTPUDPClient();
            InetAddress inetAddress = InetAddress.getByName(TIME_SERVER);
            TimeInfo timeInfo = timeClient.getTime(inetAddress);
            long returnTime = timeInfo.getMessage().getTransmitTimeStamp().getTime();

            LocalDate time = Instant.ofEpochMilli(returnTime).atZone(ZoneId.systemDefault()).toLocalDate();
            return time;
        }
        catch( IOException e ){ return LocalDate.now(); }
    }

    /**
     * Returns all bills under the same injunction.
     *
     * @param idInjunction injunction primary key
     * @return  bills formatted in a square matrix
     */
    public static String[][] getBills(String idInjunction) {
        String[][] matrix = null;
        try {
            ResultSet rs = query("SELECT idexpiredbill, Issue, Expiry, Amount" + " FROM expiredbill WHERE idInjunction = " + idInjunction);
            int j = 0;
            int nClmn = rs.getMetaData().getColumnCount();
            rs.last();
            int nElem = rs.getRow();
            matrix = new String[nElem][nClmn];
            rs.beforeFirst();
            while (rs.next()) {
                for (int i = 1; i <= nClmn; i++) {
                    matrix[j][i - 1] = rs.getString(i);
                }
                j++;
            }
        } catch (SQLException e) {
            ViewController.printErr(e);
        }
        return matrix;
    }

    /**
     * Asks database to insert a new row
     *
     * @param format    user's request
     * @param values    values passed separately to protect the database
     */
    public static void insert(String format, Object... values) throws SQLException {
        String exe = String.format("INSERT INTO %s", format);
        PreparedStatement stmt = db.conn.prepareStatement(exe);
        for (int i = 0; i < values.length; i++) {
            stmt.setString(i + 1, values[i].toString());
        }
        stmt.executeUpdate();
    }

    /**
     * Asks database to update a row
     *
     * @param format    user's request
     * @param values    values passed separately to protect the database
     */
    public static void update(String format, Object... values) throws SQLException {
        String exe = String.format("UPDATE %s", format);
        PreparedStatement stmt = db.conn.prepareStatement(exe);
        for (int i = 0; i < values.length; i++) {
            stmt.setString(i + 1, values[i].toString());
        }
        stmt.executeUpdate();
    }

    /**
     * Asks database to execute a query
     *
     * @param Q the query string
     * @return  the result returned by the database
     */
    public static ResultSet query(String Q)throws SQLException {
        PreparedStatement stmt;
        stmt = db.conn.prepareStatement(Q, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return stmt.executeQuery();
    }

    /**
     * Asks database to delete a row
     *
     * @param format    user's request
     * @param values    values passed separately to protect the database
     */
    public static void delete(String format, Object... values) throws SQLException {
        String exe = String.format("DELETE FROM %s", format);
        PreparedStatement stmt = db.conn.prepareStatement(exe);
        for (int i = 0; i < values.length; i++) {
            stmt.setString(i + 1, values[i].toString());
        }
        stmt.executeUpdate();
    }

    /**
     * Verifies if the operator is present in the database
     *
     * @param usr   the operator's username
     * @param pwd   the operator's password
     * @return true if the connection is successful, false otherwise
     */
    public static boolean access(String usr, String pwd) {
        /* Query composition */
        String Q = "SELECT * FROM operator WHERE Username = '" + usr + "' AND Password = '" + pwd + "'";
        try{
            ResultSet rs = query(Q);
            return rs.first();
        }catch(SQLException e){ ViewController.printErr(e); }
        return false;
    }
}