#!/usr/bin/python3
# -*- coding: utf-8 -*-
#
# (c) 2010 Mandriva, http://www.mandriva.com/
#
# $Id$
#
# This file is part of Pulse 2, http://pulse2.mandriva.org
#
# Pulse 2 is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# Pulse 2 is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with MMC.  If not, see <http://www.gnu.org/licenses/>.

"""
This script convert a LRS inventory database (version 10) into a Pulse 2
inventory database.
"""

import sys

try:
    from sqlalchemy import *
except ImportError:
    print "SqlAlchemy was not found, please install it !"
    sys.exit(1)

import sqlalchemy

def usage(argv):
    print >> sys.stderr, 'This script convert a LRS inventory database (version 10) into a Pulse 2 inventory database.'
    print >> sys.stderr, 'Usage: %s db_conn_string [--id entity_id|--name entity_name]' % argv[0]
    print >> sys.stderr, 'Where db_conn_string is a SQLAlchemy connection string, e.g. mysql://user:password@host/database'
    return 1

if __name__ == "__main__":
    if len(sys.argv) != 2 and len(sys.argv) != 4:
        sys.exit(usage(sys.argv))

    if not sqlalchemy.__version__.startswith('0.4'):
        print "Wrong version of SqlAlchemy found, please install a 0.4 version !"
        sys.exit(1)

    get_entity = False
    if len(sys.argv) == 4:
        if sys.argv[2] == '--id':
            id_entity = sys.argv[3]
            get_entity = True
        elif sys.argv[2] == '--name':
            id_entity = -1
            name_entity = sys.argv[3]
            get_entity = True
        else:
            print "dont know this option : %s" % sys.argv[2]
            sys.exit(1)

    mysql_db = create_engine(sys.argv[1])
    metadata = MetaData(mysql_db)
    connection = mysql_db.connect()

    inventory = Table("Inventory", metadata, autoload = True)
    machine = Table("Machine", metadata, autoload = True)
    entity = Table("Entity", metadata, autoload = True)
    hasentity = Table("hasEntity", metadata, autoload = True)
    hashardware = Table("hasHardware", metadata, autoload = True)
    hasentity = Table("hasEntity", metadata, autoload = True)

    entity_id = 1
    if get_entity:
        ent = []
        if id_entity != -1:
            ent = select([entity.c.id], and_(entity.c.id == id_entity)).execute().fetchall()
        else:
            ent = select([entity.c.id], and_(entity.c.Label == name_entity)).execute().fetchall()
        if ent == None or len(ent) == 0:
            print "Can't get the required entity"
            sys.exit(1)
        entity_id = ent[0][0]

    # First: we reset all Inventory rows to Last = 0
    connection.execute(inventory.update(values = { 'Last' : '0' }),
                       Last = 0)

    # Second: we get the latest Inventory.id for each Machine, and we set
    # Last=1 for all the Latest Inventory.id

    # Here is the query launched to get the latest Inventory.id

    ## SELECT invid1
    ## FROM (
    ##  SELECT Inventory.id as invid1, Machine.id as invmac1
    ##  FROM Machine, Inventory, hasHardware
    ##  WHERE Machine.id = hasHardware.machine
    ##  AND hasHardware.inventory = Inventory.id
    ## ) A LEFT JOIN (
    ##  SELECT Inventory.id as invid2, Machine.id as invmac2
    ##  FROM Machine, Inventory, hasHardware
    ##  WHERE Machine.id = hasHardware.machine
    ##  AND hasHardware.inventory = Inventory.id
    ## ) B ON (invmac1 = invmac2 AND invid1 < invid2)
    ## WHERE invid2 IS NULL;

    A = select([inventory.c.id, machine.c.id],
               and_(machine.c.id == hashardware.c.machine,
                    hashardware.c.inventory == inventory.c.id),
               use_labels = True) \
               .alias()
    B = select([inventory.c.id, machine.c.id],
               and_(machine.c.id == hashardware.c.machine,
                    hashardware.c.inventory == inventory.c.id),
               use_labels = True) \
               .alias()
    q = select([A.c.Inventory_id],
               from_obj=[A.outerjoin(B,
                                     and_(A.c.Machine_id == B.c.Machine_id,
                                          A.c.Inventory_id < B.c.Inventory_id))
                         ]) \
                         .where(B.c.Inventory_id == None)

    # Query built. Now just combine it with a SQL update
    connection.execute(inventory.update(inventory.c.id.in_(q),
                                        values = { 'Last' : '1' }),
                       Last = 1)

    # Third: we bind all Machine not linked to an entity to the root entity
    computers = select([inventory.c.id, machine.c.id],
                       and_(machine.c.id == hashardware.c.machine,
                            hashardware.c.inventory == inventory.c.id,
                            inventory.c.Last == 1,
                            not_(machine.c.id.in_(select([hasentity.c.machine])))
                            )
                       ).execute().fetchall()

    if computers:
        into_hasentity = []
        for computer in computers:
            into_hasentity.append({'machine' : computer[1], 'entity' : entity_id, 'inventory': computer[0]})
        connection.execute(hasentity.insert(), into_hasentity)

    sys.exit(0)
