import pytz
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.inspection import inspect
from flask_login import UserMixin, login_user, login_required, logout_user, LoginManager, current_user
from datetime import datetime
from sqlalchemy_serializer import SerializerMixin
from __init__ import db
import enum
import re
import random
import pytz
import uuid
import string
from sqlalchemy.orm import backref
tz = pytz.timezone('Africa/Nairobi')

UTC = pytz.utc
EAT = pytz.timezone('Africa/Nairobi')


def createUserName(email):
    result = re.search(r'([\w\d\.]+)@[\w\d\.]+', email)
    if result and email.count('@') == 1:
        return result.group(1)
    else:
        return email


def makeSlug(text):
    text = text.lower().strip()
    text = text + f" {str(datetime.now().strftime('%f'))}"
    text = re.sub(r'[\s_-]+', '-', text)
    text = re.sub(r'[^\w\-]+', '', text)
    return text


class Users(db.Model, UserMixin):
    __tablename__ = "users"
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    username = db.Column(db.String(100), unique=True, nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    avatar = db.Column(db.String(100), default="default.png")
    post = db.Column(db.String(20), default="Administrator")
    admin = db.Column(db.Boolean, default=False)
    email_verified = db.Column(db.Boolean, default=False)
    email_verified_at = db.Column(db.DateTime, default=datetime.now(EAT))
    firstname = db.Column(db.String(20))
    lastname = db.Column(db.String(20))
    password = db.Column(db.String(400), nullable=False)
    
    staff_id = db.Column(db.Integer, db.ForeignKey('staff.id'), unique=True, nullable=True)

    added_date = db.Column(db.DateTime, default=datetime.now(EAT))
    deleted_date = db.Column(db.DateTime)
    
    # cars = db.relationship('Listing', backref='staff', lazy=True)
    
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

    def __repr__(self):
        return self.username

    def __setattr__(self, key, value):
        super(Users, self).__setattr__(key, value)
        if key == 'email':
            self.username = createUserName(self.email)


class BlogArticles(db.Model):
    __tablename__ = 'blog_articles'
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    slug = db.Column(db.String(200))
    title = db.Column(db.String(200))
    image = db.Column(db.String(200))
    article = db.Column(LONGTEXT)
    author_id = db.Column(db.Integer)
    viewed = db.Column(db.Integer, default=0)
    likes = db.Column(db.Integer, default=0)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    comments = db.relationship('Comment', backref='blog_articles', cascade="all, delete-orphan", lazy=True)
    
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

    def __setattr__(self, key, value):
        super(BlogArticles, self).__setattr__(key, value)
        if key == 'title':
            self.slug = makeSlug(self.title)


class Comment(db.Model):
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    content = db.Column(db.Text, nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)

    post_id = db.Column(db.Integer, db.ForeignKey(
        'blog_articles.id'), nullable=False)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

    def __repr__(self):
        return '<Comments Model %r>' % self.id


class PrincipalMessage(db.Model):
    __tablename__ = 'principal_message'

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    name = db.Column(db.String(200))
    image = db.Column(db.String(200))
    message = db.Column(LONGTEXT)
    author_id = db.Column(db.Integer)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class SliderModel(db.Model):
    __tablename__ = 'slider_model'

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    message = db.Column(db.String(200))
    image = db.Column(db.String(200))
    link = db.Column(db.String(500))
    youtube = db.Column(db.String(100))
    author_id = db.Column(db.Integer)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class RequestModel(db.Model):
    __tablename__ = 'request_model'

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    name = db.Column(db.String(200))
    email = db.Column(db.String(200))
    phone = db.Column(db.String(200))
    subject = db.Column(db.String(200))
    message = db.Column(db.Text)
    seen = db.Column(db.Boolean, default=False)
    completed = db.Column(db.Boolean, default=False)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    vehicle_id = db.Column(db.Integer, db.ForeignKey('listing.id'), nullable=False)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class GalleryModel(db.Model):
    __tablename__ = 'gallery_model'

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    image = db.Column(db.String(200))
    author_id = db.Column(db.Integer)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class Condition(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))

    cars = db.relationship('Listing', backref='condition2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class CarStatus(db.Model):
    __tablename__ = 'status'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    cars = db.relationship('Listing', backref='status2', lazy=True)
    
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }
    
class CarType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class CarMake(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    slug = db.Column(db.String(100))
    logo = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    models = db.relationship('CarModel', backref='car_make', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class EngineType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    # cars = db.relationship('Listing', backref='engine_size2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class CarModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    slug = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    make_id = db.Column(db.Integer, db.ForeignKey(
        'car_make.id'), nullable=False)
    cars = db.relationship('Listing', backref='model2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class OfferType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class DriveType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='drive_type2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class Transmission(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='transmission2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class FuelType(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='fuel_type2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class BodyTypes(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))

    cars = db.relationship('Listing', backref='body_type2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class Cylinders(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='cylinders2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class Colors(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='color2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class Doors(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    
    cars = db.relationship('Listing', backref='doors2', lazy=True)
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }


class Features(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True)
    date = db.Column(db.DateTime, default=datetime.now(EAT))
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

class Listing(db.Model, SerializerMixin):
    id = db.Column(db.Integer, primary_key=True)

    # Fields corresponding to the form
    title = db.Column(db.String(300), nullable=False)
    slug = db.Column(db.String(400), nullable=True)
    # VIN typically has 17 characters
    VIN = db.Column(db.String(17))
    price = db.Column(db.Integer, nullable=False)
    mileage = db.Column(db.String(100), nullable=False)
    year = db.Column(db.String(4), nullable=False)
    engine_size = db.Column(db.Integer, nullable=False)
    make = db.Column(db.Integer, nullable=False)
    
    model = db.Column(db.Integer, db.ForeignKey('car_model.id'), nullable=False)
    condition = db.Column(db.Integer, db.ForeignKey('condition.id'), nullable=False)
    body_type = db.Column(db.Integer, db.ForeignKey('body_types.id'), nullable=False)
    drive_type = db.Column(db.Integer, db.ForeignKey('drive_type.id'), nullable=False)
    transmission = db.Column(db.Integer, db.ForeignKey('transmission.id'), nullable=False)
    fuel_type = db.Column(db.Integer, db.ForeignKey('fuel_type.id'), nullable=False)
    color = db.Column(db.Integer, db.ForeignKey('colors.id'), nullable=False)
    doors = db.Column(db.Integer, db.ForeignKey('doors.id'), nullable=False)
    cylinders = db.Column(db.Integer, db.ForeignKey('cylinders.id'), nullable=False)
    status = db.Column(db.Integer, db.ForeignKey('status.id'), nullable=False)
    staff_handler = db.Column(db.Integer, db.ForeignKey('staff.id'), nullable=False)
    
    tags_or_keyword = db.Column(db.String(500))
    youtube = db.Column(db.String(100))
    description = db.Column(db.Text, nullable=False)
    terms_of_services = db.Column(db.Boolean, nullable=False)
    features = db.Column(db.PickleType, nullable=False)
    images = db.Column(db.PickleType, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT))
    updated_at = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT), 
                           onupdate=datetime.now(EAT))
    deleted_at = db.Column(db.DateTime, nullable=True)
    active = db.Column(db.Boolean, default=True, nullable=False)
    sold = db.Column(db.Boolean, default=False, nullable=False)
    featured = db.Column(db.Boolean, default=False, nullable=False)
    deleted = db.Column(db.Boolean, default=False, nullable=False)
    
    requests = db.relationship('RequestModel', backref='requests', cascade="all, delete-orphan", lazy=True)
    
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }

    def __repr__(self):
        return f'<Listing {self.title}>'
    
class Vehicle(db.Model):
    __tablename__ = 'vehicles'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sku = db.Column(db.String(255), nullable=True)
    slug = db.Column(db.String(255), nullable=False)
    ref_no = db.Column(db.String(255), nullable=False)
    number_plate = db.Column(db.Enum('Un-Registered', 'Registered'), nullable=False)

    make = db.Column(db.Integer, db.ForeignKey('car_make.id'), nullable=False)
    model = db.Column(db.Integer, db.ForeignKey('car_model.id'), nullable=False)
    series_id = db.Column(db.Integer, nullable=True)
    body_id = db.Column(db.Integer, db.ForeignKey('body_types.id'), nullable=False)
    condition = db.Column(db.Integer, db.ForeignKey('condition.id'), nullable=False)

    engine_size = db.Column(db.Integer, nullable=True)
    year = db.Column(db.Integer, nullable=True)
    mileage = db.Column(db.String(255), nullable=True)

    color_id = db.Column(db.Integer, db.ForeignKey('colors.id'), nullable=True)
    transmission_type = db.Column(db.Integer, db.ForeignKey('transmission.id'), nullable=True)
    fuel_type = db.Column(db.Integer, db.ForeignKey('fuel_type.id'), nullable=True)
    interior_color = db.Column(db.String(255), nullable=True)
    door_count = db.Column(db.String(255), nullable=True)
    drive_setup = db.Column(db.String(255), nullable=True)
    wheel_type = db.Column(db.Integer, nullable=True)

    selling_price = db.Column(db.String(255), nullable=True)
    offer_price = db.Column(db.String(255), nullable=True)
    featured = db.Column(db.Boolean, default=False, nullable=False)
    status = db.Column(db.Enum('active', 'not active'), nullable=False)

    primary_image = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)
    left_side = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)
    right_side = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)
    dashboard = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)
    interior_image = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)
    other_image = db.Column(db.Integer, db.ForeignKey('vehicle_uploads.id'), nullable=True)

    negotiable_status = db.Column(db.Integer, nullable=True)
    stoke_status = db.Column(db.Integer, db.ForeignKey('status.id'), nullable=True)
    description = db.Column(db.Text, nullable=True)

    created_by = db.Column(db.Integer, nullable=False)
    updated_by = db.Column(db.Integer, nullable=True)

    specifications = db.Column(db.Text, nullable=True)
    directors = db.Column(db.Integer, nullable=True)
    user_to_be_contacted = db.Column(db.Integer, nullable=True)

    created_at = db.Column(db.DateTime, default=datetime.now(EAT))
    updated_at = db.Column(db.DateTime, default=datetime.now(EAT), onupdate=datetime.now(EAT))
    
    primary_image_img = db.relationship('VehicleUpload', foreign_keys=[primary_image], backref='primary_for_vehicles')
    left_side_img = db.relationship('VehicleUpload', foreign_keys=[left_side], backref='left_for_vehicles')
    right_side_img = db.relationship('VehicleUpload', foreign_keys=[right_side], backref='right_for_vehicles')
    dashboard_img = db.relationship('VehicleUpload', foreign_keys=[dashboard], backref='dashboard_for_vehicles')
    interior_image_img = db.relationship('VehicleUpload', foreign_keys=[interior_image], backref='interior_for_vehicles')
    other_image_img = db.relationship('VehicleUpload', foreign_keys=[other_image], backref='other_for_vehicles')
    model_name = db.relationship('CarModel', foreign_keys=[model], backref='car_model_')
    # make_name = db.relationship('CarMake', foreign_keys=[make], backref='car_make')


    def __repr__(self):
        return f'<Vehicle {self.ref_no}>'
    
class VehicleUpload(db.Model):
    __tablename__ = 'vehicle_uploads'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    filename = db.Column(db.String(255), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now(EAT))
    updated_at = db.Column(db.DateTime, default=datetime.now(EAT), onupdate=datetime.now(EAT))
    
    
    def __repr__(self):
        return f'<VehicleUpload {self.filename}>'

class Department(db.Model):
    __tablename__ = 'departments'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    director = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True, nullable=False)
    # Relationship with Staff
    staff = db.relationship('Staff', backref='department', lazy=True, cascade="all, delete-orphan")
    
    def __repr__(self):
        return f'<Department {self.name}>'

class Staff(db.Model):
    __tablename__ = 'staff'
    
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(120), nullable=False)
    phone_number = db.Column(db.String(20))
    hire_date = db.Column(db.Date, default=datetime.now(EAT))
    active = db.Column(db.Boolean, default=True, nullable=False)
    # Foreign Key to Department
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
    has_account = db.Column(db.Boolean, default=False)
    cars = db.relationship('Listing', backref='staff', lazy=True)
    
    user = db.relationship("Users", backref="userstaff", uselist=False, cascade="all, delete-orphan")
    
    def __repr__(self):
        return f'<Staff {self.first_name} {self.last_name}>'
    
    
class Review(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_name = db.Column(db.String(100), nullable=False)
    rating = db.Column(db.Integer, nullable=False)
    comment = db.Column(db.Text, nullable=False)
    active = db.Column(db.Boolean, default=True, nullable=False)
    date = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT))
    
    def toDict(self):
        return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs }
    
class AboutUs(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    message = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True, nullable=False)
    date = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT))
    
    
    def __repr__(self):
        return f'<About Us {self.id}>'
class ContactUs(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    message = db.Column(db.Text)
    active = db.Column(db.Boolean, default=True, nullable=False)
    date = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT))
    
    
    def __repr__(self):
        return f'<Contact Us {self.id}>'

class CarInfo(db.Model):
    __tablename__ = 'car_info'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), nullable=False)
    firstname = db.Column(db.String(100), nullable=False)
    surname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    make = db.Column(db.String(50), nullable=False)
    model = db.Column(db.String(50), nullable=False)
    year = db.Column(db.Integer, nullable=False)
    reg_number = db.Column(db.String(20), nullable=False)
    mileage = db.Column(db.Integer, nullable=False)
    comments = db.Column(db.Text, nullable=True)
    
    active = db.Column(db.Boolean, default=True, nullable=False)
    date = db.Column(db.DateTime, nullable=False, default=datetime.now(EAT))

    def __repr__(self):
        return f'<CarInfo {self.firstname} {self.surname}>'

class Enquiry(db.Model):
    __tablename__ = 'enquiries'
    
    id = db.Column(db.Integer, primary_key=True)
    nature_of_enquiry = db.Column(db.String(100), nullable=False)
    firstname = db.Column(db.String(100), nullable=False)
    surname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(15), nullable=True)
    message = db.Column(db.Text, nullable=False)

    def __repr__(self):
        return f"<Enquiry {self.firstname} {self.surname}>"
    
class Enquiry_(db.Model):
    __tablename__ = 'enquiries_'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(15), nullable=True)
    message = db.Column(db.Text, nullable=False)

    def __repr__(self):
        return f"<Enquiry {self.name}>"
