#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ***************************************************************************
# *   Copyright (C) 2012, Paul Lutus                                        *
# *                                                                         *
# *   This program 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.                                   *
# *                                                                         *
# *   This program 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 this program; if not, write to the                         *
# *   Free Software Foundation, Inc.,                                       *
# *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
# ***************************************************************************

import re, sys, os, MySQLdb, getpass, random

# suppress warning messages

import warnings 
warnings.simplefilter("ignore") 

# very important -- seed the random number generator
# so that each run of this program will create the same
# pseudorandomly distributed table

random.seed(12345)

copyright ="""
--  ***************************************************************************
--  *   Copyright (C) 2012, Paul Lutus                                        *
--  *                                                                         *
--  *   This program 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.                                   *
--  *                                                                         *
--  *   This program 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 this program; if not, write to the                         *
--  *   Free Software Foundation, Inc.,                                       *
--  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
--  ***************************************************************************
"""

head = """
create database if not exists tutorial;
use tutorial;
"""

def execsql(mysql):
  global db_conn
  cursor = db_conn.cursor()
  cursor.execute(mysql)
  result = cursor.fetchall()
  cursor.close()
  return result
    
if(len(sys.argv) > 1):
  pw = sys.argv[1]
else:
  pw = getpass.getpass('Enter password: ')

db_conn = MySQLdb.connect(host='localhost',user=os.environ['USER'],passwd=pw)

coms = (
  'create database if not exists tutorial',
  'use tutorial',
  'drop table if exists people',
  'create table people (`First Name` text not null, `Last Name` text not null, Gender enum(\'M\',\'F\') not null,Age integer not null, Music text not null, Art text not null)'
  )
for com in coms:
  execsql(com)
  
last_names = ('Hill','Scott','Green','Allen','Young','Smith','Jones','Wilson','Cartwright','Brown','Davis','Miller','Moore','Anderson','Jackson','Harris','Martin','Thompson','Lewis','Walker','Taylor','Garcia','Suzuki','Cheong','Smirnov')

first_names_male = ('John','William','Fred','George','Harry','Eric','Frank','James','Richard','Joseph','Christopher','Mark','Donald','Kenneth','Robert','Kevin','Jason','Gary','Timothy','Andrew')

first_names_female = ('Mary','Jane','Anne','Gertrude','Kimberly','Shannon','Katherine','Patricia','Barbara','Margaret','Lisa','Nancy','Karen','Jennifer','Betty','Elizabeth','Helen','Carol','Ruth','Sharon','Michelle','Laura','Sarah','Cynthia','Melissa')

musical_preferences = ('Rock','Classical','Baroque','Jazz','C & W')

artistic_preferences = ('Modern','Impressionist','Abstract','Realist','Surrealist','Romantic')

def fetch_string(strings):
  ln = len(strings)
  i = int(random.random() * ln)
  return strings[i]
  
def fetch_age():
  return int(random.random() * 60 + 13)
  
records = []
  
count = 1000

for n in range(count):
  gender = ('M','F')[n < count/2]
  first_names = (first_names_male,first_names_female)[gender == 'F']
  # loop until a unique record is generated
  while(True):
    # create partial record with just first and last name
    testrec = [fetch_string(first_names),fetch_string(last_names)]
    # make sure this is not a duplicate of an existing record
    if (testrec not in records):
      # save partial record for later comparison
      records.append(testrec)
      # create complete record with remaining fields
      record = testrec + [gender,fetch_age(),fetch_string(musical_preferences),fetch_string(artistic_preferences)]
      break
  a,b,c,d,e,f = record
  # insert this record into table
  execsql('insert into people values(\'%s\',\'%s\',\'%s\',%s,\'%s\',\'%s\')' % (a,b,c,d,e,f))
  
execsql('alter table people order by `Last Name`,`First Name`')

# can only add primary key after preferred sort

execsql('alter table people add column pk integer not null primary key auto_increment')

db_conn.commit()

os.system('echo "%s%s" > people.sql' % (copyright,head))

# create a mysqldump version of the table

os.system('mysqldump -p%s tutorial people >> people.sql' % pw)

print 'Created table "tutorial.people" with %d members.' % count