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.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author Marcel
 */
public class TransportedPeopleDB {
    /**
    * select transported people stats
    * @throws SQLException
    * @return List of List, where in inner List is (in this order) company name, year, people count and people diff
    */
    static public List<List<String>> getStats() throws SQLException{
        PreparedStatement s = DbContext.getConnection().prepareStatement("SELECT name, year, count, "
                + "(count-lag(count,1,count-count) over(partition by name order by year)) as dif from "
                + "(SELECT name, extract(year from departure_time) as year, count(*) "
                + "from companies inner join flights on flights.company_id=companies.company_id "
                + "inner join bought_tickets on bought_tickets.flight_id=flights.flight_id "
                + "where canceled=false "
                + "group by name,extract(year from departure_time)) as t order by name, year");

        ResultSet rs = s.executeQuery();

        List<List<String>> res=new ArrayList<>();
        while(rs.next()){
            res.add(List.of(rs.getString("name"), rs.getString("year"), rs.getString("count"), rs.getString("dif")));
        }
        return res;
    }
}
