package main.java.rdg;

import main.java.DbConnection;
import main.java.RevenueObj;

import java.sql.*;
import java.sql.Date;
import java.util.*;

public class Voucher {
    private long id;
    private Long customer;
    private String code;
    private Date validUntil;
    private boolean used;
    private double discountValue;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public Long getCustomer() {
        return customer;
    }

    public void setCustomer(Long customer) {
        this.customer = customer;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public Date getValidUntil() {
        return validUntil;
    }

    public void setValidUntil(Date validUntil) {
        this.validUntil = validUntil;
    }

    public boolean isUsed() {
        return used;
    }

    public void setUsed(boolean used) {
        this.used = used;
    }

    public double getDiscountValue() {
        return discountValue;
    }

    public void setDiscountValue(double discountValue) {
        this.discountValue = discountValue;
    }

    @Override
    public String toString() {
        return "Voucher{" +
                "id=" + id +
                ", customer=" + customer +
                ", code='" + code + '\'' +
                ", validUntil=" + validUntil +
                ", used=" + used +
                ", discountValue=" + discountValue +
                '}';
    }

    public boolean create () {
        try (PreparedStatement s = DbConnection.getCon().prepareStatement("INSERT INTO vouchers (customer_id, code, validuntil, used, discountvalue) VALUES (?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)){
            s.setLong(1, customer);
            s.setString(2, code);
            s.setDate(3, validUntil);
            s.setBoolean(4, false);
            s.setDouble(5, discountValue);

            s.executeUpdate();

            try (ResultSet r = s.getGeneratedKeys()){
                r.next();
                this.id=r.getLong(1);
            }

        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public static Voucher read (long id) {
        try (PreparedStatement s = DbConnection.getCon().prepareStatement("SELECT * FROM vouchers WHERE voucher_id=?")){
            s.setLong(1, id);
            Voucher voucher = new Voucher();

            try (ResultSet r = s.executeQuery()) {
                r.next();
                voucher.setId(r.getLong(1));
                voucher.setCustomer(r.getLong(2));
                voucher.setCode(r.getString(3));
                voucher.setValidUntil(r.getDate(4));
                voucher.setUsed(r.getBoolean(5));
                voucher.setDiscountValue(r.getDouble(6));
            }

            return voucher;

        } catch (SQLException e) {
            return null;
        }
    }

    public static Set<Voucher> findByCode (String code) {
        try (PreparedStatement s = DbConnection.getCon().prepareStatement("SELECT * FROM vouchers WHERE code=?")){
            s.setString(1, code);

            Set<Voucher> vouchers = new HashSet<>();

            try (ResultSet r = s.executeQuery()) {
                while (r.next()){
                    Voucher voucher = new Voucher();
                    voucher.setId(r.getLong(1));
                    voucher.setCustomer(r.getLong(2));
                    voucher.setCode(r.getString(3));
                    voucher.setValidUntil(r.getDate(4));
                    voucher.setUsed(r.getBoolean(5));
                    voucher.setDiscountValue(r.getDouble(6));

                    vouchers.add(voucher);
                }
            }

            return vouchers;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static Set<Voucher> readByCustomer (long customerID){
        try (PreparedStatement s = DbConnection.getCon().prepareStatement("SELECT * FROM vouchers WHERE customer_id=?")){
            s.setLong(1, customerID);

            try (ResultSet r = s.executeQuery()) {
                Set<Voucher> vouchers = new HashSet<>();

                while (r.next()) {
                    Voucher voucher = new Voucher();

                    voucher.setId(r.getLong(1));
                    voucher.setCustomer(r.getLong(2));
                    voucher.setCode(r.getString(3));
                    voucher.setValidUntil(r.getDate(4));
                    voucher.setUsed(r.getBoolean(5));
                    voucher.setDiscountValue(r.getDouble(6));

                    vouchers.add(voucher);
                }
                return vouchers;
            }

        } catch (SQLException e) {
            return null;
        }
    }

    public boolean update () {
        try (PreparedStatement s = DbConnection.getCon().prepareStatement("UPDATE vouchers SET used=? WHERE voucher_id=?")) {
            s.setBoolean(1, used);
            s.setLong(2, id);

            s.executeUpdate();

        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public boolean delete () {
        try (PreparedStatement ss = DbConnection.getCon().prepareStatement("DELETE FROM vouchers WHERE voucher_id=?")){

            ss.setLong(1, id);
            ss.executeUpdate();

        } catch (SQLException e) {
            return false;
        }

        return true;
    }

    public static class Statistics {
        public static double monthRevenue (int month, int year) {
            GregorianCalendar gcalFrom = new GregorianCalendar(year, month - 1, 1);
            Timestamp from = new Timestamp(gcalFrom.getTimeInMillis());

            GregorianCalendar gcalTo = new GregorianCalendar(year, month - 1, gcalFrom.getActualMaximum(Calendar.DAY_OF_MONTH), 23, 59, 59);
            Timestamp to = new Timestamp(gcalTo.getTimeInMillis());

            try (PreparedStatement s = DbConnection.getCon().prepareStatement("SELECT SUM(finalprice) FROM tickets WHERE state='SOLD' AND screening_id in (" +
                                              "SELECT screening_id FROM screenings WHERE ? <= screeningday AND screeningday <= ?)")){
                s.setTimestamp(1, from);
                s.setTimestamp(2, to);
                try (ResultSet r = s.executeQuery()){
                    r.next();
                    return r.getDouble(1);
                }
            } catch (SQLException e) {
                return 0;
            }
        }

        public static List<RevenueObj> monthGenreRevenue () {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.MONTH, -1);
            cal.set(Calendar.DAY_OF_MONTH, 1);
            Timestamp first = new Timestamp(cal.getTimeInMillis());
            cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DATE));
            Timestamp last = new Timestamp(cal.getTimeInMillis());

            try (PreparedStatement s = DbConnection.getCon().prepareStatement("" +
                    "SELECT name, SUM(finalprice) FROM (SELECT g.name, s.screeningday as date, t.finalprice, t.state FROM genres g" +
                    "        join movie_genres mg on g.genre_id = mg.genre_id" +
                    "        join movies m on mg.movie_id = m.movie_id" +
                    "        join screenings s on m.movie_id = s.movie_id" +
                    "        join tickets t on s.screening_id = t.screening_id) as subSEL" +
                    "    WHERE state='SOLD' AND ? <= date AND ? >= date" +
                    "    group by name")){

                s.setTimestamp(1, first);
                s.setTimestamp(2, last);

                List<RevenueObj> res = new ArrayList<>();

                try (ResultSet r = s.executeQuery()){
                    while(r.next()){
                        RevenueObj gr = new RevenueObj(r.getString(1),
                                r.getDouble(2));

                        res.add(gr);
                    }
                }

                return res;
            } catch (SQLException e) {
                return null;
            }
        }

        public static List<RevenueObj> topMovieRevenueByGenre (int month, int year, String genre) {
            GregorianCalendar gcalFrom = new GregorianCalendar(year, month - 1, 1);
            Timestamp from = new Timestamp(gcalFrom.getTimeInMillis());

            GregorianCalendar gcalTo = new GregorianCalendar(year, month - 1, gcalFrom.getActualMaximum(Calendar.DAY_OF_MONTH), 23, 59, 59);
            Timestamp to = new Timestamp(gcalTo.getTimeInMillis());

            try (PreparedStatement s = DbConnection.getCon().prepareStatement("" +
                    "SELECT name, SUM(finalprice) as sum FROM (\n" +
                    "    SELECT m.name, s.screeningday, t.finalprice FROM genres g\n" +
                    "         join movie_genres mg on g.genre_id = mg.genre_id\n" +
                    "         join movies m on m.movie_id = mg.movie_id\n" +
                    "         join screenings s on m.movie_id = s.movie_id\n" +
                    "         join tickets t on s.screening_id = t.screening_id\n" +
                    "    WHERE state='SOLD' AND g.name=? AND ? <= screeningday AND ? >= screeningday ) as subslct\n" +
                    "GROUP BY name " +
                    "ORDER BY sum DESC LIMIT 3")) {

                s.setString(1, genre);
                s.setTimestamp(2, from);
                s.setTimestamp(3, to);

                List<RevenueObj> revenues = new ArrayList<>();

                try (ResultSet r = s.executeQuery()){
                    while (r.next()) {
                        RevenueObj rev = new RevenueObj(r.getString(1), r.getDouble(2));
                        revenues.add(rev);
                    }
                }

                return revenues;

            } catch (SQLException e){
                return null;
            }
        }

    }
}
