/*
 * The Kuali Financial System, a comprehensive financial management system for higher education.
 *
 * Copyright 2005-2016 The Kuali Foundation
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.kuali.kfs.sys.dataaccess;

import org.kuali.kfs.sys.ConfigureContext;
import org.kuali.kfs.sys.context.KualiTestBase;
import org.kuali.kfs.sys.context.SpringContext;
import org.kuali.rice.core.framework.persistence.platform.OracleDatabasePlatform;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

@ConfigureContext
public class MismatchedForeignKeyTest extends KualiTestBase {
    private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(MismatchedForeignKeyTest.class);

    protected UnitTestSqlDao unitTestSqlDao = null;

    public MismatchedForeignKeyTest() {
        super();
    }

    @Override
    protected void setUp() throws Exception {
        super.setUp();

        LOG.debug("setUp() starting");
        unitTestSqlDao = SpringContext.getBean(UnitTestSqlDao.class);
    }

    /**
     * TODO: Remove once other tests are fixed
     */
    public void testNothing() {

    }

    /**
     * select occurrences where foreign key columns do not match the parent columns in data types or sizes.
     * TODO: Fix test once Jira KFSMI-5132 is resolved
     */
    public void NORUN_testExistingMismatchedForeignKeys() {
        if (unitTestSqlDao.getDbPlatform() instanceof OracleDatabasePlatform) {
            final List rows = unitTestSqlDao.sqlSelect("SELECT c.table_name AS child_table_name, cc.column_name AS child_column_name, " +
                "rc.table_name AS parent_table_name, rcc.column_name AS parent_column_name, " +
                "DECODE( cols.data_type, 'NUMBER', DECODE( cols.data_precision, NULL, cols.data_type, " +
                "cols.data_type||'('||cols.DATA_PRECISION||','||cols.DATA_SCALE||')' ), 'CHAR', cols.data_type||'('||cols.data_length||')', " +
                "'VARCHAR2', cols.data_type||'('||cols.data_length||')', cols.data_type ) AS child_data_type, " +
                "DECODE( cols.data_type, 'NUMBER', DECODE( rcols.data_precision, NULL, rcols.data_type, " +
                "rcols.data_type||'('||rcols.DATA_PRECISION||','||rcols.DATA_SCALE||')' ), " +
                "'CHAR', rcols.data_type||'('||rcols.data_length||')', 'VARCHAR2', rcols.data_type||'('||rcols.data_length||')', " +
                "rcols.data_type ) AS parent_data_type " +
                "FROM user_constraints c, user_constraints rc, user_cons_columns cc, user_cons_columns rcc, user_tab_columns cols, user_tab_columns rcols " +
                "WHERE c.constraint_type = 'R' AND cc.constraint_name = c.constraint_name AND rcc.constraint_name = c.r_constraint_name " +
                "AND rcc.position = cc.position AND cols.table_name = c.table_name AND cols.column_name = cc.column_name " +
                "AND rc.constraint_name = c.r_constraint_name AND rcols.table_name = rc.table_name AND rcols.column_name = rcc.column_name " +
                "AND ( cols.data_type <> rcols.data_type OR NVL( cols.data_length, 0 ) <> NVL( rcols.data_length, 0 ) " +
                "OR NVL( cols.data_precision, 0 ) <> NVL( rcols.data_precision, 0 ) OR NVL( cols.data_scale, 0 ) <> NVL( rcols.data_scale, 0 ))\n" +
                "AND c.table_name NOT LIKE 'NOTIFICATION%'"
            );

            StringBuffer failureMessage = new StringBuffer("Foreign Key Mismatches: ");

            if (rows.size() > 0) {
                for (Iterator iter = rows.iterator(); iter.hasNext(); ) {
                    Map element = (Map) iter.next();
                    failureMessage.append("\n\tCHILD table/column/data-type: ");
                    failureMessage.append(element.get("CHILD_TABLE_NAME")).append("/").append(element.get("CHILD_COLUMN_NAME")).append("/").append(element.get("CHILD_DATA_TYPE"));
                    failureMessage.append(" -- PARENT table/column/data-type: ");
                    failureMessage.append(element.get("PARENT_TABLE_NAME")).append("/").append(element.get("PARENT_COLUMN_NAME")).append("/").append(element.get("PARENT_DATA_TYPE"));
                }
                failureMessage.append("\n");
            }

            assertEquals(failureMessage.toString(), 0, rows.size());
        } else {
            System.err.println("Unable to test as no SQL available to test for this platform.");
        }
    }

}
