# Copyright 2011 Daniele Nicolodi <nicolodi@science.unitn.it>
#
# This software may be used and distributed according to the terms of
# the GNU Affero General Public License version 3 or any later version.

import MySQLdb as mysql

from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD


def install():
    """create LTDA repository database tables"""

    conn = mysql.connect(host=HOSTNAME, db='', user=USERNAME, passwd=PASSWORD, charset='utf8')
    curs = conn.cursor()

    curs.execute("CREATE DATABASE `%s`" % DATABASE)
    conn.close()

    conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8')
    curs = conn.cursor()

    curs.execute("""CREATE TABLE `available_dbs` (
                      `id` int(10) NOT NULL AUTO_INCREMENT,
                      `db_name` varchar(50) NOT NULL,
                      `name` varchar(50) NOT NULL,
                      `description` text NOT NULL,
                      `version` int(11) DEFAULT '1',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `database` (`db_name`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8""")

    curs.execute("""CREATE TABLE `users` (
                      `id` int(11) NOT NULL AUTO_INCREMENT,
                      `username` varchar(50) NOT NULL,
                      `family_name` varchar(50) NOT NULL,
                      `given_name` varchar(50) NOT NULL,
                      `email` varchar(80) NOT NULL,
                      `institution` varchar(150) NOT NULL,
                      `telephone` varchar(50) NOT NULL,
                      `is_admin` tinyint(1) NOT NULL,
                      PRIMARY KEY (`id`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8""")

    curs.execute("""CREATE TABLE `queries` (
                      `id` int NOT NULL AUTO_INCREMENT,
                      `title` text NOT NULL,
                      `db` text NOT NULL,
                      `query` text NOT NULL,
                      PRIMARY KEY (`id`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8""")

    curs.execute("""CREATE TABLE `options` (
                      `name` varchar(50) NOT NULL,
                      `value` text NOT NULL,
                      PRIMARY KEY (`name`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8""")

    curs.execute("""
    CREATE PROCEDURE makeintervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
    BEGIN
    -- create temporary timeintervals table with dt field specifed
    -- from the startdate and enddate arguments at intervals of intval
    -- unitval size

    declare thisdate timestamp;
    declare nextdate timestamp;
    set thisdate = startdate;
    
    -- create temp table
    drop temporary table if exists timeintervals;
    create temporary table if not exists timeintervals (begin timestamp, end timestamp);

    -- loop through the startdate adding each intval interval until enddate
    repeat
        select
           case unitval
               when 'SECOND'      then timestampadd(SECOND, intval, thisdate)
               when 'MINUTE'      then timestampadd(MINUTE, intval, thisdate)
               when 'HOUR'        then timestampadd(HOUR, intval, thisdate)
               when 'DAY'         then timestampadd(DAY, intval, thisdate)
               when 'WEEK'        then timestampadd(WEEK, intval, thisdate)
               when 'MONTH'       then timestampadd(MONTH, intval, thisdate)
               when 'QUARTER'     then timestampadd(QUARTER, intval, thisdate)
               when 'YEAR'        then timestampadd(YEAR, intval, thisdate)
           end into nextdate;

      insert into timeintervals select thisdate, timestampadd(second, -1, nextdate);
      set thisdate = nextdate;
    until thisdate > enddate
    end repeat;
    END;""")

    curs.execute("""INSERT INTO `options` VALUES ('version', '29')""")

    conn.commit()
    conn.close()