setup_config.py

"""
Configuration Setup Script for LIMS Application

This script populates the config_app and images_app tables with
company information and report images.

Usage:
    python setup_config.py
"""

import os
import sys
from pathlib import Path
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Database configuration
DATABASE_HOST = os.getenv("DATABASE_HOST", "localhost")
DATABASE_PORT = os.getenv("DATABASE_PORT", "1433")
DATABASE_NAME = os.getenv("DATABASE_NAME", "LIMS")
DATABASE_USER = os.getenv("DATABASE_USER", "sa")
DATABASE_PASSWORD = os.getenv("DATABASE_PASSWORD", "")
DATABASE_DRIVER = os.getenv("DATABASE_DRIVER", "SQL Server")

# Connection string (matches main application logic)
connection_string = (
    f"mssql+pyodbc://{DATABASE_USER}:{DATABASE_PASSWORD}@"
    f"{DATABASE_HOST}:{DATABASE_PORT}/{DATABASE_NAME}"
    f"?driver={DATABASE_DRIVER.replace(' ', '+')}"
)

# Image names that should be in the images directory
REQUIRED_IMAGES = [
    "logo.png",
    "coa_header.png",
    "coa_footer.png",
    "coc_header.png",
    "coc_footer.png",
    "cday_header.png",
    "cday_footer.png",
    "coa_rep.png"
]


def get_company_info():
    """Prompt user for company configuration information."""
    print("\n" + "=" * 60)
    print("LIMS CONFIGURATION SETUP")
    print("=" * 60)
    print("\nPlease enter the company configuration details:\n")

    company_name = input("Company Name (max 40 chars): ").strip()[:40]
    supervisor_name = input("Supervisor Name (max 40 chars): ").strip()[:40]
    email_supervisor = input("Supervisor Email (max 60 chars): ").strip()[:60]

    print("\n" + "-" * 60)
    print("Configuration Summary:")
    print("-" * 60)
    print(f"Company Name:     {company_name}")
    print(f"Supervisor Name:  {supervisor_name}")
    print(f"Supervisor Email: {email_supervisor}")
    print("-" * 60)

    confirm = input("\nIs this information correct? (yes/no): ").strip().lower()
    if confirm not in ['yes', 'y']:
        print("\nConfiguration cancelled. Please run the script again.")
        sys.exit(0)

    return company_name, supervisor_name, email_supervisor


def setup_config_app(engine, company_name, supervisor_name, email_supervisor):
    """Insert or update the singleton configuration record."""
    with engine.connect() as conn:
        # Check if configuration already exists
        result = conn.execute(text("SELECT COUNT(*) FROM config_app"))
        count = result.scalar()

        if count > 0:
            print("\n⚠ Configuration already exists. Updating existing record...")
            query = text("""
                UPDATE config_app
                SET company_name = :company_name,
                    supervisor_name = :supervisor_name,
                    e_mail_supervisor = :email_supervisor
            """)
        else:
            print("\n✓ Creating new configuration record...")
            query = text("""
                INSERT INTO config_app (company_name, supervisor_name, e_mail_supervisor)
                VALUES (:company_name, :supervisor_name, :email_supervisor)
            """)

        conn.execute(query, {
            "company_name": company_name,
            "supervisor_name": supervisor_name,
            "email_supervisor": email_supervisor
        })
        conn.commit()
        print("✓ Configuration saved successfully!")


def get_images_directory():
    """Prompt user for the images directory location."""
    print("\n" + "=" * 60)
    print("IMAGE FILES SETUP")
    print("=" * 60)
    print("\nRequired image files (PNG format):")
    for img in REQUIRED_IMAGES:
        print(f"  - {img}")

    print("\nPlease provide the directory containing these images.")
    print("You can use relative or absolute paths.")
    print("Example: ./images  or  C:\\Users\\YourName\\Documents\\lims_images\n")

    while True:
        img_dir = input("Images directory path: ").strip()

        if not img_dir:
            print("❌ Path cannot be empty. Please try again.")
            continue

        img_path = Path(img_dir)

        if not img_path.exists():
            print(f"❌ Directory '{img_dir}' does not exist. Please try again.")
            continue

        if not img_path.is_dir():
            print(f"❌ '{img_dir}' is not a directory. Please try again.")
            continue

        # Check for required images
        missing_images = []
        for img_file in REQUIRED_IMAGES:
            if not (img_path / img_file).exists():
                missing_images.append(img_file)

        if missing_images:
            print(f"\n❌ Missing image files:")
            for img in missing_images:
                print(f"  - {img}")
            print("\nPlease ensure all required images are in the directory and try again.")
            retry = input("Try a different directory? (yes/no): ").strip().lower()
            if retry not in ['yes', 'y']:
                print("\nSetup cancelled.")
                sys.exit(0)
            continue

        return img_path


def setup_images_app(engine, images_dir):
    """Insert or update images in the images_app table."""
    with engine.connect() as conn:
        print("\n" + "-" * 60)
        print("Processing Images...")
        print("-" * 60)

        for img_file in REQUIRED_IMAGES:
            img_path = images_dir / img_file
            img_name = img_file.replace('.png', '')  # Remove extension

            # Read image as binary
            with open(img_path, 'rb') as f:
                img_data = f.read()

            # Check if image already exists
            result = conn.execute(
                text("SELECT COUNT(*) FROM images_app WHERE name_img = :name"),
                {"name": img_name}
            )
            exists = result.scalar() > 0

            if exists:
                print(f"  Updating: {img_name} ({len(img_data)} bytes)")
                query = text("""
                    UPDATE images_app
                    SET img = :img_data
                    WHERE name_img = :name
                """)
            else:
                print(f"  Inserting: {img_name} ({len(img_data)} bytes)")
                query = text("""
                    INSERT INTO images_app (name_img, img)
                    VALUES (:name, :img_data)
                """)

            conn.execute(query, {"name": img_name, "img_data": img_data})

        conn.commit()
        print("\n✓ All images processed successfully!")


def verify_setup(engine):
    """Verify that the configuration and images were set up correctly."""
    print("\n" + "=" * 60)
    print("VERIFICATION")
    print("=" * 60)

    with engine.connect() as conn:
        # Verify config_app
        result = conn.execute(text("SELECT * FROM config_app"))
        config = result.fetchone()

        if config:
            print("\n✓ Configuration Table:")
            print(f"  Company Name:     {config[0].strip() if config[0] else 'N/A'}")
            print(f"  Supervisor Name:  {config[1].strip() if config[1] else 'N/A'}")
            print(f"  Supervisor Email: {config[2].strip() if config[2] else 'N/A'}")
        else:
            print("\n❌ No configuration found!")

        # Verify images_app
        result = conn.execute(text("SELECT name_img, LEN(img) as size FROM images_app ORDER BY name_img"))
        images = result.fetchall()

        if images:
            print("\n✓ Image Table:")
            for img in images:
                print(f"  {img[0].strip():20s} - {img[1]:,} bytes")
        else:
            print("\n❌ No images found!")

        print("\n" + "=" * 60)
        print("Setup completed successfully!")
        print("=" * 60)


def main():
    """Main setup function."""
    try:
        # Create database engine
        print("\nConnecting to database...")
        engine = create_engine(connection_string)

        # Test connection
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        print("✓ Database connection successful!")

        # Get company configuration
        company_name, supervisor_name, email_supervisor = get_company_info()

        # Setup config_app table
        setup_config_app(engine, company_name, supervisor_name, email_supervisor)

        # Get images directory
        images_dir = get_images_directory()

        # Setup images_app table
        setup_images_app(engine, images_dir)

        # Verify setup
        verify_setup(engine)

    except Exception as e:
        print(f"\n❌ Error: {str(e)}")
        import traceback
        traceback.print_exc()
        sys.exit(1)


if __name__ == "__main__":
    main()