#!/bin/bash

. ../dbcfg.orig

allschema=0
napp=0

while [ $# -gt 0 ]; do
  case $1 in
    -sdb)   origdb=$2; shift 2;;      # source db       - nicht verwendet
    -shost) orighost=$2; shift 2;;    # source host     - nicht verwendet
    -spass) origpass=$2; shift 2;;    # source password - nicht verwendet
    -ddb)   checkdb=$2; shift 2;;     # dest db
    -dhost) checkhost=$2; shift 2;;   # dest host
    -dpass) checkpass=$2; shift 2;;   # dest passwd
    -all)   allschema=1; shift 1;;    # nur datenbank leeren keine schemas droppen
    -napp)  napp=$2; shift 2;;        # neue application erstellen
     *)     shift 1 ;;
  esac
done

version=`cat ../../../installer/erp/dist/version`
echo "Database Version $version" 1>&2

rdschemas="SELECT schemata.schema_name FROM information_schema.schemata where schema_name like 'mne_%' AND NOT schema_name='mne_application' AND NOT schema_name='mne_catalog'"
rdtables="SELECT tables.table_name FROM information_schema.tables WHERE tables.table_schema = 'par1' AND table_type = 'BASE TABLE'"

echo "--Tabellen überprüfen"

schemas=`get_checkdata "$rdschemas"`
schemas="$schemas"

tables=`get_checkdata "$rdtables" public`
for t in $table
do
    echo 'DROP TABLE public.'$t' CASCADE;'
done

tables=`get_checkdata "$rdtables" mne_catalog`

for t in $tables
do
    case $t in
       id_count) ;;
       *) echo 'TRUNCATE TABLE ' mne_catalog.$t ' CASCADE;'; ;;
    esac;
done

tables=`get_checkdata "$rdtables" mne_application`

for t in $tables
do
    case $t in
      usertables) ;;
      yearday) ;;
      tableconstraintmessages) ;;
      tablecolnames) echo "ALTER TABLE mne_application.tablecolnames SET WITH OIDS; DELETE FROM mne_application.tablecolnames WHERE tab != '' AND oid in ( SELECT t0.oid FROM mne_application.tablecolnames t0 LEFT JOIN information_schema.columns t1 ON ( t0.\"schema\" = t1.table_schema AND t0.tab = t1.table_name AND t0.colname = t1.column_name ) WHERE t1.table_schema IS NULL );";;
      year) ;;
      customerfunctions) ;;
      update)    echo "delete from mne_application.$t; INSERT INTO mne_application.$t ( updateid, version, updatehost ) VALUES ( '0', '"$version"', 'update.nelson-it.ch' );" ;;
      userpref ) echo "delete from mne_application.$t where username != 'admindb'; update mne_application.$t set startweblet = 'user_settings';"; ;;
      folder )   echo 'TRUNCATE TABLE ' mne_application.$t ' CASCADE;'; ;;
      server )   echo 'TRUNCATE TABLE ' mne_application.$t ' CASCADE;'; ;;

    esac;

    case $t in
      update) ;;
           *) echo "update mne_application.$t set createuser='admindb', modifyuser='admindb';" ;;
    esac;
done

if [ "$napp" = "1" ]; then
    for s in $schemas
    do
          echo "DROP SCHEMA $s CASCADE;"
    done

else

    productselect="select t0.productid  from mne_crm.product t0 left join mne_hoai.workphase t1 on ( t0.productid = t1.productid) left join mne_hoai.feeextra t2 on ( t2.productid = t0.productid ) WHERE t1.productid is null and t2.productid is null";
	for s in $schemas
	do
	    found=0
	    
	    tables=`get_checkdata "$rdtables" $s`
	    for t in $tables
	    do
	        case $s.$t in
	             
	            mne_base.currency) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            
	            mne_crm.addresstyp) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_crm.companydatacategorie) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_crm.productdefault) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_crm.productprice) ;;
	            mne_crm.producttree) ;;
	            mne_crm.product) echo "delete from mne_personnal.producttime where productid in ( $productselect );";
	                             echo "delete from mne_personnal.producttimeopt where productid in ( $productselect );";
	                             echo "delete from mne_warehouse.productpart where productid in ( $productselect );";
	                             echo "delete from mne_crm.productprice where productid in ( $productselect );";
	                             echo "delete from mne_crm.producttree where productid in ( $productselect );";
	                             echo "delete from mne_crm.product where productid in ( $productselect );"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             echo "delete from mne_crm.producttree where treeid in ( select t0.treeid from mne_crm.producttree t0 LEFT JOIN mne_crm.product t1 ON ( t0.productid = t1.productid ) LEFT JOIN mne_crm.producttree t2 ON ( t0.treeid = t2.parentid ) where t1.productid IS NULL AND t2.treeid is NULL);"
	                             ;;
	            
	            mne_hoai.fee) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_hoai.feeextra) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_hoai.feename) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_hoai.workphase) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            
	            mne_shipment.invoicecond) echo "delete from mne_shipment.invoicecond WHERE invoicecondid not in ( 'textstd', 'condstd', 'divstd');";;
	            
	            mne_warehouse.storagelocationtyp) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_warehouse.storagetyp) echo "update $s.$t set createuser='admindb', modifyuser='admindb';" ;;
	            mne_warehouse.productpart );;
	            
	            mne_personnal.skill )  ;;
	            mne_personnal.timemanagement_param );;
	            mne_personnal.producttime );;
	            mne_personnal.producttimeopt );;
	            
	            mne_system.access ) echo "delete from mne_system.access where custom = true;";;
	            
	            *) echo 'TRUNCATE TABLE ' $s.$t ' CASCADE;'; ;;
	        esac;
	            
	    done
	done
	
    echo "delete from mne_personnal.skill WHERE skillid in ( select t0.skillid from mne_personnal.skill t0 left join mne_personnal.producttime t1 on ( t1.skillid = t0.skillid) left join mne_personnal.producttimeopt t2 on ( t2.skillid = t0.skillid)  where ( t1.skillid is null and t2.skillid is null and t0.skillid != 'special' )); update mne_personnal.skill set unitcost = 0.0;"
fi

ystart=`date +%Y`
let yend=ystart
let ystart=ystart-1;
let yend=yend+1;

echo "update mne_application.customerfunctions set funcschema = '', func = '';"

echo "update mne_application.year set yearmax = "$yend", yearmin = "$ystart";"
echo 'delete from mne_application.yearday;'
echo 'insert into mne_application.yearday select * from mne_catalog.yearday();'
echo "REVOKE ALL ON TABLE mne_system.mailaliaspublic FROM sogo$(hostname);"

if [ "$napp" = "1" ]; then
    echo "delete from mne_application.customerfunctions;"
    echo "delete from mne_application.folder;"
    echo "delete from mne_application.reportsauto;"
    echo "delete from mne_application.reportscache;"
    echo "delete from mne_application.reports;"
    echo "delete from mne_application.selectlist;"
    echo "delete from mne_application.tableconstraintmessages;"
    echo "delete from mne_application.timestyle;"
    echo "delete from mne_application.translate;"
    echo "delete from mne_application.trustrequest;"
    echo "delete from mne_application.update;"
    echo "delete from mne_application.userpref;"
    echo "delete from mne_application.usertables;"

    echo "delete from mne_application.htmlcomposetabslider WHERE htmlcomposeid in ( select htmlcomposeid from mne_application.htmlcompose where not name like 'dbadmin%' AND not name like 'main%');"
    echo "delete from mne_application.htmlcomposetabselect WHERE htmlcomposeid in ( select htmlcomposeid from mne_application.htmlcompose where not name like 'dbadmin%' AND not name like 'main%');"
    echo "delete from mne_application.htmlcomposetabnames  WHERE htmlcomposeid in ( select htmlcomposeid from mne_application.htmlcompose where not name like 'dbadmin%' AND not name like 'main%');"
    echo "delete from mne_application.htmlcomposetab       WHERE htmlcomposeid in ( select htmlcomposeid from mne_application.htmlcompose where not name like 'dbadmin%' AND not name like 'main%');"
    echo "delete from mne_application.htmlcomposenames     WHERE htmlcomposeid in ( select htmlcomposeid from mne_application.htmlcompose where not name like 'dbadmin%' AND not name like 'main%');"
    echo "delete from mne_application.htmlcompose          WHERE not name like 'dbadmin%' AND not name like 'main%';"

    echo "delete from mne_application.querycolumns WHERE queryid in ( select queryid from mne_application.queryname WHERE "schema" != 'mne_application' );"
    echo "delete from mne_application.querytables WHERE queryid in ( select queryid from mne_application.queryname WHERE "schema" != 'mne_application' );"
    echo "delete from mne_application.querywheres WHERE queryid in ( select queryid from mne_application.queryname WHERE "schema" != 'mne_application' );"
    echo "delete from mne_application.querycolnames WHERE "schema" != 'mne_application';"
    echo "delete from mne_application.queryname WHERE "schema" != 'mne_application';"
    
    echo "delete from mne_application.joindef WHERE joindefid in ( select t0.joindefid FROM mne_application.joindef t0 LEFT JOIN mne_application.querytables t1 ON ( t0.joindefid = t1.joindefid) WHERE t1.joindefid IS NULL);"

    echo "delete from mne_application.menu WHERE menuname != 'dbadmin';"
    
    echo "delete from mne_application.tablecolnames WHERE "schema" != 'mne_application';"
    echo "delete from mne_application.tablecolnames WHERE "schema" != 'mne_application';"
    echo "delete from mne_application.tablecolnames WHERE "schema" != 'mne_application';"
    echo "delete from mne_application.tablecolnames WHERE "schema" != 'mne_application';"
    echo "delete from mne_application.tablecolnames WHERE "schema" != 'mne_application';"
    
    
    
else
	if [ $allschema = 1 ]; then
	   exit 0;
	fi
	
	# Menu für HOAI
	# ==============
	echo "delete from mne_application.menu where menuid in ( '4e4b69e40000', '50bcb5e30000');";
	
	# Menu für Support
	# ================
	echo "delete from mne_application.menu where ugroup = 'erpsupport';";
	
	# Menu für Builddiary
	# ================
	echo "delete from mne_application.menu where ugroup = 'erpbuilddiary';";
	
	# Menu für Systemfunktionen
	# ==============================================
	echo "delete from mne_application.menu where ugroup like '%system' and menuname in ( 'dbadmin', 'erp');";
	
fi
