/*
 * ARX: Powerful Data Anonymization
 * Copyright 2012 - 2018 Fabian Prasser and contributors
 * 
 * Licensed 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.deidentifier.arx.gui.view.impl.wizard;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.deidentifier.arx.gui.resources.Resources;
import org.deidentifier.arx.gui.view.impl.common.DelayedChangeListener;
import org.eclipse.jface.wizard.WizardPage;
import org.eclipse.swt.SWT;
import org.eclipse.swt.custom.StackLayout;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Text;

/**
 * JDBC page
 *
 * This page offers means to specify connection details for a database. For
 * now MS SQL, MySQL, PostgreSQL and SQLite is supported. In case of remote database
 * types (i.e. MS SQL, MySQL and PostgreSQL) the user is asked for the server and a
 * username and password. In case of SQLite the user can select any *.db file.
 *
 * After ther user specified the details a connection is established and
 * passed on to {@link ImportWizardModel}.
 *
 * This includes:
 *
 * <ul>
 *  <li>{@link ImportWizardModel#setJdbcConnection(Connection)<li>
 *  <li>{@link ImportWizardModel#setJdbcTables(List)<li>
 * </ul>
 * 
 * @author Karol Babioch
 * @author Fabian Prasser
 */
public class ImportWizardPageJDBC extends WizardPage {
    
    /** Reference to the wizard containing this page. */
    private ImportWizard        wizardImport;
    
    /** Widget */
    private Label               lblType;
    
    /** Widget */
    private Combo               comboType;
    
    /** Widget */
    private Composite           compositeSwap;
    
    /** Widget */
    private Text                txtServer;
    
    /** Widget */
    private StackLayout         layout;
    
    /** Widget */
    private Composite           compositeRemote;
    
    /** Widget */
    private Composite           compositeLocal;
    
    /** Widget */
    private Text                txtPort;
    
    /** Widget */
    private Text                txtUsername;
    
    /** Widget */
    private Text                txtPassword;
    
    /** Widget */
    private Text                txtDatabase;
    
    /** Widget */
    private Label               lblLocation;
    
    /** Widget */
    private Combo               comboLocation;
    
    /** Widget */
    private Button              btnChoose;
    
    /** Widget */
    private Composite           container;
    
    /** Widget */
    private static final String ORACLE     = "Oracle";    //$NON-NLS-1$
                                                           
    /** Widget */
    private static final String MSSQL      = "MS SQL";    //$NON-NLS-1$
                                                           
    /** Widget */
    private static final String MYSQL      = "MySQL";     //$NON-NLS-1$
                                                           
    /** Widget */
    private static final String POSTGRESQL = "PostgreSQL"; //$NON-NLS-1$
                                                           
    /** Widget */
    private static final String SQLITE     = "SQLite";    //$NON-NLS-1$
                                                           
    /**
     * Creates a new instance of this page and sets its title and description.
     *
     * @param wizardImport Reference to wizard containing this page
     */
    public ImportWizardPageJDBC(ImportWizard wizardImport) {
        
        super("WizardImportJdbcPage"); //$NON-NLS-1$
        setTitle("JDBC"); //$NON-NLS-1$
        setDescription(Resources.getMessage("ImportWizardPageJDBC.6")); //$NON-NLS-1$
        this.wizardImport = wizardImport;
    }
    
    /**
     * Creates the design of this page
     * 
     * This adds all the controls to the page along with their listeners.
     *
     * @param parent
     * @note {@link #compositeSwap} contains the actual text fields. Depending
     *       upon the status of {@link #comboType}, it will either display {@link #compositeRemote} or {@link #compositeLocal}.
     */
    public void createControl(Composite parent) {
        
        container = new Composite(parent, SWT.NULL);
        
        setControl(container);
        container.setLayout(new GridLayout(2, false));
        
        /* Type label + combobox */
        lblType = new Label(container, SWT.NONE);
        lblType.setText(Resources.getMessage("ImportWizardPageJDBC.7")); //$NON-NLS-1$
        
        /* Combo for choosing database type */
        comboType = new Combo(container, SWT.READ_ONLY);
        comboType.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        comboType.setItems(new String[] { ORACLE, MSSQL, POSTGRESQL, MYSQL, SQLITE });
        comboType.addSelectionListener(new SelectionAdapter() {
            
            /**
             * Swaps the composites, resets it and triggers a relayout
             */
            @Override
            public void widgetSelected(SelectionEvent e) {
                
                setMessage(null);
                setErrorMessage(null);
                setPageComplete(false);
                
                /* Display compositeLocal in case of SQLite */
                if (comboType.getText().equals(SQLITE)) {
                    comboLocation.removeAll();
                    layout.topControl = compositeLocal;
                    
                /* Display compositeRemote otherwise */
                } else {
                    
                    layout.topControl = compositeRemote;
                    
                    /* Set default ports*/
                    if (comboType.getText().equals(MSSQL)) {
                        txtPort.setText("1433"); //$NON-NLS-1$
                    } else if (comboType.getText().equals(MYSQL)) {
                        txtPort.setText("3306"); //$NON-NLS-1$
                    } else if (comboType.getText().equals(POSTGRESQL)) {
                        txtPort.setText("5432"); //$NON-NLS-1$
                    } else if (comboType.getText().equals(ORACLE)) {
                        txtPort.setText("1521"); //$NON-NLS-1$
                    }
                }
                
                /* Trigger relayout */
                compositeSwap.layout();
            }
        });
        
        /* Placeholder */
        new Label(container, SWT.NONE);
        new Label(container, SWT.NONE);
        
        /* Swap composite */
        compositeSwap = new Composite(container, SWT.NONE);
        layout = new StackLayout();
        compositeSwap.setLayout(layout);
        compositeSwap.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, false, false, 2, 1));
        
        /* Remote composite */
        createCompositeRemote();
        
        /* Local composite */
        createCompositeLocal();
        
        /* Mark page as incomplete by default */
        setPageComplete(false);
    }
    
    /**
     * Creates the content of {@link #compositeLocal}
     *
     * This adds a file chooser and an appropriate combo to select files.
     * Selecting a file from the combo will trigger a read of the tables. If
     * everything is fine, the tables from the database will be read.
     *
     * @see {@link #readTables()}
     */
    private void createCompositeLocal() {
        
        compositeLocal = new Composite(compositeSwap, SWT.NONE);
        compositeLocal.setLayout(new GridLayout(3, false));
        
        /* Location label */
        lblLocation = new Label(compositeLocal, SWT.NONE);
        lblLocation.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblLocation.setText(Resources.getMessage("ImportWizardPageJDBC.11")); //$NON-NLS-1$
        
        /* Combo box for selection of file */
        comboLocation = new Combo(compositeLocal, SWT.READ_ONLY);
        comboLocation.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        comboLocation.addSelectionListener(new SelectionAdapter() {
            
            /* Read tables from file */
            @Override
            public void widgetSelected(SelectionEvent e) {
                setErrorMessage(null);
                connect();
                boolean ok = readTables();
                setPageComplete(ok);
                if (ok) {
                    setMessage(Resources.getMessage("ImportWizardPageJDBC.21"), INFORMATION); //$NON-NLS-1$
                }
            }
        });
        
        /* Button to open file selection dialog */
        btnChoose = new Button(compositeLocal, SWT.NONE);
        btnChoose.setText(Resources.getMessage("ImportWizardPageJDBC.12")); //$NON-NLS-1$
        btnChoose.addSelectionListener(new SelectionAdapter() {
            
            /**
             * Opens a file selection dialog for "*.db" files
             *
             * If a valid file was selected, it is added to {@link #comboLocation} when it wasn't already there. It is then
             * preselected within {@link #comboLocation}.
             */
            @Override
            public void widgetSelected(SelectionEvent arg0) {
                
                /* Open file dialog */
                final String path = wizardImport.getController().actionShowOpenFileDialog(getShell(), "*.db"); //$NON-NLS-1$
                
                if (path == null) {
                    return;
                }
                
                /* Check whether path was already added */
                if (comboLocation.indexOf(path) == -1) {
                    comboLocation.add(path, 0);
                }
                
                /* Select path and notify comboLocation about change */
                comboLocation.select(comboLocation.indexOf(path));
                comboLocation.notifyListeners(SWT.Selection, null);
            }
        });
    }
    
    /**
     * Creates the content of {@link #compositeRemote}
     *
     * This adds all of the labels and text fields necessary to connect to a
     * remote database server. If everything is fine, the tables from the
     * database will be read.
     *
     * @see {@link #readTables()}
     */
    private void createCompositeRemote() {
        
        compositeRemote = new Composite(compositeSwap, SWT.NONE);
        compositeRemote.setLayout(new GridLayout(2, false));
        
        // Tries to connect to database on changes
        DelayedChangeListener connectionTester = new DelayedChangeListener(1000) {
            @Override
            public void delayedEvent() {
                tryToConnect();
            }
        };
        
        Label lblServer = new Label(compositeRemote, SWT.NONE);
        lblServer.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblServer.setText(Resources.getMessage("ImportWizardPageJDBC.14")); //$NON-NLS-1$
        
        txtServer = new Text(compositeRemote, SWT.BORDER);
        txtServer.setText("localhost"); //$NON-NLS-1$
        txtServer.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        txtServer.addModifyListener(connectionTester);
        
        Label lblPort = new Label(compositeRemote, SWT.NONE);
        lblPort.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblPort.setText(Resources.getMessage("ImportWizardPageJDBC.16")); //$NON-NLS-1$
        
        txtPort = new Text(compositeRemote, SWT.BORDER);
        txtPort.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        txtPort.addModifyListener(connectionTester);
        
        Label lblUsername = new Label(compositeRemote, SWT.NONE);
        lblUsername.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblUsername.setText(Resources.getMessage("ImportWizardPageJDBC.0")); //$NON-NLS-1$
        
        txtUsername = new Text(compositeRemote, SWT.BORDER);
        txtUsername.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        txtUsername.addModifyListener(connectionTester);
        
        Label lblPassword = new Label(compositeRemote, SWT.NONE);
        lblPassword.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblPassword.setText(Resources.getMessage("ImportWizardPageJDBC.1")); //$NON-NLS-1$
        
        txtPassword = new Text(compositeRemote, SWT.BORDER | SWT.PASSWORD);
        txtPassword.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        txtPassword.addModifyListener(connectionTester);
        
        Label lblDatabase = new Label(compositeRemote, SWT.NONE);
        lblDatabase.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblDatabase.setText(Resources.getMessage("ImportWizardPageJDBC.19")); //$NON-NLS-1$
        
        txtDatabase = new Text(compositeRemote, SWT.BORDER);
        txtDatabase.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 1, 1));
        txtDatabase.addModifyListener(connectionTester);
    }
    
    /**
     * Tries to establish a remote JDBC connection
     *
     * Unless all mandatory fields (everything besides the password) are
     * not empty this will try to connect to the database. It sets the message
     * and errors of this page accordingly and will also try to read in the
     * tables once a successful connection has been established.
     *
     * @see {@link #readTables()}
     */
    private void tryToConnect() {
        
        setErrorMessage(null);
        setMessage(null);
        
        String server = txtServer.getText();
        String port = txtPort.getText();
        String username = txtUsername.getText();
        String database = txtDatabase.getText();
        
        if (server.isEmpty() || port.isEmpty() || username.isEmpty() || database.isEmpty()) {
            setMessage(Resources.getMessage("ImportWizardPageJDBC.6")); //$NON-NLS-1$
            setPageComplete(false);
            return;
        }
        
        setMessage(Resources.getMessage("ImportWizardPageJDBC.20"), INFORMATION); //$NON-NLS-1$
        if (connect()) {
            setMessage(Resources.getMessage("ImportWizardPageJDBC.21"), INFORMATION); //$NON-NLS-1$
            setPageComplete(readTables());
        } else {
            setPageComplete(false);
        }
    }
    
    /**
     * Connects to the database
     *
     * This tries to establish an JDBC connection. In case of an error
     * appropriate error messages are set. Otherwise the connection is passed
     * on to {@link ImportWizardModel}. The return value indicates whether a
     * connection has been established.
     *
     * @return True if successfully connected, false otherwise
     *
     * @see {@link ImportWizardModel#setJdbcConnection(Connection)}
     */
    private boolean connect() {
        
        try {
            
            Connection connection = null;
            
            if (comboType.getText().equals(SQLITE)) {
                
                Class.forName("org.sqlite.JDBC"); //$NON-NLS-1$
                connection = DriverManager.getConnection("jdbc:sqlite:" + comboLocation.getText()); //$NON-NLS-1$
                
            } else if (comboType.getText().equals(POSTGRESQL)) {
                
                Class.forName("org.postgresql.Driver"); //$NON-NLS-1$
                connection = DriverManager.getConnection("jdbc:postgresql://" + txtServer.getText() + ":" + txtPort.getText() + "/" + txtDatabase.getText(), txtUsername.getText(), txtPassword.getText()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                
            } else if (comboType.getText().equals(MSSQL)) {
                
                Class.forName("net.sourceforge.jtds.jdbc.Driver"); //$NON-NLS-1$
                connection = DriverManager.getConnection("jdbc:jtds:sqlserver://" + txtServer.getText() + ":" + txtPort.getText() + "/" + txtDatabase.getText(), txtUsername.getText(), txtPassword.getText()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
                
            } else if (comboType.getText().equals(MYSQL)) {
                
                Class.forName("com.mysql.jdbc.Driver"); //$NON-NLS-1$
                connection = DriverManager.getConnection("jdbc:mysql://" + txtServer.getText() + ":" + txtPort.getText() + "/" + txtDatabase.getText(), txtUsername.getText(), txtPassword.getText()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
            } else if (comboType.getText().equals(ORACLE)) {
                
                Class.forName("oracle.jdbc.driver.OracleDriver"); //$NON-NLS-1$
                connection = DriverManager.getConnection("jdbc:oracle:thin:@" + txtServer.getText() + ":" + txtPort.getText() + ":" + txtDatabase.getText(), txtUsername.getText(), txtPassword.getText()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
            }
            
            try {
                if (!wizardImport.getData().getJdbcConnection().isClosed()) {
                    wizardImport.getData().getJdbcConnection().close();
                }
            } catch (Exception e) {
                /* Die silently */
            }
            wizardImport.getData().setJdbcConnection(connection);
            return true;
            
        } catch (ClassNotFoundException e) {
            setErrorMessage(Resources.getMessage("ImportWizardPageJDBC.36")); //$NON-NLS-1$
            return false;
        } catch (SQLException e) {
            /* Database connection error */
            setErrorMessage(e.getLocalizedMessage());
            return false;
        }
    }
    
    /**
     * Reads in the tables
     *
     * If successful, the page is marked as complete and a list of tables is
     * assigned to {@link ImportWizardModel}. Otherwise an appropriate error message
     * is set.
     *
     * @see {@link ImportWizardModel#setJdbcTables(List)}
     */
    private boolean readTables() {
        ResultSet rs = null;
        try {
            Connection connection = wizardImport.getData().getJdbcConnection();
            String[] tableTypes = { "TABLE", "VIEW" }; //$NON-NLS-1$ //$NON-NLS-2$
            rs = connection.getMetaData().getTables(null, null, "%", tableTypes); //$NON-NLS-1$
            List<String> tables = new ArrayList<String>();
            
            while (rs.next()) {
                String name = rs.getString("TABLE_NAME"); //$NON-NLS-1$
                String schema = rs.getString("TABLE_SCHEM"); //$NON-NLS-1$
                if (schema != null) {
                    name = schema + "." + name; //$NON-NLS-1$
                }
                tables.add(name); 
            }
            
            wizardImport.getData().setJdbcTables(tables);
            return true;
        } catch (SQLException e) {
            setErrorMessage(Resources.getMessage("ImportWizardPageJDBC.41")); //$NON-NLS-1$
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                /* Ignore silently */
            }
        }
    }
}