from flask import Flask, request, jsonify, render_template
from flask_toastr import Toastr
import pandas as pd
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, UniqueConstraint, DateTime, Float
import os
from datetime import datetime

app = Flask(__name__, template_folder='templates')
toastr = Toastr(app)
app.config['SECRET_KEY'] = 'your-secret-key'

Base = declarative_base()
engine = create_engine('sqlite:///crud_app.db')
Session = sessionmaker(bind=engine)

def get_column_type(dtype):
    if 'int' in str(dtype):
        return Integer
    elif 'float' in str(dtype):
        # Handle empty strings by returning String instead of Float
        return String(255)
    elif 'datetime' in str(dtype):
        return DateTime
    return String(255)

def generate_models_from_excel(excel_file):
    xls = pd.ExcelFile(excel_file)
    models = {}
    
    for sheet_name in xls.sheet_names:
        df = pd.read_excel(excel_file, sheet_name=sheet_name)
        
        class_attrs = {
            '__tablename__': sheet_name.lower().replace(' ', '_'),
            'id': Column(Integer, primary_key=True),
        }
        
        # Generate columns from DataFrame
        for column in df.columns:
            if column.lower() == 'id':
                continue
                
            if column.lower().endswith('_id'):
                related_table = column.lower().replace('_id', '')
                class_attrs[column] = Column(Integer, ForeignKey(f'{related_table}.id'))
                class_attrs[related_table] = relationship(related_table.capitalize())
            else:
                column_type = get_column_type(df[column].dtype)
                class_attrs[column] = Column(column_type)
        
        # Create model class
        model_class = type(sheet_name.capitalize(), (Base,), class_attrs)
        models[sheet_name] = model_class
    
    return models

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/api/tables')
def get_tables():
    return jsonify(list(models.keys()))

@app.route('/api/schema/<table_name>')
def get_schema(table_name):
    if table_name not in models:
        return jsonify({'error': 'Table not found'}), 404
    
    model = models[table_name]
    columns = []
    for column in model.__table__.columns:
        col_type = str(column.type)
        columns.append({
            'name': column.name,
            'type': col_type,
            'primary_key': column.primary_key,
            'foreign_key': bool(column.foreign_keys)
        })
    return jsonify(columns)

@app.route('/api/<table_name>', methods=['GET'])
def get_records(table_name):
    session = Session()
    model = models.get(table_name)
    if not model:
        return jsonify({'error': 'Table not found'}), 404
    
    items = session.query(model).all()
    result = []
    for item in items:
        item_dict = {}
        for column in item.__table__.columns:
            value = getattr(item, column.name)
            if isinstance(value, datetime):
                value = value.isoformat()
            item_dict[column.name] = value
        result.append(item_dict)
    
    session.close()
    return jsonify(result)

@app.route('/api/<table_name>', methods=['POST'])
def create_record(table_name):
    session = Session()
    model = models.get(table_name)
    if not model:
        return jsonify({'error': 'Table not found'}), 404
    
    try:
        data = request.json
        new_record = model(**data)
        session.add(new_record)
        session.commit()
        session.refresh(new_record)
        
        result = {}
        for column in new_record.__table__.columns:
            value = getattr(new_record, column.name)
            if isinstance(value, datetime):
                value = value.isoformat()
            result[column.name] = value
            
        session.close()
        return jsonify({'message': 'Record created successfully', 'data': result})
    except Exception as e:
        session.rollback()
        return jsonify({'error': str(e)}), 400
    finally:
        session.close()

@app.route('/api/<table_name>/<int:record_id>', methods=['PUT'])
def update_record(table_name, record_id):
    session = Session()
    model = models.get(table_name)
    if not model:
        return jsonify({'error': 'Table not found'}), 404
    
    try:
        record = session.query(model).get(record_id)
        if not record:
            return jsonify({'error': 'Record not found'}), 404
        
        data = request.json
        for key, value in data.items():
            setattr(record, key, value)
        
        session.commit()
        session.refresh(record)
        
        result = {}
        for column in record.__table__.columns:
            value = getattr(record, column.name)
            if isinstance(value, datetime):
                value = value.isoformat()
            result[column.name] = value
            
        return jsonify({'message': 'Record updated successfully', 'data': result})
    except Exception as e:
        session.rollback()
        return jsonify({'error': str(e)}), 400
    finally:
        session.close()

@app.route('/api/<table_name>/<int:record_id>', methods=['DELETE'])
def delete_record(table_name, record_id):
    session = Session()
    model = models.get(table_name)
    if not model:
        return jsonify({'error': 'Table not found'}), 404
    
    try:
        record = session.query(model).get(record_id)
        if not record:
            return jsonify({'error': 'Record not found'}), 404
        
        session.delete(record)
        session.commit()
        return jsonify({'message': 'Record deleted successfully'})
    except Exception as e:
        session.rollback()
        return jsonify({'error': str(e)}), 400
    finally:
        session.close()

if __name__ == '__main__':
    print("Initializing application...")
    print(f"Current working directory: {os.getcwd()}")
    print(f"Loading Excel file: test.xlsx")
    models = generate_models_from_excel('test.xlsx')
    
    print("Creating database file: crud_app.db")
    Base.metadata.create_all(engine)
    
    session = Session()
    try:
        for sheet_name, model in models.items():
            print(f"Loading data from sheet: {sheet_name}")
            df = pd.read_excel('test.xlsx', sheet_name=sheet_name)
            for _, row in df.iterrows():
                data = row.to_dict()
                if 'id' in data:
                    del data['id']  # Remove ID column as it's auto-generated
                record = model(**data)
                session.add(record)
        session.commit()
        print("Initial data loaded successfully")
    except Exception as e:
        print(f"Error loading initial data: {e}")
        session.rollback()
    finally:
        session.close()

    print("Starting Flask server...")
    app.run(debug=True)