from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from datetime import datetime

db = SQLAlchemy()

class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    is_admin = db.Column(db.Boolean, default=False) # Maintenu pour la compatibilité initiale
    gallery_id = db.Column(db.Integer, nullable=True) # Doit être nul pour l'admin
    role = db.Column(db.String(20), nullable=False) # NOUVEAU: 'admin', 'manager', 'commercial'

class Artist(db.Model):
    __tablename__ = 'artist'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    description = db.Column(db.Text, nullable=True)
    description_en = db.Column(db.Text, nullable=True)
    image_path = db.Column(db.String(255), nullable=True)

class Artwork(db.Model):
    __tablename__ = 'artwork'
    id = db.Column(db.Integer, primary_key=True)
    cegid = db.Column(db.String(100), unique=True, nullable=False)
    title = db.Column(db.String(200), nullable=False)
    image_path = db.Column(db.String(255), nullable=False)
    artist_name = db.Column(db.String(100), nullable=False)
    type = db.Column(db.String(50))
    price_range = db.Column(db.String(50))
    height_cm = db.Column(db.Integer)
    width_cm = db.Column(db.Integer)
    depth_cm = db.Column(db.Integer)
    commentary = db.Column(db.Text)
    status = db.Column(db.String(20), nullable=False, default='pending')
    is_available = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

def create_gallery_client_table(gallery_id):
    """Dynamically create a client table for each gallery"""
    class_name = f'GalleryClient_{gallery_id}'
    
    return type(class_name, (db.Model,), {
        '__tablename__': f'gallery{gallery_id}_clients',
        'id': db.Column(db.Integer, primary_key=True),
        'commercial_id': db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True), # NOUVEAU
        'first_name': db.Column(db.String(100), nullable=False),
        'last_name': db.Column(db.String(100), nullable=False),
        'email': db.Column(db.String(120)),
        'phone_number': db.Column(db.String(30)),
        'commercial_name': db.Column(db.String(100)), # Maintenu pour la transition
        'status': db.Column(db.String(20), nullable=False, default='Prospect'),
        'nationality': db.Column(db.String(50)),
        'language': db.Column(db.String(20)),
        'favorite_artists': db.Column(db.JSON),
        'newsletter': db.Column(db.Boolean, default=False),
        'commentary': db.Column(db.Text),
        'potential_haut': db.Column(db.Boolean, default=False),
        'relance_status': db.Column(db.String(20), default='À relancer'),
        'relance_date': db.Column(db.Date, nullable=True),
        'negotiation_status': db.Column(db.String(20), default='En cours'),
        'created_at': db.Column(db.DateTime, default=datetime.utcnow),
        'updated_at': db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    })