#!/usr/bin/env python

import psycopg2
import random
from optparse import OptionParser

(SEQ, RAND) = (0, 1)

def drop_table(cur, name):
    sql = 'DROP TABLE IF EXISTS {0}'.format(name)
    cur.execute(sql)
    return

def create_table(cur, name, columns):
    sql = 'CREATE TABLE {0} ('.format(name)
    sql += ', '.join([ '{0} INT'.format(col) for col in columns ])
    sql += ')'
    cur.execute(sql)
    return

def insert_row_preprocess(name, tup):
    sql = 'INSERT INTO {0} VALUES ('.format(name)
    sql += ', '.join(['%s'] * len(tup))
    sql += ')'
    return sql

def load_table(cur, name, gens, fraction, card):
    tup = [0] * len(gens)
    presql = insert_row_preprocess(name, tup)
    nrows = int(card * fraction)
    newgens = []
    for gen in gens:
        if gen[1] <= 1.:
            newgens.append((gen[0], int(gen[1] * card)))
        else:
            newgens.append((gen[0], gen[1]))
    for i in xrange(nrows):
        for j, gen in enumerate(newgens):
            if gen[0] == SEQ:
                tup[j] = i
            elif gen[0] == RAND:
                tup[j] = random.randint(0, gen[1]-1)
        try:
            cur.execute(presql, tup)
        except:
            print presql, tup
    return

CONFIGS = [
  {
    'name': 'A',
    'gens': [ (SEQ, 0), (RAND, 1/2.0), (RAND, 1/4.0), (RAND, 1/16.0),
        (RAND, 1<<30), (RAND, 1<<16), (RAND, 1<<8) ],
    'columns': [ 'P', 'BF', 'CF', 'DF', 'X', 'Y', 'Z' ],
    'cardinality': 1,
  },
  {
    'name': 'B',
    'gens': [ (SEQ, 0), (RAND, 1<<30), ],
    'columns': [ 'P', 'X' ],
    'cardinality': 1/2.0,
  },
  {
    'name': 'C',
    'gens': [ (SEQ, 0), (RAND, 1<<30), ],
    'columns': [ 'P', 'X' ],
    'cardinality': 1/4.0,
  },
  {
    'name': 'D',
    'gens': [ (SEQ, 0), (RAND, 1<<30), ],
    'columns': [ 'P', 'X' ],
    'cardinality': 1/16.0,
  },
]

def main():
    parser = OptionParser()
    parser.add_option("-d", "--db", dest="db",
            help="Database name to connect to.", default="simple")
    parser.add_option("-u", "--user", dest="user",
            help="Username to access the database.", default="simple")
    parser.add_option("-p", "--password", dest="password",
            help="Username to access the database.", default="simple")
    parser.add_option("-c", "--cardinality", dest="cardinality",
            help="Cardinality of the fact table.", default='1000')
    (options, args) = parser.parse_args()
    options.cardinality = int(options.cardinality)

    conn = psycopg2.connect('dbname={0} user={1} password={2}'.format(
        options.db, options.user, options.password))
    cur = conn.cursor()

    for config in CONFIGS:
        drop_table(cur, config['name'])
        conn.commit()
        create_table(cur, config['name'], config['columns'])
        load_table(cur, config['name'], config['gens'], config['cardinality'],
                options.cardinality)

    conn.commit()

if __name__ == '__main__':
    main()