/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF 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.apache.empire.samples.db.advanced;

import java.sql.SQLException;

import org.apache.empire.commons.Options;
import org.apache.empire.data.DataType;
import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBCommandExpr;
import org.apache.empire.db.DBContext;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.DBTableColumn;
import org.apache.empire.db.generic.TDatabase;
import org.apache.empire.db.generic.TTable;
import org.apache.empire.db.generic.TView;
import org.apache.empire.db.validation.DBModelChecker;
import org.apache.empire.db.validation.DBModelErrorLogger;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <PRE>
 * This file contains the definition of the data model in Java.
 * The SampleDB data model consists of two tables and a foreign key relation.
 * The tables are defined as nested classes here, but you may put the in separate files if you want.
 *
 * PLEASE NOTE THE NAMING CONVENTION:
 * Since all tables, views and columns are declared as "final" constants they are all in upper case.
 * We recommend using a prefix of T_ for tables and C_ for columns in order to keep them together
 * when listed in your IDE's code completion.
 * There is no need to stick to this convention but it makes life just another little bit easier.
 *
 * You may declare other database tables or views in the same way.
 * </PRE>
 */
public class SampleAdvDB extends TDatabase<SampleAdvDB>
{
    // *Deprecated* private static final long serialVersionUID = 1L;

    // Logger
    private static final Logger log = LoggerFactory.getLogger(SampleAdvDB.class);

    /**
     * This class represents the definition of the Departments table.
     */
    public static class Departments extends TTable<SampleAdvDB>
    {
        // *Deprecated* private static final long serialVersionUID = 1L;

        public final DBTableColumn C_DEPARTMENT_ID;
        public final DBTableColumn C_NAME;
        public final DBTableColumn C_HEAD;
        public final DBTableColumn C_BUSINESS_UNIT;
        public final DBTableColumn C_UPDATE_TIMESTAMP;

        public Departments(SampleAdvDB db)
        {
            super("DEPARTMENTS", db);
            // ID
            C_DEPARTMENT_ID   = addColumn("DEPARTMENT_ID",    DataType.AUTOINC,       0, true, "DEP_ID_SEQUENCE");
            C_NAME            = addColumn("NAME",             DataType.VARCHAR,      80, true);
            C_HEAD            = addColumn("HEAD",             DataType.VARCHAR,      80, false);
            C_BUSINESS_UNIT   = addColumn("BUSINESS_UNIT",    DataType.VARCHAR,       4, true, "ITTK");
            C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP,     0, true);

            // Primary Key
            setPrimaryKey(C_DEPARTMENT_ID);
            // Set other Indexes
            addIndex("DEARTMENT_NAME_IDX", true, new DBColumn[] { C_NAME });
        }
    }

    /**
     * This class represents the definition of the Employees table.
     */
    public static class Employees extends TTable<SampleAdvDB>
    {
        // *Deprecated* private static final long serialVersionUID = 1L;
      
        public final DBTableColumn C_EMPLOYEE_ID;
        public final DBTableColumn C_SALUTATION;
        public final DBTableColumn C_FIRSTNAME;
        public final DBTableColumn C_LASTNAME;
        public final DBTableColumn C_DATE_OF_BIRTH;
        public final DBTableColumn C_GENDER;
        public final DBTableColumn C_PHONE_NUMBER;
        public final DBTableColumn C_EMAIL;
        public final DBTableColumn C_RETIRED;
        public final DBTableColumn C_CHECKSUM;
        public final DBTableColumn C_UPDATE_TIMESTAMP;

        // Useful column expressions
        public final DBColumnExpr  C_FULLNAME;
        
        public Employees(SampleAdvDB db)
        {
            super("EMPLOYEES", db);
            // ID
            C_EMPLOYEE_ID     = addColumn("EMPLOYEE_ID",      DataType.AUTOINC,      0, true, "EMPLOYEE_ID_SEQUENCE");
            C_SALUTATION      = addColumn("SALUTATION",       DataType.VARCHAR,     20, false);
            C_FIRSTNAME       = addColumn("FIRSTNAME",        DataType.VARCHAR,     40, true);
            C_LASTNAME        = addColumn("LASTNAME",         DataType.VARCHAR,     40, true);
            C_DATE_OF_BIRTH   = addColumn("DATE_OF_BIRTH",    DataType.DATE,         0, false);
            C_GENDER          = addColumn("GENDER",           DataType.VARCHAR,      1, false);
            C_PHONE_NUMBER    = addColumn("PHONE_NUMBER",     DataType.VARCHAR,     40, false);
            C_EMAIL           = addColumn("EMAIL",            DataType.VARCHAR,     80, false);
            C_RETIRED         = addColumn("RETIRED",          DataType.BOOL,         0, true, false);
            C_CHECKSUM        = addColumn("CHECKSUM",         DataType.INTEGER,      0, false);
            C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP,    0, true);

            // Primary Key
            setPrimaryKey(C_EMPLOYEE_ID);
            // Set other Indexes
            addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { C_FIRSTNAME, C_LASTNAME, C_DATE_OF_BIRTH });

            // Create Options for GENDER column
            Options genders = new Options();
            genders.set("M", "Male");
            genders.set("F", "Female");
            C_GENDER.setOptions(genders);
            
            // Define Column Expressions
            C_FULLNAME = C_LASTNAME.append(", ").append(C_FIRSTNAME).as("FULL_NAME");
            
        }
    }
    
    /**
     * This class represents the definition of the Departments table.
     */
    public static class EmployeeDepartmentHistory extends TTable<SampleAdvDB>
    {
        // *Deprecated* private static final long serialVersionUID = 1L;
      
        public final DBTableColumn C_EMPLOYEE_ID;
        public final DBTableColumn C_DEPARTMENT_ID;
        public final DBTableColumn C_DATE_FROM;

        public EmployeeDepartmentHistory(SampleAdvDB db)
        {
            super("EMPLOYEE_DEPARTMENT_HIST", db);
            // ID
            C_EMPLOYEE_ID   = addColumn("EMPLOYEE_ID",      DataType.INTEGER,       0, true);
            C_DEPARTMENT_ID = addColumn("DEPARTMENT_ID",    DataType.INTEGER,       0, true);
            C_DATE_FROM     = addColumn("DATE_FROM",        DataType.DATE,          0, true);

            // Primary Key
            setPrimaryKey(C_EMPLOYEE_ID, C_DATE_FROM);
        }
    }

    /**
     * This class represents the definition of the EmployeeDepSinceView table.
     */
    public static class EmployeeDepSinceView extends TView<SampleAdvDB>
    {
        // *Deprecated* private static final long serialVersionUID = 1L;
      
        public final DBViewColumn C_EMPLOYEE_ID;
        public final DBViewColumn C_MAX_DATE_FROM;
        
        public EmployeeDepSinceView(SampleAdvDB db, EmployeeDepartmentHistory T_EDH)
        {
            super("EMPLOYEE_DEP_SINCE_VIEW", db);
            // ID
            C_EMPLOYEE_ID     = addColumn(T_EDH.C_EMPLOYEE_ID);
            C_MAX_DATE_FROM   = addColumn("MAX_DATE_FROM", T_EDH.C_DATE_FROM); 

            // set Key-column (if any)
            setKeyColumn(C_EMPLOYEE_ID);
        }

        @Override
        public DBCommandExpr createCommand()
        {
            /* Sample DDL for this View:
            CREATE VIEW EMPLOYEE_DEP_SINCE_VIEW (EMPLOYEE_ID, MAX_DATE_FROM)
            AS (SELECT t3.EMPLOYEE_ID, max(t3.DATE_FROM)
                FROM EMPLOYEE_DEPARTMENT_HIST t3
                GROUP BY t3.EMPLOYEE_ID);
            */

            SampleAdvDB.EmployeeDepartmentHistory EDH = DB.T_EMP_DEP_HIST;
            
            // Define the sub query
            DBCommand cmd = newCommand();
            cmd.select (EDH.C_EMPLOYEE_ID, EDH.C_DATE_FROM.max());
            cmd.groupBy(EDH.C_EMPLOYEE_ID);
            return cmd;
        }
    }

    /**
     * This class represents the definition of the EmployeeInfoView table.
     */
    public static class EmployeeInfoView extends TView<SampleAdvDB>
    {
        // *Deprecated* private static final long serialVersionUID = 1L;
      
        public final DBViewColumn C_EMPLOYEE_ID;
        public final DBViewColumn C_CURRENT_DEP_ID;
        public final DBViewColumn C_NAME_AND_DEP;

        public EmployeeInfoView(SampleAdvDB db, Employees T_EMP, Departments T_DEP)
        {
            super("EMPLOYEE_INFO_VIEW", db);
            // ID
            C_EMPLOYEE_ID     = addColumn(T_EMP.C_EMPLOYEE_ID);
            C_CURRENT_DEP_ID  = addColumn("CURRENT_DEP_ID", T_DEP.C_DEPARTMENT_ID); 
            C_NAME_AND_DEP    = addColumn("NAME_AND_DEP",   DataType.VARCHAR);

            // set Key-column (if any)
            setKeyColumn(C_EMPLOYEE_ID);
        }

        @Override
        public DBCommandExpr createCommand()
        {
            /* Sample DDL for this View:
            CREATE VIEW EMPLOYEE_INFO_VIEW (EMPLOYEE_ID, CURRENT_DEP_ID, NAME_AND_DEP)
            AS (SELECT t2.EMPLOYEE_ID, t1.DEPARTMENT_ID, t2.LASTNAME + ', ' + coalesce(t2.FIRSTNAME, '') + ' (' + t1.NAME + ')'
                FROM EMPLOYEE_DEPARTMENT_HIST t3 
                INNER JOIN EMPLOYEE_DEP_SINCE_VIEW v1 ON v1.EMPLOYEE_ID = t3.EMPLOYEE_ID AND t3.DATE_FROM=v1.MAX_DATE_FROM
                INNER JOIN EMPLOYEES t2 ON t2.EMPLOYEE_ID = t3.EMPLOYEE_ID
                INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = t3.DEPARTMENT_ID);
            */
            
            SampleAdvDB.Employees   EMP = DB.T_EMPLOYEES;
            SampleAdvDB.EmployeeDepartmentHistory EDH = DB.T_EMP_DEP_HIST;
            SampleAdvDB.EmployeeDepSinceView EDS = DB.V_EMP_DEP_SINCE_VIEW;
            SampleAdvDB.Departments DEP = DB.T_DEPARTMENTS;

            // Define the query
            DBCommand cmd = newCommand();
            // Select required columns
            cmd.select(EMP.C_EMPLOYEE_ID);
            cmd.select(DEP.C_DEPARTMENT_ID);
            cmd.select(EMP.C_LASTNAME.append(", ")
                       .append(EMP.C_FIRSTNAME.coalesce(DBDatabase.EMPTY_STRING))
                       .append(" (").append(DEP.C_NAME).append(")"));
            // Set Joins
            cmd.join(EDH.C_EMPLOYEE_ID, EDS.C_EMPLOYEE_ID, EDH.C_DATE_FROM.is(EDS.C_MAX_DATE_FROM));
            cmd.join(EMP.C_EMPLOYEE_ID, EDH.C_EMPLOYEE_ID);
            cmd.join(DEP.C_DEPARTMENT_ID, EDH.C_DEPARTMENT_ID);
            // done
            return cmd;
        }
    }
    
    // Declare all Tables
    public final Departments  T_DEPARTMENTS = new Departments(this);
    public final Employees    T_EMPLOYEES   = new Employees(this);
    public final EmployeeDepartmentHistory  T_EMP_DEP_HIST = new EmployeeDepartmentHistory(this);
    // Declare all Views
    public final EmployeeDepSinceView V_EMP_DEP_SINCE_VIEW;
    public final EmployeeInfoView V_EMPLOYEE_INFO; 
    
    /**
     * Constructor of the SampleDB data model description
     *
     * Put all foreign key relations here.
     */
    public SampleAdvDB()
    {
        // Create views
        V_EMP_DEP_SINCE_VIEW = new EmployeeDepSinceView(this, T_EMP_DEP_HIST);
        V_EMPLOYEE_INFO      = new EmployeeInfoView(this, T_EMPLOYEES, T_DEPARTMENTS);
        
        // Define Foreign-Key Relations
        addRelation( T_EMP_DEP_HIST.C_EMPLOYEE_ID  .referenceOn( T_EMPLOYEES.C_EMPLOYEE_ID )).onDeleteCascade();
        addRelation( T_EMP_DEP_HIST.C_DEPARTMENT_ID.referenceOn( T_DEPARTMENTS.C_DEPARTMENT_ID ));
    }
    
    @Override
    public void open(DBContext context)
    {
        // Enable prepared statements
        setPreparedStatementsEnabled(true);
        // Check exists
        if (checkExists(context))
        {   // attach to driver
            super.open(context);
            // yes, it exists, then check the model
            checkDataModel(context);
        }
        else
        {   // PostgreSQL does not support DDL in transaction
            DBMSHandler dbms = context.getDbms();
            if (dbms instanceof DBMSHandlerPostgreSQL)
                setAutoCommit(context, true);
            // create the database
            createDatabase(context);
            // PostgreSQL does not support DDL in transaction
            if (dbms instanceof DBMSHandlerPostgreSQL)
                setAutoCommit(context, false);
            // attach to driver
            super.open(context);
        }
    }

    private void createDatabase(DBContext context)
    {
        // create DDL for Database Definition
        DBSQLScript script = new DBSQLScript(context);
        getCreateDDLScript(script);
        // Show DDL Statement
        log.info(script.toString());
        // Execute Script
        script.executeAll(false);
        // Commit
        context.commit();
    }
    
    private void checkDataModel(DBContext context)
    {   try {
            DBModelChecker modelChecker = context.getDbms().createModelChecker(this);
            // Check data model   
            log.info("Checking DataModel for {} using {}", getClass().getSimpleName(), modelChecker.getClass().getSimpleName());
            // dbo schema
            DBModelErrorLogger logger = new DBModelErrorLogger();
            modelChecker.checkModel(this, context.getConnection(), logger);
            // show result
            log.info("Data model check done. Found {} errors and {} warnings.", logger.getErrorCount(), logger.getWarnCount());
        } catch(Exception e) {
            log.error("FATAL error when checking data model. Probably not properly implemented by DBMSHandler!");
        }
    }
    
    private void setAutoCommit(DBContext context, boolean enable)
    {   try {
            context.getConnection().setAutoCommit(enable);
        } catch (SQLException e) {
            log.error("Unable to set AutoCommit on Connection", e);
        }
    }

}