import logging
import time

from service.tasks.data_model import list_caw_schemas
from service.utils.aws_connectors import run_query
from service.utils.data_model_utils import (
    change_collection_status,
    get_related_collections,
    get_rendered_sql_template,
    get_source_from_collection,
)
from service.utils.s3_file_operations import s3_delete

logger = logging.getLogger(__name__)


def _start_delete_collection(schema, user_id, collection_id, related=True):
    notify_ids = collection_id if isinstance(collection_id, list) else [collection_id]
    for cid in notify_ids:
        change_collection_status(schema, user_id, cid, "deleting")


def _delete_collection(schema, management_schema, user_id, collection_id, related=True):
    start_time = time.time()
    template_sql = "delete_collection.sql"
    """Getting the list of collection ids (source + child)"""
    notify_ids = collection_id if isinstance(collection_id, list) else [collection_id]
    if related:
        collection_ids = get_related_collections(
            schema=schema, collection_id=collection_id, no_parents=True
        )
    else:
        collection_ids = notify_ids

    c_map = {}
    if collection_ids:
        sources = get_source_from_collection(schema, collection_ids)
        for cid in notify_ids:
            c_map[cid] = change_collection_status(schema, user_id, cid, "deleting")

        # if no related collections were found, it's probably because non-existant or repeted delete.
        params = {
            "schema_name": schema,
            "management_schema": management_schema,
            "collection_ids": ",".join([str(x) for x in collection_ids]),
        }
        final_sql = get_rendered_sql_template(params, template_sql)
        run_query(final_sql)

        # unused profile id cleanup from unified management_schema fan table:
        if schema.startswith("a"):  # alliance schema, just clean up normally:
            profile_cleanup_query = (
                f"DELETE FROM {schema}.fan "
                f"WHERE id IN (SELECT id FROM {schema}.fan f "
                f"LEFT JOIN {schema}.collection_fan cf ON f.id = cf.fan_id "
                f"WHERE cf.fan_id IS NULL); "
                f"ANALYZE {schema}.fan; "
            )
        else:
            profile_cleanup_components = []
            for n, c_schema in enumerate(
                list_caw_schemas(management_schema=management_schema, alliances=False)
            ):
                profile_cleanup_components.append(
                    f"comp_{n} AS (SELECT id FROM {management_schema}.fan f "
                    f"LEFT JOIN {c_schema}.collection_fan cf ON f.id = cf.fan_id "
                    f"WHERE cf.fan_id IS NULL)"
                )
            profile_cleanup_query = (
                f"WITH {','.join(profile_cleanup_components)} "
                f"DELETE FROM {management_schema}.fan WHERE "
                f"{' AND '.join([f'id in (SELECT id FROM comp_{n})' for n in range(len(profile_cleanup_components))])};"
                f"ANALYZE {management_schema}.fan;"
            )

        run_query(profile_cleanup_query)
        for cid in notify_ids:
            change_collection_status(
                schema, user_id, cid, "deleted", collection_backup=c_map[cid]
            )

        #  delete S3 source
        for source in sources:
            s3_delete(source)

        elapsed_time = time.time() - start_time
        logger.info(
            f"{schema} - successfully deleted collection {collection_id} in {elapsed_time} seconds"
        )
        return {}
    else:
        raise RuntimeError("Collection not found, already deleted?")