package com.itstep.retailer.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.itstep.retailer.beans.Product;
import com.itstep.retailer.beans.Role;
import com.itstep.retailer.beans.UserAccount;

public class DBUtils {

	/**
	 * GENERAL_USER Role by Default
	 * @param conn
	 * @param userAccount
	 * @return
	 */
	public static boolean saveUserAccount(Connection conn, UserAccount userAccount){


		String sql = "INSERT INTO `USER_ACCOUNT`"
				+ "(`USER_NAME`, `GENDER`, `PASSWORD`, "
				+ "`ROLE_ID`, `EMAIL`, `ACTIVE`, `LAST_UPDATED_TS`, `CREATED_TS`)"
				+ " VALUES (?, ?, ?, ?, ?, 'N', ?, CURRENT_TIMESTAMP)";
		int result = 0;
		try {
			PreparedStatement pstms = conn.prepareStatement(sql);
			pstms.setString(1, userAccount.getUserName());
			pstms.setString(2, userAccount.getGender());
			pstms.setString(3, userAccount.getPassword()); // MUST BE ALREADY ENCRYPTED
			pstms.setInt(4, Role.GENERAL_USER.getId());//userAccount.getRole().getId()
			pstms.setString(5, userAccount.getEmail());
			pstms.setTimestamp(6, new Timestamp(new Date().getTime())); // GET Timestamp from JAVA
			result = pstms.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result==1;
	}




    public static UserAccount findUser(Connection conn, String userName, String password) throws SQLException {




        String sql = "Select a.User_Name, a.Password, a.Gender ,"
        		+ "a.ROLE_ID "
        		+ ", a.EMAIL, a.LAST_UPDATED_TS , "
        		+ "a.CREATED_TS, a.active  from User_Account a "
        		+ "where a.User_Name = ? and a.password= ?";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setString(1, userName);
        pstm.setString(2, password);
        ResultSet rs = pstm.executeQuery();

        if (rs.next()) {
            String gender = rs.getString("Gender");
            Role role = Role.getRole(rs.getInt("ROLE_ID"));
            String email = rs.getString("EMAIL");
            char active = rs.getString("ACTIVE").toCharArray()[0];
            Timestamp last_ts = rs.getTimestamp("LAST_UPDATED_TS");
            Timestamp created_ts = rs.getTimestamp("CREATED_TS");

            UserAccount user = new UserAccount();
            user.setUserName(userName);
            user.setPassword(password);
            user.setGender(gender);
            user.setRole(role);
            user.setEmail(email);
            user.setLastUpdatedTs(last_ts);
            user.setCreatedTs(created_ts);
            user.setActive(active);


            return user;
        }
        return null;
    }

    public static UserAccount findUser(Connection conn, String userName) throws SQLException {

        String sql = "Select a.User_Name, a.Password, a.Gender, a.ROLE_ID, a.EMAIL, a.LAST_UPDATED_TS, a.CREATED_TS, a.active from User_Account a "//
                + " where a.User_Name = ? ";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setString(1, userName);

        ResultSet rs = pstm.executeQuery();

        if (rs.next()) {
            String password = rs.getString("Password");
            String gender = rs.getString("Gender");
            Role role = Role.getRole(rs.getInt("ROLE_ID"));
            String email = rs.getString("EMAIL");
            char active = rs.getString("ACTIVE").toCharArray()[0];
            Timestamp last_ts = rs.getTimestamp("LAST_UPDATED_TS");
            Timestamp created_ts = rs.getTimestamp("CREATED_TS");

            UserAccount user = new UserAccount();
            user.setUserName(userName);
            user.setPassword(password);
            user.setGender(gender);
            user.setRole(role);
            user.setEmail(email);
            user.setLastUpdatedTs(last_ts);
            user.setCreatedTs(created_ts);
            user.setActive(active);
            return user;
        }
        return null;
    }

    public static List<Product> queryProduct(Connection conn) throws SQLException {
        String sql = "Select "
        		+ "a.productCode, a.productName, a.productLine, a.productScale, a.productVendor, a.productDescription, a.quantityInStock, a.buyPrice, a.MSRP "
        		+ "from Products a ";

        PreparedStatement pstm = conn.prepareStatement(sql);

        ResultSet rs = pstm.executeQuery();
        List<Product> list = new ArrayList<Product>();
        while (rs.next()) {
            Product product = new Product();
            product.setProductCode(rs.getString("productCode"));
            product.setProductName(rs.getString("productName"));
            product.setProductLine(rs.getString("productLine"));
            product.setProductScale(rs.getString("productScale"));
            product.setProductVendor(rs.getString("productVendor"));
            product.setProductDescription(rs.getString("productDescription"));
            product.setQuantityInStock(rs.getInt("quantityInStock"));
            product.setBuyPrice(rs.getDouble("quantityInStock"));
            product.setMSRP(rs.getDouble("MSRP"));
            list.add(product);
        }
        return list;
    }

    public static Product findProduct(Connection conn, String productCode) throws SQLException {
        String sql = "Select "
        		+ "a.productCode, a.productName, a.productLine, a.productScale, a.productVendor, a.productDescription, a.quantityInStock, a.buyPrice, a.MSRP "
        		+ "from Products a where a.productCode = ?";

        PreparedStatement pstm = conn.prepareStatement(sql);
        pstm.setString(1, productCode);

        ResultSet rs = pstm.executeQuery();

        while (rs.next()) {
            Product product = new Product(
            		rs.getString("productCode"),
            		rs.getString("productName"),
            		rs.getString("productLine"),
            		rs.getString("productScale"),
            		rs.getString("productVendor"),
            		rs.getString("productDescription"),
            		rs.getInt("quantityInStock"),
            		rs.getDouble("buyPrice"),
            		rs.getDouble("MSRP"));
            return product;
        }
        return null;
    }

    public static void updateProduct(Connection conn, Product product) throws SQLException {
        String sql = "Update Products set "
        		+ "productName=?, productLine=?, productScale=?, productVendor=?, productDescription=?, quantityInStock=?, buyPrice=?, MSRP=? "
        		+ "where productCode=? ";

        PreparedStatement pstm = conn.prepareStatement(sql);

        pstm.setString(1, product.getProductName());
        pstm.setString(2, product.getProductLine());
        pstm.setString(3, product.getProductScale());
        pstm.setString(4, product.getProductVendor());
        pstm.setString(5, product.getProductDescription());

        pstm.setInt(6, product.getQuantityInStock());
        pstm.setDouble(7, product.getBuyPrice());
        pstm.setDouble(8, product.getMSRP());

        pstm.setString(9, product.getProductCode());
        pstm.executeUpdate();
    }

    public static void insertProduct(Connection conn, Product product) throws SQLException {
        String sql = "Insert into Product(productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice,MSRP) "
        		+ "values (?,?,?,?,?,?,?,?,?)";

        PreparedStatement pstm = conn.prepareStatement(sql);

        pstm.setString(1, product.getProductName());
        pstm.setString(2, product.getProductLine());
        pstm.setString(3, product.getProductScale());
        pstm.setString(4, product.getProductVendor());
        pstm.setString(5, product.getProductDescription());

        pstm.setInt(6, product.getQuantityInStock());
        pstm.setDouble(7, product.getBuyPrice());
        pstm.setDouble(8, product.getMSRP());

        pstm.setString(9, product.getProductCode());

        pstm.executeUpdate();
    }

    public static void deleteProduct(Connection conn, String code) throws SQLException {
        String sql = "Delete From Product where Code= ?";

        PreparedStatement pstm = conn.prepareStatement(sql);

        pstm.setString(1, code);

        pstm.executeUpdate();
    }

}