from abc import ABCMeta
from openpyxl import Workbook, load_workbook


class ExcelDB:

    def __init__(self, path, password=None):
        self._path = path
        self._pass = password

    def init(self):
        pass

    def create(self, model):
        wb, ws = self.get_worksheet(model)
        if model.row is None:
            next_row = len(tuple(ws.rows)) + 1
        else:
            next_row = model.row
        for e in self._get_params(model):
            c = ws.cell(next_row, type(model).__dict__.get(e).column+model.offset_cols)
            c.value = model.__dict__.get(e)
            model.row = next_row
        wb.save(self._path)

    def _get_element(self, search_for, elements):
        for e in elements:
            if e.value == search_for:
                return e
        return None

    def _get_params(self, model):
        params = list()
        for e in model.__dict__:
            if e in ['offset_rows', 'offset_cols', '_row', '_init', 'Meta', ] or e.startswith('__'):
                continue
            else:
                params.append(e)
        return params

    def read(self, model):
        wb, ws = self.get_worksheet(model)
        entries = list()
        model_params = self._get_params(model)
        for row in ws.rows:
            if row[0].row <= (model.offset_rows + 1):
                continue
            params = dict()
            for p in model_params:
                params[p] = row[model.__dict__.get(p).column-1].value
            entries.append(model(**params))
        return entries

    def update(self, model):
        self.create(model)

    def delete(self, model):
        wb, ws = self.get_worksheet(model)
        ws.delete_rows(model.row)
        wb.save(self._path)

    def get_worksheet(self, model):
        wb = load_workbook(self._path)
        ws = wb[model.Meta.table]
        return wb, ws

    def create_tables(self, models):
        wb = Workbook()
        wb.remove_sheet(wb.active)
        for model in models:
            wb.create_sheet(model.Meta.table)
            ws = wb[model.Meta.table]
            i = 1
            for e in self._get_params(model):
                c = ws.cell(1, i)
                c.value = model.__dict__.get(e).caption
                i = i + 1
        wb.save(self._path)