import datetime
from time import perf_counter
from sqlalchemy import Column, Integer, DateTime, String, JSON, Numeric, Text
from sqlalchemy.exc import IntegrityError, SQLAlchemyError

from utils.base import Base, Session
from utils.util import log


class WalmartInfo(Base):
    session = Session()
    __tablename__ = 'walmartinfo'
    __table_args__ = {'extend_existing': True}

    sku = Column('sku', Integer, primary_key=True)
    created = Column('created', DateTime)
    last_updated = Column('last_updated', DateTime)
    results = Column('results', JSON)

    def __init__(self, kwargs=None):
        if kwargs:
            for k, v in kwargs.items():
                setattr(self, k, v)

    @classmethod
    def single_insert(cls, item: dict, sku: int):
        row = {
            'sku': sku,
            'created': datetime.datetime.now(),
            'last_updated': datetime.datetime.now(),
            'results': item
        }
        record = cls(row)
        try:
            cls.session.add(record)
            cls.session.commit()
        except (Exception, IntegrityError) as e:
            log.warning(f'item with sku {row["sku"]} already in db. updating...')
            row['last_updated'] = datetime.datetime.now()
            row.pop('created', None)
            cls.session.rollback()
            cls.session.query(cls).filter_by(sku=row['sku']).update(row)
            cls.session.commit()
            log.info(f'item with sku {row["sku"]} updated')
        finally:
            cls.session.close()


class WalmartReview(Base):
    __tablename__ = 'walmartreviews'
    __table_args__ = {'extend_existing': True}

    review_id = Column('review_id', String, primary_key=True)
    sku = Column('sku', Integer)
    created = Column('created', DateTime)
    last_updated = Column('last_updated', DateTime)
    review = Column('review', JSON)

    def __init__(self, kwargs=None):
        if kwargs:
            for k, v in kwargs.items():
                setattr(self, k, v)

    @classmethod
    def bulk_upsert(cls, data: list, sku):
        session = Session()
        records = session.query(cls).filter_by(sku=sku)
        records = {record.review_id: record for record in records}
        to_update = []
        to_insert = []
        for item in data:
            item_id = item['review_id']
            if item['review_id'] in records:
                records[item_id].last_updated = datetime.datetime.now()
                to_update.append(item)
            else:
                to_insert.append(item)
        session.bulk_insert_mappings(cls, to_insert)
        session.bulk_update_mappings(cls, to_update)
        session.flush()
        session.commit()
        session.close()


class WalmartReviewStats(Base):
    session = Session()
    __tablename__ = 'walmartreviewstats'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key=True)
    sku = Column('sku', Integer)
    created = Column('created', DateTime)
    last_updated = Column('last_updated', DateTime)
    stats = Column('stats', JSON)

    def __init__(self, kwargs=None):
        if kwargs:
            for k, v in kwargs.items():
                setattr(self, k, v)

    @classmethod
    def single_insert(cls, stats: dict, sku: int):
        row = {
            'sku': sku,
            'created': datetime.datetime.now(),
            'last_updated': datetime.datetime.now(),
            'stats': stats
        }
        record = cls(row)
        try:
            cls.session.add(record)
            cls.session.commit()
            log.info(f'stats for product review {row["sku"]} inserted')
        except (Exception, IntegrityError) as e:
            row['last_updated'] = datetime.datetime.now()
            row.pop('created', None)
            cls.session.rollback()
            cls.session.query(cls).filter_by(sku=row['sku']).update(row)
            cls.session.commit()
            log.info(f'stats for product review {row["sku"]} updated')
        finally:
            cls.session.close()
