/*
 *  Copyright (c) 2005-2011, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 *  WSO2 Inc. licenses this file to you under the Apache License,
 *  Version 2.0 (the "License"); you may not use this file except
 *  in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 *
 */
package org.wso2.carbon.dataservices.sql.driver;

import com.google.common.io.ByteStreams;
import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import org.apache.commons.codec.binary.Base64;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.wso2.carbon.context.PrivilegedCarbonContext;
import org.wso2.carbon.dataservices.sql.driver.internal.SQLDriverDSComponent;
import org.wso2.carbon.dataservices.sql.driver.parser.Constants;
import org.wso2.carbon.dataservices.sql.driver.processor.reader.DataTable;
import org.wso2.carbon.dataservices.sql.driver.query.ColumnInfo;
import org.wso2.carbon.dataservices.sql.driver.query.ParamInfo;
import org.wso2.carbon.dataservices.sql.driver.query.QueryFactory;
import org.wso2.carbon.dataservices.sql.driver.util.WorkBookOutputWriter;
import org.wso2.carbon.registry.core.Registry;
import org.wso2.carbon.registry.core.Resource;
import org.wso2.carbon.registry.core.exceptions.RegistryException;
import org.wso2.carbon.registry.core.service.RegistryService;
import org.wso2.carbon.utils.multitenancy.MultitenantConstants;

import java.io.*;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class TDriverUtil {

    private static List<String> driverProperties = new ArrayList<String>();
    public static final java.lang.String GOV_REGISTRY_PATH_PREFIX = "gov:";
    public static final java.lang.String CONF_REGISTRY_PATH_PREFIX = "conf:";

    static {
        driverProperties.add(Constants.DRIVER_PROPERTIES.FILE_PATH);
        driverProperties.add(Constants.DRIVER_PROPERTIES.SHEET_NAME);
        driverProperties.add(Constants.DRIVER_PROPERTIES.VISIBILITY);
        driverProperties.add(Constants.DRIVER_PROPERTIES.HAS_HEADER);
        driverProperties.add(Constants.DRIVER_PROPERTIES.USER);
        driverProperties.add(Constants.DRIVER_PROPERTIES.PASSWORD);
        driverProperties.add(Constants.DRIVER_PROPERTIES.DATA_SOURCE_TYPE);
        driverProperties.add(Constants.DRIVER_PROPERTIES.MAX_COLUMNS);
        driverProperties.add(Constants.GSPREAD_PROPERTIES.CLIENT_ID);
        driverProperties.add(Constants.GSPREAD_PROPERTIES.CLIENT_SECRET);
        driverProperties.add(Constants.GSPREAD_PROPERTIES.REFRESH_TOKEN);
    }

    public static List<String> getAvailableDriverProperties() {
        return driverProperties;
    }

    public static ColumnInfo[] getHeaders(Connection connection,
                                          String tableName) throws SQLException {
        if (!(connection instanceof TConnection)) {
            throw new SQLException("Invalid connection type");
        }
        String connectionType = ((TConnection) connection).getType();
        QueryFactory.QueryTypes type =
                QueryFactory.QueryTypes.valueOf(connectionType.toUpperCase());
        switch (type) {
            case EXCEL:
                return getExcelHeaders(connection, tableName);
            case GSPREAD:
                return getGSpreadHeaders(connection, tableName);
            case CUSTOM:
                return getCustomHeaders(connection, tableName);
            default:
                throw new SQLException("Invalid query type: " + type);
        }
    }

    private static ColumnInfo[] getExcelHeaders(Connection connection,
                                                String tableName) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
        if (!(connection instanceof TExcelConnection)) {
            throw new SQLException("Invalid connection type");
        }
        Workbook workbook = ((TExcelConnection) connection).getWorkbook();
        Sheet sheet = workbook.getSheet(tableName);
        if (sheet == null) {
            throw new SQLException("Sheet '" + tableName + "' does not exist");
        }
        Iterator<Cell> cellItr = sheet.getRow(0).cellIterator();
        while (cellItr.hasNext()) {
            Cell header = cellItr.next();
            ColumnInfo column = new ColumnInfo(header.getStringCellValue());
            column.setTableName(tableName);
            column.setSqlType(header.getCellType());
            column.setId(header.getColumnIndex());

            columns.add(column);
        }
        return columns.toArray(new ColumnInfo[columns.size()]);
    }

    private static ColumnInfo[] getGSpreadHeaders(Connection connection,
                                                  String sheetName) throws SQLException {
        WorksheetEntry currentWorksheet;
        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();

        if (!(connection instanceof TGSpreadConnection)) {
            throw new SQLException("Invalid connection type");
        }
        currentWorksheet = getCurrentWorkSheetEntry((TGSpreadConnection) connection, sheetName);
        if (currentWorksheet == null) {
            throw new SQLException("Worksheet '" + sheetName + "' does not exist");
        }
        CellFeed cellFeed = getGSpreadCellFeed((TGSpreadConnection) connection, currentWorksheet);
        for (CellEntry cell : cellFeed.getEntries()) {
            if (!getCellPosition(cell.getId()).startsWith("R1")) {
                break;
            }
            ColumnInfo column =
                    new ColumnInfo(cell.getTextContent().getContent().getPlainText());
            column.setTableName(sheetName);
            column.setSqlType(cell.getContent().getType());
            column.setId(getColumnIndex(cell.getId()) - 1);
            columns.add(column);
        }
        return columns.toArray(new ColumnInfo[columns.size()]);
    }

    public static WorksheetEntry getCurrentWorkSheetEntry(TGSpreadConnection connection,
                                                          String sheetName) throws SQLException {
        SpreadsheetEntry spreadsheetEntry = connection.getSpreadSheetFeed().getEntries().get(0);
        WorksheetQuery worksheetQuery =
                TDriverUtil.createWorkSheetQuery(spreadsheetEntry.getWorksheetFeedUrl());
        WorksheetFeed worksheetFeed = connection.getFeedProcessor().getFeed(worksheetQuery,
                                                                            WorksheetFeed.class);
        for (WorksheetEntry entry : worksheetFeed.getEntries()) {
            if (sheetName.equals(entry.getTitle().getPlainText())) {
                return entry;
            }
        }
        return null;
    }

    public static CellFeed getGSpreadCellFeed(TGSpreadConnection connection,
                                       WorksheetEntry currentWorkSheet) throws SQLException {
        CellQuery cellQuery = new CellQuery(currentWorkSheet.getCellFeedUrl());
        return connection.getFeedProcessor().getFeed(cellQuery, CellFeed.class);
    }

    public static ListFeed getListFeed(TGSpreadConnection connection,
                                       WorksheetEntry currentWorkSheet) throws SQLException {
        ListQuery listQuery = new ListQuery(currentWorkSheet.getListFeedUrl());
        return connection.getFeedProcessor().getFeed(listQuery, ListFeed.class);
    }

    private static ColumnInfo[] getCustomHeaders(Connection connection,
            String sheetName) throws SQLException {
    	DataTable table = ((TCustomConnection) connection).getDataSource().getDataTable(sheetName);
    	return table.getHeaders();
    }

    public static int getColumnIndex(String id) {
        String tmp = getCellPosition(id);
        id = tmp.substring(tmp.indexOf("C"), tmp.length()).substring(1);
        return Integer.parseInt(id);
    }

    public static int getRowIndex(String id) {
        String tmp = getCellPosition(id);
        id = tmp.substring(tmp.indexOf("R") + 1, tmp.indexOf("C"));
        return Integer.parseInt(id);
    }

    public static String getCellPosition(String id) {
        return id.substring(id.lastIndexOf("/") + 1);
    }

    public static SpreadsheetQuery createSpreadSheetQuery(String spreadSheetName,
                                                          URL spreadSheetFeedUrl) {
        SpreadsheetQuery spreadsheetQuery = new SpreadsheetQuery(spreadSheetFeedUrl);
        spreadsheetQuery.setTitleQuery(spreadSheetName);
        spreadsheetQuery.setTitleExact(true);
        return spreadsheetQuery;
    }
    
    public static WorksheetQuery createWorkSheetQuery(URL workSheetFeedUrl) {
        return new WorksheetQuery(workSheetFeedUrl);
    }

    public static ParamInfo findParam(ColumnInfo columnInfo, ParamInfo[] params) {
        ParamInfo param = null;
        for (ParamInfo tmpParam : params) {
            if (columnInfo.getName().equals(tmpParam.getName())) {
                param = tmpParam;
                break;
            }
        }
        return param;
    }

    /**
     * Method to write excel data to registry or file output streams.
     *
     * @param workbook
     * @param filePath
     * @throws SQLException
     */
    public static void writeRecords(Workbook workbook, String filePath) throws SQLException {
        OutputStream out = null;
        PipedInputStream pin = null;
        try {
            /*
                Security Comment :
                This file path is trustworthy, this path is configured in the dbs file.
            */
            if (isRegistryPath(filePath)) {
                try {
                    RegistryService registryService = SQLDriverDSComponent.getRegistryService();
                    if (registryService == null) {
                        throw new SQLException("DBUtils.getInputStreamFromPath(): Registry service is not available");
                    }
                    Registry registry;
                    if (filePath.startsWith(CONF_REGISTRY_PATH_PREFIX)) {
                        if (filePath.length() > CONF_REGISTRY_PATH_PREFIX.length()) {
                            filePath = filePath.substring(CONF_REGISTRY_PATH_PREFIX.length());
                            registry = registryService.getConfigSystemRegistry(getCurrentTenantId());
                        } else {
                            throw new SQLException("Empty configuration registry path given");
                        }
                    } else {
                        if (filePath.length() > GOV_REGISTRY_PATH_PREFIX.length()) {
                            filePath = filePath.substring(GOV_REGISTRY_PATH_PREFIX.length());
                            registry = registryService.getGovernanceSystemRegistry(getCurrentTenantId());
                        } else {
                            throw new SQLException("Empty governance registry path given");
                        }
                    }
                    if (registry.resourceExists(filePath)) {
                        pin = new PipedInputStream();
                        out = new PipedOutputStream(pin);
                        new WorkBookOutputWriter(workbook,out).start();
                        Resource serviceResource = registry.get(filePath);
                        serviceResource.setContentStream(pin);
                        registry.put(filePath, serviceResource);
                    } else {
                        throw new SQLException(
                                "The given XSLT resource path at '" + filePath + "' does not exist");
                    }
                } catch (RegistryException e) {
                    throw new SQLException(e);
                }
            } else {
                File file = new File(filePath);
                if (filePath.startsWith("." + File.separator) || filePath.startsWith(".." + File.separator)) {
                /* this is a relative path */
                    filePath = file.getAbsolutePath();
                }
                out = new FileOutputStream(filePath);
                workbook.write(out);
            }
        } catch (FileNotFoundException e) {
            throw new SQLException("Error occurred while locating the EXCEL datasource", e);
        } catch (IOException e) {
            throw new SQLException("Error occurred while writing the records to the EXCEL " +
                                   "data source", e);
        } finally {
            if (pin != null) {
                try {
                    pin.close();
                } catch (IOException ignore) {

                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException ignore) {

                }
            }
        }
    }

    /**
     * Creates and returns an InputStream from the file path / http location given.
     *
     * @throws IOException, SQLException
     * @see java.io.InputStream
     */
    public static InputStream getInputStreamFromPath(String path) throws IOException, SQLException {
        InputStream ins;
        if (path.startsWith("http://")) {
            /* This is a url file path */
            /*
                Security Comment :
                This file path is trustworthy, this path is configured in the dbs file.
            */
            URL url = new URL(path);
            ins = url.openStream();
        } else if (isRegistryPath(path)) {
            try {
                RegistryService registryService = SQLDriverDSComponent.getRegistryService();
                if (registryService == null) {
                    throw new SQLException("DBUtils.getInputStreamFromPath(): Registry service is not available");
                }
                Registry registry;
                if (path.startsWith(CONF_REGISTRY_PATH_PREFIX)) {
                    if (path.length() > CONF_REGISTRY_PATH_PREFIX.length()) {
                        path = path.substring(CONF_REGISTRY_PATH_PREFIX.length());
                        registry = registryService.getConfigSystemRegistry(getCurrentTenantId());
                    } else {
                        throw new SQLException("Empty configuration registry path given");
                    }
                } else {
                    if (path.length() > GOV_REGISTRY_PATH_PREFIX.length()) {
                        path = path.substring(GOV_REGISTRY_PATH_PREFIX.length());
                        registry = registryService.getGovernanceSystemRegistry(getCurrentTenantId());
                    } else {
                        throw new SQLException("Empty governance registry path given");
                    }
                }
                if (registry.resourceExists(path)) {
                    Resource serviceResource = registry.get(path);
                    ins = serviceResource.getContentStream();
                } else {
                    throw new SQLException(
                            "The given XSLT resource path at '" + path + "' does not exist");
                }
            } catch (RegistryException e) {
                throw new SQLException(e);
            }
        } else {
            File file = new File(path);
            if (path.startsWith("." + File.separator) || path.startsWith(".." + File.separator)) {
                /* this is a relative path */
                path = file.getAbsolutePath();
            }
            /* local file */
            ins = new FileInputStream(path);
        }
        return ins;
    }

    public static boolean isRegistryPath(String path) {
        if (path.startsWith(CONF_REGISTRY_PATH_PREFIX) || path.startsWith(GOV_REGISTRY_PATH_PREFIX)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Returns the best effort way of finding the current tenant id,
     * even if this is not in a current message request, i.e. deploying services.
     * Assumption: when tenants other than the super tenant is activated,
     * the registry service must be available. So, the service deployment and accessing the registry,
     * will happen in the same thread, without the callbacks being used.
     *
     * @return The tenant id
     */
    public static int getCurrentTenantId() {
        try {
            int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
            if (tenantId == -1) {
                throw new RuntimeException("Tenant id cannot be -1");
            }
            return tenantId;
        } catch (NoClassDefFoundError e) { // Workaround for Unit Test failure
            return MultitenantConstants.SUPER_TENANT_ID;
        } catch (ExceptionInInitializerError e) {
            return MultitenantConstants.SUPER_TENANT_ID;
        }
    }

}
