import SwiftUI
import AppKit
import UniformTypeIdentifiers

// Pure conversion engine; can be exercised independently of the UI.
enum LaravelMigrationGenerator {
    static func generate(_ ddl: String) throws -> String {
        guard let match = ddl.range(of: #"(?is)CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([\w]+)`?\s*\("#, options: .regularExpression) else {
            throw NSError(domain: "DBSyn.Migration", code: 1, userInfo: [NSLocalizedDescriptionKey: "No CREATE TABLE definition found. Supply MySQL CREATE TABLE SQL."])
        }
        let prefix = String(ddl[match]); let name = prefix.replacingOccurrences(of: #"(?is)^.*?CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?"#, with: "", options: .regularExpression).replacingOccurrences(of: "(", with: "").replacingOccurrences(of: "`", with: "").trimmingCharacters(in: .whitespacesAndNewlines)
        let start = match.upperBound
        var depth = 1; var end = start; var quote: Character? = nil
        for index in ddl[start...].indices {
            let c = ddl[index]
            if let q = quote { if c == q { quote = nil }; continue }
            if c == "'" || c == "\"" || c == "`" { quote = c; continue }
            if c == "(" { depth += 1 }; if c == ")" { depth -= 1; if depth == 0 { end = index; break } }
        }
        guard depth == 0 else { throw NSError(domain: "DBSyn.Migration", code: 2, userInfo: [NSLocalizedDescriptionKey: "Unbalanced CREATE TABLE parentheses."]) }
        let body = String(ddl[start..<end]); var lines: [String] = []
        let definitions = splitColumns(body)
        let primaryKeys = definitions.filter { $0.trimmingCharacters(in: .whitespacesAndNewlines).uppercased().hasPrefix("PRIMARY KEY") }
            .flatMap { backtickNames($0) }
        let autoPrimary = definitions.contains { $0.uppercased().contains("AUTO_INCREMENT") } && primaryKeys.count == 1
        for definition in definitions {
            let item = definition.trimmingCharacters(in: .whitespacesAndNewlines)
            let upper = item.uppercased()
            if upper.hasPrefix("PRIMARY KEY") {
                if autoPrimary { continue }
                let columns = backtickNames(item)
                if columns.count == 1 {
                    lines.append("            $table->primary('\(columns[0])');")
                } else if !columns.isEmpty {
                    let quotedColumns = columns.map { "'\($0)'" }.joined(separator: ", ")
                    lines.append("            $table->primary([\(quotedColumns)]);")
                }
                continue
            }
            if upper.hasPrefix("UNIQUE KEY") || upper.hasPrefix("UNIQUE INDEX") || upper.hasPrefix("KEY ") || upper.hasPrefix("INDEX ") {
                let columns = backtickNames(item)
                if columns.count > 1 {
                    let columnNames = Array(columns.dropFirst())
                    let quotedColumns = columnNames.map { "'\($0)'" }.joined(separator: ", ")
                    let indexMethod = upper.hasPrefix("UNIQUE") ? "unique" : "index"
                    lines.append("            $table->\(indexMethod)([\(quotedColumns)]);")
                }
                continue
            }
            if upper.hasPrefix("CONSTRAINT") || upper.hasPrefix("FOREIGN KEY") || upper.hasPrefix("CHECK") { lines.append("            // Review manually: " + item.replacingOccurrences(of: "\n", with: " ")); continue }
            guard let column = item.range(of: #"^`?([A-Za-z_][\w]*)`?\s+"#, options: .regularExpression) else { continue }
            let col = String(item[column]).trimmingCharacters(in: .whitespaces).replacingOccurrences(of: "`", with: "")
            let spec = String(item[column.upperBound...]); let type = spec.lowercased()
            let method: String
            if type.hasPrefix("bigint") { method = "bigInteger" }
            else if type.hasPrefix("tinyint(1)") || type.hasPrefix("boolean") { method = "boolean" }
            else if type.hasPrefix("tinyint") { method = "tinyInteger" }
            else if type.hasPrefix("smallint") { method = "smallInteger" }
            else if type.hasPrefix("mediumint") { method = "mediumInteger" }
            else if type.hasPrefix("int") || type.hasPrefix("integer") { method = "integer" }
            else if type.hasPrefix("varchar") { method = "string" }
            else if type.hasPrefix("char") { method = "char" }
            else if type.hasPrefix("longtext") { method = "longText" }
            else if type.hasPrefix("mediumtext") { method = "mediumText" }
            else if type.hasPrefix("text") { method = "text" }
            else if type.hasPrefix("datetime") { method = "dateTime" }
            else if type.hasPrefix("timestamp") { method = "timestamp" }
            else if type.hasPrefix("date") { method = "date" }
            else if type.hasPrefix("time") { method = "time" }
            else if type.hasPrefix("json") { method = "json" }
            else if type.hasPrefix("double") { method = "double" }
            else if type.hasPrefix("float") { method = "float" }
            else if type.hasPrefix("decimal") { method = "decimal" }
            else if type.hasPrefix("blob") { method = "binary" }
            else { method = "string" }
            var args = "'\(col)'"
            if method == "string" || method == "char", let range = type.range(of: #"\(\s*\d+\s*\)"#, options: .regularExpression) { args += ", " + type[range].filter(\.isNumber) }
            if method == "decimal", let range = type.range(of: #"\(\s*\d+\s*,\s*\d+\s*\)"#, options: .regularExpression) { args += ", " + type[range].dropFirst().dropLast() }
            if upper.contains("AUTO_INCREMENT") && primaryKeys == [col] && method == "bigInteger" {
                lines.append("            $table->id('\(col)');")
                continue
            }
            var expr = "            $table->\(method)(\(args))"
            if upper.contains("UNSIGNED") { expr += "->unsigned()" }
            if !upper.contains("NOT NULL") { expr += "->nullable()" }
            if upper.contains("AUTO_INCREMENT") { expr += "->autoIncrement()" }
            lines.append(expr + ";")
        }
        let safeName = name.replacingOccurrences(of: "'", with: "\\'")
        return """
        <?php

        use Illuminate\\Database\\Migrations\\Migration;
        use Illuminate\\Database\\Schema\\Blueprint;
        use Illuminate\\Support\\Facades\\Schema;

        return new class extends Migration {
            public function up(): void
            {
                Schema::create('\(safeName)', function (Blueprint $table) {
        \(lines.joined(separator: "\n"))
                });
            }

            public function down(): void
            {
                Schema::dropIfExists('\(safeName)');
            }
        };
        """
    }
    private static func backtickNames(_ text: String) -> [String] {
        guard let regex = try? NSRegularExpression(pattern: "`([^`]+)`") else { return [] }
        let ns = text as NSString
        return regex.matches(in: text, range: NSRange(location: 0, length: ns.length)).map { ns.substring(with: $0.range(at: 1)) }
    }
    private static func splitColumns(_ text: String) -> [String] {
        var parts: [String] = []; var current = ""; var depth = 0; var quote: Character? = nil
        for c in text {
            if let q = quote { current.append(c); if c == q { quote = nil }; continue }
            if c == "'" || c == "\"" || c == "`" { quote = c; current.append(c); continue }
            if c == "(" { depth += 1 }; if c == ")" { depth -= 1 }
            if c == "," && depth == 0 { parts.append(current); current = "" } else { current.append(c) }
        }
        if !current.isEmpty { parts.append(current) }; return parts
    }
}

struct LaravelMigrationView: View {
    @ObservedObject var model: DatabaseModel
    @AppStorage("preferences.migrationFilenamePrefix") private var prefix = "create"
    @State private var sourceSQL = ""
    @State private var generated = ""
    @State private var errorText = ""
    @State private var tableName = ""
    @State private var chosenDatabase = ""
    @State private var isLoading = false
    @State private var tableSearch = ""
    @State private var statusText = ""
    var onClose: () -> Void = {}
    var body: some View {
        VStack(alignment: .leading, spacing: 14) {
            HStack {
                Image(systemName: "curlybraces.square").font(.title2).foregroundStyle(.tint)
                VStack(alignment: .leading) {
                    Text("Laravel Migration Generator").font(.title2.bold())
                    Text("Convert a MySQL table or CREATE TABLE SQL into a Laravel migration.").foregroundStyle(.secondary)
                }
                Spacer()
                Button(action: onClose) { Image(systemName: "xmark.circle.fill").font(.title2).foregroundStyle(.secondary) }
                    .buttonStyle(.plain).help("Close Migration Generator")
            }
            VStack(alignment: .leading, spacing: 10) {
                HStack(spacing: 12) {
                    Text("Database").frame(width: 80, alignment: .leading)
                    Picker("Database", selection: $chosenDatabase) {
                        Text("Select database…").tag("")
                        ForEach(databaseOptions, id: \.self) { name in
                            Text(name).tag(name)
                        }
                    }
                    .labelsHidden()
                    .frame(maxWidth: 330)
                    Button("Use Current Database") {
                        chosenDatabase = model.completionDatabase
                    }
                    .disabled(model.completionDatabase.isEmpty)
                    Spacer()
                }
                HStack(spacing: 12) {
                    Text("Table").frame(width: 80, alignment: .leading)
                    TextField("Enter a table name", text: $tableName)
                        .frame(maxWidth: 330)
                    if !availableTables.isEmpty {
                        Menu {
                            TextField("Search tables", text: $tableSearch)
                            Divider()
                            ForEach(filteredTables.prefix(100), id: \.self) { name in
                                Button(name) { tableName = name }
                            }
                            if filteredTables.isEmpty { Text("No matching tables") }
                        } label: {
                            Label("Browse Tables (\(availableTables.count))", systemImage: "magnifyingglass")
                        }
                        .frame(maxWidth: 230)
                    }
                    Button(isLoading ? "Loading…" : "Load Table") { loadTable() }
                        .disabled(tableName.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty || chosenDatabase.isEmpty || model.selected == nil || isLoading)
                    Button("Open SQL File…") { openFile() }
                    Spacer()
                }
                if chosenDatabase.isEmpty {
                    Text("Choose a database before loading a table. SQL files can be opened without a connection.")
                        .font(.caption).foregroundStyle(.secondary)
                }
            }
            HSplitView {
                VStack(alignment: .leading) {
                    Text("MySQL CREATE TABLE").font(.headline)
                    TextEditor(text: $sourceSQL).font(.system(.body, design: .monospaced)).frame(minWidth: 330)
                }
                VStack(alignment: .leading) {
                    Text("Laravel Migration · PHP").font(.headline)
                    TextEditor(text: $generated).font(.system(.body, design: .monospaced)).frame(minWidth: 330)
                }
            }
            if !errorText.isEmpty { Text(errorText).foregroundStyle(.red).font(.callout) }
            if !statusText.isEmpty { Text(statusText).foregroundStyle(.green).font(.callout) }
            HStack {
                Text("Review indexes, foreign keys, defaults and unsupported MySQL types before running a migration.").font(.caption).foregroundStyle(.secondary)
                Spacer()
                Button("Generate / Refresh") { generate() }.buttonStyle(.borderedProminent)
                    .disabled(sourceSQL.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty)
                Button("Copy PHP") { NSPasteboard.general.clearContents(); NSPasteboard.general.setString(generated, forType: .string) }.disabled(generated.isEmpty)
                Button("Save Migration…") { save() }.disabled(generated.isEmpty)
            }
        }
        .padding(22)
        .frame(minWidth: 980, minHeight: 650)
        .onAppear {
            if chosenDatabase.isEmpty {
                chosenDatabase = model.completionDatabase
            }
        }
        .onChange(of: chosenDatabase) { _, _ in tableName = ""; tableSearch = "" }
        .onChange(of: sourceSQL) { _, _ in statusText = "Source changed — select Generate / Refresh to update PHP." }
    }
    private var databaseOptions: [String] {
        Array(Set(model.databases + model.schemaTables.keys + [model.completionDatabase].filter { !$0.isEmpty })).sorted()
    }
    private var availableTables: [String] {
        model.schemaTables[chosenDatabase] ?? []
    }
    private var filteredTables: [String] {
        let query = tableSearch.trimmingCharacters(in: .whitespacesAndNewlines)
        return query.isEmpty ? availableTables : availableTables.filter { $0.localizedCaseInsensitiveContains(query) }
    }
    private func generate() {
        do {
            generated = try LaravelMigrationGenerator.generate(sourceSQL)
            errorText = ""
            statusText = "Migration generated — review the PHP, then Copy or Save."
        } catch {
            errorText = error.localizedDescription
            statusText = ""
        }
    }
    private func openFile() {
        let panel = NSOpenPanel(); panel.allowedContentTypes = [UTType(filenameExtension: "sql") ?? .plainText, .plainText]; panel.allowsMultipleSelection = false
        if panel.runModal() == .OK, let url = panel.url {
            do { sourceSQL = try String(contentsOf: url, encoding: .utf8); generate() }
            catch { errorText = error.localizedDescription }
        }
    }
    private func loadTable() {
        guard let profile = model.selected else { return }
        isLoading = true
        let db = chosenDatabase.trimmingCharacters(in: .whitespacesAndNewlines)
        guard !db.isEmpty else {
            errorText = "Select a database before loading a table."
            isLoading = false
            return
        }
        let escaped = tableName.trimmingCharacters(in: .whitespacesAndNewlines).replacingOccurrences(of: "`", with: "``")
        let escapedDB = db.replacingOccurrences(of: "`", with: "``")
        errorText = ""
        Task {
            do {
                let result = try await NativeMySQLRunner.run(profile: profile, database: db, password: PasswordStore.read(profile.id), sql: "SHOW CREATE TABLE `\(escapedDB)`.`\(escaped)`")
                guard let ddl = result.rows.first?.last, !ddl.isEmpty else { throw NSError(domain: "DBSyn.Migration", code: 3, userInfo: [NSLocalizedDescriptionKey: "No table definition returned."]) }
                sourceSQL = ddl; generate()
            } catch { errorText = "Could not load \(db).\(tableName): \(error.localizedDescription)" }
            isLoading = false
        }
    }
    private func save() {
        let panel = NSSavePanel(); panel.allowedContentTypes = [UTType(filenameExtension: "php") ?? .plainText]
        let name = tableName.isEmpty ? "table" : tableName.lowercased()
        let timestamp = DateFormatter()
        timestamp.dateFormat = "yyyy_MM_dd_HHmmss"
        let useTimestamps = UserDefaults.standard.object(forKey: "preferences.migrationUseTimestamps") as? Bool ?? true
        let datePrefix = useTimestamps ? timestamp.string(from: Date()) + "_" : ""
        panel.nameFieldStringValue = "\(datePrefix)\(prefix)_\(name)_table.php"
        let folder = UserDefaults.standard.string(forKey: "preferences.migrationOutputDirectory") ?? ""
        if !folder.isEmpty { panel.directoryURL = URL(fileURLWithPath: (folder as NSString).expandingTildeInPath, isDirectory: true) }
        if panel.runModal() == .OK, let url = panel.url {
            do { try generated.write(to: url, atomically: true, encoding: .utf8); errorText = ""; statusText = "Saved: \(url.lastPathComponent)" }
            catch { errorText = error.localizedDescription }
        }
    }
}