/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (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.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * 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 com.queplix.core.modules.eql.parser.oracle;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.Dataschema;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.Entity;
import com.queplix.core.jxb.entity.Table;
import com.queplix.core.jxb.entity.types.RefSType;
import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.modules.eql.EQLERes;
import com.queplix.core.modules.eql.EQLNullObject;
import com.queplix.core.modules.eql.EQLObject;
import com.queplix.core.modules.eql.EQLResCell;
import com.queplix.core.modules.eql.EQLResRecord;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.parser.generic.SQLUpdateBuilderGenericImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * <p>Update SQL builder ORACLE 9i implementation class</p>
 * Oracle implementation support external memo field updates!
 *
 * @author Yulya.Neymirok
 * @version $Revision: 1.2 $ $Date: 2006/01/24 20:30:20 $
 */

public class SQLUpdateBuilderOracleImpl
        extends SQLUpdateBuilderGenericImpl {

    /*
     * (No javadoc)
     * @see SQLBuilder#getUpdateSql
     */
    protected String getUpdateSql(EQLERes res, EQLResRecord resRecord)
            throws EQLException {

        Entity entity = res.getEntity();
        int size = resRecord.size();

        // add fields and values
        int fieldsAdded = 0;
        StringBuffer sql1 = new StringBuffer();
        sql1.append("\nUPDATE ");
        sql1.append(entity.getDbobject());
        sql1.append(" SET\n");

        for(int i = 0; i < size; i++) {
            EQLResCell resCell = resRecord.getResCell(i);
            Efield field = resCell.getReqField().getField();

            // is filed updateable?
            if(!field.getUpdatable().booleanValue()) {
                continue;
            }

            // is field an external memo?
            if(isExternalMemo(field)) {

                EQLResCell listResCell = resCell.getListField();
                Efield listField = listResCell.getReqField().getField();

                if(listField.getUpdatable().booleanValue()) {

                    if(fieldsAdded > 0) {
                        sql1.append(",");
                    }

                    sql1.append(field.getName().toUpperCase());
                    sql1.append(" = ");
                    sql1.append(updateExternalMemo(resCell));
                    fieldsAdded++;
                }

            } else if(resCell.isValueChanged() && field.getUpdatable()
                    .booleanValue()) {

                // filed is updateable
                if(fieldsAdded > 0) {
                    sql1.append(",");
                }

                sql1.append(field.getName().toUpperCase());
                sql1.append(" = ");

                // get EQL object
                EQLObject eqlObj = resCell.getEQLObject();
                sql1.append(_getSQLValue(eqlObj));

                fieldsAdded++;
            }
        }

        // add constraint
        int pkeysAdded = 0;
        StringBuffer sql2 = new StringBuffer();
        sql2.append("\nWHERE\n");

        for(int i = 0; i < size; i++) {
            EQLResCell resCell = resRecord.getResCell(i);
            Efield field = resCell.getReqField().getField();

            if(field.getPkey().booleanValue()) {
                // field is pkey!
                EQLObject pkeyEqlObj = resCell.getOldEQLObject();

                if(pkeysAdded > 0) {
                    sql2.append(" AND ");
                }

                sql2.append(field.getName().toUpperCase());

                String temp = _getSQLValue(pkeyEqlObj);
                if("NULL".equals(temp.toUpperCase())) {
                    sql2.append(" IS NULL");
                } else {
                    sql2.append(" = ");
                    sql2.append(temp);
                }

                pkeysAdded++;
            }
        }

        if(pkeysAdded == 0) {
            throw new GenericSystemException(
                    "Can't find pkey in entity '" + entity.getName() + "'");
        }

        return (fieldsAdded > 0) ? (sql1.toString() + sql2.toString()):null;
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#getInsertSql
     */
    protected String getInsertSql(EQLERes res, EQLResRecord resRecord)
            throws EQLException {

        Entity entity = res.getEntity();

        StringBuffer sql1 = new StringBuffer();
        sql1.append("\nINSERT INTO\n ");
        sql1.append(entity.getDbobject());
        sql1.append("\n(");

        StringBuffer sql2 = new StringBuffer();
        sql2.append("\nVALUES\n(");

        // add fields and values
        int fieldsAdded = 0;
        int size = resRecord.size();
        for(int i = 0; i < size; i++) {
            EQLResCell resCell = resRecord.getResCell(i);
            Efield field = resCell.getReqField().getField();

            // is filed updateable?
            if(!field.getUpdatable().booleanValue()) {
                continue;
            }

            // is field an external memo?
            if(isExternalMemo(field)) {
                EQLResCell listResCell = resCell.getListField();

                // is memo not initialized?
                if(listResCell == null) {
                    continue;
                }

                Efield listField = listResCell.getReqField().getField();
                if(listField.getUpdatable().booleanValue()) {

                    if(fieldsAdded > 0) {
                        sql1.append(",");
                        sql2.append(",");
                    }

                    sql1.append(field.getName().toUpperCase());
                    sql2.append(updateExternalMemo(resCell));
                    fieldsAdded++;
                }
            } else {

                // EQL object
                EQLObject eqlObj = resCell.getEQLObject();

                if(fieldsAdded > 0) {
                    sql1.append(",");
                    sql2.append(",");
                }

                sql1.append(field.getName().toUpperCase());
                sql2.append(_getSQLValue(eqlObj));

                fieldsAdded++;
            }
        }

        sql1.append(")");
        sql2.append(")");

        return (fieldsAdded > 0) ? (sql1.toString() + sql2.toString()):null;
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#getDeleteSql
     */
    protected String getDeleteSql(EQLERes res, EQLResRecord resRecord)
            throws EQLException {

        String sql = super.getDeleteSql(res, resRecord);
        Entity entity = res.getEntity();

        for(int i = 0; i < entity.getEfieldCount(); i++) {
            Efield field = entity.getEfield(i);

            // is field an external memo and have List Reference with Ref type 1:1?
            if(isExternalMemo(field)) {

                Dataschema dataschema = field.getListref().getDataschema();
                if(dataschema.getReftype().getType() != RefSType.VALUE_0_TYPE) {
                    continue;
                }

                Table extTable = dataschema.getTable(1);
                if(extTable.getKeyCount() > 1) {
                    throw new GenericSystemException(
                            "Can't support compound pkey for memo listref, table is '"
                                    + extTable.getName() + "'");
                }

                StringBuffer sqlMemo = new StringBuffer();
                sqlMemo.append("DELETE FROM ").append(extTable.getName())
                        .append(" WHERE ");
                sqlMemo.append(extTable.getKey(0)).append(" = (SELECT ");
                sqlMemo.append(field.getName()).append(" ");
                sqlMemo.append(sql.substring(sql.indexOf(" FROM "))).append(
                        ")");

                Connection con = null;
                Statement stat = null;

                try {
                    con = sqlWrapper.doConnection();
                    stat = sqlWrapper.doStatement(con);
                    sqlWrapper.executeUpdate(stat, sqlMemo.toString());

                } catch (SQLException e) {
                    ERROR(e);
                    throw new GenericSystemException(
                            "Cannot update memo as a List Reference. " +
                                    "Sql: " + sqlMemo, e);

                } finally {
                    sqlWrapper.closeConnection(con, stat);
                }
            }
        }

        return sql;
    }

// ---------------- PROTECTED METHODS ------------------------

    //
    // Checks if <code>field</code> an external memo field
    //

    protected boolean isExternalMemo(Efield field) {
        int dataType = field.getDatatype().getType();
        int sqlType = field.getSqltype().getType();

        /** @todo refactor it and make universal procedure to update Listref */
        return ((dataType == SqlSType.MEMO_TYPE
                || dataType == SqlSType.MEMO_LONG_TYPE) &&
                dataType != sqlType && field.getListref() != null);
    }

    //
    // Updates external memo record and return memo pkey as SQL string
    //
    protected String updateExternalMemo(EQLResCell resCell) {
        EQLResCell listResCell = resCell.getListField();
        Entity listEntity = listResCell.getReqField().getReqEntity()
                .getEntity();

        String updatePKFieldName = null;
        for(int j = 0; j < listEntity.getEfieldCount(); j++) {
            if(listEntity.getEfield(j).getPkey().booleanValue()) {
                updatePKFieldName = listEntity.getEfield(j).getName();
                break;
            }
        }
        if(updatePKFieldName == null) {
            throw new GenericSystemException(
                    "Can't find pkey in entity '" + listEntity.getName() + "'");
        }

        String updateTableName = listEntity.getDbobject();
        String updateFieldName = listResCell.getReqField().getField().getName();

        Connection con = null;
        PreparedStatement ps = null;
        StringBuffer sql = new StringBuffer();

        try {
            con = sqlWrapper.doConnection();

            if(resCell.getOldEQLObject() instanceof EQLNullObject) {

                if(listResCell.getEQLObject() instanceof EQLNullObject) {
                    // New value is null - don't insert any record
                    return "NULL";
                }

                // Insert new memo record
                sql.append("INSERT INTO ").append(updateTableName).append(" (");
                sql.append(updatePKFieldName).append(", ").append(
                        updateFieldName);
                sql.append(") VALUES (?, ?)");

                long pkey = sqlWrapper.getNextKey(con, updateTableName);
                ps = sqlWrapper.doPreparedStatement(con, sql.toString());
                ps.setLong(1, pkey);
                sqlWrapper.getMemoParser().setValue(ps, 2,
                        listResCell.getMemo());
                int ret = sqlWrapper.executeUpdate(ps);

                DEBUG("Inserted " + ret + " memo records. Pkey: " + pkey);

                return String.valueOf(pkey);

            } else if((listResCell.getEQLObject() instanceof EQLNullObject) &&
                    !(listResCell.getOldEQLObject() instanceof EQLNullObject)) {

                // Get old memo pkey
                long pkey = resCell.getOldLong().longValue();

                // Delete memo record
                sql.append("DELETE FROM ").append(updateTableName).append(
                        " WHERE ");
                sql.append(updatePKFieldName).append(" = ?");

                ps = sqlWrapper.doPreparedStatement(con, sql.toString());
                ps.setLong(1, pkey);
                int ret = sqlWrapper.executeUpdate(ps);

                DEBUG("Deleted " + ret + " memo records. Pkey: " + pkey);
                return "NULL";

            } else {

                // Get old memo pkey
                long pkey = resCell.getOldLong().longValue();

                if(listResCell.isValueChanged()) {
                    // Value was changed - update memo record
                    sql.append("UPDATE ").append(updateTableName).append(
                            " SET ");
                    sql.append(updateFieldName).append(" = ? ");
                    sql.append("WHERE ").append(updatePKFieldName).append(
                            " = ?");

                    ps = sqlWrapper.doPreparedStatement(con, sql.toString());
                    sqlWrapper.getMemoParser().setValue(ps, 1,
                            listResCell.getMemo());
                    ps.setLong(2, pkey);
                    int ret = sqlWrapper.executeUpdate(ps);

                    DEBUG("Updated " + ret + " memo records. Pkey: " + pkey);
                }

                return String.valueOf(pkey);
            }

        } catch (SQLException e) {
            ERROR(e);
            throw new GenericSystemException(
                    "Cannot update memo as a List Reference. " +
                            "Sql: " + sql, e);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }
    }

}
