import os

from flask import Flask, send_from_directory, jsonify
from werkzeug.exceptions import HTTPException, NotFound, BadRequest
from sqlalchemy import text

from .extensions import db, migrate, cors
from config import config


def create_app(config_name=None):
    if config_name is None:
        config_name = os.environ.get('FLASK_ENV', 'production')

    app = Flask(__name__, static_folder='static', static_url_path='')
    app.config.from_object(config.get(config_name, config['default']))

    db.init_app(app)
    migrate.init_app(app, db)
    cors.init_app(app, resources={r'/api/*': {'origins': '*'}})

    from .routes.projects import projects_bp
    from .routes.deliverables import deliverables_bp

    app.register_blueprint(projects_bp, url_prefix='/api')
    app.register_blueprint(deliverables_bp, url_prefix='/api')

    with app.app_context():
        db.create_all()
        _run_migrations()

    @app.route('/')
    def index():
        return send_from_directory(app.static_folder, 'index.html')

    @app.errorhandler(404)
    def handle_404(e):
        if request.path.startswith('/api/'):
            return jsonify({'error': 'Resource not found', 'message': str(e)}), 404
        return send_from_directory(app.static_folder, 'index.html')

    @app.errorhandler(400)
    def bad_request(e):
        return jsonify({'error': 'Bad request', 'message': str(e)}), 400

    @app.errorhandler(Exception)
    def handle_exception(e):
        if isinstance(e, HTTPException):
            return jsonify({'error': e.name, 'message': e.description}), e.code
        app.logger.error(f"Unhandled exception: {str(e)}", exc_info=True)
        return jsonify({'error': 'Internal server error', 'message': 'An unexpected error occurred'}), 500

    return app


def _run_migrations():
    """
    Safe idempotent migrations for existing databases.
    ALTER TABLE is a no-op if the column already exists (exception silently caught).
    Add new columns here as the schema evolves.
    """
    migrations = [
        'ALTER TABLE projects ADD COLUMN drive_url VARCHAR(500)',
        'ALTER TABLE projects ADD COLUMN archived_at DATETIME',
    ]

    with db.engine.connect() as conn:
        for stmt in migrations:
            try:
                conn.execute(text(stmt))
                conn.commit()
            except Exception:
                # Column already exists — safe to ignore
                pass