package com.adobe.arise.database
{

    import flash.data.SQLConnection;
    import flash.data.SQLStatement;
    import flash.events.SQLEvent;
    import com.adobe.xml.syndication.generic.IFeed;
    import com.adobe.xml.syndication.generic.IItem;
    import com.adobe.xml.syndication.generic.Author;

    public class AggregateFeed
        extends DBOperation
    {
        private var feedUrl:String;
        private var feed:IFeed;
        private var items:Array;
        private var item:IItem;
        private var feedId:Number;
        private var itemId:Number;
        private var authors:Array;
        private var topics:Array;
        private var author:Author;
        private var topic:String;
        private var isNewPost:Boolean;
        
        public function AggregateFeed(responder:DatabaseResponder, sql:XML, connection:SQLConnection, feedUrl:String, feed:IFeed)
        {
            this.feedUrl = feedUrl;
            this.feed = feed;
            super(responder, sql, connection);
        }
        
        public function execute():void
        {
            var findFeedStmt:SQLStatement = this.getSQLStatement();
            findFeedStmt.addEventListener(SQLEvent.RESULT,
                function(findFeedEvent:SQLEvent):void
                {
                    var feedStmt:SQLStatement = getSQLStatement();
                    feedStmt.addEventListener(SQLEvent.RESULT,
                        function(e:SQLEvent):void
                        {
                            feedId = SQLStatement(e.target).getResult().lastInsertRowID;
                            items = feed.items;
                            aggregatePosts();
                        });

                    var feedResult:Array = SQLStatement(findFeedEvent.target).getResult().data;

                    // insert a new feed
                    if (feedResult == null || feedResult.length == 0)
                    {
                        // TBD: use all correct values
                        feedStmt.text = sql.feeds.insert;
                        feedStmt.parameters[":name"] = feed.metadata.title;
                        feedStmt.parameters[":description"] = feed.metadata.description;
                        feedStmt.parameters[":icon"] = null;
                        feedStmt.parameters[":url"] = feedUrl;
                        feedStmt.parameters[":sort_order"] = -1;
                        feedStmt.parameters[":etag"] = null;
                        feedStmt.parameters[":last_updated"] = new Date();
                        feedStmt.parameters[":parsable"] = 1;
                        feedStmt.parameters[":error_message"] = null;
                    }
                    else // update the existing feed
                    {
                        // TBD: use all correct values
                        feedStmt.text = sql.feeds.update;
                        feedStmt.parameters[":name"] = feed.metadata.title;
                        feedStmt.parameters[":description"] = feed.metadata.description;
                        feedStmt.parameters[":icon"] = null;
                        feedStmt.parameters[":url"] = feedUrl;
                        feedStmt.parameters[":sort_order"] = -1;
                        feedStmt.parameters[":etag"] = null;
                        feedStmt.parameters[":last_updated"] = new Date();
                        feedStmt.parameters[":parsable"] = 1;
                        feedStmt.parameters[":error_message"] = null;
                        feedStmt.parameters[":feed_id"] = feedResult[0].id;
                    }
                    feedStmt.execute();
                });
            findFeedStmt.text = sql.feeds.selectIdByUrl;
            findFeedStmt.parameters[":feed_url"] = feedUrl;
            findFeedStmt.execute();
        }

        private function aggregatePosts():void
        {
            if (items == null || items.length == 0)
            {
                dispatchEmptyResultEvent(null);
                return;
            }
            item = items.shift();
            if (item.title == null || item.link == null || item.date == null)
            {
                aggregatePosts();
                return;
            }
            var findPostStmt:SQLStatement = getSQLStatement();
            findPostStmt.addEventListener(SQLEvent.RESULT,
                function(findPostEvent:SQLEvent):void
                {
                    var postStatement:SQLStatement = getSQLStatement();
                    postStatement.addEventListener(SQLEvent.RESULT,
                        function(e:SQLEvent):void
                        {
                            itemId = (isNewPost) ? SQLStatement(e.target).getResult().lastInsertRowID : postResult[0].id;
                            authors = item.authors;
                            topics = item.topics;
                            if (isNewPost)
                            {
                                insertAuthors();
                            }
                            else
                            {
                                updateAuthors();
                            }
                        });
                    var postResult:Array = SQLStatement(findPostEvent.target).getResult().data;
                    // insert a new post
                    if (postResult == null || postResult.length == 0)
                    {
                        isNewPost = true;
                        postStatement.text = sql.posts.insert;
                        postStatement.parameters[":feed_id"] = feedId;
                        postStatement.parameters[":title"] = item.title;
                        postStatement.parameters[":content"] = item.excerpt.value;
                        postStatement.parameters[":url"] = item.link;
                        postStatement.parameters[":post_date"] = item.date;
                        postStatement.parameters[":read"] = 0;
                        postStatement.parameters[":checked"] = 0;
                        postStatement.parameters[":scrapbooked"] = 0;
                    }
                    else // update an existing post
                    {
                        isNewPost = false;
                        postStatement.text = sql.posts.update;
                        postStatement.parameters[":title"] = item.title;
                        postStatement.parameters[":content"] = item.excerpt.value;
                        postStatement.parameters[":url"] = item.link;
                        postStatement.parameters[":post_date"] = item.date;
                        postStatement.parameters[":read"] = postResult[0].read;
                        postStatement.parameters[":checked"] = postResult[0].checked;
                        postStatement.parameters[":scrapbooked"] = postResult[0].scrapbooked;
                        postStatement.parameters[":post_id"] = postResult[0].id;
                    }
                    postStatement.execute();
                });
            findPostStmt.text = sql.posts.selectByUrl;
            findPostStmt.parameters[":post_url"] = item.link;
            findPostStmt.execute();
        }

        private function insertAuthors():void
        {
            if (authors == null || authors.length == 0 || authors[0].name == null)
            {
                if (isNewPost)
                {
                    insertTopics();
                }
                else
                {
                    updateTopics();
                }
                return;
            }
            author = authors.shift();
            var insertAuthorStmt:SQLStatement = getSQLStatement();
            insertAuthorStmt.addEventListener(SQLEvent.RESULT,
                function(insertAuthorEvent:SQLEvent):void
                {
                    if (authors.length > 0)
                    {
                        insertAuthors();
                        return;
                    }
                    else
                    {
                        if (isNewPost)
                        {
                            insertTopics();
                        }
                        else
                        {
                            updateTopics();
                        }
                        return;
                    }
                });
            insertAuthorStmt.text = sql.authors.insert;
            insertAuthorStmt.parameters[":post_id"] = itemId;
            insertAuthorStmt.parameters[":author"] = author.name;
            insertAuthorStmt.execute();
        }

        private function updateAuthors():void
        {
            var deleteAuthorStmt:SQLStatement = getSQLStatement();
            deleteAuthorStmt.addEventListener(SQLEvent.RESULT,
                function(deleteAuthorEvent:SQLEvent):void
                {
                    insertAuthors();
                    return;
                });
            deleteAuthorStmt.text = sql.authors.deleteByPostId;
            deleteAuthorStmt.parameters[":post_id"] = itemId;
            deleteAuthorStmt.execute();
        }

        private function insertTopics():void
        {
            if (topics == null || topics.length == 0 || topics[0] == null)
            {
                aggregatePosts();
                return;
            }
            topic = topics.shift();
            var insertTopicStmt:SQLStatement = getSQLStatement();
            insertTopicStmt.addEventListener(SQLEvent.RESULT,
                function(insertTopicEvent:SQLEvent):void
                {
                    if (topics.length > 0)
                    {
                        insertTopics();
                        return;
                    }
                    else
                    {
                        aggregatePosts();
                        return;
                    }
                });
            insertTopicStmt.text = sql.topics.insert;
            insertTopicStmt.parameters[":post_id"] = itemId;
            insertTopicStmt.parameters[":topic"] = topic;
            insertTopicStmt.execute();
        }

        private function updateTopics():void
        {
            var deleteTopicsStmt:SQLStatement = getSQLStatement();
            deleteTopicsStmt.addEventListener(SQLEvent.RESULT,
                function(deleteTopicEvent:SQLEvent):void
                {
                    insertTopics();
                    return;
                });
            deleteTopicsStmt.text = sql.topics.deleteByPostId;
            deleteTopicsStmt.parameters[":post_id"] = itemId;
            deleteTopicsStmt.execute();
        }
    }
}