#!/usr/bin/env python

import MySQLdb


# delete the database tables
def drop_tables (curs):

    # drop tables
    curs.execute('DROP TABLE IF EXISTS pages')
    curs.execute('DROP TABLE IF EXISTS categories')
    curs.execute('DROP TABLE IF EXISTS users')


# create database tables
def create_tables (curs):

    # create tables
    curs.execute("""CREATE TABLE pages (
                                path TEXT, 
                                title TEXT, 
                                category TEXT,
                                description TEXT,
                                tags TEXT,
                                status TEXT,
                                pubdate DATETIME, 
                                updated DATETIME
                              )""")


    curs.execute("""CREATE TABLE categories (
                                name TEXT,
                                path TEXT,
                                description TEXT,
                                tags TEXT,
                                status TEXT,
                                pubdate DATETIME, 
                                updated DATETIME
                              )""")


    curs.execute("""CREATE TABLE users (
                                username TEXT, 
                                password TEXT,
                                email_addr TEXT,
                                joined DATETIME
                              )""")


# add a user to the user table
def add_user (curs, user_name, password, email_addr):

    sql_str = "INSERT INTO users values(%s, %s, %s, CURRENT_DATE())"
    curs.execute(sql_str, (user_name, password, email_addr))


# get a user record from the database
def get_user (curs, user_name):

    sql_str = "SELECT * FROM users WHERE username=%s"
    curs.execute(sql_str, (user_name,))

    return curs.fetchone()



# the program starts here
def main():

    print "Setting up database..."


    conn = MySQLdb.connect('127.0.0.1', 'db_user', 'mypassword', 'my_db')
    curs = conn.cursor()


    # delete the tables 
    try:
        drop_tables(curs)
        conn.commit()
        print "Dropped tables\n"
    except:
        conn.rollback()
        print "Failed to drop tables\n"


    # create the database tables
    try:
        create_tables(curs)
        conn.commit()
        print "Created tables\n"
    except:
        conn.rollback()
        print "Failed to create tables\n"

    # add a user
    try:
        add_user(curs, "Steve", "password", "email@addr.com")
        conn.commit()
        print "Added user\n"
    except:
        conn.rollback()
        print "Failed to add user\n"

    # get a user record
    try:
        user = get_user (curs, "Steve")
        print user
    except:
        print "Failed to get user\n"

    # close the connection to the database
    conn.close()
    print "DB Connection closed"


if __name__ == "__main__":
    main()