#-------------------------------------------------------------------------------
# Name:        module1
# Purpose:
#
# Author:      User
#
# Created:     01/02/2015
# Copyright:   (c) User 2015
# Licence:     <your licence>
#-------------------------------------------------------------------------------
 
 
import config
import json
import mysql.connector
from utils import *
 
 
def insert_json(connection,story_json):
    cursor =  connection.cursor()
    story_api_dict = json.loads(story_json)
    values = {
    "author_id":"",#1
    "author_name":"",
    "categories":"",
    "chapters":"",
    "comments":"",#5
    "content_rating":"",
    "content_rating_text":"",
    "date_modified":"",
    "description":"",
    "dislikes":"",#10
    "full_image":"",
    "story_id":"",
    "image":"",
    "likes":"",
    "short_description":"",
    "status":"",
    "title":"",
    "total_vievs":"",
    "url":"",
    "views":"",#20
    "words":"",
    "version_number":"",
    "folder_path":"",#23
    }
    fields = values.keys()
    values = values.values()
    query = (
    "INSERT INTO table (chapter_metadata) VALUES (%%s);" % (",".join(fields),)# Values from dict
    +"%s, "*22# 23 values to insert
    +"%s);"
    )
    print repr(query)
    result = cursor.execute(query,values)
    return
 
 
 
def insert_story_metadata(connection,api_dict,version=1):
    cursor =  connection.cursor()
    logging.debug("Inserting into story_metadata")
    story_dict = api_dict["story"]
    # Table: story_metadata
    story_metadata_values = {
    "version":version,
    "status":story_dict["status"],#1
    "total_views":story_dict["total_views"],
    "full_image":story_dict["full_image"],
    "description":story_dict["description"],
    "views":story_dict["views"],#5
    "date_modified":story_dict["date_modified"],
    "url":story_dict["url"],
    "image":story_dict["image"],
    "title":story_dict["title"],
    "dislikes":story_dict["dislikes"],# 10
    "comments":story_dict["comments"],
    "content_rating":story_dict["content_rating"],
    "chapter_count":story_dict["chapter_count"],
    "author_name":story_dict["author"]["name"],
    "author_id":story_dict["author"]["id"],# 15
    "words":story_dict["words"],
    "content_rating_text":story_dict["content_rating_text"],
    "short_description":story_dict["short_description"],
    "id":story_dict["id"],
    "likes":story_dict["likes"],# 20
    }
    fields = story_metadata_values.keys()
    values = story_metadata_values.values()
    query = (
    "INSERT INTO story_metadata (%s)"" VALUES ("% (",".join(fields))# Values from dict
    +"%s, "*20# 21 values to insert
    +"%s);"
    )
    print repr(query)
    result = cursor.execute(query, values)
    connection.commit()
    return
 
 
def insert_chapter_metadata(connection,api_dict,parent_story_id,chapter_id,version=1):
    cursor =  connection.cursor()
    logging.debug("Inserting into chapter_metadata")
    story_dict = api_dict["story"]
    chapters_list = story_dict["chapters"]
    # Table: chapter_metadata
    chapter_number_counter = 0
    for chapter_dict in chapters_list:
        chapter_number_counter += 1
        chapter_metadata_values = {
        # Local stuff
        "version":version,#1
        "parent_story_id":parent_story_id,
        "chapter_number":chapter_number_counter,
        # From API
        "id":story_dict["id"],
        "link":story_dict["link"],
        "title":story_dict["title"],
        "views":story_dict["views"],
        "words":story_dict["words"],#8
        }
        fields = chapter_metadata_values.keys()
        values = chapter_metadata_values.values()
        query = ("INSERT INTO table (chapter_metadata) VALUES (%%s);" % (",".join(fields),)# Values from dict
        +"%s, "*7# 8 values to insert
        +"%s);")
        print repr(query)
        result = cursor.execute(query,values)
        continue
    return
 
 
def insert_chapter_text(connection,parent_story_id,chapter_id,chapter_number,version=1,html=None,txt=None,epub=None):
    """Insert the text of a chapter into the DB"""
    cursor =  connection.cursor()
    chapter_to_insert = {
    "version":version,#1
    "parent_chapter_id":chapter_id,
    "parent_story_id":parent_story_id,
    "chapter_number":chapter_number,
    "chapter_text":txt,#5
    }
    fields = chapter_to_insert.keys()
    values = chapter_to_insert.values()
    query = ("INSERT INTO table (chapter_texts) VALUES (%%s);" % (",".join(fields),)# Values from dict
    +"%s, "*4# 8 values to insert
    +"%s);")
    print repr(query)
    result = cursor.execute(query,values)
    assert(False)
    return
 
 
 
 
 
def find_newest_version(connection,story_id):
    cursor =  connection.cursor()
    logging.debug("Finding latest version number for"+repr(story_id))
    query = (
    "SELECT * FROM (story_metadata) WHERE id = %s ORDER BY version LIMIT 1"
    )
    print repr(query)
    result = cursor.execute(query, (int(story_id),))
    print repr(cursor)
    print dir(cursor)
    if type(result) is int:
        return result
    elif type(result) is type(None):
        return 0
    else:
        print type(result)
        print repr(result)
        print dir(result)
        raise ValueError
    return
 
 
 
def lookup_date(connection,story_id,version):
    """Look up and return the unix time date stamp of version of a story"""
    #cursor =  connection.cursor()
    assert(False)
 
 
 
 
def show_api_structure():
    with open("api.json", "rb") as file:
        story_json = file.read()
    initial_story_api_dict = json.loads(story_json)
    story_api_dict = initial_story_api_dict["story"]
    #insert_json(story_json)
    for key in story_api_dict.keys():
        print repr(key)+""+repr(type(story_api_dict[key]))
 
 
def main():
    setup_logging(log_file_path=os.path.join("debug","fimfic-dl-sql-log.txt"))
    pass
    cnx = mysql.connector.connect(**config.sql_login)
    cursor = cnx.cursor()
 
 
 
    with open("api.json", "rb") as file:
        story_json = file.read()
        story_api_dict = json.loads(story_json)
    insert_story_metadata(cnx,story_api_dict)
 
 
    cnx.close()
 
if __name__ == '__main__':
    main()