package mainapp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class Production {
    private int ID;
    private int producerID;
    private int factoryID;
    private String timestamp;
    private float olivesWeight;
    private float oilWeight;
    private float acidity;
    private float costs;
    private boolean sold;
    private boolean isBio;
    private float soldPrice;

    public Production(int ID, int producerID, int factoryID, String timestamp, float olivesWeight, float oilWeight, float acidity, boolean isBio, float costs, boolean sold, float soldPrice) {
        this.ID = ID;
        this.producerID = producerID;
        this.factoryID = factoryID;
        this.timestamp = timestamp;
        this.olivesWeight = olivesWeight;
        this.oilWeight = oilWeight;
        this.isBio = isBio;
        this.acidity = acidity;
        this.costs = costs;
        this.sold = sold;
        this.soldPrice = soldPrice;
    }
    
    public Production (int ID) throws ClassNotFoundException, SQLException {
        DB db = DB.getInstance();
        ResultSet rs = DB.selectRows("production", "*", "`id` = "+ID, null, null);
        ArrayList res = new ArrayList<>();
        while(rs.next()){
            this.ID = ID;
            this.producerID = rs.getInt("producer_id");
            this.factoryID = rs.getInt("factory_id");
            this.timestamp = rs.getString("timestamp");
            this.olivesWeight = rs.getFloat("olives_weight");
            this.oilWeight = rs.getFloat("oil_weight");
            this.acidity = rs.getFloat("acidity");
            this.costs = rs.getFloat("costs");
            if (rs.getInt("sold") == 1)
                this.sold = true;
            else
                this.sold = false;
            if (rs.getInt("is_bio") == 1)
                this.isBio = true;
            else
                this.isBio = false;
            this.soldPrice = rs.getFloat("sold_price");
        }
    }
    
    public static ArrayList<Production> getAllProductions() throws SQLException, ClassNotFoundException{
        DB db = DB.getInstance();
        ResultSet rs = DB.selectRows("production", "*", null, null, null);
        ArrayList res = new ArrayList<>();
        while(rs.next()){
            res.add(new Production(rs.getInt("id"), rs.getInt("producer_id"), 
                    rs.getInt("factory_id"), rs.getString("timestamp"), 
                    rs.getFloat("olives_weight"), rs.getFloat("oil_weight"),
                    rs.getFloat("acidity"), (rs.getInt("is_bio") == 1), rs.getFloat("costs"),
                    (rs.getInt("sold") == 1), rs.getFloat("sold_price")));
        }
        return res;
    }
    
    public static ArrayList<Production> getAllProductionsForUser(int userID) throws SQLException, ClassNotFoundException{
        ResultSet rs = DB.selectRows("production", "*", "`producer_id` = "+userID, null, null);
        ArrayList res = new ArrayList<>();
        while(rs.next()){
            res.add(new Production(rs.getInt("id"), rs.getInt("producer_id"), 
                    rs.getInt("factory_id"), rs.getString("timestamp"), 
                    rs.getFloat("olives_weight"), rs.getFloat("oil_weight"),
                    rs.getFloat("acidity"), (rs.getInt("is_bio") == 1), rs.getFloat("costs"),
                    (rs.getInt("sold") == 1), rs.getFloat("sold_price")));
        }
        return res;
    }
    
    public static ArrayList<HashMap> getProductionsInfoByFactory() throws ClassNotFoundException, SQLException {
        String sql = "SELECT `factory_id`, SUM(`olives_weight`) as `olives_weight`, "
                + "SUM(`oil_weight`) as `oil_weight`, ROUND(SUM(`sold_price`), 2) as `income`, "
                + "ROUND(AVG(`acidity`), 1) as `acidity`"
                + "FROM `production` GROUP BY `factory_id`";
        ResultSet rs = DB.execSelectSql(sql);
        ArrayList res = new ArrayList<>();
        while(rs.next()){
            res.add(new FactoryInfoHelper(rs.getInt("factory_id"), rs.getFloat("olives_weight"), 
                    rs.getFloat("oil_weight"), rs.getFloat("acidity"), rs.getFloat("income")));
        }
        return res;
    }
    
    public static ArrayList<HashMap> getProductionsInfoByUser() throws ClassNotFoundException, SQLException {
        String sql = "SELECT `producer_id`, SUM(`olives_weight`) as `olives_weight`, "
                + "SUM(`oil_weight`) as `oil_weight`, ROUND(SUM(`sold_price`), 2) as `income`, "
                + "ROUND(AVG(`acidity`), 1) as `acidity`"
                + "FROM `production` WHERE `sold` = 1 GROUP BY `producer_id`";
        System.out.println(sql);
        ResultSet rs = DB.execSelectSql(sql);
        ArrayList res = new ArrayList<>();
        while(rs.next()){
            res.add(new UserInfoHelper(rs.getInt("producer_id"), rs.getFloat("olives_weight"), 
                    rs.getFloat("oil_weight"), rs.getFloat("acidity"), rs.getFloat("income")));
        }
        return res;
    }
    
    public static float getTotalOlivesWeight(Boolean bio, Boolean sold) throws SQLException, ClassNotFoundException{
        String where = null;
        if (sold != null) {
            if (sold == true) where = "`sold` = 1";
            else where = "`sold` = 0";
        }
        if (bio != null) {
            if (where != null) where = where+" AND ";
            if (bio == true) where = where + "`is_bio` = 1";
            else where = where + "`is_bio` = 0";
        }
        ResultSet rs = DB.selectRows("production", "SUM(`olives_weight`) as `total_olives`", where, null, null);
        float total = 0;
        while(rs.next()){
            total = rs.getFloat("total_olives");
        }
        return total;
    }
    
    public static float getTotalOilWeight(Boolean bio, Boolean sold) throws SQLException, ClassNotFoundException{
        String where = null;
        if (sold != null) {
            if (sold == true) where = "`sold` = 1";
            else where = "`sold` = 0";
        }
        if (bio != null) {
            if (where != null) where = where+" AND ";
            if (bio == true) where = where + "`is_bio` = 1";
            else where = where + "`is_bio` = 0";
        }
        ResultSet rs = DB.selectRows("production", "SUM(`oil_weight`) as `total_oil`", where, null, null);
        float total = 0;
        while(rs.next()){
            total = rs.getFloat("total_oil");
        }
        return total;
    }
    
    public static float getAverageAcidity(Boolean bio, Boolean sold) throws SQLException, ClassNotFoundException{
        String where = null;
        if (sold != null) {
            if (sold == true) where = "`sold` = 1";
            else where = "`sold` = 0";
        }
        if (bio != null) {
            if (where != null) where = where+" AND ";
            if (bio == true) where = where + "`is_bio` = 1";
            else where = where + "`is_bio` = 0";
        }
        ResultSet rs = DB.selectRows("production", "ROUND(AVG(`acidity`), 2) as `acidity`", where, null, null);
        float avg = 0;
        while(rs.next()){
            avg = rs.getFloat("acidity");
        }
        return avg;
    }
    
    public static float getTotalIncome(Boolean bio, Boolean sold) throws SQLException, ClassNotFoundException{
        String where = null;
        if (sold != null) {
            if (sold == true) where = "`sold` = 1";
            else where = "`sold` = 0";
        }
        if (bio != null) {
            if (where != null) where = where+" AND ";
            if (bio == true) where = where + "`is_bio` = 1";
            else where = where + "`is_bio` = 0";
        }
        ResultSet rs = DB.selectRows("production", "SUM(`sold_price`) as `total_income`", where, null, null);
        float total = 0;
        while(rs.next()){
            total = rs.getFloat("total_income");
        }
        return total;
    }
    
    public float calculateSellingPrice() throws ClassNotFoundException, SQLException {
        float sold_price = 0;
        PricingOptions opts = new PricingOptions(true);
        if (this.getAcidity() < 1) {
            float diff = 1 - this.getAcidity();
            int decs = (int) (diff/0.1);
            float price = this.isBio() ? opts.getBioPrice() : opts.getNormalPrice();
            sold_price = ((price + decs*opts.getAcidityStep())*this.getOilWeight())-this.getCosts();
        } else if (this.getAcidity() > 1) {
            float diff = this.getAcidity() - 1;
            int decs = (int) (diff/0.1);
            float price = this.isBio() ? opts.getBioPrice() : opts.getNormalPrice();
            sold_price = ((price - decs*opts.getAcidityStep())*this.getOilWeight())-this.getCosts();
        } else {
            float diff = 1 - this.getAcidity();
            int decs = (int) (diff/0.1);
            float price = this.isBio() ? opts.getBioPrice() : opts.getNormalPrice();
            sold_price = price*this.getOilWeight()-this.getCosts();
        }
        return sold_price;
    }
    
    public void sell() throws ClassNotFoundException, SQLException {
        DB db = DB.getInstance();
        PricingOptions opts = new PricingOptions(true);
        float sold_price = this.calculateSellingPrice();
        
        DB.execUpdateSql("UPDATE `production` SET `sold` = 1, sold_price = "+sold_price+" WHERE `id` = "+this.getID());
        this.setSold(true);
    }
    
    public void unsell() throws ClassNotFoundException, SQLException {
        DB.execUpdateSql("UPDATE `production` SET `sold` = 0, sold_price = 0 WHERE `id` = "+this.getID());
        this.setSold(false);
    }
    
    public void delete() throws ClassNotFoundException, SQLException {
        DB db = DB.getInstance();
        DB.deleteRow("production", "`id` = "+this.ID);
    }
    
    public static void delete(int ID) throws ClassNotFoundException, SQLException {
        DB db = DB.getInstance();
        DB.deleteRow("production", "`id` = "+ID);
    }

    /**
     * @return the ID
     */
    public int getID() {
        return ID;
    }

    /**
     * @param ID the ID to set
     */
    public void setID(int ID) {
        this.ID = ID;
    }

    /**
     * @return the producerID
     */
    public int getProducerID() {
        return producerID;
    }

    /**
     * @param producerID the producerID to set
     */
    public void setProducerID(int producerID) {
        this.producerID = producerID;
    }

    /**
     * @return the factoryID
     */
    public int getFactoryID() {
        return factoryID;
    }

    /**
     * @param factoryID the factoryID to set
     */
    public void setFactoryID(int factoryID) {
        this.factoryID = factoryID;
    }

    /**
     * @return the timestamp
     */
    public String getTimestamp() {
        return timestamp;
    }

    /**
     * @param timestamp the timestamp to set
     */
    public void setTimestamp(String timestamp) {
        this.timestamp = timestamp;
    }

    /**
     * @return the olivesWeight
     */
    public float getOlivesWeight() {
        return olivesWeight;
    }

    /**
     * @param olivesWeight the olivesWeight to set
     */
    public void setOlivesWeight(float olivesWeight) {
        this.olivesWeight = olivesWeight;
    }

    /**
     * @return the oilWeight
     */
    public float getOilWeight() {
        return oilWeight;
    }

    /**
     * @param oilWeight the oilWeight to set
     */
    public void setOilWeight(float oilWeight) {
        this.oilWeight = oilWeight;
    }
    
    /**
     * @return the isBio
     */
    public boolean isBio() {
        return isBio;
    }

    /**
     * @param isBio the acidity to set
     */
    public void setBio(boolean isBio) {
        this.isBio = isBio;
    }

    /**
     * @return the acidity
     */
    public float getAcidity() {
        return acidity;
    }

    /**
     * @param acidity the acidity to set
     */
    public void setAcidity(float acidity) {
        this.acidity = acidity;
    }

    /**
     * @return the costs
     */
    public float getCosts() {
        return costs;
    }

    /**
     * @param costs the costs to set
     */
    public void setCosts(float costs) {
        this.costs = costs;
    }

    /**
     * @return the sold
     */
    public boolean isSold() {
        return sold;
    }

    /**
     * @param sold the sold to set
     */
    public void setSold(boolean sold) {
        this.sold = sold;
    }

    /**
     * @return the soldPrice
     */
    public float getSoldPrice() {
        return soldPrice;
    }

    /**
     * @param soldPrice the soldPrice to set
     */
    public void setSoldPrice(float soldPrice) {
        this.soldPrice = soldPrice;
    }
}
