import datetime
import re
from urllib.request import urlopen

import dateutil.parser
import sqlite3

from tag import Tag


DB_PATH = 'bookmarks.db'

SQL_SELECT_BOOKMARK = "SELECT * FROM bookmarks WHERE id=?"
SQL_INSERT_BOOKMARK = "INSERT INTO bookmarks VALUES (?,?,?,?,?,?,?,?,?)"
SQL_UPDATE_BOOKMARK = "UPDATE bookmarks SET {:s} WHERE id=?"
SQL_SELECT_CAT_TAGS = "SELECT tag_id FROM category_tags WHERE bookmark_id=?"
SQL_SELECT_DESC_TAGS = "SELECT tag_id FROM description_tags WHERE bookmark_id=?"

SQL_DELETE_CAT_TAGS = "DELETE FROM category_tags WHERE bookmark_id=?"
SQL_DELETE_DESC_TAGS = "DELETE FROM description_tags WHERE bookmark_id=?"

SQL_INSERT_CAT_TAGS = "INSERT INTO category_tags VALUES (?, ?)"
SQL_INSERT_DESC_TAGS = "INSERT INTO description_tags VALUES (?, ?)"

HTTP_OKAY = 200

class BookmarkNotFoundError(Exception):
    
    def __init__(self, value):
        self.value = value
        
    def __str__(self, value):
        return repr(self.value)
        

class DBConnectionError(Exception):
    
    def __init__(self, value):
        self.value = value
        
    def __str__(self, value):
        return repr(self.value)
        

def _format_date(date):
    if date is not None:
        return date.isoformat()
    else:
        return date


class Bookmark(object):
    
    def __init__(self, db_conn, id=None, url=None):
        
        # Make sure the DB connection is valid before doing anything else
        if not db_conn:
            raise DBConnectionError('Invalid database connection object!')
        
        # Setup internal parameters
        self._db_conn = db_conn
        self._content = None
        
        # Setup defaut object data attributes
        self.id = id
        self.url = url
        self.title = ''
        self.description = ''
        self.times_visited = 0
        self.last_visited = None
        self.last_reachable = None
        self.date_added = None
        self.deleted = False
        self.category_tags = set()
        self.description_tags = set()
        
        # Retrieve an existing bookmark if an ID is passed in
        if self.id is not None:
            db = self._db_conn.cursor()
            db.execute(SQL_SELECT_BOOKMARK, (self.id,))
            row = db.fetchone()
            
            # Check to see if a record was found
            if not row:
                raise BookmarkNotFoundError(self.id)
            
            # Assign data attributes
            self.url = row[1]
            self.title = row[2]
            self.description = row[3]
            self.times_visited = int(row[4])
            if row[5]:
                self.last_visited = dateutil.parser.parse(row[5])
            if row[6]:
                self.last_reachable = dateutil.parser.parse(row[6])
            self.date_added = dateutil.parser.parse(row[7])
            if int(row[8]) == 0:
                self.deleted = False
            else:
                self.deleted = True
                
            # Grab category tags
            for row in db.execute(SQL_SELECT_CAT_TAGS, (self.id,)):
                self.category_tags.add(Tag(int(row[0])))
                
            # Grab description tags
            for row in db.execute(SQL_SELECT_DESC_TAGS, (self.id,)):
                self.description_tags.add(Tag(int(row[0])))
            
        # Store hash of tag sets for dirty comparison
        self._cat_tags_hash = hash(frozenset(self.category_tags))
        self._desc_tags_hash = hash(frozenset(self.description_tags))
        
        # This dict MUST appear *after* the data attributes, it is used to 
        # record which data attributes are dirty
        self._dirty = {'url': False,
                       'title': False,
                       'description': False,
                       'times_visited': False,
                       'last_visited': False,
                       'last_reachable': False,
                       'deleted': False}
    
    def __del__(self):
        """ Automatically save any changes to a record before the object is 
        destroyed. 
        """
        self.save()
    
    def save(self):
        """ Save any changes to a bookmark record if it is dirty. """
        # Check if the record is dirty
        if self.record_dirty:
            if self.id:
                # Update existing bookmark record
                self._db_update()
            else:
                # Insert new bookmark record
                self._db_insert()
    
    def __setattr__(self, name, value):
        """ Customize setting data attributes so we can tell which ones
        are dirty.
        """
        # The date_added attribute is immutable once it has been set
        if hasattr(self, 'date_added') and name == 'date_added':
            if getattr(self, name) is not None:
                return
        
        # Check if the attr is a data attribute then check if the value has 
        # changed. If so, mark the attribute as dirty
        if hasattr(self, '_dirty'):
            if name in self._dirty and getattr(self, name) != value:
                self._dirty[name] = True
                
        # Set the attribute value using the super class's __setattr__ to avoid
        # infinite recursion (recursion joke here)
        super(Bookmark, self).__setattr__(name, value)
        
    def _db_insert(self):
        """ Insert a new bookmark record into the database. """
        self.date_added = datetime.datetime.now()
        
        db = self._db_conn.cursor()
        values = (None, self.url, self.title, self.description, 
                  self.times_visited, _format_date(self.last_visited), 
                  _format_date(self.last_reachable), 
                  _format_date(self.date_added),
                  self.deleted and '1' or '0')
        db.execute(SQL_INSERT_BOOKMARK, values)
        
        # Set bookmark ID after insert
        self.id = db.lastrowid
        
        # Insert any category tags
        for tag in self.category_tags:
            tag.save()
            db.execute(SQL_INSERT_CAT_TAGS, (tag.id, self.id))
            
        # Insert any description tags
        for tag in self.description_tags:
            tag.save()
            db.execute(SQL_INSERT_DESC_TAGS, (tag.id, self.id))
        
        self._db_conn.commit()
        
        # Reset dirty flags
        for key in self._dirty.keys():
            self._dirty[key] = False
            
    def _db_update(self):
        """ Update an exisitng bookmark record in the database. """
        values = []
        sets = []
        
        # Find dirty data attributes for updating
        for key in self._dirty.keys():
            if self._dirty[key] == True:
                sets.append('{:s}=?'.format(key))
                if key in ['last_reachable', 'last_visited']:
                    # Datetime objects need to be formated before update
                    values.append(_format_date(getattr(self, key)))
                else:
                    values.append(getattr(self, key))
        
        if values and sets:
            # Add the record id as the last value
            values.append(self.id)
            
            # Build full SQL statement then execute it
            sql = SQL_UPDATE_BOOKMARK.format(','.join(sets))
            db = self._db_conn.cursor()
            db.execute(sql, values)
            
            # Reset dirty flags
            for key in self._dirty.keys():
                self._dirty[key] = False
        
        # Inner function FTW
        def update_tags_lists(delete_sql, insert_sql, tags):
            db.execute(delete_sql, (self.id,))
            tag_updates = []
            for tag in tags:
                tag.save()
                tag_updates.append((tag.id, self.id))
            db.executemany(insert_sql, tag_updates)
            
        # Update tag lists if they are dirty
        if self.category_tags_dirty:
            update_tag_lists(SQL_DELETE_CAT_TAGS, SQL_INSERT_CAT_TAGS, 
                             self.category_tags)
        if self.description_tags_dirty:
            update_tag_lists(SQL_DELETE_DESC_TAGS, SQL_INSERT_DESC_TAGS,
                             self.description_tags)
                             
        self._db_conn.commit()
    
    @property
    def category_tags_dirty(self):
        return self._cat_tags_hash != hash(frozenset(self.category_tags))
        
    @property 
    def description_tags_dirty(self):
        return self._desc_tags_hash != hash(frozenset(self.description_tags))
    
    @property
    def record_dirty(self):
        """ Check if any data attributes are dirty, which indicates the record 
        is dirty.
        """
        dirty = [val for key, val in self._dirty.items()]
        dirty.append(self.category_tags_dirty)
        dirty.append(self.description_tags_dirty)
        return any(dirty)
        
    def _get_content(self):
        """ Read the page and get its raw page HTML content. """
        if self.url:
            response = urlopen(self.url)
            if response.getcode() == HTTP_OKAY:
                self._content = response.read().decode('utf-8')
        
    def suggest_title(self):
        """ Return the contents of the page's <title> tag as a title suggestion
        """
        if self.url and not self._content:
            self._get_content()
        
        try:
            title = re.search('<title>(.+)</title>', self._content).group(1)
        except IndexError:
            title = None
        return title
        
    def is_reachable(self):
        """ Check if the page is reachable. """
        if self.url:
            response = urlopen(self.url)
            if response.getcode() == HTTP_OKAY:
                self.last_reachable = datetime.datetime.now()
                return True
        return False
        
    def visit(self):
        """ Increment the visit count and update the last visited datestamp """
        self.times_visited += 1
        self.last_visited = datetime.datetime.now()