#!/usr/bin/python

# match orange-stickered to reserve
# match unstickered to circulating
# etc.

import sys

import mitsfs

d = mitsfs.dexdb()

((inventory_code, inventory_id, inventory_desc),) = \
    d.cursor.execute('select inventory_code, inventory_id, inventory_desc'
                     ' from inventory order by inventory_stamp desc limit 1')

print '%s (%s)' % (inventory_desc, inventory_code)

visibility_dysphoria = set(d.cursor.execute(
    "select distinct a.title_id"
    " from book a join book b"
    "  on a.title_id = b.title_id and a.book_id != b.book_id"
    " where a.book_series_visible != b.book_series_visible"
    ))

found_tags = list(d.cursor.execute(
    'select distinct found_tag from inventory_found'
    ' where inventory_id = %s',
    (inventory_id,)
    ))

unserved_shelfcodes = list(d.cursor.execute(
    'select distinct shelfcode'
    ' from inventory_entry natural join inventory_missing natural join shelfcode'
    '  natural left join shelfcode_format'
    ' where inventory_id=%s and format_id is null'
    ' order by shelfcode',
    (inventory_id,)
    ))

if unserved_shelfcodes:
    print 'SHELFCODES WITHOUT SPECIFIED FORMATS DETECTED'
    print
    print "(Go tell the avatar of libcomm if you ain't they)"
    print "((If you are they, what are you standing there for?))"
    print
    print ' '.join(sorted(unserved_shelfcodes))
    raise Exception('Eldritch Horror detected, bailing...')

def pdquery(d, q, *args):
    return list(mitsfs.propdict(zip([col[0] for col in d.cursor.description], row))
                for row in d.cursor.execute(q, args))

def matches(found_tag, match_orange=True):
    # get found: inventory_found_id, title_id, format, orange [not reshelved and inventory_entry_id is null]
    found = pdquery(d,
                    'select title_id, inventory_found_id, orange, format, format_id'
                    ' from inventory_found natural join format'
                    ' where not inventory_reshelved and inventory_entry_id is null'
                    '  and inventory_id = %s and found_tag = %s', inventory_id, found_tag)

    # get missing: title_id, shelf_id, missing_count [missing and missing_count != 0] ??book_series_visible?
    matches=[]


    # candidate set is a box name and a restriction
    # target set is all missing books, possibly restricted by reservedness


    # candidates (found and targets)
    # iterate through found;
    #  if there is a set of matched targets, pick one
    #  add pair to matchset
    for candidate in found:
        args = ('R' if candidate.orange else 'C', ) if match_orange else ()
        orangep = ' and shelfcode_type = %s' if match_orange else ''
        targets = pdquery(d,
                          'select inventory_entry_id, shelfcode, missing_count'
                          ' from inventory_missing natural join inventory_entry natural join shelfcode'
                          ' where title_id = %s and shelfcode_id in (select shelfcode_id from shelfcode_format where format_id = %s)'
                          '  and missing and missing_count > (select count(inventory_found.inventory_entry_id)'
                          '                                   from inventory_found'
                          '                                   where inventory_found.inventory_entry_id = inventory_entry.inventory_entry_id'
                          '                                    and inventory_found.inventory_id = inventory_entry.inventory_id)'
                          '  and inventory_id=%s'
                          + orangep,
                          candidate.title_id, candidate.format_id,
                          inventory_id,
                          *args
                          )
        if targets:
            matches.append((candidate, targets[0]))

    return matches;


def printmatches(matches, output=sys.stdout ):
    for (found, target) in matches:
        print >>output, \
              '%-8.8s' % target.shelfcode, \
              '%3.3s' % found.format, \
              '*' if found.title_id in visibility_dysphoria else ' ', \
              str(mitsfs.title(d, found.title_id)), \
              found.inventory_found_id

def markmatches(matches, found_tag, output=sys.stdout):
    ((resolving_id,),) = d.cursor.execute("select nextval('id_seq')").fetchall()

    print >>output, 'box',found_tag,'resolving_id',resolving_id
    print >>output

    for (found, target) in matches:
        print >>output, '%-8.8s' % target.shelfcode, \
              '%3.3s' % found.format, \
              '*' if found.title_id in visibility_dysphoria else ' ', \
              str(mitsfs.title(d, found.title_id)), \
              found.inventory_found_id
        #  set an inventory_entry
        d.cursor.execute('update inventory_found'
                         ' set inventory_entry_id = %s, resolving_id = %s'
                         ' where inventory_found_id = %s',
                         (target.inventory_entry_id, resolving_id,
                          found.inventory_found_id)
                         )
    d.db.commit();

def missing(d):
    rdex = mitsfs.dex(zerok=True)
    c = d.getcursor()
    for shelfcode, title_id, missing, found in \
        c.execute('select shelfcode, title_id, missing_count, found_count'
                  ' from inventory_entry'
                  ' natural join shelfcode'
                  ' natural join inventory_missing'
                  ' natural left join (select inventory_entry_id, count(inventory_entry_id) as found_count'
                  '  from inventory'
                  '  natural join inventory_found'
                  '  where inventory_entry_id is not null'
                  '   and inventory_id=%s group by inventory_entry_id) as found_count_tmp'
                  ' where inventory_id=%s', (inventory_id, inventory_id)):
        if found is None:
            count = missing
        else:
            count = missing - found
        if count == 0:
            continue
        line = mitsfs.dexline(line = d.get(mitsfs.propdict(title_id=title_id)),
                              codes = {shelfcode: -count})
        if count > 0:
            rdex.add(line)
        elif count < 0:
            print '# more found than missing: ', line
    return rdex

def found(d):
    founddex = mitsfs.dex()
    founddexes = {}
    hassledexes = {}
    huhdexes = {}

    c = d.getcursor()

    boxes = list(c.execute('select distinct found_tag from inventory_found where inventory_id=%s',
                           (inventory_id,)))
    for i in boxes:
        founddexes[i] = mitsfs.dex()
        hassledexes[i] = mitsfs.dex()
        huhdexes[i] = mitsfs.dex()

    for title_id, box, fmt in \
        c.execute('select title_id, found_tag, format'
                  ' from inventory'
                  ' natural join inventory_found'
                  ' natural join format'
                  ' where inventory_id = %s and inventory_entry_id is null and not inventory_reshelved', (inventory_id,)):
        line = d.get(mitsfs.propdict(title_id=title_id))
        codes = line.codes
        try:
            xline = founddex[line]
            codes += xline.codes
        except KeyError:
            pass
        huhline = mitsfs.dexline(line = line, codes={(fmt if fmt != '?' else 'WRD'): 1})
        if int(line.codes) == 0 or fmt == '?':
            huhdexes[box].add(huhline)
        elif anycodes(line.codes, ('S', 'L', 'P', 'LP', 'VLP', 'H', 'VLH', 'C/P', 'R/H', 'SR-LP', 'SR-H', 'SR-P', 'SR-VLH', 'SR-VLP')):
            # fiction
            circulating = sum(line.codes[code] for code in ('H', 'VLH', 'C/P'))
            reserve = sum(line.codes[code] for code in ('P', 'LP', 'VLP'))
            if not reserve and fmt in ('P', 'LP', 'VLP'):
                newline = mitsfs.dexline(line = line, codes={{'P': 'S', 'LP': 'L', 'VLP': 'VLP'}[fmt]: 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            elif circulating < 2:
                shelfcode = {'H':'L', 'LP':'L', 'VLP': 'VLH', 'VLH': 'VLH', 'P':'S'}[fmt]
                newline = mitsfs.dexline(line = line, codes={shelfcode: 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            else:
                hassledexes[box].add(huhline)
        elif anycodes(line.codes, ('VLPA', 'PA', 'A', 'LPA', 'HA', 'C/PA', 'SR-HA', 'SR-LPA', 'SR-PA', 'SR-VLPA', 'SR-VLHA', 'SA', 'LA')):
            circulating = sum(line.codes[code] for code in ('HA', 'C/PA'))
            reserve = sum(line.codes[code] for code in ('PA', 'LPA', 'VLPA'))
            if not reserve and fmt in ('P', 'LP', 'VLP'):
                newline = mitsfs.dexline(line = line, codes={{'P': 'SA', 'LP': 'LA', 'VLP': 'VLPA'}[fmt]: 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            elif not circulating:
                shelfcode = {'H':'LA', 'LP':'LA', 'VLH': 'LA', 'VLP': 'LA', 'P':'SA'}[fmt]
                newline = mitsfs.dexline(line = line, codes={shelfcode: 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            else:
                hassledexes[box].add(huhline)
        elif anycodes(line.codes, ('C/REF-P', 'C/REF-H', 'R/REF-P', 'R/REF-H')):
            circulating = sum(line.codes[code] for code in line.codes if code[0] == 'C')
            reserve = sum(line.codes[code] for code in line.codes if code[0] == 'R')
            if not reserve:
                newline = mitsfs.dexline(line = line, codes={'R/REF-'+('P' if fmt == 'P' else 'H'): 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            elif not circulating:
                shelfcode = 'C/REF-'+('P' if fmt == 'P' else 'H')
                newline = mitsfs.dexline(line = line, codes={shelfcode: 1})
                founddex.add(newline)
                founddexes[box].add(mitsfs.dexline(newline))
            else:
                hassledexes[box].add(huhline)
        else:
            huhdexes[box].add(huhline)
    return founddexes, hassledexes, huhdexes

def anycodes(codes, codeset):
    return any(code in codes for code in codeset)

def writeto(founddexes, hassledexes, huhdexes, fp=sys.stdout):
    for k in founddexes.keys():
        print >>fp, chr(12)+k
        print >>fp,''
        print >>fp,'FOUND (sticker as necessary given that they were misshelved before, and shelve)'
        print >>fp,founddexes[k]
        print >>fp,''
        print >>fp,'HASSLE (box)'
        print >>fp,hassledexes[k]
        if huhdexes[k]:
            print >>fp,''
            print >>fp,'HUH (give to Karl)'
            print >>fp,huhdexes[k]