# -*- coding: utf-8 -*-
"""
Create, Read, Update, Delete functionality

"""

from sqlalchemy import cast, Text, insert, update, delete
from typing import List
from geoalchemy2.types import Geometry
from geoalchemy2.elements import WKBElement, WKTElement
from sqlalchemy.orm import Session
from sqlalchemy.dialects.postgresql import JSONB, ARRAY
from fastapi import File, UploadFile
from fastapi.responses import JSONResponse
from . import models
from .models import StationmetaCore, StationmetaChangelog, stationmeta_core_stationmeta_roles_table, \
                    stationmeta_core_stationmeta_annotations_table, \
                    CZ_enum, CV_enum, ST_enum, TA_enum
from toardb.generic.models import RS_enum, RC_enum
from .schemas import get_coordinates_from_geom, get_geom_from_coordinates, StationmetaCreate, StationmetaPatch, Coordinates
from pydantic import ValidationError
from toardb.utils.utils import get_value_from_str


def get_stationmeta_core(db: Session, station_code: str):
    db_object = db.query(models.StationmetaCore).filter(cast(models.StationmetaCore.codes,Text).contains(station_code.strip())).first()
    # there is a mismatch with coordinates and additional_metadata
    if db_object:
        if isinstance(db_object.coordinates, (WKBElement, WKTElement)):
            db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata).replace("'",'"')
    return db_object


def get_stationmeta(db: Session, station_code: str):
    db_object = db.query(models.StationmetaCore).filter(cast(models.StationmetaCore.codes,Text).contains(station_code.strip())).first()
    # there is a mismatch with coordinates and additional_metadata
    if db_object:
        if isinstance(db_object.coordinates, (WKBElement, WKTElement)):
            db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata).replace("'",'"')
    return db_object


def get_all_stationmeta_core(db: Session, skip : int = 0, limit: int = None):
    db_objects = db.query(models.StationmetaCore).offset(skip).limit(limit).all()
    for db_object in db_objects:
        # there is a mismatch with coordinates and additional_metadata
        if isinstance(db_object.coordinates, (WKBElement, WKTElement)):
            db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata).replace("'",'"')
    return db_objects


def get_all_stationmeta(db: Session, skip : int = 0, limit: int = None):
    db_objects = db.query(models.StationmetaCore).offset(skip).limit(limit).all()
    for db_object in db_objects:
        # there is a mismatch with coordinates and additional_metadata
        if isinstance(db_object.coordinates, (WKBElement, WKTElement)):
            db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata).replace("'",'"')
    return db_objects


def get_stationmeta_changelog(db: Session, station_id: int):
    return db.query(models.StationmetaChangelog).filter(models.StationmetaChangelog.station_id == station_id).all()


# is this internal, or should this also go to public REST api?
def get_unique_stationmeta_role(db: Session, role: int, contact_id: int, status: int):
    db_object = db.query(models.StationmetaRole).filter(models.StationmetaRole.role == role) \
                                                .filter(models.StationmetaRole.contact_id == contact_id) \
                                                .filter(models.StationmetaRole.status == status) \
                                                .first()
    return db_object


# is this internal, or should this also go to public REST api?
def get_unique_stationmeta_annotation(db: Session, text: str, contributor_id: int):
    db_object = db.query(models.StationmetaAnnotation).filter(models.StationmetaAnnotation.text == text) \
                                                      .filter(models.StationmetaAnnotation.contributor_id == contributor_id) \
                                                      .first()
    return db_object


def create_stationmeta(db: Session, stationmeta: StationmetaCreate):
    stationmeta_dict = stationmeta.dict()
    roles_data         = stationmeta_dict.pop('roles', None)
    annotations_data   = stationmeta_dict.pop('annotations', None)
    aux_images_data    = stationmeta_dict.pop('aux_images', None)
    aux_docs_data      = stationmeta_dict.pop('aux_docs', None)
    aux_urls_data      = stationmeta_dict.pop('aux_urls', None)
    globalmeta_data    = stationmeta_dict.pop('globalmeta', None)
    globalservice_data = stationmeta_dict.pop('globalservice', None)
    db_stationmeta = models.StationmetaCore(**stationmeta_dict)
    # there's a mismatch with coordinates --> how to automatically switch back and forth?!
    tmp_coordinates = db_stationmeta.coordinates
    db_stationmeta.coordinates = get_geom_from_coordinates(Coordinates(**db_stationmeta.coordinates))
    # there's also a mismatch with additional_metadata --> BUT: this should not be switched back!
    # in upload command, we have now: "additional_metadata": "{}"
    # but return from this method gives: "additional_metadata": {}
    # ==> there is a mismatch between model(JSONB) and schema(JSON)
    db_stationmeta.additional_metadata = str(db_stationmeta.additional_metadata).replace("'",'"')
    db_stationmeta.type_of_environment = get_value_from_str(ST_enum,db_stationmeta.type_of_environment)
    db_stationmeta.type_of_area = get_value_from_str(TA_enum,db_stationmeta.type_of_area)
    db.add(db_stationmeta)
    result = db.commit()
    db.refresh(db_stationmeta)
    # get staionmeta_core_id
    stationmeta_core_id = db_stationmeta.id
    # store roles and update association table
    if roles_data:
        for r in roles_data:
            db_role = models.StationmetaRole(**r)
            db_role.role = get_value_from_str(RC_enum,db_role.role)
            db_role.status = get_value_from_str(RS_enum,db_role.status)
            # check whether role is already present in database
            db_object = get_unique_stationmeta_role(db, db_role.role, db_role.contact_id, db_role.status)
            if db_object:
                role_id = db_object.id
            else:
                db.add(db_role)
                db.commit()
                db.refresh(db_role)
                role_id = db_role.id
            db.execute(insert(stationmeta_core_stationmeta_roles_table).values(station_id=stationmeta_core_id, role_id=role_id))
            db.commit()
    # store annotations and update association table
    if annotations_data:
        for a in annotations_data:
            db_annotation = models.StationmetaAnnotation(**a)
            # check whether annotation is already present in database
            db_object = get_unique_stationmeta_annotation(db, db_annotation.text, db_annotation.contributor_id)
            if db_object:
                annotation_id = db_object.id
            else:
                db.add(db_annotation)
                db.commit()
                db.refresh(db_annotation)
                annotation_id = db_annotation.id
            db.execute(insert(stationmeta_core_stationmeta_annotations_table).values(station_id=stationmeta_core_id, annotation_id=annotation_id))
            db.commit()
    # store aux_images
    if aux_images_data:
        for i in aux_images_data:
            db_aux_image = models.StationmetaAuxImage(**i)
            db_aux_image.station_id = stationmeta_core_id
            db.add(db_aux_image)
            db.commit()
            db.refresh(db_aux_image)
    # store aux_docs
    if aux_docs_data:
        for d in aux_docs_data:
            db_aux_doc = models.StationmetaAuxDoc(**d)
            db_aux_doc.station_id = stationmeta_core_id
            db.add(db_aux_doc)
            db.commit()
            db.refresh(db_aux_doc)
    # store aux_urls
    if aux_urls_data:
        for u in aux_urls_data:
            db_aux_url = models.StationmetaAuxUrl(**u)
            db_aux_url.station_id = stationmeta_core_id
            db.add(db_aux_url)
            db.commit()
            db.refresh(db_aux_url)
    # store globalmeta
    if globalmeta_data:
        db_global = models.StationmetaGlobal(**globalmeta_data)
        if db_global.climatic_zone:
            db_global.climatic_zone = get_value_from_str(CZ_enum,db_global.climatic_zone)
        db_global.station_id = stationmeta_core_id
        db.add(db_global)
        db.commit()
        db.refresh(db_global)
    # store globalservice
    if globalservice_data:
        db_globalservice = models.StationmetaGlobalService(**globalservice)
        db_globalservice.station_id = stationmeta_core_id
        db.add(db_globalservice)
        db.commit()
        db.refresh(db_globalservice)
    # there's a mismatch with coordinates --> how to automatically switch back and forth?!
    db_stationmeta.coordinates = tmp_coordinates
    return db_stationmeta

def patch_stationmeta(db: Session, description: str, station_id: int, stationmeta: StationmetaPatch):
    stationmeta_dict = stationmeta.dict()
    roles_data         = stationmeta_dict.pop('roles', None)
    annotations_data   = stationmeta_dict.pop('annotations', None)
    aux_images_data    = stationmeta_dict.pop('aux_images', None)
    aux_docs_data      = stationmeta_dict.pop('aux_docs', None)
    aux_urls_data      = stationmeta_dict.pop('aux_urls', None)
    globalmeta_data    = stationmeta_dict.pop('globalmeta', None)
    globalservice_data = stationmeta_dict.pop('globalservice', None)
    # there's a mismatch with coordinates --> how to automatically switch back and forth?!
    # ==> the following two commands are not working
    # ==> workaround
    # stationmeta_dict2 = {k: v for k, v in stationmeta_dict.items() if v is not None}
    # db.query(models.StationmetaCore).filter(models.StationmetaCore.id == stationm_id).update(stationmeta_dict2)
    db_obj = models.StationmetaCore(**stationmeta_dict)
    tmp_coordinates = db_obj.coordinates
    db_stationmeta = db.query(models.StationmetaCore).get(station_id)
    db_stationmeta.coordinates = get_geom_from_coordinates(db_stationmeta.coordinates)
    for k, v in stationmeta_dict.items():
        if v is not None:
            # prepare changelog entry/entries
            db_changelog = StationmetaChangelog(description=description, station_id=station_id, author_id=1, type_of_change=1)
            db_changelog.old_value=str(getattr(db_stationmeta,k))
            setattr(db_stationmeta,k,stationmeta_dict[k])
            db_changelog.new_value=str(getattr(db_stationmeta,k))
            db_changelog.description=description + f"; field: {k}"
            db.add(db_changelog)
            result = db.commit()
    db.refresh(db_stationmeta)
    # store roles and update association table
    if roles_data:
        for r in roles_data:
            # prepare changelog entry/entries
            db_changelog = StationmetaChangelog(description=description, station_id=station_id, author_id=1, type_of_change=1)
            db_changelog.old_value=str(getattr(db_stationmeta,"roles"))
            db_role = models.StationmetaRole(**r)
            db_role.role = get_value_from_str(RC_enum,db_role.role)
            db_role.status = get_value_from_str(RS_enum,db_role.status)
            # check whether role is already present in database
            db_object = get_unique_stationmeta_role(db, db_role.role, db_role.contact_id, db_role.status)
            if db_object:
                role_id = db_object.id
            else:
                db.add(db_role)
                db.commit()
                db.refresh(db_role)
                role_id = db_role.id
            db.execute(insert(stationmeta_core_stationmeta_roles_table).values(station_id=station_id, role_id=role_id))
            db_changelog.new_value=str(r)
            db.add(db_changelog)
            db.commit()
    # store annotations and update association table
    if annotations_data:
        for a in annotations_data:
            db_annotation = models.StationmetaAnnotation(**a)
            # check whether annotation is already present in database
            db_object = get_unique_stationmeta_annotation(db, db_annotation.text, db_annotation.contributor_id)
            if db_object:
                annotation_id = db_object.id
            else:
                db.add(db_annotation)
                db.commit()
                db.refresh(db_annotation)
                annotation_id = db_annotation.id
            db.execute(insert(stationmeta_core_stationmeta_annotations_table).values(station_id=station_id, annotation_id=annotation_id))
            db.commit()
    # store aux_images
    if aux_images_data:
        for i in aux_images_data:
            db_aux_image = models.StationmetaAuxImage(**i)
            db_aux_image.station_id = station_id
            db.add(db_aux_image)
            db.commit()
            db.refresh(db_aux_image)
    # store aux_docs
    if aux_docs_data:
        for d in aux_docs_data:
            db_aux_doc = models.StationmetaAuxDoc(**d)
            db_aux_doc.station_id = station_id
            db.add(db_aux_doc)
            db.commit()
            db.refresh(db_aux_doc)
    # store aux_urls
    if aux_urls_data:
        for u in aux_urls_data:
            db_aux_url = models.StationmetaAuxUrl(**u)
            db_aux_url.station_id = station_id
            db.add(db_aux_url)
            db.commit()
            db.refresh(db_aux_url)
    # store globalmeta
    if globalmeta_data:
        db_global = models.StationmetaGlobal(**globalmeta_data)
        if db_global.climatic_zone:
            db_global.climatic_zone = get_value_from_str(CZ_enum,db_global.climatic_zone)
        db_global.station_id = station_id
        db.add(db_global)
        db.commit()
        db.refresh(db_global)
    # store globalservice
    if globalservice_data:
        db_globalservice = models.StationmetaGlobalService(**globalservice)
        db_globalservice.station_id = station_id
        db.add(db_globalservice)
        db.commit()
        db.refresh(db_globalservice)
    db.commit()
    # there's a mismatch with coordinates --> how to automatically switch back and forth?!
    db_stationmeta.coordinates = tmp_coordinates
    return db_stationmeta


def delete_stationmeta_field(db: Session, station_id: int, keyword: str):
    if keyword == 'roles':
        db.execute(delete(stationmeta_core_stationmeta_roles_table).where(stationmeta_core_stationmeta_roles_table.c.station_id==station_id))
#       stationmeta_core_stationmeta_roles_table.delete().where(stationmeta_core_stationmeta_roles_table.c.station_id==station_id)
    # problem with automatic conversion of coordinates (although not explicitly fetched from database)
    # ==> next two lines are a workaround
    db_stationmeta = db.query(models.StationmetaCore).get(station_id)
    tmp_coordinates = db_stationmeta.coordinates
    db_stationmeta.coordinates = get_geom_from_coordinates(db_stationmeta.coordinates)
    db.commit()
    # there's a mismatch with coordinates --> how to automatically switch back and forth?!
    db_stationmeta.coordinates = tmp_coordinates
    return db_stationmeta