#coding:utf-8

import os
import sys
import sunhpc
import random
import sqlalchemy
import sunhpc.invoke
class Command(sunhpc.commands.database.command):
    """
    Create sunhpc database tables.
    <arg type='path' name='cfg'>
    supply an config file. default cfg in /opt/sunhpc/etc/sunhpc.cfg
    </arg>
    """

    def run(self, params, args):

        cfg = os.path.join(self.prefix, 'etc', 'sunhpc.conf')
        if args:
            cfg = args[0]

        if not os.path.exists(cfg):
            self.msg('The * configuration file does not exists.must provide a.', 'a', cfg)

        self.readConfig(cfg)
        self.insertdb()

    def insertdb(self):
        try:

            [ self.db.execute(t) for t in self.baseTables()]

        except sqlalchemy.exc.OperationalError as e:
            raise
            # e.args, e.code, e.orig, e.statement
            if e.code == 'e3q8':
                self.msg(e.args, 'e')
                self.msg('Database * already exists.please delete it first!!!', 
                'a', self.db.database.datafile)
            else:
                print (e)

        self.msg('The %s database is create finish.' % self.db.database.datafile)

        # add data to DB
        [self.db.execute(d) for d in self.tableData()]
        self.msg('The %s database has been added.' % self.db.database.datafile)

    def readConfig(self, conf):
        
        confs = {}
        with open(conf, 'r') as f:
            for i in f.readlines():
                r = i.split()
                if i.startswith('#') or len(r) < 2:
                    continue
                key, val = r[0], ' '.join(r[1:])
                confs[key.lower()] = val

        self.country        = confs['country']   if 'country'   in confs else 'CN'
        self.state          = confs['state']     if 'state'     in confs else 'LiaoNing'
        self.city           = confs['locality']  if 'locality'  in confs else 'DaLian'
        self.URL            = confs['url']       if 'url'       in confs else 'https://www.sunhpc.com'
        self.cname          = confs['name']      if 'name'      in confs else 'Sunhpc-cluster'
        self.contact        = confs['contact']   if 'contact'   in confs else 'info@sunhpc.com'
        self.worksdir       = confs['worksdir']  if 'worksdir'  in confs else '/export'
        self.distrodir      = confs['distrodir'] if 'distrodir' in confs else '/export/sunhpc'
        self.partition      = confs['partition'] if 'partition' in confs else 'default'

        # safe security 
        self.safeport       = confs['safeport'] if 'safeport' in confs else '372'
        self.safedirs       = confs['safedirs'] if 'safedirs' in confs else 'safe.d'
        self.safesecurity   = confs['safecurity'] if 'safecurity' in confs else 'safe-security'

        try:

            self.wan_hostname   = confs['publichostname']
            self.wan_device     = confs['publicinterface']
            self.wan_address    = confs['publicaddress']
            self.wan_gateway    = confs['publicgateway']
            self.wan_dns        = confs['publicdnsserver']
            self.wan_netmask    = confs['publicnetmask']
            
            wan_mac = os.popen("nmcli device show %s|grep -i general.hwaddr|awk '{print $2}'" % 
                                    self.wan_device).readline().strip()
            self.wan_mac        = confs['publicmacaddr']    if 'publicmacaddr'   in confs else wan_mac

            wan_cidr            = self.net.netmask_to_cidr(self.wan_netmask)
            self.wan_cidr       = confs['publiccidr']       if 'publiccidr'      in confs else wan_cidr

            wan_network = self.net.getNetwork(self.wan_address, self.wan_netmask)
            self.wan_network    = confs['publicnetwork']    if 'publicnetwork'   in confs else wan_network

            self.wan_ntphost    = confs['publicntphost']    if 'publicntphost'   in confs else 'pool.ntp.org'
            self.wan_domain     = confs['publicdnsdomain']  if 'publicdnsdomain' in confs else 'hpc.org'
            self.wan_mtu        = confs['publicmtu']        if 'publicmtu'       in confs else '1500'

            # private network configure
            self.lan_hostname   = confs['privatehostname']  if 'privatehostname' in confs else 'cluster'
            self.lan_device     = confs['privateinterface']
            self.lan_address    = confs['privateaddress']
            self.lan_netmask    = confs['privatenetmask']

            lan_mac = os.popen("nmcli device show %s|grep -i general.hwaddr|awk '{print $2}'" % 
                                    self.lan_device).readline().strip()
            self.lan_mac        = confs['privatemacaddr']   if 'privatemacaddr'     in confs else lan_mac

            lan_cidr            = self.net.netmask_to_cidr(self.lan_netmask)
            self.lan_cidr       = confs['privatecidr']      if 'privatecidr'        in confs else lan_cidr

            lan_network = self.net.getNetwork(self.lan_address, self.lan_netmask)
            self.lan_network    = confs['privatenetwork']   if 'privatenetwork'     in confs else lan_network


            self.lan_gateway    = confs['privategateway']   if 'privategateway'     in confs else self.lan_address
            self.lan_ntphost    = confs['privatentphost']   if 'privatentphost'     in confs else self.lan_address
            self.lan_dns        = confs['privatednsserver'] if 'privatednsserver'   in confs else self.lan_address
            self.lan_domain     = confs['privatednsdomain'] if 'privatednsdomain'   in confs else 'local'
            self.lan_mtu        = confs['privatemtu']       if 'privatemtu'         in confs else '1500'

        except KeyError as e:
            self.msg("The %s config file error. Field -> *" % conf, 'a', e.args)

        self.daemonPort     = confs['plugin_port'] if 'plugin_port' in confs else '12345'
        self.time_zone      = confs['timezone'] if 'timezone' in confs else 'Asia/Shanghai'
        self.def_args       = confs['bootargs'] if 'bootargs' in confs else 'net.ifnames=0 biosdevname=0'
        self.sunhpcdist     = confs['distribution'] if 'distribution' in confs else 'sunhpc-dist'
        self.ksbasedir      = confs['basedir'] if 'basedir' in confs else 'install'
        self.pxefilename    = confs['dhcp_filename'] if 'dhcp_filename' in confs else 'pxelinux.0'
        self.nextserver     = confs['dhcp_nextserver'] if 'dhcp_nextserver' in confs else self.lan_address
        self.gangliaaddr    = confs['ganglia'] if 'ganglia' in confs else '224.0.0.3'
        self.pxelinuxdir    = confs['pxelinuxdir'] if 'pxelinuxdir' in confs else '/tftpboot/pxelinux'


    def attributions(self):
        attrs = [
                ('Info_CertificateCountry',     self.country,       '', 1),
                ('Info_CertificateState',       self.state,         '', 1),
                ('Info_CertificateLocality',    self.city,          '', 1),
                ('Info_CertificateOrganization','DLHP',             '', 1),
                ('Info_ClusterUrl',             self.URL,           '', 1),
                ('Info_ClusterName',            self.cname,         '', 1),
                ('Info_ClusterContact',         self.contact,       '', 1),
                ('Kickstart_WorksDir',          self.worksdir,      '', 1),
                ('Kickstart_DistroDir',         self.distrodir,     '', 1),
                ('Kickstart_Partition',         self.partition,     '', 1),
                ('Kickstart_PublicHostname',    self.wan_hostname,  '', 1),
                ('Kickstart_PublicInterface',   self.wan_device,    '', 1),
                ('Kickstart_PublicAddress',     self.wan_address,   '', 1),
                ('Kickstart_PublicMacAddr',     self.wan_mac,       '', 1),
                ('Kickstart_PublicNetmask',     self.wan_netmask,   '', 1),
                ('Kickstart_PublicNetmaskCIDR', self.wan_cidr,      '', 1),
                ('Kickstart_PublicGateway',     self.wan_gateway,   '', 1),
                ('Kickstart_PublicNetwork',     self.wan_network,   '', 1),
                ('Kickstart_PublicNTPHost',     self.wan_ntphost,   '', 1),
                ('Kickstart_PublicDNSServer',   self.wan_dns,       '', 1),
                ('Kickstart_PublicDNSDomain',   self.wan_domain,    '', 1),
                ('Kickstart_PublicMTU',         self.wan_mtu,       '', 1),

                ('Kickstart_PrivateHostname',   self.lan_hostname,  '', 1),
                ('Kickstart_PrivateInterface',  self.lan_device,    '', 1),
                ('Kickstart_PrivateAddress',    self.lan_address,   '', 1),
                ('Kickstart_PrivateMacAddr',    self.lan_mac,       '', 1),
                ('Kickstart_PrivateNetmask',    self.lan_netmask,   '', 1),
                ('Kickstart_PrivateNetmaskCIDR',self.lan_cidr,      '', 1),
                ('Kickstart_PrivateGateway',    self.lan_address,   '', 1),
                ('Kickstart_PrivateNetwork',    self.lan_network,   '', 1),
                ('Kickstart_PrivateNTPHost',    self.lan_ntphost,   '', 1),
                ('Kickstart_PrivateDNSServer',  self.lan_dns,       '', 1),
                ('Kickstart_PrivateDNSDomain',  self.lan_domain,    '', 1),
                ('Kickstart_PrivateMTU',        self.lan_mtu,       '', 1),

                ('Kickstart_Plugin_Port',       self.daemonPort,    '', 1),
                ('Kickstart_Plugin_Keys',       self.daemon_pass(), '', 1),

                ('Kickstart_Timezone',          self.time_zone,     '', 1),
                ('Kickstart_Bootargs',          self.def_args,      '', 1),
                ('distribution',                self.sunhpcdist,    '', 1),
                ('Kickstart_BaseDir',           self.ksbasedir,     '', 1),

                ('safeport',                    self.safeport,      '', 1),
                ('safedirs',                    self.safedirs,      '', 1),
                ('safesecurity',                self.safesecurity,  '', 1),

                ('sunhpc_version',              sunhpc.version,     '', 1),
                ('sunhpc_major',                self.major,         '', 1),
                ('sunhpc_minor',                self.minor,         '', 1),
                ('sunhpc_micro',                self.micro,         '', 1),
                ('sunhpc_release',              sunhpc.release,     '', 1),
                ('ganglia_address',             self.gangliaaddr,   '', 1),

                ('dhcp_filename',               self.pxefilename,   '', 1),
                ('dhcp_nextserver',             self.nextserver,    '', 1),
                ('pxelinuxdir',                 self.pxelinuxdir,   '', 1),

                ('kickstartable',               'yes',              '', 1),
                ('managed',                     'true',             '', 1),
                ('os',                          'linux',            '', 1)
                ]
        return attrs

    def tableData(self):
        queuelist   = []
        bootver     = '%s-%s' % (sunhpc.version, self.arch)
        vmlinuz     = 'vmlinuz-%s' % bootver
        initrds     = 'initrd-%s'  % bootver
        defArgs     = "ramdisk_size=150000 net.ifnames=0 biosdevname=0"
        insArgs     = "%s inst.ks.sendmac" % (defArgs)
        resArgs     = "%s rescue" % defArgs
        lesArgs     = "%s vnc"    % defArgs
        bootaction  = [ 
                "insert into bootactions values (1, 'install', '%s', '%s', '%s')" % (vmlinuz, initrds, insArgs),
                "insert into bootactions values (2, 'os', 'localboot 0', NULL, NULL)",
                "insert into bootactions values (3, 'memtest', 'kernel memtest', NULL, NULL)",
                "insert into bootactions values (4, 'install headless', '%s', '%s', '%s')" % (vmlinuz, initrds, lesArgs),
                "insert into bootactions values (5, 'rescue', '%s', '%s', '%s')" % (vmlinuz, initrds, resArgs),
                "insert into bootactions values (6, 'pxeflash', 'kernel memdisk bigraw', 'pxeflash.img', 'keeppxe')"]

        queuelist.extend(bootaction)

        distributions = [
            "insert into distributions values (1, 'sunhpc-dist', '%s', '%s')" % (self.os, sunhpc.release)
        ]
        queuelist.extend(distributions)

        cmds    = "dmidecode -t memory|sed s/[[:space:]]//g|grep '^Size'|grep -E 'MB|GB'"
        Names   = os.popen("cat /sys/class/dmi/id/product_name").readline().strip()
        Vender  = os.popen("cat /sys/class/dmi/id/sys_vendor").readline().strip()
        Serial  = os.popen("cat /sys/class/dmi/id/product_serial").readline().strip()
        CPUs    = os.popen("dmidecode -t processor|grep Version|cut -d ':' -f2|wc -l").readline().strip()
        Core    = os.popen("cat /proc/cpuinfo |grep processor|wc -l").readline().strip()
        Model   = os.popen("dmidecode -t processor|grep Version|head -n1|cut -d ':' -f2").readline().strip()
        MemNumb = os.popen("%s|wc -l" % cmds).readline().strip()
        MemSize = os.popen("%s|head -n1|cut -d ':' -f2" % cmds).readline().strip()

        Machines = [
            "insert into machines values (1, 1, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % 
            (Names, Vender, Serial, CPUs, Core, Model, MemNumb, MemSize)
        ]
        queuelist.extend(Machines)

        part        = sunhpc.core.partition.Partition()
        part.discoveredDisks()
        disks       = part.getDisks()
        nodeDisks   = part.getNodePartInfo(disks)
        disklist    = sorted(nodeDisks)
        for disk in disklist:
            for d in nodeDisks[disk]:
                dev, sec, size, partid, fstype, bootflags, partflags, mountpoint = d
                if dev and sec and size:
                    queuelist.append('''
                insert into partitions(node, device, mountpoint, sectorstart,
                partitionsize, fstype, partitionflags, formatflags) values 
                (1, "%s", "%s", "%s" ,"%s", "%s", "%s", "%s")''' % (
            dev, mountpoint, sec, size, fstype, bootflags, partflags))

        for attr in self.attributions():
            k, v, s, n = attr
            queuelist.append('insert into attributes values (NULL, "%s","%s", "%s", %d)' % (k, v, s, n))

        # add frontend node
        nodes = [ "insert into nodes values (1, '%s', %d, 0, 0, '%s', '%s', '','', 'os')" \
                    % (self.lan_hostname, int(Core), self.arch, self.os)]
        queuelist.extend(nodes)

        # add frontend network
        network = [ "insert into networks values (1, 1, '%s', '%s', '%s', '%s', '2')" % 
                        (self.wan_mac, self.wan_address, self.lan_hostname, self.wan_device),
                    "insert into networks values (2, 1, '%s', '%s', '%s', '%s', '1')" % 
                        (self.lan_mac, self.lan_address, self.lan_hostname, self.lan_device)]
        queuelist.extend(network)

        # add subnet data
        subnet = [ "insert into subnets values (1, 'private', '%s', '%s', '%s', '%s', '1')" % 
                        (self.lan_domain, self.lan_network, self.lan_netmask, self.lan_mtu),
                   "insert into subnets values (2, 'public', '%s', '%s', '%s', '%s', '0')" % 
                        (self.wan_domain, self.wan_network, self.wan_netmask, self.wan_mtu)]
        queuelist.extend(subnet)

        # add globalRouters
        globalrouter = [ "insert into globalroutes values ('0.0.0.0', '0.0.0.0', '%s', NULL)" % 
                        self.lan_address,
                   "insert into globalroutes values ('%s', '255.255.255.255', '%s', NULL)" % 
                        (self.wan_address, self.lan_address)]
        queuelist.extend(globalrouter)

        return queuelist

    def baseTables(self):
        """Base data tables"""

        drop    = 'DROP TABLE IF EXISTS tablename'
        tables  = ['Nodes', 'Networks', 'Subnets', 'GlobalRouters',
                   'PublicKeys', 'SecNodes', 'Attributes', 'Partitions',
                   'Firewalls', 'Rolls', 'Bootactions', 'distributions', 
                   'SecGlobals'
                  ]
        datalist = []

        Nodes = '''
        CREATE TABLE Nodes (
            ID            integer primary key autoincrement,
            Name          varchar(128) default NULL,
            CPUs          integer(11) NOT NULL default '1',
            Rack          integer(11) default NULL,
            Rank          integer(11) default NULL,
            Arch          varchar(32) default NULL,
            OS            varchar(64) NOT NULL default 'linux',
            Alias         varchar(64) default '',
            Flags         varchar(256) default '',
            Status        varchar(32) default 'os'
        )'''
        datalist.append(Nodes)

        Networks = '''
        CREATE TABLE Networks (
            ID        integer NOT NULL primary key autoincrement,
            Node      integer(11) default NULL,
            MAC       varchar(64) default NULL,
            IP        varchar(32) default NULL,
            Name      varchar(128) default NULL,
            Device    varchar(32) default NULL,
            Subnet    integer(11) default NULL,
            Foreign key(subnet) references subnets(id) on delete cascade on update restrict,
            Foreign key(node) references nodes(id) on delete cascade on update restrict
        )'''
        datalist.append(Networks)

        Subnets = '''
        CREATE TABLE Subnets (
            ID          integer NOT NULL primary key autoincrement,
            name        varchar(32) UNIQUE NOT NULL,
            dnszone     varchar(64) UNIQUE NOT NULL ,
            subnet      varchar(32) NOT NULL,
            netmask     varchar(32) NOT NULL,
            mtu         integer(11) default '1500',
            servedns    boolean     default false
        )'''
        datalist.append(Subnets)

        GlobalRouters = '''
        CREATE TABLE GlobalRoutes (
            Network     varchar(32) NOT NULL default '',
            Netmask     varchar(32) NOT NULL default '',
            Gateway     varchar(32) NOT NULL default '',
            Subnet      integer(11) default NULL,
            Primary key(Network, Netmask),
            Foreign key(subnet) references subnets(id) on delete cascade on update restrict
        )'''
        datalist.append(GlobalRouters)

        PublicKeys = '''
        CREATE TABLE PublicKeys (
            ID             integer NOT NULL primary key autoincrement,
            Public_Key     varchar(4096) default NULL,
            Description    varchar(4096) default NULL,
            Node           integer(11) NOT NULL default '0',
            Foreign key(Node) references nodes(id) on delete cascade on update restrict
        )'''
        datalist.append(PublicKeys)

        SecNodes = '''
        CREATE TABLE SecNodes (
            Attr      varchar(128) default NULL,
            Enc       varchar(64) default NULL,
            Value     text,
            Node      integer(11) NOT NULL default '0',
            PRIMARY KEY (Node, Attr)
        )'''
        datalist.append(SecNodes)

        Attributes = '''
        CREATE TABLE Attributes (
            ID      integer NOT NULL primary key autoincrement,
            Attr    varchar(128) NOT NULL,
            Value   text,
            Shadow  text,
            Node    integer(11) NOT NULL,
            Foreign key(Node) references nodes(id) on delete cascade on update restrict
        )'''
        datalist.append(Attributes)

        Partitions = '''
        CREATE TABLE Partitions (
            ID              integer NOT NULL primary key autoincrement,
            Node            integer(11)  NOT NULL default '0',
            Device          varchar(128) NOT NULL default '',
            Mountpoint      varchar(128) NOT NULL default '',
            SectorStart     varchar(128) NOT NULL default '',
            PartitionSize   varchar(128) NOT NULL default '',
            PartitionID     varchar(128) NOT NULL default '',
            FsType          varchar(128) NOT NULL default '',
            PartitionFlags  varchar(128) NOT NULL default '',
            FormatFlags     varchar(128) NOT NULL default ''
        )'''
        datalist.append(Partitions)

        Firewalls = '''
        CREATE TABLE Firewalls (
            ID          integer NOT NULL primary key autoincrement,
            Rulename    varchar(128) NOT NULL,
            Service     varchar(256),
            Protocol    varchar(256),
            Ports       varchar(256),
            Action      varchar(256),
            Comment     varchar(256),
            Node        integer(11)  NOT NULL default '0'
        )'''
        datalist.append(Firewalls)

        Rolls = '''
        CREATE TABLE Rolls (
            ID        integer NOT NULL primary key autoincrement,
            Name      varchar(128) NOT NULL default '',
            Version   varchar(32)  NOT NULL default '',
            Arch      varchar(32)  NOT NULL default '',
            OS        varchar(64)  NOT NULL default 'linux',
            Enabled   varchar(11)  NOT NULL default 'yes'
        )'''
        datalist.append(Rolls)

        Bootactions = '''
        CREATE TABLE Bootactions (
            ID        integer NOT NULL primary key autoincrement,
            Action    varchar(1024) default NULL,
            Kernel    varchar(1024) default NULL,
            Ramdisk   varchar(1024) default NULL,
            Args      varchar(1024) default NULL
        )'''
        datalist.append(Bootactions)

        Distributions = '''
        CREATE TABLE Distributions (
            ID        integer NOT NULL primary key autoincrement,
            Name      varchar(32) NOT NULL default '',
            OS        varchar(32) default '',
            Release   varchar(32) default ''
        )'''
        datalist.append(Distributions)

        SecGlobals = '''
        CREATE TABLE SecGlobals (
            Attr          varchar(128) default NULL,
            Value         text,
            Enc           varchar(128) default NULL,
            PRIMARY KEY (Attr)
        )'''
        datalist.append(SecGlobals)

        Machines = '''
        CREATE TABLE Machines (
            ID          integer primary key autoincrement,
            Node        integer(128) default '0',
            Name        varchar(128) default NULL,
            Vender      varchar(128) default NULL,
            Serial      varchar(128) default NULL,
            CPUs        integer(128) default '1',
            Cores       integer(128) default '1',
            Model       varchar(128) default '',
            MemNumber   integer(128) default '1',
            MemSize     varchar(64) default NULL,
            foreign key(Node) references nodes(id) on delete cascade on update restrict
        )'''
        datalist.append(Machines)

        return datalist

    def daemon_pass(self):
        char  = 'abcdefghijklmnopqrstuvwxyz'
        char += '!@#$%+=-_^&*.?'
        char += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
        return ''.join(random.sample(char,16))














