package RDG;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author Marcel
 */
public class FractionOfMarketDB {
    /**
    * Select stats from Timestamp from to Timestamp to between city_from to city_to
    * @param from Timestamp from select stats
    * @param to Timestamp to select stats
    * @param city_from departure city_id
    * @param city_to arrival city_id
    * @return Map of string where key is name of company and value percent of market
    * @throws SQLException
    */
    static public Map<String, String> selectStats(Timestamp from, Timestamp to, Integer city_from, Integer city_to) throws SQLException{
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT name, "
                + "count(case when flights.departure_time>? AND flights.departure_time<? AND d.city_id=? AND a.city_id=? then 1 else null end)/"
                + "(SELECT count(case when flights.departure_time>? AND flights.departure_time<? AND d.city_id=? AND a.city_id=? then 1 else null end) "
                + "FROM flights INNER JOIN bought_tickets ON canceled=false and flights.flight_id=bought_tickets.flight_id INNER JOIN airports d ON flights.departure_airport_id=d.airport_id "
                + "INNER JOIN airports a ON flights.arrival_airport_id=a.airport_id)::float*100 as percent "
                + "FROM flights INNER JOIN bought_tickets ON canceled=false and flights.flight_id=bought_tickets.flight_id "
                + "INNER JOIN companies ON companies.company_id=flights.company_id INNER JOIN airports d ON flights.departure_airport_id=d.airport_id "
                + "INNER JOIN airports a ON flights.arrival_airport_id=a.airport_id GROUP BY companies.company_id");
        
        s.setTimestamp(1, from);
        s.setTimestamp(2, to);
        s.setInt(3, city_from);
        s.setInt(4, city_to);
        s.setTimestamp(5, from);
        s.setTimestamp(6, to);
        s.setInt(7, city_from);
        s.setInt(8, city_to);
        ResultSet rs= null;
        try{
            rs = s.executeQuery();
        }
        catch(Exception e){
            if(e.getMessage().equals("ERROR: division by zero"));
                return null;
        }
        Map<String, String> res = new HashMap<>();
        while(rs.next()){
            res.put(rs.getString("name"), rs.getString("percent"));
        }
        return res;
    }
}
