do_hr() {
   echo "==============================================================="
}

#############################################################################
do_hr
echo "Defining database parameters"
do_hr
#############################################################################

DB_USER=worldmap
DB_PW=worldmap
DB_HOST=localhost
NEW_DB=worldmap_new
OLD_DB=worldmap
GEONODE_PATH=/code/geonode/
export DATABASE_URL=postgres://$DB_USER:$DB_PW@$DB_HOST:5432/$NEW_DB

#############################################################################
do_hr
echo "Removing previous saved database"
do_hr
#############################################################################

if [ $( psql -l | grep $NEW_DB | wc -l ) = '1' ]
then
	echo "Removing $NEW_DB"
	dropdb $NEW_DB
fi

#############################################################################
do_hr
echo "Create database $NEW_DB"
do_hr
#############################################################################

createdb $NEW_DB

#############################################################################
do_hr
echo "Executing geonode migrations"
do_hr
#############################################################################

python $GEONODE_PATH/manage.py makemigrations
python $GEONODE_PATH/manage.py migrate account --noinput
python $GEONODE_PATH/manage.py migrate

#############################################################################
do_hr
echo "Removing previous saved data"
do_hr
#############################################################################

# Clear old layers.
psql -d $NEW_DB -c "DELETE FROM layers_layer"

# Clear old resourcebase.
psql -d $NEW_DB -c "DELETE FROM base_resourcebase"

# Clear old accounts.
psql -d $NEW_DB -c "DELETE FROM account_account"

# Clear old accounts email
psql -d $NEW_DB -c "DELETE FROM account_emailaddress"

# Clear old groups.
psql -d $NEW_DB -c "DELETE FROM people_profile_groups"

# Clear old users.
psql -d $NEW_DB -c "DELETE FROM people_profile WHERE id > -1"


#############################################################################
do_hr
echo "Executing migration"
do_hr
#############################################################################

# Create view for bbox in legacy database
PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c "CREATE OR REPLACE VIEW maps_layer_bbox AS SELECT id, cast(bbox[1] as float) as bbox_x0, cast(bbox[2] as float) as bbox_y0, cast(bbox[3] as float) as bbox_x1, cast(bbox[4] as float) as bbox_y1 from (select id, string_to_array(replace(replace(replace(llbbox, ']',''), '[',''), ',',''), ' ') as bbox from maps_layer) as seq"

# Create new layers table view with bbox in legacy database.
PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c "CREATE OR REPLACE VIEW augmented_maps_layer AS SELECT * FROM maps_layer INNER JOIN maps_layer_bbox USING (id) WHERE maps_layer_bbox.bbox_x0 > -181 and maps_layer_bbox.bbox_x1 < 181 and maps_layer_bbox.bbox_y0 > -91 and maps_layer_bbox.bbox_y1 < 90"

# Copy users.
PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c 'copy (select id, password, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined from auth_user) to stdout with csv' | psql $NEW_DB -c 'copy people_profile (id, password, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined) from stdin csv'

# Get layers content type needed for polymorphic
ID=$(psql worldmap -c "copy (select id from django_content_type where name like 'layer') to stdout with csv")

# Copy items to resourcebase. Removing temporal extent works.

# PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c "copy (select id, $ID, uuid, owner_id, title, date, date_type, abstract, language, supplemental_information, 'EPSG:4326', 'csw_typename', 'csw_schema', 'csw_mdsource', 'csw_type', 'csw_wkt_geometry', false, 0, 0, false, true, bbox_x0, bbox_y0, bbox_x1, bbox_y1, typename, false, temporal_extent_start, temporal_extent_end from augmented_maps_layer) to stdout with csv" | psql $NEW_DB -c "copy base_resourcebase (id, polymorphic_ctype_id, uuid, owner_id, title, date, date_type, abstract, language, supplemental_information, srid, csw_typename, csw_schema, csw_mdsource, csw_type, csw_wkt_geometry, metadata_uploaded, popular_count, share_count, featured, is_published, bbox_x0, bbox_y0, bbox_x1, bbox_y1, detail_url, metadata_uploaded_preserve, temporal_extent_start, temporal_extent_end) from stdin csv"
PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c "copy (select id, $ID, uuid, owner_id, title, date, date_type, abstract, language, supplemental_information, 'EPSG:4326', 'csw_typename', 'csw_schema', 'csw_mdsource', 'csw_type', 'csw_wkt_geometry', false, 0, 0, false, true, bbox_x0, bbox_y0, bbox_x1, bbox_y1, typename, false from augmented_maps_layer) to stdout with csv" | psql $NEW_DB -c "copy base_resourcebase (id, polymorphic_ctype_id, uuid, owner_id, title, date, date_type, abstract, language, supplemental_information, srid, csw_typename, csw_schema, csw_mdsource, csw_type, csw_wkt_geometry, metadata_uploaded, popular_count, share_count, featured, is_published, bbox_x0, bbox_y0, bbox_x1, bbox_y1, detail_url, metadata_uploaded_preserve) from stdin csv"

# Set detail_url as /layers/{{typename}}
psql $NEW_DB -c "UPDATE base_resourcebase SET detail_url = '/layers/'||detail_url;"

# Copy items to layer table.
PGPASSWORD=$DB_PW psql -U $DB_USER -h $DB_HOST $OLD_DB -c 'copy (select id, title, abstract, purpose, constraints_other, supplemental_information, distribution_description, data_quality_statement, workspace, store, "storeType", name, typename, name from augmented_maps_layer) to stdout with csv' | psql $NEW_DB -c 'copy layers_layer (resourcebase_ptr_id, title_en, abstract_en, purpose_en, constraints_other_en, supplemental_information_en, distribution_description_en, data_quality_statement_en, workspace, store, "storeType", name, typename, charset) from stdin csv'

# Test.
# 2 db
# worldmaplegacy -> old geonode (default).
# data_geonode -> new geonode (default).