""" Classes to handle actions associated with animals 

    Copyright PR Hardman 2008-2011. All rights reserved.

    Licence - see licence.txt

"""
import time
import re

import genlib

from html import animalhtml


class AnimalResources(genlib.Resource):
    """ The base Animal and Flock Resource class. 
    
        This class provides methods for working with animal and flock resources 
        from the database tables.
        
        This class should be sub-classed by the applications so that they can 
        customise the behaviour of their resources.
    """
    #
    # Flock query templates
    #
    flock_by_fullname = ("select f.flock_no from %s.flock f "
                            "where LOWER(f.flock_name) = ?;")
    
    flocks_with_prefix = ("select f.flock_no from %s.flock_prefix f "
                           "where f.tag_prefix = ?;")

    select_flocks_like_name = ("select f.flock_no from %s.flock f "
                                "where lower(f.sort_name) LIKE ?;")

    data_for_list = ("select f.flock_no, f.flock_name, "
            "cast('no' as text) as mva, cast('no' as text) as scrapie_mon, "
            "cp.tag_prefix, "
            "(p.title || ' ' || p.initials || ' ' || p.surname) as owner_name, "
            "p.surname "
            "from %s.flock f "
            "join %s.current_flock_owner co on f.flock_no = co.flock_no "
            " join %s.person p on co.owner_person_id = p.person_id "
            " join %s.current_flock_prefix cp on f.flock_no = cp.flock_no "
            " where f.flock_no in %s order by %s %s;")

    # These fields must be selected by the data_for_list query 
    list_order = {'flkno': 'flock_no', 'name': 'flock_name',
                'mva': 'mva', 'smon': 'scrapie_mon', 'prefix': 'tag_prefix', 
                'owner': 'surname'}   
                

    # +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++#
    # Animal Query templates
    #
#~     ped_query = ("select * from %s.sheep_data s where s.regn_no = ?;")
    
    ped_query = ("SELECT s.*, of.flock_name as org_flock_name, "
                "rf.flock_name as regn_flock_name, et.tag_prefix, "
                "et.indiv_no as tag_no, ot.tag_prefix as original_tag_prefix, "
                "ot.indiv_no as original_tag_no, ei.eid, i.result_code, "
                "p.allele_1, p.allele_2 "
        "FROM %s.sheep s JOIN %s.flock of ON s.originating_flock = of.flock_no "
        "JOIN %s.flock rf ON s.registering_flock = rf.flock_no "
        "JOIN (select * from %s.current_ear_tag where regn_no = ?) et "
            "ON s.regn_no = et.regn_no "
        "JOIN (select * from %s.original_ear_tag where regn_no = ?) ot "
            "ON s.regn_no = ot.regn_no "
        "LEFT JOIN (select * from %s.current_eid where regn_no = ?) ei "
            "ON s.regn_no = ei.regn_no "
        "LEFT JOIN (select * from %s.current_inspection where regn_no = ?) i "
            "ON s.regn_no = i.regn_no "
        "LEFT JOIN (select * from %s.current_prp where regn_no = ?) p "
        "ON s.regn_no = p.regn_no "
        "where s.regn_no = ?;")
                        
    progeny_root = ("select * from %s.sheep_data s where s.regn_no = ?;")
                        
    progeny_prog_sire = ("select s.regn_no, s.sort_dob, s.sex "
            "from %s.sheep s where s.sire_no = ? order by s.regn_no;")
                                 
    progeny_prog_dam = ("select s.regn_no, s.sort_dob, s.sex "
            "from %s.sheep s where s.dam_no = ? order by s.regn_no;")

    # This query is in the progeny loop so must be optimised - the use of 
    # sheep_progeny_data view is too slow on SQLite since that engine only 
    # applies the 'where' clause after all the data has been collected.    
    progeny_data = ("SELECT s.*, of.flock_name as org_flock_name, "
                    "i.result_code, p.allele_1, p.allele_2 "
                    "FROM %s.sheep s "
                    "JOIN %s.flock of ON s.originating_flock = of.flock_no "
        "LEFT JOIN (select * from %s.current_inspection where regn_no = ?) i "
            "ON s.regn_no = i.regn_no "
        "LEFT JOIN (select * from %s.current_prp where regn_no = ?) p "
            "ON s.regn_no = p.regn_no "
                    "where s.regn_no = ?;")
                        
    get_animal = ("select s.regn_no from %s.sheep s where s.regn_no = ?;")
                        
    regn_no_from_tag = ("select e.regn_no from %s.ear_tag e "
                            "where e.tag_prefix = ? and e.indiv_no = ? "
                            "order by e.regn_no;")
    
    regn_no_from_num_tag = ("select e.regn_no from %s.ear_tag e "
                            "where e.tag_prefix = ? and e.num_indiv_no = ? "
                            "order by e.regn_no;")
    

    animals_with_prefix = ("select e.regn_no from %s.ear_tag e "
                            "where e.tag_prefix = ? order by e.regn_no;")
                            
    regn_no_from_name = ("select s.regn_no from %s.sheep s "
                            "where LOWER(s.sheep_name) LIKE ? "
                            "order by s.regn_no;")
                            
    regn_no_from_fullname = ("select s.regn_no from %s.sheep s "
                                "where s.originating_flock = ? "
                                "and LOWER(s.sheep_name) LIKE ? "
                                "and s.register_code <> 'X' "
                                "order by s.regn_no;")
                            
    regn_no_from_flock = ("select s.regn_no from %s.sheep s "
                            "where s.originating_flock = ? "
                            "and s.register_code <> 'X' "
                            "order by s.regn_no;")

    def __init__(self, config):
        """ Initialise the instance attributes """
        super(AnimalResources, self).__init__(config)
        print "Instantiating AnimalResources"
        # Callables, primarily to allow for different species
        self.html_view_page = animalhtml.sheep_view_html
        self.html_form_page = animalhtml.sheep_form_html
        
        # HTML/XML templates and unconditional fragments
        self.pedcert_tmpl_name = 'pedcert_tmpl.odt'
    
    def __del__(self):
        """ Clean up """
        print "AnimalResources instance about to be destroyed"
        super(AnimalResources, self).__del__()
        
    def parse_id(self, search_arg):
        """ Present to allow this module to be compiled. 
            Must be replaced with a breed specific method
        """
        return '', {}
    
    def get_regn_nos_from_arg(self, arg):
        """ Return a list of registration numbers matching a parsed search arg 
        
            The form of the parsed arg is breed dependant and is defined in the
            breed's parse_id() method. 
        """
        regn_nos = self.dblib.query_column(self.get_animal % self.schema, (arg,))
        return regn_nos
        
        
    def find_animal(self, search_arg):
        """ Return a list of registration numbers matching the search string.
        
            The search string may be a registration number, an ear tag or a full
            or partial name.
            Return a message if a non-fatal error is found.
        """
        dup = ''
        regn_nos = []
        if re.search("[0-9]", search_arg):
            # Assume it's an regn_no or tag - pass any exception upwards
            dup, parse_data = self.parse_id(search_arg)
            
            if parse_data['idmode'] == self.ID_MODE_REGN_NO:
                # Handle a 'registration number'
                regn_nos = self.get_regn_nos_from_arg(parse_data['leader'])
                
            elif parse_data['idmode'] == self.ID_MODE_TAG:
                # Handle an ear tag - lookup the regn number from an ear-tag
                if parse_data['trailer'].isdigit():
                    regn_nos = self.dblib.query_column(self.regn_no_from_num_tag % 
                     self.schema, (parse_data['leader'], parse_data['trailer']))
                else:
                    regn_nos = self.dblib.query_column(self.regn_no_from_tag % 
                    self.schema, (parse_data['leader'], parse_data['trailer']))
                
            else:
                # Handle a Prefix
                regn_nos = self.dblib.query_column(self.animals_with_prefix % 
                                self.schema, (parse_data['leader'],))
        else:
            # Handle an animal name.
            # Use the algorithm from Paradox FINDSHEEP.Search()
            flockno = ''
            words = search_arg.split()
            pos = -1
            try: 
                pos = words.index('of')
            except ValueError:
                pass
                
            if pos != -1:
                # May be a flock suffix..
                flockname = ' '.join((words[pos:]))
                flockno = self.get_flock_by_fullname(flockname)
                if flockno:
                    animalname = ' '.join((words[0:pos]))
                
            if not flockno:
                # Either no 'of', or search for a suffix failed.
                # Hunt for a flock no
                namelist = []
                indx = 0
                for word in words:
                    namelist.append(word)
                    indx += 1
                    flockno = self.get_flock_by_fullname(
                                                            ' '.join(namelist))
                    if flockno:
                        break
             
                if not flockno:
                    # Assume the search string is just a animal name
                    animalname = search_arg
                else:
                    animalname = ' '.join((words[indx:]))
            
            if animalname != '':
                animalname.replace('*', '%')
            
            if flockno == '':
                regn_nos = self.dblib.query_column(self.regn_no_from_name % 
                                            self.schema, (animalname.lower(),))
            elif animalname == '':
                regn_nos = self.dblib.query_column(self.regn_no_from_flock % 
                                                        self.schema, (flockno,))
            else:
                regn_nos = self.dblib.query_column(self.regn_no_from_fullname % 
                                    self.schema, (flockno, animalname.lower()))

        return dup, regn_nos
    
    
    def get_pedigree(self, animal_no, generations=4, count=False):
        """ Get an animal's pedigree data.
        
            The default behaviour is to build a dictionary of data dicts.
            The dict's keys are a number representing the position in the
            pedigree reading from left to right and top to bottom 
            (in the conventional horizontal tree),
            ie a two generation pedigree would have nodes 1, 2 and 3, 
            a four generation nodes 1 - 15, etc.
        """
        
        # Initialise the variables
        currgen = 1
        queue = [(animal_no, currgen, 1)]
        ped_dict = {}
        
        # Iterate through the ancestors
        while len(queue) > 0:
            curranc = queue.pop(0)
            regn_no = curranc[0]
            currgen = curranc[1]
            node = curranc[2]

            # Adam and Eve are queued and processed, 
            # but their 'parents' are not queued
#~             datarow = self.dblib.query_row(self.ped_query % self.schema, 
#~                                                     (regn_no,), asdict=True)
            datarow = self.dblib.query_row(self.ped_query % 
                        ((self.schema,) * 8), ((regn_no,) * 6), asdict=True)
           
            if not count:
                ped_dict[(2**(currgen - 1) + node) -1] = datarow
            else:
                if (datarow['regn_no'] == self.ADAM or
                                            datarow['regn_no'] == self.EVE):
                    ped_dict['bad_parent'] = True
                
            # Queue the sire/dam nodes if the maximum depth has not been reached
            # Must queue both parents even if one is Adam or Eve as the nodes
            # must increment in twos and the test is more efficient elsewhere.
            if regn_no not in (self.ADAM, self.EVE):
                if currgen < generations:
                    node *= 2
                    queue.append((datarow['sire_no'], currgen + 1, node - 1))
                    queue.append((datarow['dam_no'], currgen + 1, node))

        return ped_dict
    

    def get_progeny(self, animal_no, limit=True, generations=4):
        """Get an animal's progeny. 
        
        The progeny are returned as a dictionary of data dicts.
        The dictionary keys are a sequential number and have no intrinsic 
        significance, except that key 0 is the subject (root) animal.
        The root animal's data dict contains the animal's full data. 
        Progenys' data dicts contain a subset of the full data.
        
        In all cases the data dict has two items 'gen', which indicates
        generation of the progeny fron the root animal, and 'progeny' which 
        gives the number of progeny of the progeny.
        
        """
        # Set the boundary year - any progeny not bred and born before this
        # year won't be added to the tree
        bdy_year = time.localtime(time.time())[0] - 3
        
        # Insert the root node in the queue
        item = 0
        rootrow = self.dblib.query_row(self.progeny_root % self.schema, 
                                    (animal_no,), asdict=True)
        if not rootrow:
            raise ValueError("Registration number '%s' not found in database" %
                                    animal_no)
        
        rootrow.update({'gen':0, 'progeny': 0}) # To make all rows the same

        queue = [(animal_no, 0, str(rootrow['sort_dob']), rootrow['sex'])]
        progeny_dict = {item: rootrow}
        progenylist = {}
        
        # Traverse the progeny
        while len(queue) > 0:
            q_item = queue.pop(0)
            regn_no = q_item[0]
            currgen = q_item[1]
            sort_dob = q_item[2]
            sex = q_item[3]
            
            # Check to see if we've already got this animal
            if progenylist.has_key(regn_no):
                continue

            # Check for progeny of this animal
            if sex == 'M':
                cmd = self.progeny_prog_sire
            else:
                cmd = self.progeny_prog_dam
            
            progeny = self.dblib.query_all_rows(cmd % self.schema,
                                                    (regn_no,), asdicts=True)
            num_prog = len(progeny)
            if limit and num_prog == 0 and int(sort_dob[0:4]) < bdy_year:
                continue

            if currgen !=  0:
                datarow = self.dblib.query_row(self.progeny_data % 
                                        ((self.schema,) * 4), ((regn_no,) * 3), 
                                        asdict = True)
                if not datarow:
                    raise ValueError("Registration number '%s' not found in "
                                                    "database" % regn_no)
               
                datarow.update({'gen':currgen,
                                'progeny': num_prog,
                                'tag_prefix': '',
                                'tag_no': ''})
                progeny_dict[item] = datarow
                progenylist[regn_no] = item
                
            item += 1
            
            # Insert the progeny in the queues
            if num_prog != 0 and currgen < generations:
                for row in progeny:
                    queue.append((row['regn_no'], currgen + 1,
                                str(row['sort_dob']), row['sex']))
           
        return progeny_dict
        

