"""
Database configuration and connection management for MySQL backend
"""
import pymysql
import configparser
import os
from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Boolean, Float, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import bcrypt
import logging

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Load database configuration from environment or config file
def load_database_config():
    """Load database configuration from environment variables or config file"""
    # Check for environment variable first (recommended for production)
    db_url = os.environ.get('SQLALCHEMY_DATABASE_URI')
    if db_url:
        logger.info("Using database URL from environment variable")
        return db_url, None
    
    # Fall back to config file
    try:
        config = configparser.ConfigParser()
        config_file = os.path.join(os.path.dirname(__file__), 'database.ini')
        config.read(config_file)
        
        if not config.has_section('database'):
            raise configparser.NoSectionError('database')
        
        db_config = {
            'host': config['database']['host'],
            'port': int(config['database']['port']),
            'user': config['database']['user'],
            'password': config['database']['password'],
            'database': config['database']['database'],
            'charset': config['database']['charset']
        }
        
        db_url = f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}"
        logger.info(f"Using database config from {config_file}")
        return db_url, db_config
        
    except (configparser.NoSectionError, KeyError, FileNotFoundError) as e:
        logger.error(f"Database configuration error: {e}")
        logger.info("Please set SQLALCHEMY_DATABASE_URI environment variable or create database.ini file")
        raise RuntimeError(f"Database configuration missing. Set SQLALCHEMY_DATABASE_URI env var or create database.ini with [database] section. Error: {e}")

# Load configuration
SQLALCHEMY_DATABASE_URL, DB_CONFIG = load_database_config()

# Create SQLAlchemy engine
engine = create_engine(SQLALCHEMY_DATABASE_URL, echo=False)

# Create SessionLocal class
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Create Base class for models
Base = declarative_base()

# Database connection functions
def get_db_connection():
    """Get a raw PyMySQL database connection"""
    try:
        if DB_CONFIG:
            # Use config dictionary for raw PyMySQL connection
            conn = pymysql.connect(**DB_CONFIG)
        else:
            # Parse environment URL for raw connection (less common case)
            logger.warning("Using SQLAlchemy engine for raw connection (DB_CONFIG not available)")
            return engine.raw_connection()
        
        logger.info("✅ Connected to MySQL database successfully")
        return conn
    except Exception as e:
        logger.error(f"❌ Failed to connect to database: {e}")
        raise

def get_db_session():
    """Get SQLAlchemy database session"""
    db = SessionLocal()
    try:
        return db
    finally:
        db.close()

def test_connection():
    """Test database connection"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Test query
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()
        logger.info(f"🗄️  Database Version: {version[0]}")

        cursor.execute("SELECT COUNT(*) FROM wp_posts")
        post_count = cursor.fetchone()
        logger.info(f"📊 WordPress Posts: {post_count[0]:,} posts")

        cursor.close()
        conn.close()
        logger.info("✅ Database connection test successful")
        return True
    except Exception as e:
        logger.error(f"❌ Database connection test failed: {e}")
        return False

# SQLAlchemy Models
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True, nullable=False)
    email = Column(String(100), unique=True, index=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    role = Column(String(20), default="user")
    created_at = Column(DateTime, default=datetime.utcnow)
    last_login = Column(DateTime, nullable=True)
    is_active = Column(Boolean, default=True)

    def set_password(self, password):
        """Hash and set password"""
        self.password_hash = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')

    def check_password(self, password):
        """Check password against hash"""
        return bcrypt.checkpw(password.encode('utf-8'), self.password_hash.encode('utf-8'))

class Order(Base):
    __tablename__ = "orders"

    id = Column(Integer, primary_key=True, index=True)
    order_number = Column(String(50), unique=True, nullable=False)
    customer_name = Column(String(100), nullable=False)
    customer_email = Column(String(100))
    total_amount = Column(Float, nullable=False)
    freight_amount = Column(Float, default=0.0)
    platform = Column(String(50))  # MercadoLivre, Shopee, Magalu, etc.
    status = Column(String(20), default="pending")  # pending, processing, shipped, delivered, cancelled
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, index=True)
    sku = Column(String(50), unique=True, nullable=False)
    name = Column(String(255), nullable=False)
    description = Column(Text)
    cost_price = Column(Float)
    sale_price = Column(Float)
    stock_quantity = Column(Integer, default=0)
    category = Column(String(100))
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class OrderItem(Base):
    __tablename__ = "order_items"

    id = Column(Integer, primary_key=True, index=True)
    order_id = Column(Integer, ForeignKey("orders.id"), nullable=False)
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
    quantity = Column(Integer, nullable=False)
    unit_price = Column(Float, nullable=False)
    total_price = Column(Float, nullable=False)
    product_code = Column(String(50))  # Product code/size
    product_size = Column(String(20))  # Product size

    # Relationships
    order = relationship("Order", back_populates="items")
    product = relationship("Product", back_populates="order_items")

# Extended Product model with WordPress-like functionality
class ProductCode(Base):
    """Product codes/sizes with stock tracking"""
    __tablename__ = "product_codes"

    id = Column(Integer, primary_key=True, index=True)
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
    code = Column(String(50), nullable=False)  # Product code/SKU
    size = Column(String(20))  # Size variant
    ean = Column(String(20))  # EAN barcode
    stock_store = Column(Integer, default=0)  # Store stock
    stock_one = Column(Integer, default=0)  # One marketplace stock
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    product = relationship("Product", back_populates="codes")

class ProductGallery(Base):
    """Product gallery images for different marketplaces"""
    __tablename__ = "product_galleries"

    id = Column(Integer, primary_key=True, index=True)
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
    gallery_type = Column(String(20), nullable=False)  # 'tray', 'enjoei'
    image_url = Column(String(500), nullable=False)
    image_order = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    product = relationship("Product", back_populates="galleries")

class ProductAttribute(Base):
    """Product attributes like category, subcategory, etc."""
    __tablename__ = "product_attributes"

    id = Column(Integer, primary_key=True, index=True)
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
    attribute_type = Column(String(50), nullable=False)  # 'category', 'subcategory', 'linha'
    attribute_value = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    product = relationship("Product", back_populates="attributes")

class Sale(Base):
    """WordPress-like sales/vendas management"""
    __tablename__ = "sales"

    id = Column(Integer, primary_key=True, index=True)
    id_venda = Column(String(50), unique=True, nullable=False)
    data = Column(DateTime, nullable=False)
    vendedor = Column(String(100), nullable=False)
    cliente = Column(String(100))
    frete = Column(Float, default=0.0)
    plataforma = Column(String(50))  # WordPress platform field
    outro_plataforma = Column(String(100))  # Custom platform name
    taxas = Column(Float, default=0.0)
    id_venda_ml = Column(String(50))  # MercadoLivre sale ID
    obs = Column(Text)  # Observations
    devolucao = Column(Boolean, default=False)  # Return/devolution
    status_pacote = Column(String(20), default='recebido')  # Package status
    situacao_pedido_fox = Column(String(20), default='recebido')  # Fox order status
    subtotal = Column(Float, default=0.0)
    total = Column(Float, default=0.0)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    items = relationship("SaleItem", back_populates="sale", cascade="all, delete-orphan")

class SaleItem(Base):
    """Items within a sale"""
    __tablename__ = "sale_items"

    id = Column(Integer, primary_key=True, index=True)
    sale_id = Column(Integer, ForeignKey("sales.id"), nullable=False)
    product_id = Column(Integer, ForeignKey("products.id"))
    product_name = Column(String(255), nullable=False)
    code = Column(String(50), nullable=False)
    size = Column(String(20))
    quantity = Column(Integer, nullable=False)
    price = Column(Float, nullable=False)
    total = Column(Float, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    sale = relationship("Sale", back_populates="items")

# Add relationships
Order.items = relationship("OrderItem", order_by=OrderItem.id, back_populates="order")
Product.order_items = relationship("OrderItem", order_by=OrderItem.id, back_populates="product")

# Add new relationships for extended product model
Product.codes = relationship("ProductCode", order_by=ProductCode.id, back_populates="product", cascade="all, delete-orphan")
Product.galleries = relationship("ProductGallery", order_by=ProductGallery.image_order, back_populates="product", cascade="all, delete-orphan")
Product.attributes = relationship("ProductAttribute", order_by=ProductAttribute.id, back_populates="product", cascade="all, delete-orphan")

# Initialize database tables
def init_database():
    """Create all tables if they don't exist"""
    try:
        Base.metadata.create_all(bind=engine)
        logger.info("✅ Database tables initialized successfully")
    except Exception as e:
        logger.error(f"❌ Failed to initialize database tables: {e}")
        raise

if __name__ == "__main__":
    # Test connection when run directly
    test_connection()
    init_database()