class Activity(db.Model):

    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    time = db.Column(db.Integer, nullable=True)
    activity = db.Column(db.Integer, primary_key=True, autoincrement=False) class ActivityMaterial(db.Model):

    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    activity = db.Column(db.Integer, primary_key=True, autoincrement=False)
    material_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    quantity = db.Column(db.Integer, nullable=True) class ActivityProduct(db.Model):

    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    activity = db.Column(db.Integer, primary_key=True, autoincrement=False)
    product_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    quantity = db.Column(db.Integer, nullable=True)
    probability = db.Column(db.Numeric(precision=3, scale=2, decimal_return_scale=2, asdecimal=False), nullable=True) class ActivitySkill(db.Model):

    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    activity = db.Column(db.Integer, primary_key=True, autoincrement=False)
    skill_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    level = db.Column(db.Integer, nullable=True) class Constellation(db.Model):

    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    name = db.Column(db.String(100), nullable=False)
    region_id = db.Column(db.Integer, db.ForeignKey('region.id')) class Decryptor(db.Model):
    item_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    probability_multiplier = db.Column(db.Numeric(precision=4, scale=2, decimal_return_scale=2, asdecimal=False), nullable=True)
    material_modifier = db.Column(db.Integer, nullable=True)
    time_modifier = db.Column(db.Integer, nullable=True)
    run_modifier = db.Column(db.Integer, nullable=True)

    item = db.relationship("Item") class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    name = db.Column(db.String(100), nullable=True)
    max_production_limit = db.Column(db.Integer, nullable=True)
    market_group_id = db.Column(db.Integer)
    group_id = db.Column(db.Integer)
    category_id = db.Column(db.Integer)
    volume = db.Column(db.Numeric(precision=16, scale=4, decimal_return_scale=4, asdecimal=False), nullable=True)

    # calculated field on import
    is_from_manufacturing = db.Column(db.Boolean(), default=True)
    is_from_reaction = db.Column(db.Boolean(), default=True)

    base_cost = db.Column(
        db.Numeric(
            precision=17,
            scale=2,
            decimal_return_scale=2,
            asdecimal=False
        ),
        nullable=True,
    )

    # foreign keys
    activities = db.relationship(
        'Activity',
        backref='blueprint',
        lazy='dynamic'
    )
    activity_products = db.relationship(
        'ActivityProduct',
        backref='blueprint',
        lazy='dynamic',
        foreign_keys='ActivityProduct.item_id'
    )

    activity_skills = db.relationship(
        'ActivitySkill',
        backref='blueprint',
        lazy='dynamic',
        foreign_keys='ActivitySkill.item_id'
    )
    activity_materials = db.relationship(
        'ActivityMaterial',
        backref='blueprint',
        lazy='dynamic',
        foreign_keys='ActivityMaterial.item_id'
    )

    product_for_activities = db.relationship(
        'ActivityProduct',
        backref='product',
        lazy='dynamic',
        foreign_keys='ActivityProduct.product_id'
    )
    skill_for_activities = db.relationship(
        'ActivitySkill',
        backref='skill',
        lazy='dynamic',
        foreign_keys='ActivitySkill.skill_id'
    )
    material_for_activities = db.relationship(
        'ActivityMaterial',
        backref='material',
        lazy='dynamic',
        foreign_keys='ActivityMaterial.material_id'
    )

    # relationship only defined for performance issues
    # ------------------------------------------------
    activity_products__eager = db.relationship(
        'ActivityProduct',
        lazy='joined',
        foreign_keys='ActivityProduct.item_id'
    )

    def icon_32(self):
        static_url = "ccp/Types/%d_32.png" % self.id
        return url_for('static', filename=static_url)

    def icon_64(self):
        static_url = "ccp/Types/%d_64.png" % self.id
        return url_for('static', filename=static_url)

    def is_moon_goo(self):
        return self.market_group_id == 499

    def is_pi(self):
        return self.category_id == 43

    def is_mineral_salvage(self):
        return self.market_group_id in [1857, 1033, 1863]

    def is_ancient_relic(self):
        return self.category_id == 34

    def is_cap_part(self):
        """ Return if the item is a cap part / blueprint of cap part.

        914 / 915 are Blueprints
        913 / 873 are their respective items """
        return self.group_id in [914, 915, 913, 873] class OreRefining(db.Model):
    ore_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    material_id = db.Column(db.Integer, db.ForeignKey('item.id'), primary_key=True)
    quantity = db.Column(db.Integer)
    batch = db.Column(db.Integer)
    is_compressed = db.Column(db.Boolean, default=False)
    is_ice = db.Column(db.Boolean, default=False) class Region(db.Model):

    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    name = db.Column(db.String(100), nullable=False)
    wh = db.Column(db.Boolean, default=False) class SolarSystem(db.Model):

    id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    name = db.Column(db.String(100), nullable=False)

    region_id = db.Column(db.Integer, db.ForeignKey('region.id'))
    constellation_id = db.Column(db.Integer, db.ForeignKey('constellation.id'))
import sys
import time
from lazyblacksmith.models import Activity
from lazyblacksmith.models import ActivityMaterial
from lazyblacksmith.models import ActivityProduct
from lazyblacksmith.models import ActivitySkill
from lazyblacksmith.models import Constellation
from lazyblacksmith.models import Decryptor
from lazyblacksmith.models import Item
from lazyblacksmith.models import OreRefining
from lazyblacksmith.models import Region
from lazyblacksmith.models import SolarSystem
from lazyblacksmith.models import db
            ore_refining = {
                'ore_id': ore_id,
                'material_id': material_id,
                'quantity': quantity,
                'batch': batch,
                'is_compressed': compressed,
                'is_ice': ice,
            }

            new.append(ore_refining)
            added += 1

        # then create the new blueprints if they exist
        if new:
            self.lb_engine.execute(
                OreRefining.__table__.insert(),
                new
            )

        return (added, total, "")

    def import_decryptor(self):
        """
        Import the decryptor stats in a specific table.

        CCP Table : dgmTypeAttributes, invTypes
        """
        added = 0
        total = 0

        # get all data
        self.sde_cursor.execute("""
            SELECT
                i.typeID,
                COALESCE(dta2.valueInt,dta2.valueFloat) multiplier,
                COALESCE(dta3.valueInt,dta3.valueFloat) me,
                COALESCE(dta4.valueInt,dta4.valueFloat) te,
                COALESCE(dta5.valueInt,dta5.valueFloat) runs
            FROM invTypes i
            JOIN dgmTypeAttributes dta2 ON (dta2.typeid = i.typeid AND dta2.attributeID = 1112)
            JOIN dgmTypeAttributes dta3 ON (dta3.typeid = i.typeid AND dta3.attributeID = 1113)
            JOIN dgmTypeAttributes dta4 ON (dta4.typeid = i.typeid AND dta4.attributeID = 1114)
            JOIN dgmTypeAttributes dta5 ON (dta5.typeid = i.typeid AND dta5.attributeID = 1124)
            WHERE i.groupID = 1304
        """)

        # for each decryptor from SDE, check valid data and import them.
        new = []
        for data in self.sde_cursor:
            total += 1

            if not data[0]:
                continue

            decryptor = {
                'item_id': int(data[0]),
                'probability_multiplier': float(data[1]),
                'material_modifier': int(data[2]),
                'time_modifier': int(data[3]),
                'run_modifier': int(data[4]),
            }

            new.append(decryptor)
            added += 1

        # then create the new blueprints if they exist
        if new:
            self.lb_engine.execute(
                Decryptor.__table__.insert(),
                new
            )

        return (added, total, "")

    def import_activity(self):
        """
        Import blueprints activity time from SDE to our database.

        CCP Table : IndustryActivity
        """
        added = 0
        total = 0

        # get all data
        self.sde_cursor.execute("""
            SELECT
                  ia.typeID
                , ia.time
                , ia.activityID
                , i.typeName
            FROM industryActivity ia
            JOIN invTypes i
                ON  i.typeID = ia.typeID
                AND i.published = 1
        """)

        bulk_data = []
        for row in self.sde_cursor:
            bulk_data.append(row)

        # for each blueprints from SDE, check valid data and import them.
        new = []
        for data in bulk_data:
            total += 1

            if not data[0] or not data[1] or not data[2] or not data[3]:
                continue

            activity = {
                'item_id': int(data[0]),
                'time': int(data[1]),
                'activity': int(data[2]),
            }

            new.append(activity)
            added += 1

        # then create the new blueprints if they exist
        if new:
            self.lb_engine.execute(

                Activity.__table__.insert(),