app.py

from flask import Flask, render_template, jsonify, request
import sqlite3
import os
from pathlib import Path

app = Flask(__name__)

SOURCE_DIR = Path('databases/source')
DEST_DIR = Path('databases/destination')

class DatabaseHandler:
    def __init__(self, db_path):
        self.db_path = db_path

    def get_connection(self):
        return sqlite3.connect(self.db_path)

    def get_tables(self):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
        tables = [row[0] for row in cursor.fetchall()]
        conn.close()
        return tables

    def get_table_schema(self, table_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
        result = cursor.fetchone()
        conn.close()
        return result[0] if result else None

    def get_table_info(self, table_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        conn.close()
        return columns

    def get_constraints(self, table_name):
        conn = self.get_connection()
        cursor = conn.cursor()

        constraints = {
            'foreign_keys': [],
            'indexes': [],
            'primary_key': None
        }

        # Get foreign keys
        cursor.execute(f"PRAGMA foreign_key_list({table_name})")
        fks = cursor.fetchall()
        for fk in fks:
            constraints['foreign_keys'].append({
                'id': fk[0],
                'seq': fk[1],
                'table': fk[2],
                'from': fk[3],
                'to': fk[4],
                'on_update': fk[5],
                'on_delete': fk[6]
            })

        # Get indexes
        cursor.execute(f"PRAGMA index_list({table_name})")
        indexes = cursor.fetchall()
        for idx in indexes:
            cursor.execute(f"PRAGMA index_info({idx[1]})")
            idx_info = cursor.fetchall()
            constraints['indexes'].append({
                'name': idx[1],
                'unique': bool(idx[2]),
                'columns': [col[2] for col in idx_info]
            })

        # Get primary key info
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        pk_cols = [col[1] for col in columns if col[5] > 0]
        if pk_cols:
            constraints['primary_key'] = pk_cols

        conn.close()
        return constraints

    def get_table_data(self, table_name, limit=100):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f"SELECT * FROM {table_name} LIMIT ?", (limit,))
        rows = cursor.fetchall()

        # Get column names
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [col[1] for col in cursor.fetchall()]

        conn.close()
        return {'columns': columns, 'rows': rows}

    def get_row_count(self, table_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
        count = cursor.fetchone()[0]
        conn.close()
        return count

    def table_exists(self, table_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
        exists = cursor.fetchone() is not None
        conn.close()
        return exists

    def copy_table_structure(self, table_name, dest_handler):
        schema = self.get_table_schema(table_name)
        if not schema:
            return False, "Table schema not found"

        dest_conn = dest_handler.get_connection()
        dest_cursor = dest_conn.cursor()

        try:
            # Drop table if exists
            dest_cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
            # Create table
            dest_cursor.execute(schema)
            dest_conn.commit()
            dest_conn.close()
            return True, "Table structure copied successfully"
        except Exception as e:
            dest_conn.close()
            return False, str(e)

    def copy_table_data(self, table_name, dest_handler, mode='replace'):
        """
        Copy data from this database to destination database
        mode: 'replace' - delete existing data first, 'append' - add to existing data
        """
        conn = self.get_connection()
        cursor = conn.cursor()

        # Get all data
        cursor.execute(f"SELECT * FROM {table_name}")
        rows = cursor.fetchall()

        # Get column info
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [col[1] for col in cursor.fetchall()]
        conn.close()

        dest_conn = dest_handler.get_connection()
        dest_cursor = dest_conn.cursor()

        try:
            if mode == 'replace':
                dest_cursor.execute(f"DELETE FROM {table_name}")

            placeholders = ','.join(['?' for _ in columns])
            insert_sql = f"INSERT INTO {table_name} ({','.join(columns)}) VALUES ({placeholders})"

            dest_cursor.executemany(insert_sql, rows)
            dest_conn.commit()
            rows_copied = len(rows)
            dest_conn.close()
            return True, f"{rows_copied} rows copied successfully"
        except Exception as e:
            dest_conn.close()
            return False, str(e)

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

@app.route('/api/databases')
def get_databases():
    source_dbs = [f.name for f in SOURCE_DIR.glob('*.db')] if SOURCE_DIR.exists() else []
    dest_dbs = [f.name for f in DEST_DIR.glob('*.db')] if DEST_DIR.exists() else []
    return jsonify({
        'source': source_dbs,
        'destination': dest_dbs
    })

@app.route('/api/tables/<db_type>/<db_name>')
def get_tables(db_type, db_name):
    db_dir = SOURCE_DIR if db_type == 'source' else DEST_DIR
    db_path = db_dir / db_name

    if not db_path.exists():
        return jsonify({'error': 'Database not found'}), 404

    handler = DatabaseHandler(db_path)
    tables = handler.get_tables()
    return jsonify({'tables': tables})

@app.route('/api/table-info/<db_type>/<db_name>/<table_name>')
def get_table_info(db_type, db_name, table_name):
    db_dir = SOURCE_DIR if db_type == 'source' else DEST_DIR
    db_path = db_dir / db_name

    if not db_path.exists():
        return jsonify({'error': 'Database not found'}), 404

    handler = DatabaseHandler(db_path)

    info = handler.get_table_info(table_name)
    constraints = handler.get_constraints(table_name)
    schema = handler.get_table_schema(table_name)
    row_count = handler.get_row_count(table_name)

    return jsonify({
        'columns': [{'name': col[1], 'type': col[2], 'notnull': bool(col[3]), 'default': col[4], 'pk': col[5]} for col in info],
        'constraints': constraints,
        'schema': schema,
        'row_count': row_count
    })

@app.route('/api/table-data/<db_type>/<db_name>/<table_name>')
def get_table_data(db_type, db_name, table_name):
    db_dir = SOURCE_DIR if db_type == 'source' else DEST_DIR
    db_path = db_dir / db_name

    if not db_path.exists():
        return jsonify({'error': 'Database not found'}), 404

    limit = request.args.get('limit', 100, type=int)
    handler = DatabaseHandler(db_path)
    data = handler.get_table_data(table_name, limit)

    return jsonify(data)

@app.route('/api/copy-structure', methods=['POST'])
def copy_structure():
    data = request.json
    source_db = data.get('source_db')
    dest_db = data.get('dest_db')
    table_name = data.get('table_name')

    source_path = SOURCE_DIR / source_db
    dest_path = DEST_DIR / dest_db

    if not source_path.exists() or not dest_path.exists():
        return jsonify({'error': 'Database not found'}), 404

    source_handler = DatabaseHandler(source_path)
    dest_handler = DatabaseHandler(dest_path)

    success, message = source_handler.copy_table_structure(table_name, dest_handler)

    return jsonify({'success': success, 'message': message})

@app.route('/api/copy-data', methods=['POST'])
def copy_data():
    data = request.json
    source_db = data.get('source_db')
    dest_db = data.get('dest_db')
    table_name = data.get('table_name')
    mode = data.get('mode', 'replace')  # 'replace' or 'append'

    source_path = SOURCE_DIR / source_db
    dest_path = DEST_DIR / dest_db

    if not source_path.exists() or not dest_path.exists():
        return jsonify({'error': 'Database not found'}), 404

    source_handler = DatabaseHandler(source_path)
    dest_handler = DatabaseHandler(dest_path)

    # Check if table exists in destination
    if not dest_handler.table_exists(table_name):
        return jsonify({'error': 'Table does not exist in destination database. Copy structure first.'}), 400

    success, message = source_handler.copy_table_data(table_name, dest_handler, mode)

    return jsonify({'success': success, 'message': message})

if __name__ == '__main__':
    # Create directories if they don't exist
    SOURCE_DIR.mkdir(parents=True, exist_ok=True)
    DEST_DIR.mkdir(parents=True, exist_ok=True)

    app.run(debug=True, host='0.0.0.0', port=5000)