package com.db2019.dbshowcase.api;

import com.db2019.dbshowcase.exception.FetchDataException;
import com.db2019.dbshowcase.exception.SaveDataException;
import com.db2019.dbshowcase.model.*;
import com.db2019.dbshowcase.util.DBPropertyLoader;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataHandlerImpl implements DataHandler {
    private static final DataHandler _instance = new DataHandlerImpl();
    private final String url;
    private final String username;
    private final String password;

    private DataHandlerImpl() {
        url = DBPropertyLoader.getProperty("url");
        username = DBPropertyLoader.getProperty("username");
        password = DBPropertyLoader.getProperty("password");
    }

    public static DataHandler getInstance() {
        return _instance;
    }

    @Override
    public List<Customer> getAllCustomers() throws FetchDataException {
        List<Customer> customers = new ArrayList<>();

        try {
            Connection con = DriverManager.getConnection(url, username, password);

            Statement st = con.createStatement();
            String sql = ("SELECT * FROM customer");
            ResultSet rs = st.executeQuery(sql);

            while (rs.next()) {
                Customer tmp = new Customer();
                int id = rs.getInt("customer_id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");

                tmp.setCustomerId(id);
                tmp.setFirstName(firstName);
                tmp.setLastName(lastName);

                customers.add(tmp);
            }

            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return customers;
    }

    @Override
    public List<Product> getAllProducts() throws FetchDataException {
        List<Product> products = new ArrayList<>();

        try {
            Connection con = DriverManager.getConnection(url, username, password);

            Statement st = con.createStatement();
            String sql = ("SELECT * FROM customer");
            ResultSet rs = st.executeQuery(sql);

            while (rs.next()) {
                Product tmp = new Product();
                int id = rs.getInt("product_id");
                String productName = rs.getString("product_name");
                Double price = rs.getDouble("price");

                tmp.setProductId(id);
                tmp.setProductName(productName);
                tmp.setPrice(price);

                products.add(tmp);
            }

            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return products;
    }

    @Override
    public List<Order> getAllOrders() throws FetchDataException {
        return null;
    }

    @Override
    public boolean addOrder(Customer c, Product p, Date date) throws SaveDataException {
        return false;
    }
}