from django.core.management.base import BaseCommand, CommandError
from django.db.models import Count
from django.db.models import OuterRef, Subquery, Exists

from datetime import date
import datetime
from django.utils import timezone
import astropy
from astropy.io import ascii
import pandas as pd
import pymysql
from sqlalchemy import create_engine

from heasarc.models import HeasarcBase
from artsurvey.models import ArtSurveySource, ArtSurvey, EnergyBand, MetaSource
from srglib.utils import find_counterparts
from srgcat.models import SrgDataDump

class Command(BaseCommand):
    help = 'Initiates data dase'

    def handle(self, *args, **options):

        #srcs = ArtSurveySource.objects.all()

        """ Find first survey """
        version=12.2

        try:
            survey = ArtSurvey.objects.get(version=version)
        except ArtSurvey.DoesNotExist:
            return

        print("use ArtSurvey {}".format(survey))


        try:
            exclude_survey = ArtSurvey.objects.get(version=2.4)
        except ArtSurvey.DoesNotExist:
            return

        print("exclude ArtSurvey {}".format(exclude_survey))


        try:
            band = EnergyBand.objects.get(slug='E0')
            print("use EnergyBand {}".format(band))
        except EnergyBand.DoesNotExist:
            print("EnergyBand bot found")
            return

        try:
            exclude_band = EnergyBand.objects.get(slug='E0')
            print("use exclude EnergyBand {}".format(exclude_band))
        except EnergyBand.DoesNotExist:
            print("EnergyBand bot found")
            return


        srcs = ArtSurveySource.objects.all().filter(survey=survey)
        srcs=srcs.filter(band=band)

        subquery=MetaSource.objects.filter(artsurveysource=OuterRef("pk")).filter(artsurveysource__survey=exclude_survey).filter(artsurveysource__band=exclude_band)
        #srcs=srcs.annotate(exclude=Subquery(subquery.values("name")[:1]))
        srcs=srcs.annotate(exclude=Exists(subquery))

        for s in srcs:
            print(s,s.exclude)

        print(srcs.count())

        """
        msrcs=MetaSource.objects.annotate(num_sources=Count("artsurveysource"))
        for m in msrcs:
            print(m, m.num_sources)
        """
        self.stdout.write(self.style.SUCCESS('Done'))