importer.py

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import mysql.connector
import os
import json
from datetime import datetime
import re

class ImporterApp(tk.Tk):
    def __init__(self):
        super().__init__()
        self.geometry('500x768')
        self.title("Database Import Tool")
        self.config_json = self.load_config()
        
    def load_config(self):
        try:
            with open('config.json', 'r') as f:
                return json.load(f)
        except:
            return {"configs": []}

    def create_widgets(self):
        # Database connection frame
        conn_frame = ttk.LabelFrame(self, text="Database Connection", padding=10)
        conn_frame.pack(fill='x', padx=5, pady=5)

        # Host
        ttk.Label(conn_frame, text='Host:').pack(anchor='w')
        self.host = ttk.Entry(conn_frame)
        self.host.insert(0, 'localhost')
        self.host.pack(fill='x', pady=(0, 5))

        # Username
        ttk.Label(conn_frame, text='Username:').pack(anchor='w')
        self.username = ttk.Entry(conn_frame)
        self.username.insert(0, 'root')
        self.username.pack(fill='x', pady=(0, 5))

        # Password
        ttk.Label(conn_frame, text='Password:').pack(anchor='w')
        self.password = ttk.Entry(conn_frame, show='*')
        self.password.pack(fill='x', pady=(0, 5))

        # Database
        ttk.Label(conn_frame, text='Database:').pack(anchor='w')
        self.database = ttk.Entry(conn_frame)
        self.database.pack(fill='x', pady=(0, 5))

        # Load from config if available
        if self.config_json["configs"]:
            first_config = self.config_json["configs"][0]["database"]
            self.host.delete(0, tk.END)
            self.host.insert(0, first_config["host"])
            self.username.delete(0, tk.END)
            self.username.insert(0, first_config["user"])
            self.password.delete(0, tk.END)
            self.password.insert(0, first_config["password"])
            self.database.delete(0, tk.END)
            self.database.insert(0, first_config["database"])

        # Import directory frame
        dir_frame = ttk.LabelFrame(self, text="Import Directory", padding=10)
        dir_frame.pack(fill='x', padx=5, pady=5)

        # Directory path
        ttk.Label(dir_frame, text='Directory Path:').pack(anchor='w')
        self.dir_path = ttk.Entry(dir_frame)
        self.dir_path.pack(fill='x', side='left', expand=True)
        ttk.Button(dir_frame, text='Browse', command=self.browse_directory).pack(side='left', padx=(5, 0))

        # Import options frame
        options_frame = ttk.LabelFrame(self, text="Import Options", padding=10)
        options_frame.pack(fill='x', padx=5, pady=5)

        # Checkboxes for different types
        self.import_tables = tk.BooleanVar(value=True)
        ttk.Checkbutton(options_frame, text='Import Tables', variable=self.import_tables).pack(anchor='w')
        
        self.import_views = tk.BooleanVar(value=True)
        ttk.Checkbutton(options_frame, text='Import Views', variable=self.import_views).pack(anchor='w')
        
        self.import_routines = tk.BooleanVar(value=True)
        ttk.Checkbutton(options_frame, text='Import Routines', variable=self.import_routines).pack(anchor='w')

        # Max retry attempts
        ttk.Label(options_frame, text='Max Retry Attempts:').pack(anchor='w')
        self.max_retries = ttk.Entry(options_frame)
        self.max_retries.insert(0, '5')
        self.max_retries.pack(fill='x', pady=(0, 5))

        # Log frame
        log_frame = ttk.LabelFrame(self, text="Import Log", padding=10)
        log_frame.pack(fill='both', expand=True, padx=5, pady=5)

        # Log text with scrollbar
        log_scroll = ttk.Scrollbar(log_frame)
        log_scroll.pack(side='right', fill='y')
        
        self.log_text = tk.Text(log_frame, height=15, yscrollcommand=log_scroll.set)
        self.log_text.pack(fill='both', expand=True)
        
        log_scroll.config(command=self.log_text.yview)

        # Import button
        ttk.Button(self, text="Start Import", command=self.start_import).pack(pady=10)

    def browse_directory(self):
        directory = filedialog.askdirectory()
        if directory:
            self.dir_path.delete(0, tk.END)
            self.dir_path.insert(0, directory)

    def log_message(self, message):
        self.log_text.insert(tk.END, f"{datetime.now().strftime('%H:%M:%S')} - {message}\n")
        self.log_text.see(tk.END)
        self.update_idletasks()

    def has_foreign_key(self, sql_content):
        """
        Memeriksa apakah SQL content mengandung FOREIGN KEY constraint
        Args:
            sql_content (str): Isi file SQL yang akan diperiksa
        Returns:
            bool: True jika mengandung FOREIGN KEY, False jika tidak
        """
        return 'FOREIGN KEY' in sql_content.upper()

    def get_table_name(self, sql_content):
        # Ekstrak nama tabel dari CREATE TABLE statement
        match = re.search(r'CREATE TABLE `?(\w+)`?', sql_content)
        return match.group(1) if match else None

    def execute_sql_file(self, cursor, file_path):
        try:
            with open(file_path, 'r') as f:
                content = f.read()
            
            # Split file content berdasarkan DELIMITER
            statements = []
            current_delimiter = ';'
            current_statement = ''
            
            for line in content.split('\n'):
                # Skip komentar
                if line.startswith('--') or line.startswith('/*') or line.strip() == '':
                    continue
                    
                # Cek perubahan DELIMITER
                if line.strip().upper().startswith('DELIMITER'):
                    if current_statement:
                        statements.append(current_statement.strip())
                        current_statement = ''
                    current_delimiter = line.strip().split(' ')[1]
                    continue
                
                # Tambahkan line ke statement
                current_statement += ' ' + line
                
                # Cek apakah statement sudah lengkap
                if line.strip().endswith(current_delimiter):
                    # Hapus delimiter dari akhir statement
                    if current_delimiter != ';':
                        current_statement = current_statement.rsplit(current_delimiter, 1)[0]
                    else:
                        current_statement = current_statement.rstrip(';')
                    
                    statements.append(current_statement.strip())
                    current_statement = ''

            # Execute setiap statement
            for stmt in statements:
                if stmt:
                    try:
                        cursor.execute(stmt)
                        # Jika statement adalah INSERT, verifikasi jumlah rows
                        if stmt.upper().startswith('INSERT'):
                            table_name = stmt[stmt.upper().find('INTO')+5:stmt.find('(')].strip().replace('`','')
                            cursor.execute(f"SELECT COUNT(*) FROM `{table_name}`")
                            count = cursor.fetchone()[0]
                            if count == 0:
                                self.log_message(f"Peringatan: Tidak ada data di tabel {table_name} setelah INSERT")
                            else:
                                self.log_message(f"Berhasil insert {count} data ke tabel {table_name}")
                    except mysql.connector.Error as err:
                        raise Exception(f"Error pada statement: {stmt[:100]}... : {str(err)}")

            self.log_message(f"Berhasil mengeksekusi: {os.path.basename(file_path)}")
        except Exception as e:
            raise Exception(f"Error pada file {os.path.basename(file_path)}: {str(e)}")

    def import_files_from_directory(self, cursor, directory):
        if not os.path.exists(directory):
            return [], []  # Return empty lists for success and retry

        files = sorted([f for f in os.listdir(directory) if f.endswith('.sql')])
        success_list = []
        retry_list = []
        
        # Pertama, kategorikan file berdasarkan ada tidaknya foreign key
        for file in files:
            file_path = os.path.join(directory, file)
            with open(file_path, 'r') as f:
                content = f.read()
            
            if self.has_foreign_key(content):
                retry_list.append(file_path)
            else:
                success_list.append(file_path)

        # Import file tanpa foreign key terlebih dahulu
        for file_path in success_list:
            try:
                self.execute_sql_file(cursor, file_path)
            except mysql.connector.Error as err:
                self.log_message(f"Error pada file {os.path.basename(file_path)}: {err}")
                retry_list.append(file_path)

        return success_list, retry_list

    def set_foreign_key_checks(self, cursor, value):
        """
        Mengatur status foreign key checks
        Args:
            cursor: Database cursor
            value: 1 untuk mengaktifkan, 0 untuk menonaktifkan
        """
        try:
            cursor.execute(f"SET FOREIGN_KEY_CHECKS = {value}")
            status = "aktif" if value == 1 else "nonaktif"
            self.log_message(f"Foreign key checks diset ke {status}")
        except mysql.connector.Error as err:
            self.log_message(f"Error saat mengatur foreign key checks: {err}")

    def start_import(self):
        if not all([self.host.get(), self.username.get(), self.database.get(), self.dir_path.get()]):
            messagebox.showerror("Error", "Semua field harus diisi!")
            return

        try:
            # Clear log
            self.log_text.delete(1.0, tk.END)
            self.log_message("Memulai proses import...")

            # Connect to database
            conn = mysql.connector.connect(
                host=self.host.get(),
                user=self.username.get(),
                password=self.password.get(),
                database=self.database.get()
            )
            cursor = conn.cursor()

            # Nonaktifkan foreign key checks
            self.set_foreign_key_checks(cursor, 0)

            try:
                base_dir = self.dir_path.get()
                max_retries = int(self.max_retries.get())
                
                # Import routines first
                if self.import_routines.get():
                    routines_dir = os.path.join(base_dir, 'routines')
                    if os.path.exists(routines_dir):
                        self.log_message("Mengimport routines...")
                        success_list, retry_list = self.import_files_from_directory(cursor, routines_dir)

                # Import tables
                if self.import_tables.get():
                    tables_dir = os.path.join(base_dir, 'tables')
                    if os.path.exists(tables_dir):
                        self.log_message("Mengimport tables...")
                        success_list, retry_list = self.import_files_from_directory(cursor, tables_dir)
                        
                        # Tidak perlu retry karena foreign key checks dinonaktifkan
                        for file_path in retry_list:
                            try:
                                self.execute_sql_file(cursor, file_path)
                            except Exception as err:
                                self.log_message(f"Gagal: {os.path.basename(file_path)}: {err}")

                # Import views last
                if self.import_views.get():
                    views_dir = os.path.join(base_dir, 'views')
                    if os.path.exists(views_dir):
                        self.log_message("\nMengimport views...")
                        success_list, retry_list = self.import_files_from_directory(cursor, views_dir)

                conn.commit()

                # Aktifkan kembali foreign key checks
                self.set_foreign_key_checks(cursor, 1)

                # Verifikasi integritas data
                self.log_message("\nMemverifikasi integritas data...")
                try:
                    cursor.execute("CHECK TABLE users, activations, master_packages")
                    check_results = cursor.fetchall()
                    for result in check_results:
                        if result[3] != 'OK':
                            self.log_message(f"Peringatan: Tabel {result[0]} status: {result[3]}")
                except:
                    self.log_message("Tidak dapat melakukan verifikasi integritas data")

                self.log_message("\nImport selesai!")
                messagebox.showinfo("Success", "Proses import telah selesai!")

            except Exception as e:
                self.log_message(f"Error selama proses import: {str(e)}")
                messagebox.showerror("Error", f"Terjadi kesalahan: {str(e)}")
            finally:
                # Pastikan foreign key checks diaktifkan kembali
                self.set_foreign_key_checks(cursor, 1)
                cursor.close()
                conn.close()

        except mysql.connector.Error as err:
            self.log_message(f"Database error: {err}")
            messagebox.showerror("Error", f"Terjadi kesalahan database: {err}")
        except Exception as e:
            self.log_message(f"Error: {str(e)}")
            messagebox.showerror("Error", f"Terjadi kesalahan: {str(e)}")

if __name__ == '__main__':
    app = ImporterApp()
    app.create_widgets()
    app.mainloop()