#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-
"""clippingkeeper.py: show a simple form to save a weblog feed's entry for clipping
To customize:
- change the appropriate values in clippingkeeper.cfg
"""
__author__ = "Andrew Ittner <http://rhymingpanda.com/>"
__version__ = "0.2.4, $Revision: 1802 $"
__date__ = "$Date: 2009-09-07 19:46:42 -0700 (Mon, 07 Sep 2009) $"
__copyright__ = "Copyright (c) 2007 Andrew Ittner"
__license__ = "Expat (aka MIT)"

# built-in
import cgi, traceback, os, cgitb, datetime, ConfigParser, types
# 3rd-party
import HTMLTags as htags
from pyPgSQL import PgSQL
import mx.DateTime as mxDT

# enable trackback on errors
cgitb.enable()

# global variables
global lstcategories

# list of categories for filing an entry
lstcategories = []

def main():
    """primary routine: check if this is show or save, handle data appropriately
    possible 'action' querystring values:
    start: show the blank form
    save: save the data & show the new data
    test: show basic info (cgi testing)
    view: view all records in db
    update: set isnew=1 on selected entries & show them
    info: show bookmarklets and other data
    """
    # get options first
    getoptions()
    
    # prep vars
    strform = '' # form for data entry or viewing
    strtitle = '' # <title> contents
    strh1 = '' # <h1> contents
    stage = 0 #0=show form for editing; 10 = save data & show results
    strqs = '' # for debugging
    strqstitle = '' # title param, if any
    strqsurl = '' # url param, if any
    
    dateadded = None
    #dbg = {'dbpath_absolute': os.path.abspath(DBPATH),
           #'dbpathexists_absolute': os.path.exists(os.path.abspath(DBPATH)),
           #'dbpathexists': os.path.exists(DBPATH)}
    dbg = {'dbhost': DBHOST,
           'dbname': DBNAME,
           'dbuser': DBUSER}
    
    qs = os.environ.get('QUERY_STRING', None)
    strqs = unicode(qs)

    if qs == None:
        # assume this is the start; show form
        stage = 0
    else:
        # split it out
        dctqs = cgi.parse_qs(qs)
        # check for action
        if dctqs.has_key('action'):
            if dctqs['action'][0] == 'start':
                stage = 0
            elif dctqs['action'][0] == 'save':
                stage = 10
                dateadded = datetime.datetime.now()
            elif dctqs['action'][0] == 'test':
                stage = 200
            elif dctqs['action'][0] == 'view':
                stage = 300
            elif dctqs['action'][0] == 'update':
                stage = 400
            elif dctqs['action'][0] == 'info':
                stage = 500
            else:
                # unknown stage
                stage = -1
                strform = "unknown stage; dctqs['action'] == %s" % dctqs['action']
        else:
            stage = 0
            
        # per http://www.voidspace.org.uk/python/articles/cgi_web_applications_two.shtml
        #encoding = dctqs.get('_charset_', 'UTF8')

        if dctqs.has_key('title'):
            strqstitle = dctqs['title'][0] #.decode(encoding)
        else:
            strqstitle = '' #'no title given'
            
        if dctqs.has_key('url'):
            strqsurl = dctqs['url'][0]#.decode(encoding)
            
    # what to do?
    if stage == 0:
        strtitle = 'Add entry'
        strh1 = 'Add an entry'
        strform = makeform(False, title=unicode(strqstitle, 'utf-8'), url=unicode(strqsurl, 'utf-8'))
    elif stage == 10:
        strtitle = 'Saved entry'
        objform = cgi.FieldStorage()
        # deal with unicode title
        thetitle = objform.getvalue('inputtitle', '').decode('latin_1').encode('utf-8')
        strform = makeform(True, 
                           title=unicode(thetitle, 'utf-8'), 
                           url=objform.getvalue('inputurl'),
                           category=objform.getvalue('inputcats', '{no category}'),
                           text=objform.getvalue('inputcktext'),
                           dateadded=dateadded)
        tplresults = savedata(title=thetitle, 
                              url=objform.getvalue('inputurl'),
                              category=objform.getvalue('inputcats', '{no category}'),
                              text=objform.getvalue('inputcktext'),
                              dateadded=dateadded)
        if tplresults[0]:
            # saved successfully
            strh1 = 'Entry save results (new id: %s)' % tplresults[1]
        else:
            # problem saving
            strh1 = 'Failed to save: %s' % tplresults[1]

    elif stage == 200:
        # testing; retrieve test.txt & display
        strtitle = 'Testing'
        strh1 = 'Testing area'
        testcontents = ''
        try:
            testcontents = file('clippingkeeper.cfg', 'r').read()
        except:
            testcontents = traceback.format_exc()
            
        strform = str(htags.P('Contents of "clippingkeeper.cfg" in the same directory as clippingkeeper.py.') + \
                      htags.PRE(testcontents))

    elif stage == 300:
        # show existing entries
        strtitle = 'View all records'
        strh1 = 'Viewing all records'
        # view type: all, new, used
        viewtype = -1
        if not dctqs.has_key('view'):
            # view all
            viewtype = -1
        elif dctqs['view'][0] == 'new':
            # view new (isnew=1)
            viewtype = 1
        elif dctqs['view'][0] == 'used':
            viewtype = 0
        else:
            # default to viewtype = -1
            pass
        
        result = getdata(viewtype=viewtype)
        if result[0]:
            entries = result[1]
            # make form to mark entries as used
            strform = '<form id="updateform" action="clippingkeeper.py?action=update" method="POST">'
            for category in sorted(entries.keys()):
                strform += makecategoryview(category, entries[category], True)
            strform += '\n<input type="submit"/>\n</form>'
        else:
            strform = 'getdata() failed: <pre>%s</pre>' % result[1]
            
    elif stage == 400:
        strtitle = 'Updated records'
        strh1 = 'Records recently marked "used"'
        strform = ''
        # get form contents
        form = cgi.FieldStorage()
        if form.has_key('entries'):
            # update db
            entriestoset = tuple([int(x) for x in form.getlist('entries')])
            strform = str(htags.P('entries to set: %s' % str(entriestoset)))
            if not updateentries(entriestoset):
                # warn user
                strform = str(htags.P('updateentries(%s) failed.' % str(entriestoset)))
            else:
                # get list of updated entries
                strform += str(htags.P('entries set successfully'))
                result = getdata(entriestoset)
                #strform += str(htags.P(str(result)))
                if result[0]:
                    entries = result[1]
                    for category in sorted(entries.keys()):
                        strform += makecategoryview(category, entries[category], False)
                        
                    strform += "<hr/>"
                    strform += makeentriesoutputbox('rest', entries.values()[0])
                else:
                    strform += str(htags.P('getdata failed'))
            
        else:
            for k in form.keys():
                if k == 'entries':
                    strform += str(htags.P('Entries = %s' % form.getlist(k)))
                else:
                    strform += str(htags.P('%s = %s' % (cgi.escape(k), cgi.escape(form.getvalue(k, '')))))

    elif stage == 500:
        # info
        strtitle = 'Information about clippingkeeper'
        strh1 = 'Info'
        servername = os.environ.get('SERVER_NAME', 'SERVER')
        JS_TITLEANDURL = """javascript:window.open('http://%(servername)s/clippingkeeper.py?title=' + encodeURI(document.title) + '&url=' + encodeURIComponent(document.URL), 'es');return;"""
        JS_SELECTION = """javascript:if (top.frames.length > 0){for (var i=0; i<top.frames.length;i++){var r=top.frames[i].document.selection.createRange();if (r.text.length>0){break;}}} else {var r=document.selection.createRange();}var
 w=window.open('http://%(servername)s/clippingkeeper.py?title=' + encodeURI(r.text) + '&url=' + encodeURIComponent(r.parentElement().toString()), 'es');"""
        tags = [htags.H2('Bookmarklets'), 
                htags.P('The following are the two types of javascript bookmarklets for accessing clippingkeeper.' + \
                        'Currently, these are only tested on Opera.')]
        tags.append(htags.H3('Single page'))
        tags.append(htags.P('uses document.title and document.URL'))
        tags.append(htags.P(htags.A('Title and url', Href=JS_TITLEANDURL % {'servername': servername})))
        tags.append(htags.H3('Selected text'))
        tags.append(htags.P('uses selected text in current document OR first document in set of frames that has selected text'))
        tags.append(htags.P(htags.A('Selected text', Href=JS_SELECTION % {'servername': servername})))
        tags.append(htags.H2('Notes'))
        tags.append(htags.P('Replace "SERVER" with the server and port combination that serves clippingkeeper.py, if the server and port were not customized.'))
        strform = '\n'.join([unicode(h) for h in tags])

    else:
        strtitle = "Limbo"
        strh1 = "A problem occurred."
        strform = "Unanticipated code path."
    
    # return value
    headers = u'Content-Type: text/html\nCache-Control: no-cache\n\n'
    doctype = u'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"\n' + \
            '"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">\n'
    links = u' | '.join(linklist())
    strreturn = u'%(headers)s%(doctype)s<html><head><title>%(strtitle)s</title>\n' + \
              '<link rel="stylesheet" type="text/css" href="static/clippingkeeper.css"/></head>\n' + \
              '<body>\n<h1>%(strh1)s</h1>\n%(strform)s\n<!--\nqs: %(strqs)s\n%(dbg)s\n-->\n' + \
              '<p>%(links)s</p></body>\n</html>'
    
    return strreturn.encode('utf-8') % vars() #{'title': '?', 'form': strform, 'h1': '?'}

def linklist():
    """return a list of the available internal links as anchor elements
    returns list"""
    links = [htags.A('start', Href='%s?action=start' % CGIURL),
             htags.A('view all', Href='%s?action=view' % CGIURL),
             htags.A('view new', Href='%s?action=view&view=new' % CGIURL),
             htags.A('view used', Href='%s?action=view&view=used' % CGIURL),
             htags.A('info', Href='%s?action=info' % CGIURL)]
    return [str(a) for a in links]
    
def makeform(reportvalues=False, title='', url='', category='', 
             text='', dateadded=None):
    """create the <form> element with empty values
    returns string
    NOTE: assumes title, url, category and text are ALL UNICODE STRINGS suitable for encoding to utf-8;
    caller must set them properly"""
    try:
        # force title to unicode
        #title = unicode(title, 'utf-8')
        ##thetitle = objform.getvalue('inputtitle', '').decode('latin_1').encode('utf-8')
        #title = title.decode('latin_1').encode('utf-8')
        
        # change &raquo; to single greater-than symbol
        ## <!ENTITY raquo  CDATA "&#187;" -- right-pointing double angle quotation mark
        ##                          = right pointing guillemet, U+00BB ISOnum -->
        ## NOTE: &raquo; escaped as %C2%BB
        if u'\xbb' in title:#title.decode('utf-8'):
            title = title.replace(u'\xbb', u'>')
            
        # convert these to spaces
        for thecharacter in (u'\xc2', u'\xa0'):
            title = title.replace(thecharacter, ' ')
            
        # strip whitespace
        title = title.strip()

        if reportvalues:
            # show the parameters
            tagform = htags.UL(htags.LI('<strong>Date added</strong>: %s' % cgi.escape(dateadded.isoformat())) + \
                               htags.LI('<strong>Title</strong>: %s' % cgi.escape(title or '')) + \
                               htags.LI('<strong>URL</strong>: %s' % cgi.escape(url or '')) + \
                               htags.LI('<strong>Category</strong>: %s' % cgi.escape(category or '')) + \
                               htags.LI('<strong>Text</strong>: %s' % cgi.escape(text or '')))
            return str(tagform)
        else:
            # escape quotes 
            titlevalue = cgi.escape(title)
            titlevalue = titlevalue.replace('"', '&quot;')
            tagtitle = htags.INPUT(Value=titlevalue, Name='inputtitle', Type="text", Size="80")
            tagurl = htags.INPUT(Value=cgi.escape(url), Name='inputurl', Type="text", Size="80")
            tagcktext = htags.TEXTAREA(Name='inputcktext', Id='inputcktext', Rows="10", Cols="80")
            tagsubmit = htags.INPUT(Name='submit', Id='btnsubmit', Type='submit')
            tagbr = htags.BR()
            tagform = htags.FORM(htags.TEXT("Title: ") + tagtitle + tagbr + \
                                 htags.TEXT("URL: ") + tagurl + tagbr + \
                                 htags.TEXT("Category: %s" % makeselect()) + tagbr + \
                                 htags.TEXT("Text:") + tagbr + tagcktext + tagbr + \
                                 tagsubmit, Action='%s?action=save' % CGIURL, Method="POST")
            return unicode(tagform).decode('utf-8')
    except:
        return 'error in makeform() (error:\n<pre>%s</pre>)' % traceback.format_exc()
    
def makeselect():
    """return a <select> element with the user-defined categories
    returns string"""
    try:
        lstoptions = []
        tmploption = '<option value="%(value)s">%(value)s</option>\n'
        for cat in sorted(lstcategories):
            lstoptions.append(tmploption % {'value': cat})
            
        return '<select id="inputcats" name="inputcats" size="1">\n%s</select>' % ''.join(lstoptions)
        
    except:
        return 'error in makeselect'
    
#def savedata(title='', url='', category='', 
             #fulltext='', selectedtext='', dateadded=None):
    #"""save the given data as a new record in the DBPATH db
    #returns (T/F, new ckentries.id/err msg)"""
    #try:
        ## open the db
        #conn = sqlite.connect(DBPATH)
        ## get a cursor
        #cur = conn.cursor()
        ## build SQL
        #strsql = 'INSERT INTO ckentries (title, link, fulltext, selectedtext, dateadded, isnew) ' + \
               #'VALUES(?, ?, ?, ?, ?, 0)'
        ## exec sql
        #cur.execute(strsql, (title, url, fulltext, selectedtext, dateadded))
        ## commit changes
        #conn.commit()
        ## get last row id
        #intlastrowid = cur.lastrowid
        ## close cursor & connection
        #cur.close()
        #conn.close()
        ## return results
        #return True, intlastrowid
    #except:
        #return False, traceback.format_exc()

def savedata(title='', url='', category='', 
             text='', dateadded=None):
    """save the given data as a new record in the DBPATH db
    returns (T/F, new ckentries.id/err msg)"""
    ##>>> from pyPgSQL import PgSQL
    ##>>> cx = PgSQL.connect(database="mydb", client_encoding="utf-8", unicode_results=1)
    ##>>> cu = cx.cursor()
    ##>>> cu.execute("set client_encoding to unicode")
    ##>>> cu.execute("insert into test(v) values (%s)", (u'\x99sterreich',))
    ##>>> cu.execute("select v from test")
    ##>>> cu.fetchone()
    ##[u'\x99sterreich']

    try:
        # open the db
        conn = PgSQL.connect(host=DBHOST, database=DBNAME, user=DBUSER, password=DBPW, 
                             client_encoding='utf-8', unicode_results=1)
        # get a cursor
        cur = conn.cursor()
        # build SQL
        strsql = 'INSERT INTO ckentries (title, link, cktext, category, isnew) ' + \
               'VALUES(%s, %s, %s, %s, 1)'
        # exec sql
        cur.execute('set client_encoding to unicode')
        #cur.execute(strsql, (title.encode('ascii', 'xmlcharrefreplace'),
                             #url.encode('ascii', 'xmlcharrefreplace'),
                             #text.encode('ascii', 'xmlcharrefreplace'),
                             #category))
        cur.execute(strsql, (title,
                             url,
                             text,
                             category))
        # commit changes
        conn.commit()
        # get last row id
        #intlastrowid = cur.oidValue
        cur.execute("SELECT CURRVAL('ckentries_id_seq');")
        intlastrowid = cur.fetchone()[0]
        # close cursor & connection
        cur.close()
        conn.close()
        # return results
        return True, intlastrowid
    except:
        return False, traceback.format_exc()
    
def getdata(entriestoget=(), viewtype=-1):
    """retrieve data from db
    viewtype: -1==all, 0==used, 1==new
    dict format: {category: [{id, anchor, dateadded}, ]}
    returns (T/F, dict/err msg)"""
    try:
        # open the db
        conn = PgSQL.connect(host=DBHOST, database=DBNAME, user=DBUSER, password=DBPW, 
                             client_encoding='utf-8', unicode_results=1)
        # get a cursor
        cur = conn.cursor()
        cur.execute('set client_encoding to unicode')
        # prep sql
        whereclauses = []
        if viewtype == 0:
            whereclauses.append("isnew = 0")
        elif viewtype == 1:
            whereclauses.append("isnew = 1")
            
        if len(entriestoget) > 1:
            whereclauses.append('id IN %s')
        elif len(entriestoget) == 1:
            whereclauses.append('id = %s' % entriestoget[0])
            
        query = 'SELECT * FROM ckentries '
        if len(whereclauses) > 0:
            query += 'WHERE '
            query += ' AND %s'.join(whereclauses)
            
        query += ' ORDER BY category, dateadded'
        
        # exec SQL
        if len(entriestoget) > 1:
            cur.execute(query % (entriestoget,))
        else:
            cur.execute(query)

        # The fetchmany and fetchall methods return a sequence of PgResultSet objects instead of a sequence of sequences.
        records = cur.fetchall()
        recbycat = {}
        for record in records:
            assert isinstance(record, PgSQL.PgResultSet)
            category = record['category']
            # insert category into dict
            if category not in recbycat.keys():
                recbycat[category] = []
            # create record dict (split out for debugging)
            dctrec = {}
            thetitle = record['title'] #unicode(record['title'], 'utf-8')
            thelink = record['link'] #unicode(record['link'], 'utf-8')
            #dctrec['anchor'] = unicode(htags.A(thetitle, Href=thelink))
            dctrec['anchor'] = u'<a href="%s">%s</a>' % (thelink, thetitle)
            dctrec['id'] = record['id']
            dctrec['title'] = thetitle
            dctrec['link'] = thelink
            dctrec['cktext'] = record['cktext']
            dctrec['dateadded'] = record['dateadded']
            # append record to dict
            recbycat[category].append(dctrec)
        # close cur & conn
        cur.close()
        conn.close()

        return True, recbycat
        
    except:
        return False, traceback.format_exc() + '\n' + locals().get('query', 'no query specified')
    
def updateentries(entriestoset=()):
    """updates the given ckentries to isnew=0
    return T/F"""
    try:
        # open the db
        conn = PgSQL.connect(host=DBHOST, database=DBNAME, user=DBUSER, password=DBPW)
        # get a cursor
        cur = conn.cursor()
        # exec sql
        if len(entriestoset) > 1:
            cur.execute('UPDATE ckentries SET isnew = 0 WHERE id IN %s' % (entriestoset,))
        else:
            cur.execute('UPDATE ckentries SET isnew = 0 WHERE id = %s' % (entriestoset[0]))
        # commit the update
        conn.commit()
        # close db objects
        cur.close()
        conn.close()
        return True
        
    except:
        return False
    
def makecategoryview(category='', entries=[], asform=False):
    """given all the entries as a list from a single category, return a decorated HTML table
    returns string"""
    try:
        lines = [htags.H2(category), '<table>']
        for entry in entries:
            if asform:
                lines.append(htags.TR(htags.TD(htags.INPUT(Type="checkbox", 
                                                           Value=entry['id'], 
                                                           Name="entries")) + \
                                                           htags.TD(entry['id']) + \
                                                           htags.TD(entry['anchor']) + \
                                                           htags.TD(mxDT.ISO.str(entry['dateadded']))))
            else:
                # no form
                lines.append(htags.TR(htags.TD(entry['id']) + \
                                      htags.TD(entry['anchor']) + \
                                      htags.TD(mxDT.ISO.str(entry['dateadded']))))

        lines.append('</table>')
        ## append header row
        #htmlrows = [str(htags.TR(htags.TH('id') + htags.TH('title') + htags.TH('link')))]
        # return table
        #return True, str(htags.TABLE('\n'.join(htmlrows)))
        return '\n'.join([unicode(x) for x in lines])
    except:
        return '<pre>%s</pre>' % traceback.format_exc()
    
def makeentriesoutputbox(style, entries=[]):
    """given a list of entries, make a <textbox> element holding the properly-formatted text
    returns string"""
    try:
        anchorlist = []
        #return str(entries)
        for entry in entries:
            if style == 'rest':
                anchorlist.append('- %s' % entrystyler(style, entry['title'], 
                                                       entry['link'], 
                                                       entry.get('cktext') or ''))
                anchorlist.append('')
            else:
                anchorlist.append(entrystyler(style, entry['title'], 
                                              entry['link'], 
                                              entry.get('cktext') or ''))
                anchorlist.append('\n')
                
        return str(htags.TEXTAREA('\n'.join(anchorlist), Cols="100", Rows="20"))
    except:
        return '<p>%s</p>' % cgi.escape(traceback.format_exc())
    
def entrystyler(style, title, link, text=''):
    """style a given anchor & optional text in one of these formats:
    rest (ReStructuredText), html
    returns string"""
    try:
        anchor = ''
        if style == 'rest':
            anchor = "`%s <%s>`__" % (title, link)
            if len(text) > 0:
                # add as paragraph under link (2 new lines, 2 spaces indent)
                anchor += '\n\n  %s' % text
        elif style == 'html':
            anchor = '<a href="%s">%s</a>' % (link, cgi.escape(title))
            
            if len(text) > 0:
                # add in paragraph underneath
                anchor += '\n<p>%s</p>' % text
        
        return anchor
    except:
        return '' #'entrystyler failed'
    
def getoptions():
    """get the options & place in global variables"""
    try:
        objcfg = ConfigParser.ConfigParser()
        objcfg.read('./clippingkeeper.cfg')
        
        # get standard options
        global CGIURL
        CGIURL = objcfg.get('main', 'cgiurl')
        
        # get db options
        global DBHOST, DBNAME, DBUSER, DBPW
        DBHOST = objcfg.get('db', 'host')
        DBNAME = objcfg.get('db', 'db')
        DBUSER = objcfg.get('db', 'user')
        DBPW = objcfg.get('db', 'password')
        
        # get category list
        for id, name in objcfg.items('categories'):
            lstcategories.append(name)

    except:
        traceback.print_exc()
    
# run main code here
print main()