import os
from app import db
from flask import request
from sqlalchemy import and_, desc, delete, exists, literal, select
from sqlalchemy.orm import load_only, joinedload
from app.models import DigFile, DigStore, DocFile, DocStore, FileStore, Rent, TypeDoc
from app.dao.database import commit_to_database
from app.main.common import mget_filestore_dir


def clean_file_store_table(in_db):
    stmt = (delete(FileStore).where(FileStore.summary.in_(in_db)))
    db.session.execute(stmt)
    commit_to_database()


def del_all_drafts():
    # TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax
    db.session.query(DocFile).filter(DocFile.summary.ilike('draft-doc:%')) \
        .delete(synchronize_session='fetch')
    # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%')))
    # db.session.execute(stmt)
    commit_to_database()


def del_doc_dig(doc_dig, doc_dig_id):
    if doc_dig == 'doc':
        stmt = (delete(DocFile).where(DocFile.id == doc_dig_id))
    elif doc_dig == 'dig':
        stmt = (delete(DigFile).where(DigFile.id == doc_dig_id))
    else:
        file_store = get_file_store_row(doc_dig_id)
        os.remove(os.path.join(mget_filestore_dir(), file_store.summary))
        stmt = (delete(FileStore).where(FileStore.id == doc_dig_id))
    db.session.execute(stmt)
    commit_to_database()


def del_dig_object(dig_id):
    stmt = (delete(DigStore).where(DigStore.id == dig_id))
    db.session.execute(stmt)
    commit_to_database()


def del_doc_object(doc_id):
    stmt = (delete(DocStore).where(DocStore.id == doc_id))
    db.session.execute(stmt)
    commit_to_database()


def del_draft_docs_for_rent(rent_id):
    # TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax
    db.session.query(DocFile).filter(and_(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%'))) \
        .delete(synchronize_session='fetch')
    # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%')))
    # db.session.execute(stmt)
    commit_to_database()


def dig_filename_already_exists(filename, rent_id):
    return db.session.query(exists().where(and_(DigFile.summary == filename, DigFile.rent_id == rent_id))).scalar()


def dig_store_filename_already_exists(filename, object_id, object_type_id):
    return db.session.query(exists().where(and_(DigStore.summary == filename, DigStore.object_id == object_id,
                                                DigStore.object_type_id == object_type_id))).scalar()


def execute_files_statements(docfile_filter, digfile_filter, file_store_filter, doc_stmt, dig_stmt, file_stmt):
    docfiles = db.session.execute(doc_stmt.filter(*docfile_filter)).scalars().all()
    digfiles = db.session.execute(dig_stmt.filter(*digfile_filter)).scalars().all()
    file_store_files = db.session.execute(file_stmt.filter(*file_store_filter)).scalars().all()
    return docfiles, digfiles, file_store_files


def filename_already_exists(filename):
    return db.session.query(exists().where(FileStore.summary == filename)).scalar()


def get_digfile_object(doc_object_id):
    return db.session.get(DigStore, doc_object_id)


def get_digfile_row(digfile_id):
    return DigFile.query.get(digfile_id)


def get_digfiles_all():
    return db.session.query(DigFile).all()


def get_digfiles_summary(rent_id):
    return db.session.query(DigFile).filter_by(rent_id=rent_id).order_by(desc(DigFile.time_date)).all()


def get_digfiles_for_rent(rent_id):
    return db.session.query(DigFile).filter_by(rent_id=rent_id).all()


def get_docfile(doc_id):
    doc_dig = request.args.get('doc_dig', "doc", type=str)
    rent_id = int(request.args.get('rent_id', "0", type=str))
    # new file has to be doc as new digital file uses upload function
    if doc_id == 0:
        docfile = DocFile()
        docfile.id = 0
        docfile.rent_id = int(rent_id)
        # docfile.rentcode = Rent.query.with_entities(Rent.rentcode).filter(Rent.id == rent_id).one()[0]
        docfile.summary = "email in"
        docfile.doc_text = ""
    else:
        if doc_dig == "doc":
            docfile = DocFile.query \
                .join(Rent) \
                .join(TypeDoc) \
                .with_entities(DocFile.id, DocFile.summary, DocFile.doc_text,
                               DocFile.time_date,
                               literal("doc").label('doc_dig'), Rent.rentcode, Rent.id.label("rent_id"), TypeDoc.desc) \
                .filter(DocFile.id == doc_id).one_or_none()
        else:
            docfile = DigFile.query.join(Rent).join(TypeDoc).with_entities(DigFile.id, DigFile.summary,
                                                                           DigFile.time_date,
                                                                           literal("dig").label('doc_dig'),
                                                                           Rent.rentcode, Rent.id.label("rent_id"),
                                                                           TypeDoc.desc) \
                .filter(DigFile.id == doc_id).one_or_none()

    return docfile, doc_dig


def get_docfile_object(doc_object_id):
    return db.session.get(DocStore, doc_object_id)


def get_docfile_objects(object_id, object_type_id):
    stmt = select(DocStore) \
        .where(and_(DocStore.object_id == object_id, DocStore.object_type_id == object_type_id))
    return db.session.execute(stmt).all()


def get_docfile_row(docfile_id):
    return DocFile.query.get(docfile_id)


def get_docfile_combo_meta(rent_id):
    stmt = get_docfile_combo_statement(rent_id)
    return db.session.execute(stmt).scalars().first()


def get_docfiles_combo(rent_id):
    stmt = get_docfile_combo_statement(rent_id)
    return db.session.execute(stmt).scalars().all()


def get_docfile_combo_statement(rent_id):
    return select(DocFile).where(and_(DocFile.rent_id == rent_id, DocFile.combined == True,
                                      DocFile.summary.notlike('draft-doc: %')))\
        .order_by(desc(DocFile.time_date))


# Cannot figure out how to use union_all with sqlalchemy 2.0 syntax, so reverted to combining results in python
def get_docfiles(docfile_filter, digfile_filter, file_store_filter):
    doc_stmt = select(DocFile).join(Rent) \
        .options(load_only('time_date', 'summary', 'rent_id', 'combined'), joinedload('rent').load_only('rentcode'))
    dig_stmt = select(DigFile).join(Rent) \
        .options(load_only('time_date', 'summary', 'rent_id'), joinedload('rent').load_only('rentcode'))
    file_stmt = select(FileStore).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    docfiles, digfiles, file_store_files = execute_files_statements(docfile_filter, digfile_filter, file_store_filter,
                                                                    doc_stmt, dig_stmt, file_stmt)
    return docfiles, digfiles, file_store_files


def get_docfiles_enhanced(docfile_filter, digfile_filter, file_store_filter):
    doc_stmt = select(DocFile).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    dig_stmt = select(DigFile).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    file_stmt = select(FileStore).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    docfiles, digfiles, file_store_files = execute_files_statements(docfile_filter, digfile_filter, file_store_filter,
                                                                    doc_stmt, dig_stmt, file_stmt)
    return docfiles, digfiles, file_store_files


def get_docfiles_all():
    return db.session.query(DocFile).all()


def get_docfiles_event(rent_id):
    return db.session.query(DocFile).filter_by(rent_id=rent_id).options(load_only('time_date', 'summary')).all()


def get_docfiles_for_rent(rent_id):
    return db.session.query(DocFile).filter_by(rent_id=rent_id).order_by(desc(DocFile.time_date)).all()


def get_docfiles_text(rent_id):
    return DocFile.query.filter_by(rent_id=rent_id).order_by(desc(DocFile.time_date)).all()


def get_docfile_text(doc_id):
    return db.session.query(DocFile).filter_by(id=doc_id).options(load_only('doc_text')).one_or_none()


def get_file_store_file_names():
    stmt = select(FileStore).options(load_only('summary'))
    return db.session.execute(stmt).scalars().all()


def get_file_store_files_for_rent(rent_id):
    return db.session.query(FileStore).filter_by(rent_id=rent_id).order_by(desc(FileStore.time_date)).all()


def get_file_store_row(file_store_id):
    return FileStore.query.get(file_store_id)


def get_groupdigs(group_id):
    stmt = select(DigStore).options(load_only('time_date', 'summary', 'object_id'))\
        .filter(DigStore.object_id == group_id, DigStore.object_type_id == 8)
    return db.session.execute(stmt).scalars().all()


def get_groupdocs(group_id):
    stmt = select(DocStore).options(load_only('time_date', 'summary', 'object_id'))\
        .filter(DocStore.object_id == group_id, DocStore.object_type_id == 8)
    return db.session.execute(stmt).scalars().all()


def get_typedoc_id(doctype):
    return TypeDoc.query.with_entities(TypeDoc.id).filter(TypeDoc.desc == doctype).one()[0]


def post_docfile(time_date, doc_text, rent_id, summary, combined=False):
    docfile = DocFile(time_date=time_date, doc_text=doc_text, rent_id=rent_id, summary=summary, combined=combined)
    db.session.add(docfile)
    commit_to_database()
    return docfile


def upload_docfile(docfile):
    db.session.add(docfile)
    commit_to_database()

import os
from app import db
from flask import request
from sqlalchemy import and_, desc, delete, exists, literal, select
from sqlalchemy.orm import load_only, joinedload
from app.models import DigFile, DigStore, DocFile, DocStore, FileStore, Rent, TypeDoc
from app.dao.database import commit_to_database
from app.main.common import mget_filestore_dir


def clean_file_store_table(in_db):
    stmt = (delete(FileStore).where(FileStore.summary.in_(in_db)))
    db.session.execute(stmt)
    commit_to_database()


def del_all_drafts():
    # TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax
    db.session.query(DocFile).filter(DocFile.summary.ilike('draft-doc:%')) \
        .delete(synchronize_session='fetch')
    # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%')))
    # db.session.execute(stmt)
    commit_to_database()


def del_doc_dig(doc_dig, doc_dig_id):
    if doc_dig == 'doc':
        stmt = (delete(DocFile).where(DocFile.id == doc_dig_id))
    elif doc_dig == 'dig':
        stmt = (delete(DigFile).where(DigFile.id == doc_dig_id))
    else:
        file_store = get_file_store_row(doc_dig_id)
        os.remove(os.path.join(mget_filestore_dir(), file_store.summary))
        stmt = (delete(FileStore).where(FileStore.id == doc_dig_id))
    db.session.execute(stmt)
    commit_to_database()


def del_dig_object(dig_id):
    stmt = (delete(DigStore).where(DigStore.id == dig_id))
    db.session.execute(stmt)
    commit_to_database()


def del_doc_object(doc_id):
    stmt = (delete(DocStore).where(DocStore.id == doc_id))
    db.session.execute(stmt)
    commit_to_database()


def del_draft_docs_for_rent(rent_id):
    # TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax
    db.session.query(DocFile).filter(and_(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%'))) \
        .delete(synchronize_session='fetch')
    # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%')))
    # db.session.execute(stmt)
    commit_to_database()


def dig_filename_already_exists(filename, rent_id):
    return db.session.query(exists().where(and_(DigFile.summary == filename, DigFile.rent_id == rent_id))).scalar()


def dig_store_filename_already_exists(filename, object_id, object_type_id):
    return db.session.query(exists().where(and_(DigStore.summary == filename, DigStore.object_id == object_id,
                                                DigStore.object_type_id == object_type_id))).scalar()


def execute_files_statements(docfile_filter, digfile_filter, file_store_filter, doc_stmt, dig_stmt, file_stmt):
    docfiles = db.session.execute(doc_stmt.filter(*docfile_filter)).scalars().all()
    digfiles = db.session.execute(dig_stmt.filter(*digfile_filter)).scalars().all()
    file_store_files = db.session.execute(file_stmt.filter(*file_store_filter)).scalars().all()
    return docfiles, digfiles, file_store_files


def filename_already_exists(filename):
    return db.session.query(exists().where(FileStore.summary == filename)).scalar()


def get_digfile_object(doc_object_id):
    return db.session.get(DigStore, doc_object_id)


def get_digfile_row(digfile_id):
    return DigFile.query.get(digfile_id)


def get_digfiles_all():
    return db.session.query(DigFile).all()


def get_digfiles_summary(rent_id):
    return db.session.query(DigFile).filter_by(rent_id=rent_id).order_by(desc(DigFile.time_date)).all()


def get_digfiles_for_rent(rent_id):
    return db.session.query(DigFile).filter_by(rent_id=rent_id).all()


def get_docfile(doc_id):
    doc_dig = request.args.get('doc_dig', "doc", type=str)
    rent_id = int(request.args.get('rent_id', "0", type=str))
    # new file has to be doc as new digital file uses upload function
    if doc_id == 0:
        docfile = DocFile()
        docfile.id = 0
        docfile.rent_id = int(rent_id)
        # docfile.rentcode = Rent.query.with_entities(Rent.rentcode).filter(Rent.id == rent_id).one()[0]
        docfile.summary = "email in"
        docfile.doc_text = ""
    else:
        if doc_dig == "doc":
            docfile = DocFile.query \
                .join(Rent) \
                .join(TypeDoc) \
                .with_entities(DocFile.id, DocFile.summary, DocFile.doc_text,
                               DocFile.time_date,
                               literal("doc").label('doc_dig'), Rent.rentcode, Rent.id.label("rent_id"), TypeDoc.desc) \
                .filter(DocFile.id == doc_id).one_or_none()
        else:
            docfile = DigFile.query.join(Rent).join(TypeDoc).with_entities(DigFile.id, DigFile.summary,
                                                                           DigFile.time_date,
                                                                           literal("dig").label('doc_dig'),
                                                                           Rent.rentcode, Rent.id.label("rent_id"),
                                                                           TypeDoc.desc) \
                .filter(DigFile.id == doc_id).one_or_none()

    return docfile, doc_dig


def get_docfile_object(doc_object_id):
    return db.session.get(DocStore, doc_object_id)


def get_docfile_objects(object_id, object_type_id):
    stmt = select(DocStore) \
        .where(and_(DocStore.object_id == object_id, DocStore.object_type_id == object_type_id))
    return db.session.execute(stmt).all()


def get_docfile_row(docfile_id):
    return DocFile.query.get(docfile_id)


def get_docfile_combo_meta(rent_id):
    stmt = get_docfile_combo_statement(rent_id)
    return db.session.execute(stmt).scalars().first()


def get_docfiles_combo(rent_id):
    stmt = get_docfile_combo_statement(rent_id)
    return db.session.execute(stmt).scalars().all()


def get_docfile_combo_statement(rent_id):
    return select(DocFile).where(and_(DocFile.rent_id == rent_id, DocFile.combined == True,
                                      DocFile.summary.notlike('draft-doc: %')))\
        .order_by(desc(DocFile.time_date))


# Cannot figure out how to use union_all with sqlalchemy 2.0 syntax, so reverted to combining results in python
def get_docfiles(docfile_filter, digfile_filter, file_store_filter):
    doc_stmt = select(DocFile).join(Rent) \
        .options(load_only('time_date', 'summary', 'rent_id', 'combined'), joinedload('rent').load_only('rentcode'))
    dig_stmt = select(DigFile).join(Rent) \
        .options(load_only('time_date', 'summary', 'rent_id'), joinedload('rent').load_only('rentcode'))
    file_stmt = select(FileStore).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    docfiles, digfiles, file_store_files = execute_files_statements(docfile_filter, digfile_filter, file_store_filter,
                                                                    doc_stmt, dig_stmt, file_stmt)
    return docfiles, digfiles, file_store_files


def get_docfiles_enhanced(docfile_filter, digfile_filter, file_store_filter):
    doc_stmt = select(DocFile).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    dig_stmt = select(DigFile).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    file_stmt = select(FileStore).join(Rent) \
        .options(joinedload('rent').load_only('rentcode'))
    docfiles, digfiles, file_store_files = execute_files_statements(docfile_filter, digfile_filter, file_store_filter,
                                                                    doc_stmt, dig_stmt, file_stmt)
    return docfiles, digfiles, file_store_files


def get_docfiles_all():
    return db.session.query(DocFile).all()


def get_docfiles_event(rent_id):
    return db.session.query(DocFile).filter_by(rent_id=rent_id).options(load_only('time_date', 'summary')).all()


def get_docfiles_for_rent(rent_id):
    return db.session.query(DocFile).filter_by(rent_id=rent_id).order_by(desc(DocFile.time_date)).all()


def get_docfiles_text(rent_id):
    return DocFile.query.filter_by(rent_id=rent_id).order_by(desc(DocFile.time_date)).all()


def get_docfile_text(doc_id):
    return db.session.query(DocFile).filter_by(id=doc_id).options(load_only('doc_text')).one_or_none()


def get_file_store_file_names():
    stmt = select(FileStore).options(load_only('summary'))
    return db.session.execute(stmt).scalars().all()


def get_file_store_files_for_rent(rent_id):
    return db.session.query(FileStore).filter_by(rent_id=rent_id).order_by(desc(FileStore.time_date)).all()


def get_file_store_row(file_store_id):
    return FileStore.query.get(file_store_id)


def get_groupdigs(group_id):
    stmt = select(DigStore).options(load_only('time_date', 'summary', 'object_id'))\
        .filter(DigStore.object_id == group_id, DigStore.object_type_id == 8)
    return db.session.execute(stmt).scalars().all()


def get_groupdocs(group_id):
    stmt = select(DocStore).options(load_only('time_date', 'summary', 'object_id'))\
        .filter(DocStore.object_id == group_id, DocStore.object_type_id == 8)
    return db.session.execute(stmt).scalars().all()


def get_typedoc_id(doctype):
    return TypeDoc.query.with_entities(TypeDoc.id).filter(TypeDoc.desc == doctype).one()[0]


def post_docfile(time_date, doc_text, rent_id, summary, combined=False):
    docfile = DocFile(time_date=time_date, doc_text=doc_text, rent_id=rent_id, summary=summary, combined=combined)
    db.session.add(docfile)
    commit_to_database()
    return docfile


def upload_docfile(docfile):
    db.session.add(docfile)
    commit_to_database()

# TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%'))) # db.session.execute(stmt) # TODO: No sqlalchemy 2.0 documentation on how to implement synchronize_session, so using 1.4 syntax # stmt = (delete(DocFile).where(DocFile.rent_id == rent_id, DocFile.summary.ilike('draft-doc:%'))) # db.session.execute(stmt) # new file has to be doc as new digital file uses upload function # docfile.rentcode = Rent.query.with_entities(Rent.rentcode).filter(Rent.id == rent_id).one()[0] # Cannot figure out how to use union_all with sqlalchemy 2.0 syntax, so reverted to combining results in python