from sqlalchemy import Column
#from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Float
from sqlalchemy import String
from sqlalchemy import Boolean
from sqlalchemy import DateTime
from sqlalchemy import Date
from sqlalchemy import UUID
from sqlalchemy import ARRAY
from sqlalchemy import text

from sqlalchemy.ext.mutable import MutableList
from sqlalchemy.dialects.postgresql import ARRAY

#from geoalchemy2 import Geometry
#from sqlalchemy.orm import declarative_base
#from sqlalchemy.orm import relationship

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import bcrypt

import uuid
import datetime
#from datetime import timezone

import app_settings
import app_func

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = app_settings.DB_URL #"postgresql://postgres:karting123@localhost:5432/test_db" #"postgresql://psqluser:testing123@localhost:5432/postgres" #app_settings.DB_URL
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False # no tracking
db = SQLAlchemy(app)

#Base = declarative_base()

# DB set up and seeders
##################################
@app.cli.command('db_create')
def db_create():
    db.create_all()
    print('Database created')
    
@app.cli.command('db_drop')
def db_drop():
    db.drop_all()
    print('Database dropped')


@app.cli.command('db_seed')
def db_seed():
    import json
    import data_json
    user = data_json.customer_arr
    ind=0
    for item in user:
        user = json.loads(item)
        
        test_user = Customer(
             uuid = uuid.uuid4(),
            country_code = user['country_code'],
            mobile = user['mobile'],
            password = user['password'])
        db.session.add(test_user)
        db.session.commit()
        print('Customer seeded no.',ind+1)
            
        db.session.refresh(test_user)
        customer_id = test_user.id
        user_data = data_json.customer_data_arr[ind]
        user_data = json.loads(user_data)
        
        # Setup location from address
        address = user_data['address']
        city = user_data['city']
        location = str(app_func.set_location(address,city))
                      
        test_user_data = Customer_data(
            customer_id=customer_id,
            customer_type = user_data['customer_type'],
            email = user_data['email'],
            first_name = user_data['first_name'],
            last_name = user_data['last_name'],
            city = user_data['city'],
            address = user_data['address'],
            location = location, #user_data['location'],
            language = user_data['language'])
    
        db.session.add(test_user_data)
        db.session.commit()
        print('Customer_data seeded no,',ind+1)
        ind=ind+1
        
    # Provider
    providers = data_json.provider_arr
    ind=0
    for item in providers:
        provider = json.loads(item)
        
        test_provider = Provider(
             uuid = uuid.uuid4(),
            country_code = provider['country_code'],
            mobile = provider['mobile']
            #password = user['password']
            )
        db.session.add(test_provider)
        db.session.commit()
        print('Provider seeded no.',ind+1)
            
        db.session.refresh(test_provider)
        provider_id = test_provider.id
        provider_data = data_json.provider_data_arr[ind]
        provider_data = json.loads(provider_data)
        
        # Setup location from address
        address = provider_data['address']
        city = provider_data['city']
        location = str(app_func.set_location(address,city))
                      
        test_provider_data = Provider_data(
            provider_id = provider_id,
            provider_type=provider_data['provider_type'],
            email = provider_data['email'],
            first_name = provider_data['first_name'],
            last_name = provider_data['last_name'],
            city = provider_data['city'],
            address = provider_data['address'],
            vat = provider_data['vat'],
            location = location, 
            language = provider_data['language'],
            description_long = provider_data['description_long'])

        db.session.add(test_provider_data)
        db.session.commit()
        print('Provider_data seeded no,',ind+1)
        ind=ind+1
     
    return

@app.cli.command('db_seed_services')
def db_seed_services():
    import json
    import data_json
    services = data_json.service_data_arr

    ind=0
    for item in services:
        service = json.loads(item)
        
        test_service = Provider_service(
            provider_id = service['provider_id'],
            service = service['service'],
            #advice = service['advice'],
            work_start = service['work_start'],
            work_end = service['work_end'],
            price = service['price'],
            holidays = service['holidays'],
            saturdays = service['saturdays'],
            sundays = service['sundays'],
            reviews = [],
            reviews_aver = None,
            description_short = service['description_short']
            )
        db.session.add(test_service)
        db.session.commit()
        print('Service seeded no.',ind+1)
        ind=ind+1
        
    return
    

@app.cli.command('db_seed_reservations')
def db_seed_reservations():
    import json
    import data_json
    
    reservations = data_json.reserved_data_arr

    ind=0
    for item in reservations:
        reservation = json.loads(item)
        # Get location of customer
        if reservation['customer_id'] > 0:
            reservation_loc = db.session.query(Customer_data.location).filter_by(customer_id=reservation['customer_id']).scalar()
        else:
            reservation_loc = db.session.query(Provider_data.location).filter_by(provider_id=reservation['provider_id']).scalar()
            
        test_reservation = Provider_reserved(
            provider_id = reservation['provider_id'],
            customer_id = reservation['customer_id'],
            date=datetime.datetime.strptime(reservation['date'], "%d/%m/%Y").date(),
            location = reservation_loc, #reservation['location'],
            description = reservation['description']
            )
        db.session.add(test_reservation)
        db.session.commit()
        print('Reservation seeded no.',ind+1)
        ind=ind+1
        
    return

@app.cli.command('db_seed_reviews')
def db_seed_reviews():
    import json
    import data_json
    
    reviews = data_json.review_data_arr

    ind=0
    for item in reviews:
        review = json.loads(item)
        
        test_review = Reviews(
            provider_id = review['provider_id'],
            customer_id = review['customer_id'],
            service = review['service'],
            review_type = review['review_type'],
            description = review['description'],
            review_rate = review['review_rate']
            )
        db.session.add(test_review)
        db.session.commit()
        print('Review seeded no.',ind+1)
        ind=ind+1
        
    return
    
######################################### 
class TokenBlockList(db.Model):
    __tablename__ = "blocklist"
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, nullable=False)
    jti = Column(String, nullable=False)
    created_at = Column(DateTime(timezone=True), nullable=False)

class Customer(db.Model):
    __tablename__ = "customers"
    id = Column(Integer, primary_key=True)
    uuid = Column(UUID, nullable=False)
    country_code = Column(String(5), nullable=False)
    mobile = Column(String(30), nullable=False)
    password = Column(String, nullable=False)
    active = Column(Boolean, nullable=False)
    
    def __init__(self, uuid, country_code, mobile, password):
        self.uuid = uuid
        self.country_code = country_code
        self.mobile = mobile
        pwhash = bcrypt.hashpw(password.encode('utf8'), bcrypt.gensalt())
        self.password = pwhash.decode('utf8') # decode the hash to prevent is encoded twice
        self.active=True

class Customer_data(db.Model):
    __tablename__ = "customers_data"
    customer_id = Column(Integer, primary_key=True)
    customer_type = Column(Integer, nullable=False)
    email = Column(String, nullable=True)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    city = Column(String, nullable=True)
    address = Column(String, nullable=True)
    location = Column(String, nullable=True)
    language = Column(String, nullable=True)
    
    #for debugging
    def __repr__(self) -> str:
        return f"Customer_data(customer_id={self.customer_id!r}, first_name={self.first_name!r})"

class Provider(db.Model):
    __tablename__ = "providers"
    id = Column(Integer, primary_key=True)
    uuid = Column(UUID, nullable=True)
    country_code = Column(String(5), nullable=False)
    mobile = Column(String(30), nullable=False)
    #password = Column(String, nullable=False)
    active = Column(Boolean, nullable=False)
    
    def __init__(self, uuid, country_code, mobile):
        self.uuid = uuid,
        self.country_code = country_code
        self.mobile = mobile
        #pwhash = bcrypt.hashpw(password.encode('utf8'), bcrypt.gensalt())
        #self.password = pwhash.decode('utf8') # decode the hash to prevent is encoded twice
        self.active=True
        
class Provider_data(db.Model):
    __tablename__ = "providers_data"
    provider_id = Column(Integer, primary_key=True)
    provider_type = Column(Integer, nullable=False)
    email = Column(String, nullable=True)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    city = Column(String, nullable=False)
    address = Column(String, nullable=True)
    location = Column(String, nullable=True)
    vat = Column(Float,nullable=False)
    language = Column(ARRAY(String), nullable=True)
    description_long = Column(String,nullable=True)
    
    
    #for debugging
    def __repr__(self) -> str:
        return f"Provider_data(provider_id={self.provider_id!r}, first_name={self.first_name!r})"
    
class Provider_service(db.Model):
    __tablename__ = "providers_service"
    id = Column(Integer,primary_key=True)
    provider_id = Column(Integer, nullable=False)
    service = Column(Integer,nullable=False)
    #advice = Column(Float,nullable=False)
    work_start = Column(Float,nullable=False)
    work_end = Column(Float,nullable=False)
    price = Column(Float,nullable=False)
    holidays = Column(Float, default=False)
    saturdays = Column(Float, default=False)
    sundays = Column(Float, default=False)
    #reviews = Column(ARRAY(Integer),nullable=True)
    reviews = Column(MutableList.as_mutable(ARRAY(Integer)), nullable=True)
    reviews_aver = Column(Float,nullable=True)
    description_short = Column(String,nullable=True)
    
    #for debugging
    def __repr__(self) -> str:
        return f"Provider_service(provider_id={self.provider_id!r}, service={self.service!r})"

class Provider_reserved(db.Model):
    __tablename__ = "providers_reserved"
    id = Column(Integer,primary_key=True)
    provider_id = Column(Integer, nullable=False)
    customer_id = Column(Integer, nullable=False) # 0=others
    date = Column(Date,nullable=False) # Date of reservation
    time1 = Column(Float,nullable=True) # for future
    time2 = Column(Float,nullable=True) # for future
    location = Column(String, nullable=False)
    date_init = Column(DateTime(timezone=True), default=lambda: datetime.datetime.now(datetime.timezone.utc)) # Created (-> Select)
    date_done = Column(DateTime(timezone=True), nullable=True) # Done (Execute->Review)
    description = Column(String,nullable=False) # Customer note
    status = Column(Boolean, nullable=False) # True = Active
    
    def __init__(self,provider_id, customer_id, date, location,description):
        self.provider_id= provider_id
        self.customer_id= customer_id
        self.date = date
        #self.time1 = time1
        #self.time2 = time2
        self.location = location
        #self.date_init = datetime.datetime.now()
        self.description = description #(Select-Confirm-Execute-Review)
        self.status = True # Provider is reserved (not available)
    
    
    #for debugging
    def __repr__(self) -> str:
        return f"Provider_reserved(provider_id={self.provider_id!r}, status={self.status!r})"

class Payments(db.Model):
    __tablename__ = "payments"
    id = Column(Integer,primary_key=True)
    provider_id = Column(Integer, nullable=False)
    service = Column(Integer,nullable=False)
    customer_id = Column(Integer, nullable=False)
    date = Column(String,nullable=True)
    quantity = Column(Integer,nullable=False)
    price = Column(Float, nullable=False)
    total_price = Column(Float, nullable=False) # VAT included
    currency = Column(String,nullable=False)
    description = Column(String,nullable=True)
    date_init = Column(DateTime(timezone=True), default=lambda: datetime.datetime.now(datetime.timezone.utc)) # Created
    title = Column(String,nullable=True)
    session_id = Column(String,nullable=True)
    status = Column(Boolean,nullable=True)
    mail_sent = Column(String(2), nullable=True, default="", server_default=text("''"))
    
    def __init__(self,provider_id, service_id, customer_id, date, quantity, price, total_price, currency, description):
        self.provider_id= provider_id
        self.service = service_id
        self.customer_id= customer_id
        self.date = date
        self.quantity = quantity
        self.price = price
        self.total_price = total_price
        self.currency = currency
        self.description = description

    #for debugging
    def __repr__(self) -> str:
        return f"Payments(id={self.id!r}, provider_id={self.provider_id!r}, customer_id={self.customer_id!r}, total_price={self.total_price!r})"
        
class Reviews(db.Model):
    __tablename__ = "reviews"
    id = Column(Integer,primary_key=True)
    payment_id = Column(Integer, nullable=False)
    provider_id = Column(Integer, nullable=False)
    service = Column(Integer,nullable=False)
    customer_id = Column(Integer, nullable=False)
    status = Column(Integer, nullable=False) # 1=Paid; 2=Done; 3=Review
    review_type = Column(Integer,nullable=True) # 1: Customer-> Provider; 2: Provider -> Customer
    comment = Column(String,nullable=True)
    review_rate = Column(Float,nullable=True) # 0.5 -> 5.0
    date_done = Column(DateTime(timezone=True)) # Done date
    date_review = Column(DateTime(timezone=True)) # Review date

    def __init__(self,payment_id,provider_id, service, customer_id,status):
        self.payment_id= payment_id
        self.provider_id= provider_id
        self.service = service
        self.customer_id= customer_id
        self.status=status

    #for debugging
    def __repr__(self) -> str:
        return f"Reviews(id={self.id!r},provider_id={self.provider_id!r}, customer_id={self.customer_id!r})"
    
class Orders(db.Model):
    __tablename__ = "orders"
    id = Column(Integer,primary_key=True)
    service_id = Column(Integer,nullable=False)
    customer_id = Column(Integer, nullable=False)
    status = Column(Integer, nullable=False, default=1) # 1=Order; 2=Confirm; 3=Done
    datetime_order = Column(String,nullable=False)
    comment = Column(String,nullable=True)
    date_init = Column(DateTime(timezone=True), default=lambda: datetime.datetime.now(datetime.timezone.utc)) # Created
    date_done = Column(DateTime(timezone=True)) # Done date
    

    def __init__(self,service_id, customer_id,datetime_order,comment):
        self.service_id = service_id
        self.customer_id= customer_id
        self.datetime_order=datetime_order
        self.comment = comment

    #for debugging
    def __repr__(self) -> str:
        return f"Orders(id={self.id!r},service_id={self.service_id!r}, customer_id={self.customer_id!r})"
    
if __name__ == '__main__':
    db_seed()