package database.crud.denormalised;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import database.Crud;
import database.dataset.Facture;
import database.dataview.view_facture;
import database.dataview.view_groupe;
import database.dataview.view_reservation;
import database.dataview.view_telephone;
import database.dataview.view_vente;
import database.tools.DbConnection;

public class ViewVenteCrud implements Crud<view_vente> {
	private Connection connection;
	private Statement statement;

	/**
	 * @param connection
	 * @throws SQLException 
	 */
	public ViewVenteCrud(Connection connection) throws SQLException {
		super();
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(view_vente dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public view_vente readById(int id) throws SQLException {
		String query = "SELECT * FROM view_vente WHERE vente_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new view_vente(rs.getInt("vente_p_id"), 
							  rs.getDate("vente_jour"), 
							  rs.getTime("vente_heure"), 
							  rs.getBigDecimal("vente_prix"), 
							  rs.getInt("facture_fk_id"), 
							  rs.getInt("article_p_id"), 
							  rs.getString("article_code"), 
							  rs.getString("article_description"), 
							  rs.getBoolean("article_taxable"), 
							  rs.getBoolean("article_actif"), 
							  rs.getString("categorie_article_description"), 
							  rs.getInt("reservation_fk_id"), 
							  rs.getInt("quantite"), 
							  rs.getBigDecimal("taxe_tps"), 
							  rs.getBigDecimal("taxe_tvq"), 
							  rs.getBigDecimal("herbergement"));
	}

	@Override
	public List<view_vente> readAll() throws SQLException {
		List<view_vente> lst = new ArrayList<>();
		
		String query = "SELECT * FROM view_vente LIMIT 0, 100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		    //Construire le dataset � partir du resultset
			view_vente tmp = new view_vente(rs.getInt("vente_p_id"), 
					  rs.getDate("vente_jour"), 
					  rs.getTime("vente_heure"), 
					  rs.getBigDecimal("vente_prix"), 
					  rs.getInt("facture_fk_id"), 
					  rs.getInt("article_p_id"), 
					  rs.getString("article_code"), 
					  rs.getString("article_description"), 
					  rs.getBoolean("article_taxable"), 
					  rs.getBoolean("article_actif"), 
					  rs.getString("categorie_article_description"), 
					  rs.getInt("reservation_fk_id"), 
					  rs.getInt("quantite"), 
					  rs.getBigDecimal("taxe_tps"), 
					  rs.getBigDecimal("taxe_tvq"), 
					  rs.getBigDecimal("herbergement"));
			lst.add(tmp);
		}
		
		return lst;
	}

	@Override
	public boolean update(view_vente dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public boolean destroy(view_vente dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		throw new UnsupportedOperationException();
	}
	
	public Map<String,BigDecimal> getSumVenteFromFacture(int facture_p_id) throws SQLException{
		String query = "SELECT SUM(soustotal) AS soustotal, "+
	                          "SUM(taxe_tps*soustotal) AS tps, "+
				              "SUM(taxe_tvq*soustotal) AS tvq, "+
	                          "SUM(herbergement) AS hebergement "+
				       "FROM( SELECT facture_fk_id, "+
	                                "vente_prix*quantite AS soustotal, "+
				                    "taxe_tps, "+
	                                "taxe_tvq, "+
				                    "herbergement "+
	                         "FROM view_vente "+
                             "WHERE facture_fk_id='"+facture_p_id+"' "+
                       ") AS ventes GROUP BY facture_fk_id";
		System.out.println(query);
		
		Map<String,BigDecimal> dataset = new HashMap<>();
		ResultSet rs = statement.executeQuery(query);
		if (rs.next()){
			dataset.put("soustotal", rs.getBigDecimal("soustotal"));
			dataset.put("tps", rs.getBigDecimal("tps"));
			dataset.put("tvq", rs.getBigDecimal("tvq"));
			dataset.put("hebergement", rs.getBigDecimal("hebergement"));
		}else{
			dataset.put("soustotal", new BigDecimal("0"));
			dataset.put("tps", new BigDecimal("0"));
			dataset.put("tvq", new BigDecimal("0"));
			dataset.put("hebergement", new BigDecimal("0"));
		}
		return dataset;
	}
	
	public static void main(String ... arg) throws ClassNotFoundException, IOException, SQLException{
		Connection connection = DbConnection.createConnection();
		ViewVenteCrud crud = new ViewVenteCrud(connection);
		for (view_vente v : crud.readAll())
			System.out.println(v.toString());
	}
}