#!/usr/bin/env python
"""Interactive ticket change reversion for Trac.

Query ticket changes, and optionally revert them. This is an interactive 
command-line program that presents the most recent change on a set of
tickets. For each change presented, you can choose to revert or leave it
alone. If you choose to revert, you can continue reverting prior changes.
If you choose to leave it alone, then we move to the next ticket in the
set.

If you don't choose to revert anything, the Trac db isn't changed. When
you do choose to revert something, the master ticket information is 
potentially changed, and the change record is deleted.

This is useful to clean up ticket spam, mostly.

Questions to: Paul McNett <p@ulmcnett.com>
This software is placed in the public domain, and I take no
responsibility for trashed Trac instances. *Please* backup
your trac db before doing any reversions. 
"""
__version__ = "1.0"
_description = '\n'.join(__doc__.splitlines()[2:8])

import sys
import os
import time
from optparse import OptionParser
try:
	import sqlite3.dbapi2 as sqlite
except ImportError:
	# Prior to python 2.5:
	import pysqlite2.dbapi2 as sqlite

usage = "usage: %prog [-n] PATH [ticket1 [ticket2 [...]]]"
parser = OptionParser(usage=usage, version="%%prog %(__version__)s" % locals())
parser.add_option("-n", "--number-days", dest="number_days", type="int", default=1,
                metavar="DAYS", help="Review only changes in the past DAYS days. Only effective if explicit tickets not specified.")
parser.set_description(_description)

(options, args) = parser.parse_args()

if not args:
	sys.exit(parser.print_usage())

repo = args[0]

if len(args) > 1:
	tickets = args[1:]
	cutoff_time = 0
else:
	tickets = None
	cutoff_time = time.time() - (options.number_days * 60*60*24)


con = sqlite.connect("%s/db/trac.db" % repo)
cur = con.cursor()

if not tickets:
	cur.execute("select id from ticket")
	tickets = cur.fetchall()
	tickets = [t[0] for t in tickets]
else:
	tickets = [int(t) for t in tickets]

for ticket in tickets:
	cur.execute("""
select ticket, 
       time, 
       author, 
       field, 
       substr(oldvalue, 0, 128), 
       substr(newvalue, 0, 128) 
  from ticket_change 
 where ticket = ?
   and time >= ? 
 order by time desc
""", (ticket, cutoff_time))
	changes = cur.fetchall()
	times_to_change = []
	cur_time = None
	already_asked = False
	for change in changes:
		if cur_time is not None and cur_time != change[1]:
			already_asked = True
			if raw_input("Revert the above change(s)? (y/n)") == "y":
				times_to_change.append(cur_time)
			else:
				# can only revert most recent changes
				break
		print change
		cur_time = change[1]

	if changes and not already_asked:
		if raw_input("Revert the above change(s)? (y/n)") == "y":
			times_to_change.append(cur_time)

	for revert_time in times_to_change:
		# these are already in descending order
		cur.execute("select ticket, field, oldvalue, newvalue from ticket_change where ticket=%s and time=%s" % (ticket, revert_time))
		change = cur.fetchall()
		fields = []
		for r in change:
			field = r[1]
			val = r[2].replace("'", "\'")
			if field != "comment":
				fields.append((field, val))
		for f, v in fields: 
			updateSQL = "update ticket set %s=? where id=?" % (f)
			print updateSQL, v, ticket
			cur.execute(updateSQL, (v, ticket))
		deleteSQL = "delete from ticket_change where ticket=%s and time=%s" % (ticket, revert_time)
		print deleteSQL
		cur.execute(deleteSQL)
		con.commit()