from datetime import datetime, timezone
from time import time
import jwt
from typing import Optional
from app import db, login, app
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import UserMixin

class User(UserMixin, db.Model):
    __table_args__ = {'extend_existing': True}
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    name = db.Column(db.String(64), index=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    admin = db.Column(db.String(1))
    insert = db.Column(db.String(1))
    stat = db.Column(db.String(1))
    send = db.Column(db.String(1))
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)
    #last_seen: so.Mapped[Optional[datetime]] = so.mapped_column(default=lambda: datetime.now(timezone.utc))
    def get_reset_password_token(self, expires_in=600):
        return jwt.encode(
            {'reset_password': self.id, 'exp': time() + expires_in}, app.config['SECRET_KEY'], algorithm='HS256')

    @staticmethod
    def verify_reset_password_token(token):
        try:
            id = jwt.decode(token, app.config['SECRET_KEY'], algorithms=['HS256'])['reset_password']
        except:
            return
        return db.session.get(User, id)

class licenze(UserMixin, db.Model):
    idlicenze = db.Column(db.Integer, primary_key=True)
    codFitre = db.Column(db.String(64), index=True)
    desFitre = db.Column(db.String(64), index=True)
    codMatrix = db.Column(db.String(64), index=True)
    desMatrix = db.Column(db.String(64), index=True)
    pins = db.relationship('PIN', backref='Lic', lazy='dynamic')
    def lista_lic(self):    # ritorna tutte le licenze
        return licenze.query.all()
    def search_codF(self, codF):
        return self.query.filter(licenze.codFitre == codF).all()
    def search_id(self, id):
        return self.query.filter(licenze.idlicenze == id).all()
    def search_desM(self, desM):
        return self.query.filter(licenze.desMatrix == desM).scalar()

class PIN(UserMixin, db.Model):
    idPIN = db.Column(db.Integer, primary_key=True)
    data = db.Column(db.DateTime, index=True)
    licenza = db.Column(db.Integer, db.ForeignKey('licenze.idlicenze'))
    ragsoc = db.Column(db.String(75), index=True)
    codcliente = db.Column(db.String(64), index=True)
    email = db.Column(db.String(120), index=True)
    nsrif = db.Column(db.String(64), index=True)
    pin = db.Column(db.String(64), index=True)
    ordcl = db.Column(db.String(64), index=True)
    dataordcl = db.Column(db.Date, index=True)
    def toList(self):
        return [self.idPIN, self.data, self.licenza, self.ragsoc, self.codcliente, self.email, self.nsrif, self.pin, self.ordcl, self.dataordcl]
    def __eq__(self, other):
        if not hasattr(other, 'a'):
           return False
        return self.a == other.a
    def tot_pin(self):  # ritorna il numero totale di licenze vendute
        return self.query.count()
    def cod_desc_num(self):  # ritorna una lista di tuple composta da codice lic, desc Fitre e numero lic vendute, in ordine discendente
        result = []
        listalic = licenze.query.all()
        for lic in listalic:
            pins = lic.pins.all()
            tot = 0
            for pin in pins:
                tot = tot + 1
            result.append((lic.codFitre,lic.desFitre,tot))
        result.sort(key=lambda x:x[2], reverse=True)
        return result
    def orfani(self): #ritorna i pin non usati
        pin = self.query.all()
        lic = lic_cli()
        res = []
        for p in pin:
            if not(lic.query.filter(lic_cli.pin == p.pin).count()):
                res.append(p)
        return res
    def stampa_orfani(self):
        orfani = self.orfani(self)
        st_orfani = []
        lic = licenze()
        for orf in orfani:
            list_orf = orf.toList()
            list_orf.append(lic.search_id(orf.licenza)[0].desFitre)
            st_orfani.append(list_orf)
        return(st_orfani)
            
    
class lic_price(UserMixin, db.Model):
    idlic_price = db.Column(db.Integer, primary_key=True)
    licenza = db.Column(db.Integer, db.ForeignKey('licenze.idlicenze'))
    lic_cod = db.Column(db.String(64), index=True)
    lic_name = db.Column(db.String(64), index=True)
    price = db.Column(db.Float, index=True)
    start_date = db.Column(db.Date, index=True)
    end_date = db.Column(db.Date, index=True)

class lic_cli(db.Model):
    idlic_cli = db.Column(db.Integer, primary_key=True)
    data = db.Column(db.Date, index=True)
    ordine = db.Column(db.String(64), index=True)
    cod_cli = db.Column(db.String(64), index=True)
    codPBX = db.Column(db.String(64), index=True)
    serialPBX = db.Column(db.String(64), index=True)
    macPBX = db.Column(db.String(64), index=True)
    licatt = db.Column(db.String(128), index=True)
    codLic = db.Column(db.String(64), db.ForeignKey('licenze.codFitre'))
    pin = db.Column(db.String(64), index=True)
    licNEW = db.Column(db.String(128), index=True)
    attiva =  db.Column(db.Integer)
    ragsoc = db.Column(db.String(75), index=True)
    email = db.Column(db.String(120), index=True)
    nome = db.Column(db.String(64), index=True)
    cognome = db.Column(db.String(64), index=True)
    def search_codcli(self, codcli):    # ritorna tutti le licenze in cui il codice cliente è uguale a codcli
        return self.query.filter(self.cod_cli == codcli).all()
    def search_codcliA(self, codcli):    # ritorna tutti le licenze ATTIVE in cui il codice cliente è uguale a codcli
        return self.query.filter(self.cod_cli == codcli, self.attiva == 1).all()
    def search_ordine(self, ordine):    # ritorna tutti le licenze in cui il numero ordine è uguale a ordine
        return self.query.filter(self.ordine == ordine).all()
    def search_mac(self, mac):    # ritorna tutti le licenze in cui il mac address è uguale a mac
        return self.query.filter(self.macPBX == mac).all()
    def search_sn(self, sn):    # ritorna tutti le licenze in cui il numero seriale è uguale a sn
        return self.query.filter(self.serialPBX == sn).all()
    def search_snm(self, sn, modello):    # ritorna tutti le licenze in cui il numero seriale è uguale a sn ed il codice è modello
        return self.query.filter(self.serialPBX == sn).filter(self.codPBX == modello).all()
    
    
class lic_oem(db.Model):
    idlic_oem = db.Column(db.Integer, primary_key=True)
    codPBX = db.Column(db.String(64), index=True)
    serialPBX = db.Column(db.String(64), index=True)
    macPBX = db.Column(db.String(64), index=True)
    licOEM = db.Column(db.String(128), index=True)
    data = db.Column(db.Date, index=True)
    FW = db.Column(db.String(64), index=True)
    def search_mac(self, mac):    # ritorna tutti i centralini il cui mac address è uguale a mac
        return self.query.filter(self.macPBX == mac).all()
    def search_sn(self, sn):    # ritorna tutti i centralini il cui numero seriale è uguale a sn
        return self.query.filter(self.serialPBX == sn).all()

class PBX(db.Model):
    idPBX = db.Column(db.Integer, primary_key=True)
    codPBX = db.Column(db.String(64), index=True)
    nomePBX = db.Column(db.String(64), index=True)
    def lista_PBX():    # ritorna tutti i centralini
        return PBX.query.all()

@login.user_loader
def load_user(id):
    return db.session.get(User, int(id))