#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Copyright (C) 2004-2013 Edgewall Software
# Copyright (C) 2004 Dmitry Yusupov <dmitry_yus@yahoo.com>
# Copyright (C) 2004 Mark Rowe <mrowe@bluewire.net.nz>
# Copyright (C) 2010 Anatoly Techtonik <techtonik@php.net>
# All rights reserved.
#
# This software is licensed as described in the file COPYING, which
# you should have received as part of this distribution. The terms
# are also available at http://trac.edgewall.com/license.html.
#
# This software consists of voluntary contributions made by many
# individuals. For the exact contribution history, see the revision
# history and logs, available at http://trac.edgewall.org/.

"""
Import a Sourceforge project's tracker items into a Trac database.

Requires:
   Trac 1.0 from http://trac.edgewall.org/
   Python 2.5 from http://www.python.org/

1.0 clean-up by cboos **untested**, use at your own risks and send patches

The Sourceforge tracker items can be exported from the 'Backup' page
of the project admin section. Substitute XXXXX with project id:
https://sourceforge.net/export/xml_export2.php?group_id=XXXXX

$Id$


Uses Trac 0.11 DB format version 21
SourceForge XML Export format identified by the header:
<!DOCTYPE project_export SYSTEM "http://sourceforge.net/export/sf_project_export_0.2.dtd">

Works with all DB backends. Attachments are not downloaded, but inserted
as links to SF tracker.


Ticket Types, Priorities and Resolutions
----------------------------------------
Conversion kills default Trac ticket types:
- defect      1
- enhancement 2
- task        3

and priorities:
- blocker  1
- critical 2
- major    3
- minor    4
- trivial  5

and resolutions:
- fixed      1
- invalid    2
- wontfix    3
- duplicate  4
- worksforme 5


Versions and Milestones
-----------------------
Kills versions and milestones from existing Trac DB


Mapping
-------
tracker_name == ticket_type
group_name == version
category_name == component

user nobody == anonymous


Not implemented (feature:reason)
--------------------------------
attachments:made as a comment with links to attachments stored on SF
            (type,id,filename,size,time,description,author,ipnr)
ticket_custom:unknown (ticket,name,value)
history:imported only for summary, priority. closed date and owner fields

severities:no field in source data
"""


#: rename users from SF to Trac
user_map = {"nobody":"anonymous"}



complete_msg = """
Conversion complete.

You may want to login into Trac to verify names for ticket owners. You may
also want to rename ticket types and priorities to default.
"""

from __future__ import with_statement

from xml.etree.ElementTree import ElementTree
import time
import sys

import trac.env

# --- utility
class DBNotEmpty(Exception):
    def __str__(self):
        return "Will not modify database with existing tickets!"

class FlatXML(object):
    """Flat XML is XML without element attributes. Also each element
       may contain other elements or text, but not both.

       This object mirrors XML structure into own properties for convenient
       access to tree elements, i.e. flat.trackers[2].groups[2].group_name

       Uses recursion.
    """

    def __init__(self, el=None):
        """el is ElementTree element"""
        if el:
            self.merge(el)

    def merge(self, el):
        """merge supplied ElementTree element into current object"""
        for c in el:
            if len(c.getchildren()) == 0:
                if c.text != None and len(c.text.strip()) != 0:
                    self.__setattr__(c.tag, c.text)
                else:
                    self.__setattr__(c.tag, [])
            else: #if c.getchildren()[0].tag == c.tag[:-1]:
                # c is a set of elements
                self.__setattr__(c.tag, [FlatXML(x) for x in c.getchildren()])


    def __str__(self):
        buf = ""
        for sub in self.__dict__:
            val = self.__dict__[sub]
            if type(val) != list:
                buf += "%s : %s\n" % (sub, val)
            else:
                for x in val:
                    buf += "\n  ".join(x.__str__().split("\n"))
        return buf

    def __repr__(self):
        buf = ""
        for sub in self.__dict__:
            val = self.__dict__[sub]
            if type(val) != list:
                buf += "<%s>%s</%s>\n" % (sub, val, sub)
            else:
                for x in val:
                    buf += "\n  ".join(x.__repr__().split("\n"))
        return buf


# --- SF data model
class Tracker(FlatXML):
    """
 <trackers>
  <tracker>
   <url>http://sourceforge.net/?group_id=175454&#38;atid=873299</url>
   <tracker_id>873299</tracker_id>
   <name>Bugs</name>
   <description>Bug Tracking System</description>
   <is_public>All site users</is_public>
   <allow_anon>Yes</allow_anon>
   <email_updates>Send to goblinhack@gmail.com</email_updates>
   <due_period>2592000</due_period>
   <submit_instructions></submit_instructions>
   <browse_instructions></browse_instructions>
   <status_timeout>1209600</status_timeout>
   <due_period_initial>0</due_period_initial>
   <due_period_update>0</due_period_update>
   <reopen_on_comment>1</reopen_on_comment>
   <canned_responses>
   </canned_responses>
   <groups>
    <group>
     <id>632324</id>
      <group_name>v1.0 (example)</group_name>
    </group>
   </groups>
   <categories>
    <category>
     <id>885178</id>
      <category_name>Interface (example)</category_name>
     <auto_assignee>nobody</auto_assignee>
    </category>
   </categories>
   <resolutions>
    <resolution>
     <id>1</id>
     <name>Fixed</name>
    </resolution>
    <resolution>
     <id>2</id>
     <name>Invalid</name>
    </resolution>
    ...
   </resolutions>
   <statuses>
    <status>
      <id>1</id>
      <name>Open</name>
    </status>
    <status>
      <id>2</id>
      <name>Closed</name>
    </status>
    <status>
      <id>3</id>
      <name>Deleted</name>
    </status>
    <status>
      <id>4</id>
      <name>Pending</name>
    </status>
   </statuses>
   ...
   <tracker_items>
    <tracker_item>
<url>http://sourceforge.net/support/tracker.php?aid=2471428</url>
<id>2471428</id>
<status_id>2</status_id>
<category_id>100</category_id>
<group_id>100</group_id>
<resolution_id>100</resolution_id>
<submitter>sbluen</submitter>
<assignee>nobody</assignee>
<closer>goblinhack</closer>
<submit_date>1230400444</submit_date>
<close_date>1231087612</close_date>
<priority>5</priority>
<summary>glitch with edge of level</summary>
<details>The mini-laser that the future soldier carries is so powerful that it even lets me go outside the level. I stand at the top edge of the level and then shoot up, and then it gets me somewhere where I am not supposed to go.</details>
<is_private>0</is_private>
<followups>
 <followup>
  <id>2335316</id>
  <submitter>goblinhack</submitter>
  <date>1175610236</date>
  <details>Logged In: YES
  user_id=1577972
  Originator: NO

  does this happen every game or just once?

  you could send me the saved file and I'll try and load it - old
  versions harldy ever work with newer versions - need to add some
  kind of warnings on that

  tx</details>
 </followup>
 ...
</followups>
<attachments>
 <attachment>
  <url>http://sourceforge.net/tracker/download.php?group_id=175454&#38;atid=873299&#38;file_id=289080&#38;aid=</url>
  <id>289080</id>
  <filename>your_most_recent_game.gz</filename>
  <description>my saved game</description>
  <filesize>112968</filesize>
  <filetype>application/x-gzip</filetype>
  <date>1218987770</date>
  <submitter>sbluen</submitter>
 </attachment>
...
</attachments>
<history_entries>
 <history_entry>
  <id>7304242</id>
  <field_name>IP</field_name>
  <old_value>Artifact Created: 76.173.48.148</old_value>
  <date>1230400444</date>
  <updator>sbluen</updator>
 </history_entry>
 ...
</history_entries>
    </tracker_item>
    ...
   </tracker_items>
  ...
  </tracker>
 </trackers>
    """
    def __init__(self, e):
        self.merge(e)


class ExportedProjectData(object):
    """Project data container as Python object.
    """
    def __init__(self, f):
        """Data parsing"""

        self.trackers = []    #: tracker properties and data
        self.groups = []      #: groups []
        self.priorities = []  #: priorities used
        self.resolutions = [] #: resolutions (index, name)
        self.tickets = []     #: all tickets
        self.statuses = []    #: status (idx, name)

        self.used_resolutions = {} #: id:name
        self.used_categories  = {} #: id:name
        # id '100' means no category
        self.used_categories['100'] = None
        self.users = {}       #: id:name

        root = ElementTree().parse(f)

        self.users = dict([(FlatXML(u).userid, FlatXML(u).username)
                          for u in root.find('referenced_users')])

        for tracker in root.find('trackers'):
            tr = Tracker(tracker)
            self.trackers.append(tr)

            # groups-versions
            for grp in tr.groups:
                # group ids are tracker-specific even if names match
                g = (grp.id, grp.group_name)
                if g not in self.groups:
                    self.groups.append(g)

            # resolutions
            for res in tr.resolutions:
                r = (res.id, res.name)
                if r not in self.resolutions:
                    self.resolutions.append(r)

            # statuses
            self.statuses = [(s.id, s.name) for s in tr.statuses]

            # tickets
            for tck in tr.tracker_items:
                if type(tck) == str: print repr(tck)
                self.tickets.append(tck)
                if int(tck.priority) not in self.priorities:
                    self.priorities.append(int(tck.priority))
                res_id = getattr(tck, "resolution_id", None)
                if res_id is not None and res_id not in self.used_resolutions:
                    for idx, name in self.resolutions:
                        if idx == res_id: break
                    self.used_resolutions[res_id] = \
                            dict(self.resolutions)[res_id]
                # used categories
                categories = dict(self.get_categories(tr, noowner=True))
                if tck.category_id not in self.used_categories:
                    self.used_categories[tck.category_id] = \
                            categories[tck.category_id]

        # sorting everything
        self.trackers.sort(key=lambda x:x.name)
        self.groups.sort()
        self.priorities.sort()

    def get_categories(self, tracker=None, noid=False, noowner=False):
        """ SF categories : Trac components
            (id, name, owner) tuples for specified tracker or all trackers
            if noid or noowner flags are set, specified tuple attribute is
            stripped
        """
        trs = [tracker] if tracker is not None else self.trackers
        categories = []
        for tr in trs:
            for cat in tr.categories:
                c = (cat.id, cat.category_name, cat.auto_assignee)
                if c not in categories:
                    categories.append(c)
        #: sort by name
        if noid:
            categories.sort()
        else:
            categories.sort(key=lambda x:x[1])
        if noowner:
            categories = [x[:2] for x in categories]
        if noid:
            categories = [x[1:] for x in categories]
        return categories


class TracDatabase(object):
    def __init__(self, path):
        self.env = trac.env.Environment(path)

    def hasTickets(self):
        return int(self.env.db_query("SELECT count(*) FROM ticket")[0][0]) > 0

    def dbCheck(self):
        if self.hasTickets():
            raise DBNotEmpty

    def setTypeList(self, s):
        """Remove all types, set them to `s`"""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM enum WHERE type='ticket_type'")
            for i, value in enumerate(s):
                db("INSERT INTO enum (type, name, value) VALUES (%s, %s, %s)",
                   ("ticket_type", value, i))

    def setPriorityList(self, s):
        """Remove all priorities, set them to `s`"""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM enum WHERE type='priority'")
            for i, value in enumerate(s):
                db("INSERT INTO enum (type, name, value) VALUES (%s, %s, %s)",
                   ("priority", value, i))

    def setResolutionList(self, t):
        """Remove all resolutions, set them to `t` (index, name)"""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM enum WHERE type='resolution'")
            for value, name in t:
                db("INSERT INTO enum (type, name, value) VALUES (%s, %s, %s)",
                   ("resolution", name, value))

    def setComponentList(self, t):
        """Remove all components, set them to `t` (name, owner)"""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM component")
            for name, owner in t:
                db("INSERT INTO component (name, owner) VALUES (%s, %s)",
                   (name, owner))

    def setVersionList(self, v):
        """Remove all versions, set them to `v`"""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM version")
            for value in v:
                # time and description are also available
                db("INSERT INTO version (name) VALUES (%s)", value)

    def setMilestoneList(self, m):
        """Remove all milestones, set them to `m` ("""
        self.dbCheck()
        with self.env.db_transaction as db:
            db("DELETE FROM milestone")
            for value in m:
                # due, completed, description are also available
                db("INSERT INTO milestone (name) VALUES (%s)", value)

    def addTicket(self, type, time, changetime, component,
                  priority, owner, reporter, cc,
                  version, milestone, status, resolution,
                  summary, description, keywords):
        """ ticket table db21.py format

        id              integer PRIMARY KEY,
        type            text,           -- the nature of the ticket
        time            integer,        -- the time it was created
        changetime      integer,
        component       text,
        severity        text,
        priority        text,
        owner           text,           -- who is this ticket assigned to
        reporter        text,
        cc              text,           -- email addresses to notify
        version         text,           --
        milestone       text,           --
        status          text,
        resolution      text,
        summary         text,           -- one-line summary
        description     text,           -- problem description (long)
        keywords        text
        """
        if status.lower() == 'open':
            if owner != '':
                status = 'assigned'
            else:
                status = 'new'

        with self.env.db_transaction as db:
            c = db.cursor()
            c.execute("""
                INSERT INTO ticket (type, time, changetime, component,
                                    priority, owner, reporter, cc, version,
                                    milestone, status, resolution, summary,
                                    description, keywords)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                        %s, %s)
                """, (type, time, changetime, component, priority, owner,
                      reporter, cc, version, milestone, status.lower(),
                      resolution, summary, '%s' % description, keywords))
            return db.get_last_id(c, 'ticket')

    def addTicketComment(self, ticket, time, author, value):
        with self.env.db_transaction as db:
            db("""
                INSERT INTO ticket_change (ticket, time, author, field,
                                           oldvalue, newvalue)
                VALUES (%s, %s, %s, %s, %s, %s)
                """, (ticket, time, author, 'comment', '', '%s' % value))

    def addTicketChange(self, ticket, time, author, field, oldvalue, newvalue):
        with self.env.db_transaction as db:
            db("""INSERT INTO ticket_change (ticket, time, author, field,
                                             oldvalue, newvalue)
                  VALUES (%s, %s, %s, %s, %s, %s)
                  """, (ticket, time, author, field, oldvalue, newvalue))


def importData(f, env, opt):
    project = ExportedProjectData(f)
    trackers = project.trackers

    trac = TracDatabase(env)

    # Data conversion
    typeList = [x.name for x in trackers]
    print("%d trackers will be converted to the following ticket types:\n  %s" \
        % (len(trackers), typeList))

    used_cat_names = set(project.used_categories.values())
    #: make names unique, forget about competing owners (the last one wins)
    components = dict(project.get_categories(noid=True)).items()
    components.sort()
    components = [x for x in components if x[0] in used_cat_names]
    print "%d out of %d categories are used and will be converted to the following components:\n  %s" \
        % (len(components), len(project.get_categories()), components)
    print "..renaming component owners:"
    for i,c in enumerate(components):
        if c[1] in user_map:
            components[i] = (c[0], user_map[c[1]])
    print "  %s" % components

    print "%d groups which will be converted to the following versions:\n  %s" \
        % (len(project.groups), project.groups)
    print "%d resolutions found :\n  %s" \
        % (len(project.resolutions), project.resolutions)
    resolutions = [(k,project.used_resolutions[k])
                   for k in project.used_resolutions]
    resolutions.sort(key=lambda x:int(x[0]))
    print ".. only %d used will be imported:\n  %s" \
        % (len(resolutions), resolutions)
    print "Priorities used so far: %s" % project.priorities
    if not(raw_input("Continue [y/N]?").lower() == 'y'):
        sys.exit()

    # Data save
    trac.setTypeList(typeList)
    trac.setComponentList(components)
    trac.setPriorityList(range(min(project.priorities),
                               max(project.priorities)))
    trac.setVersionList(set([x[1] for x in project.groups]))
    trac.setResolutionList(resolutions)
    trac.setMilestoneList([])

    for tracker in project.trackers:
        # id 100 means no component selected
        component_lookup = dict(project.get_categories(noowner=True) +
                                [("100", None)])
        for t in tracker.tracker_items:
            i = trac.addTicket(type=tracker.name,
                               time=int(t.submit_date),
                               changetime=int(t.submit_date),
                               component=component_lookup[t.category_id],
                               priority=t.priority,
                               owner=t.assignee \
                                       if t.assignee not in user_map \
                                       else user_map[t.assignee],
                               reporter=t.submitter \
                                       if t.submitter not in user_map \
                                       else user_map[t.submitter],
                               cc=None,
                               # 100 means no group selected
                               version=dict(project.groups +
                                            [("100", None)])[t.group_id],
                               milestone=None,
                               status=dict(project.statuses)[t.status_id],
                               resolution=dict(resolutions)[t.resolution_id] \
                                       if hasattr(t, "resolution_id") else None,
                               summary=t.summary,
                               description=t.details,
                               keywords='sf' + t.id)

            print 'Imported %s as #%d' % (t.id, i)

            if len(t.attachments):
                attmsg = "SourceForge attachments:\n"
                for a in t.attachments:
                    attmsg = attmsg + " * [%s %s] (%s) - added by '%s' %s [[BR]] "\
                             % (a.url+t.id, a.filename, a.filesize+" bytes",
                                user_map.get(a.submitter, a.submitter),
                                time.strftime("%Y-%m-%d %H:%M:%S",
                                              time.localtime(int(a.date))))
                    attmsg = attmsg + "''%s ''\n" % (a.description or '')
                    # empty description is as empty list
                trac.addTicketComment(ticket=i,
                                      time=time.strftime("%Y-%m-%d %H:%M:%S",
                                              time.localtime(int(t.submit_date))),
                                      author=None, value=attmsg)
                print '    added information about %d attachments for #%d' % \
                        (len(t.attachments), i)

            for msg in t.followups:
                """
                <followup>
                <id>3280792</id>
                <submitter>goblinhack</submitter>
                <date>1231087739</date>
                <details>done</details>
                </followup>
                """
                trac.addTicketComment(ticket=i,
                                      time=msg.date,
                                      author=msg.submitter,
                                      value=msg.details)
            if t.followups:
                print '    imported %d messages for #%d' % (len(t.followups), i)

            # Import history
            """
            <history_entry>
            <id>4452195</id>
            <field_name>resolution_id</field_name>
            <old_value>100</old_value>
            <date>1176043865</date>
            <updator>goblinhack</updator>
            </history_entry>
            """
            revision = t.__dict__.copy()

            # iterate the history in reverse order and update ticket revision from
            # current (last) to initial
            changes = 0
            for h in sorted(t.history_entries, reverse=True):
                """
                 Processed fields (field - notes):
                IP         - no target field, just skip
                summary
                priority
                close_date
                assigned_to

                 Fields not processed (field: explanation):
                File Added - TODO
                resolution_id - need to update used_resolutions
                status_id
                artifact_group_id
                category_id
                group_id
                """
                f = None
                if h.field_name in ("IP",):
                    changes += 1
                    continue
                elif h.field_name in ("summary", "priority"):
                    f = h.field_name
                    oldvalue = h.old_value
                    newvalue = revision.get(h.field_name, None)
                elif h.field_name == 'assigned_to':
                    f = "owner"
                    newvalue = revision['assignee']
                    if h.old_value == '100': # was not assigned
                        revision['assignee'] = None
                        oldvalue = None
                    else:
                        username = project.users[h.old_value]
                        if username in user_map: username = user_map[username]
                        revision['assignee'] = oldvalue = username
                elif h.field_name == 'close_date' and revision['close_date'] != 0:
                    f = 'status'
                    oldvalue = 'assigned'
                    newvalue = 'closed'

                if f:
                    changes += 1
                    trac.addTicketChange(ticket=i,
                                         time=h.date,
                                         author=h.updator,
                                         field=f,
                                         oldvalue=oldvalue,
                                         newvalue=newvalue)

                if h.field_name != 'assigned_to':
                    revision[h.field_name] = h.old_value
            if changes:
                print '    processed %d out of %d history items for #%d' % \
                        (changes, len(t.history_entries), i)


def main():
    import optparse
    p = optparse.OptionParser(
            "Usage: %prog xml_export.xml /path/to/trac/environment")
    opt, args = p.parse_args()
    if len(args) != 2:
        p.error("Incorrect number of arguments")

    try:
        importData(open(args[0]), args[1], opt)
    except DBNotEmpty, e:
        print 'Error:', e
        sys.exit(1)

    print complete_msg


if __name__ == '__main__':
    main()