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

"""

from sqlalchemy import cast, Text, insert
from typing import List
from geoalchemy2.types import Geometry
from sqlalchemy.orm import Session
from sqlalchemy.dialects.postgresql import JSONB, ARRAY
from fastapi import File, UploadFile
from fastapi.responses import JSONResponse
from fastapi.encoders import jsonable_encoder
from . import models
from .models import stationmeta_core_stationmeta_roles_table, stationmeta_core_stationmeta_annotations_table
from .schemas import get_coordinates_from_geom, get_geom_from_coordinates, StationmetaCreate, Coordinates
from pydantic import ValidationError


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:
        db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata)
    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:
        db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata)
    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
        db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata)
    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
        db_object.coordinates = get_coordinates_from_geom(db_object.coordinates)
        db_object.additional_metadata = str(db_object.additional_metadata)
    return db_objects


# is this internal, or should this also go to public REST api?
def get_unique_stationmeta_role(db: Session, role: int, person_id: int, status: int):
    db_object = db.query(models.StationmetaRole).filter(models.StationmetaRole.role == role) \
                                                .filter(models.StationmetaRole.person_id == person_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)
    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)
            # check whether role is already present in database
            db_object = get_unique_stationmeta_role(db, db_role.role, db_role.person_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:
        for g in globalmeta_data:
            db_global = models.StationmetaGlobal(**g)
            db_global.station_id = stationmeta_core_id
            db.add(db_global)
            db.commit()
            db.refresh(db_global)
    # store globalservice
    if globalservice_data:
        for s in globalservice_data:
            db_globalservice = models.StationmetaGlobalService(**s)
            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