package com.reallinkbilling.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.db.GetDBConnection;

public class LabelCapSchema {

	public static void ensure(SessionMap<String, Object> sessionMap) throws SQLException, IOException {
		Connection connection = null;
		try {
			String db = (String) sessionMap.get("db");
			connection = GetDBConnection.getDBConnection(db);
			ensure(connection);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public static void ensure(Connection connection) throws SQLException {
		Statement statement = null;
		try {
			statement = connection.createStatement();
			statement.execute("CREATE TABLE IF NOT EXISTS shop_label_master ("
					+ "label_id INT NOT NULL,"
					+ "shop_id VARCHAR(50) NOT NULL,"
					+ "shop_code VARCHAR(50) NOT NULL,"
					+ "label_name VARCHAR(200) NOT NULL,"
					+ "label_details VARCHAR(500),"
					+ "label_code VARCHAR(50) NOT NULL,"
					+ "customer_id INT NOT NULL,"
					+ "customer_name VARCHAR(200),"
					+ "add_date DATE,"
					+ "entry_by VARCHAR(100),"
					+ "PRIMARY KEY (shop_id, shop_code, label_id),"
					+ "UNIQUE KEY uk_label_code (shop_id, shop_code, label_code)"
					+ ")");
			statement.execute("CREATE TABLE IF NOT EXISTS shop_cap_master ("
					+ "cap_id INT NOT NULL,"
					+ "shop_id VARCHAR(50) NOT NULL,"
					+ "shop_code VARCHAR(50) NOT NULL,"
					+ "cap_name VARCHAR(200) NOT NULL,"
					+ "cap_details VARCHAR(500),"
					+ "cap_code VARCHAR(50) NOT NULL,"
					+ "customer_id INT NOT NULL,"
					+ "customer_name VARCHAR(200),"
					+ "add_date DATE,"
					+ "entry_by VARCHAR(100),"
					+ "PRIMARY KEY (shop_id, shop_code, cap_id),"
					+ "UNIQUE KEY uk_cap_code (shop_id, shop_code, cap_code)"
					+ ")");
			statement.execute("CREATE TABLE IF NOT EXISTS shop_product_label_cap_map ("
					+ "map_id INT NOT NULL,"
					+ "shop_id VARCHAR(50) NOT NULL,"
					+ "shop_code VARCHAR(50) NOT NULL,"
					+ "finished_product_id INT NOT NULL,"
					+ "label_id INT NOT NULL,"
					+ "cap_id INT NOT NULL,"
					+ "product_id INT NOT NULL,"
					+ "product_name VARCHAR(250),"
					+ "product_short_code VARCHAR(50),"
					+ "add_date DATE,"
					+ "PRIMARY KEY (shop_id, shop_code, map_id),"
					+ "UNIQUE KEY uk_finished_label_cap (shop_id, shop_code, finished_product_id, label_id, cap_id)"
					+ ")");
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}

		addColumnIfMissing(connection, "shop_production_details_master", "label_id", "INT NULL");
		addColumnIfMissing(connection, "shop_production_details_master", "cap_id", "INT NULL");
		addColumnIfMissing(connection, "shop_production_details_master", "product_id", "INT NULL");
	}

	private static void addColumnIfMissing(Connection connection, String tableName, String columnName, String definition)
			throws SQLException {
		if (columnExists(connection, tableName, columnName)) {
			return;
		}
		Statement statement = null;
		try {
			statement = connection.createStatement();
			statement.execute("ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " " + definition);
		} catch (SQLException e) {
			if (e.getErrorCode() == 1060 || (e.getMessage() != null && e.getMessage().toLowerCase().contains("duplicate"))) {
				return;
			}
			throw e;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	private static boolean columnExists(Connection connection, String tableName, String columnName) {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(
					"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?");
			preparedStatement.setString(1, tableName);
			preparedStatement.setString(2, columnName);
			resultSet = preparedStatement.executeQuery();
			return resultSet.next() && resultSet.getInt(1) > 0;
		} catch (SQLException e) {
			return false;
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
		}
	}
}
