plugins.js

class Plugin {
    constructor(workspace) {
        this.workspace = workspace;
        this.toolboxCategoryName = 'SQL';
        this.toolboxCategoryColor = '#3498db';
        this.blockTypes = [
            'sql_select', 'sql_insert', 'sql_update', 'sql_delete',
            'sql_json_to_sql', 'sql_json_dataset_to_sql', 'sql_custom_query'
        ];
        this.viewerId = 'sql-data-viewer';
        this.helpOverlayId = 'sql-help-overlay';
    }

    async onload() {
        if (typeof Blockly === 'undefined') {
            console.warn('SQL Generator Plugin: Blockly is not available.');
            return;
        }

        this.registerBlocks();
        this.addToolboxCategory();
        this.createDataViewer();
        this.setupBlockClickHandlers();
        console.log("SQL Generator Plugin v1.2.0 Loaded: JSON-SQL Integration Enabled");
    }

    async onunload() {
        this.removeToolboxCategory();
        this.unregisterBlocks();
        this.removeDataViewer();
        this.removeHelpOverlay();
        console.log("SQL Generator Plugin Unloaded");
    }

    registerBlocks() {
        if (!Blockly?.Blocks) return;

        const self = this;

        // --- SQL 基本ブロック ---

        // SELECT ブロック
        Blockly.Blocks['sql_select'] = {
            init: function() {
                this.appendValueInput("COLUMNS").setCheck("String").appendField("SELECT");
                this.appendValueInput("TABLE").setCheck("String").appendField("FROM");
                this.appendValueInput("WHERE").setCheck("String").appendField("WHERE");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(160);
                this.setTooltip("SQLのSELECT文を作成します。");
            }
        };

        // INSERT ブロック
        Blockly.Blocks['sql_insert'] = {
            init: function() {
                this.appendValueInput("TABLE").setCheck("String").appendField("INSERT INTO");
                this.appendValueInput("COLUMNS").setCheck("String").appendField("COLUMNS");
                this.appendValueInput("VALUES").setCheck("String").appendField("VALUES");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(160);
                this.setTooltip("SQLのINSERT文を作成します。");
            }
        };

        // UPDATE ブロック
        Blockly.Blocks['sql_update'] = {
            init: function() {
                this.appendValueInput("TABLE").setCheck("String").appendField("UPDATE");
                this.appendValueInput("SET").setCheck("String").appendField("SET");
                this.appendValueInput("WHERE").setCheck("String").appendField("WHERE");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(160);
                this.setTooltip("SQLのUPDATE文を作成します。");
            }
        };

        // DELETE ブロック
        Blockly.Blocks['sql_delete'] = {
            init: function() {
                this.appendValueInput("TABLE").setCheck("String").appendField("DELETE FROM");
                this.appendValueInput("WHERE").setCheck("String").appendField("WHERE");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(160);
                this.setTooltip("SQLのDELETE文を作成します。");
            }
        };

        // --- 連携・移行支援ブロック ---

        // JSONデータをSQL(INSERT)に変換
        Blockly.Blocks['sql_json_to_sql'] = {
            init: function() {
                this.appendValueInput("JSON").setCheck(null).appendField("JSONデータ");
                this.appendValueInput("TABLE").setCheck("String").appendField("をテーブル");
                this.appendDummyInput().appendField("用のINSERT文に変換");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(210);
                this.setTooltip("JSONオブジェクトをSQLのINSERT文に変換します。他ツールへの移行に便利です。");
            }
        };

        // JSONデータセットをSQL(INSERT)に変換
        // FieldJsonDatasetDropdownが利用可能な場合はそれを使用
        Blockly.Blocks['sql_json_dataset_to_sql'] = {
            init: function() {
                const dropdown = (typeof FieldJsonDatasetDropdown !== 'undefined') 
                    ? new FieldJsonDatasetDropdown() 
                    : new Blockly.FieldTextInput('dataset_name');
                
                this.appendDummyInput()
                    .appendField("データセット")
                    .appendField(dropdown, 'DATASET');
                this.appendValueInput("TABLE").setCheck("String").appendField("をテーブル");
                this.appendDummyInput().appendField("用のINSERT文に変換");
                this.setInputsInline(true);
                this.setOutput(true, "String");
                this.setColour(210);
                this.setTooltip("EDBPのデータセット全体をSQLのINSERT文に変換します。");
            }
        };

        // 自由記述SQL
        Blockly.Blocks['sql_custom_query'] = {
            init: function() {
                this.appendDummyInput()
                    .appendField("SQL自由記述:")
                    .appendField(new Blockly.FieldTextInput("SELECT * FROM table"), "QUERY");
                this.setOutput(true, "String");
                this.setColour(160);
                this.setTooltip("任意のSQLクエリを記述します。");
            }
        };

        // --- ジェネレータ ---

        if (Blockly.Python) {
            const generators = {
                'sql_select': (block) => {
                    const columns = Blockly.Python.valueToCode(block, 'COLUMNS', Blockly.Python.ORDER_NONE) || '"*"';
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '""';
                    const where = Blockly.Python.valueToCode(block, 'WHERE', Blockly.Python.ORDER_NONE);
                    let code = `f"SELECT {${columns}} FROM {${table}}"`;
                    if (where) code = `f"SELECT {${columns}} FROM {${table}} WHERE {${where}}"`;
                    return [code, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_insert': (block) => {
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '""';
                    const columns = Blockly.Python.valueToCode(block, 'COLUMNS', Blockly.Python.ORDER_NONE) || '""';
                    const values = Blockly.Python.valueToCode(block, 'VALUES', Blockly.Python.ORDER_NONE) || '""';
                    return [`f"INSERT INTO {${table}} ({${columns}}) VALUES ({${values}})"`, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_update': (block) => {
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '""';
                    const set = Blockly.Python.valueToCode(block, 'SET', Blockly.Python.ORDER_NONE) || '""';
                    const where = Blockly.Python.valueToCode(block, 'WHERE', Blockly.Python.ORDER_NONE) || '""';
                    return [`f"UPDATE {${table}} SET {${set}} WHERE {${where}}"`, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_delete': (block) => {
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '""';
                    const where = Blockly.Python.valueToCode(block, 'WHERE', Blockly.Python.ORDER_NONE) || '""';
                    return [`f"DELETE FROM {${table}} WHERE {${where}}"`, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_json_to_sql': (block) => {
                    const jsonData = Blockly.Python.valueToCode(block, 'JSON', Blockly.Python.ORDER_NONE) || '{}';
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '"table_name"';
                    // Python側での変換ロジック
                    const convertFunc = `(lambda d, t: f"INSERT INTO {t} ({', '.join(d.keys())}) VALUES ({', '.join([repr(v) for v in d.values()])})")`;
                    return [`${convertFunc}(${jsonData}, ${table})`, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_json_dataset_to_sql': (block) => {
                    const datasetName = block.getFieldValue('DATASET');
                    const table = Blockly.Python.valueToCode(block, 'TABLE', Blockly.Python.ORDER_NONE) || '"table_name"';
                    
                    // blocks.js の getJsonDatasetLiteral 相当のロジックが必要だが
                    // 実行時には globals().get('_edbb_json_cache') から取得できる
                    const cacheCode = `((globals().get('_edbb_json_cache')) if ('_edbb_json_cache' in globals()) else {})`;
                    const datasetCode = `${cacheCode}.get(${JSON.stringify(datasetName)}, {})`;
                    
                    const convertFunc = `(lambda d, t: f"INSERT INTO {t} ({', '.join(d.keys())}) VALUES ({', '.join([repr(v) for v in d.values()])})")`;
                    return [`${convertFunc}(${datasetCode}, ${table})`, Blockly.Python.ORDER_ATOMIC];
                },
                'sql_custom_query': (block) => {
                    const query = block.getFieldValue('QUERY');
                    return [`${JSON.stringify(query)}`, Blockly.Python.ORDER_ATOMIC];
                }
            };
            Object.keys(generators).forEach(key => {
                Blockly.Python.forBlock[key] = generators[key];
            });
        }
    }

    unregisterBlocks() {
        if (typeof Blockly === 'undefined') return;
        this.blockTypes.forEach(type => {
            if (Blockly.Blocks && Blockly.Blocks[type]) delete Blockly.Blocks[type];
            if (Blockly.Python?.forBlock && Blockly.Python.forBlock[type]) delete Blockly.Python.forBlock[type];
        });
    }

    addToolboxCategory() {
        const toolbox = document.getElementById('toolbox');
        if (!toolbox) return;
        let category = toolbox.querySelector(`category[name="${this.toolboxCategoryName}"]`);
        if (!category) {
            category = document.createElement('category');
            category.setAttribute('name', this.toolboxCategoryName);
            category.setAttribute('data-icon', '📊');
            category.setAttribute('colour', this.toolboxCategoryColor);
            toolbox.appendChild(category);
        }
        this.blockTypes.forEach(type => {
            if (!category.querySelector(`block[type="${type}"]`)) {
                const block = document.createElement('block');
                block.setAttribute('type', type);
                category.appendChild(block);
            }
        });
        if (this.workspace?.updateToolbox) this.workspace.updateToolbox(toolbox);
    }

    removeToolboxCategory() {
        const toolbox = document.getElementById('toolbox');
        if (!toolbox) return;
        const category = toolbox.querySelector(`category[name="${this.toolboxCategoryName}"]`);
        if (category) {
            category.remove();
            if (this.workspace?.updateToolbox) this.workspace.updateToolbox(toolbox);
        }
    }

    createDataViewer() {
        if (document.getElementById(this.viewerId)) return;
        const viewer = document.createElement('div');
        viewer.id = this.viewerId;
        viewer.style.cssText = `
            position: fixed; bottom: 20px; right: 20px; width: 320px; height: 240px;
            background: #fff; border: 2px solid #3498db; border-radius: 8px;
            z-index: 1000; display: flex; flex-direction: column; box-shadow: 0 4px 12px rgba(0,0,0,0.15);
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; overflow: hidden;
            transition: all 0.3s ease;
        `;
        viewer.innerHTML = `
            <div style="background: #3498db; color: white; padding: 10px; font-weight: bold; display: flex; justify-content: space-between; align-items: center; cursor: move;">
                <div style="display: flex; align-items: center; gap: 6px;">
                    <span style="font-size: 16px;">📊</span>
                    <span>SQL Generator Viewer</span>
                </div>
                <div style="display: flex; gap: 8px;">
                    <button id="sql-viewer-copy" title="全コピー" style="background:rgba(255,255,255,0.2); border:none; color:white; cursor:pointer; border-radius:4px; padding: 2px 6px; font-size: 10px;">COPY</button>
                    <button onclick="this.parentElement.parentElement.parentElement.style.display='none'" style="background:none; border:none; color:white; cursor:pointer; font-size: 18px;">×</button>
                </div>
            </div>
            <div id="sql-viewer-content" style="padding: 12px; font-size: 12px; overflow-y: auto; flex-grow: 1; background: #f8f9fa;">
                <p style="color: #666; text-align: center; margin-top: 20px;">SQLブロックを配置すると<br>ここにクエリが表示されます。</p>
            </div>
            <div style="padding: 6px 10px; font-size: 10px; background: #eee; color: #777; border-top: 1px solid #ddd; display: flex; justify-content: space-between;">
                <span>EDBP SQL Integration</span>
                <span id="sql-viewer-count">0 queries</span>
            </div>
        `;
        document.body.appendChild(viewer);

        document.getElementById('sql-viewer-copy').onclick = () => {
            const queries = Array.from(document.querySelectorAll('#sql-viewer-content code')).map(c => c.textContent).join('\n\n');
            if (queries) {
                navigator.clipboard.writeText(queries).then(() => {
                    const btn = document.getElementById('sql-viewer-copy');
                    const oldText = btn.textContent;
                    btn.textContent = 'COPIED!';
                    setTimeout(() => btn.textContent = oldText, 2000);
                });
            }
        };

        // ワークスペースの変更を監視してSQLを更新
        this.workspace.addChangeListener((e) => {
            if (e.type === Blockly.Events.BLOCK_CHANGE || e.type === Blockly.Events.BLOCK_MOVE || e.type === Blockly.Events.BLOCK_CREATE || e.type === Blockly.Events.BLOCK_DELETE) {
                this.updateViewerContent();
            }
        });
        
        // 初回更新
        this.updateViewerContent();
    }

    updateViewerContent() {
        const content = document.getElementById('sql-viewer-content');
        const countLabel = document.getElementById('sql-viewer-count');
        if (!content) return;
        
        const blocks = this.workspace.getAllBlocks(false).filter(b => this.blockTypes.includes(b.type));
        if (countLabel) countLabel.textContent = `${blocks.length} queries`;

        if (blocks.length === 0) {
            content.innerHTML = '<p style="color: #666; text-align: center; margin-top: 20px;">SQLブロックが配置されていません。</p>';
            return;
        }

        let html = '<div style="display: flex; flex-direction: column; gap: 10px;">';
        blocks.forEach(block => {
            try {
                // Pythonジェネレータを使用して擬似的にSQLを取得
                // 注: ブラウザ上では実際のPython実行は行われないため、
                // 文字列リテラルやf-stringの形式をパースして表示用に整形する
                let code = Blockly.Python.blockToCode(block);
                let displaySql = '';
                
                if (Array.isArray(code)) code = code[0];
                
                if (code) {
                    // 表示用に整形
                    displaySql = code
                        .replace(/^f"|"$/g, '')      // f-stringのクォート除去
                        .replace(/^"|"$/g, '')       // 通常文字列のクォート除去
                        .replace(/\{|\}/g, '')       // 変数展開のブラケット除去
                        .replace(/repr\((.*?)\)/g, '$1') // repr()の除去
                        .replace(/\(lambda.*?\)\((.*)\)/, 'INSERT INTO ...'); // lambdaの簡易表示
                    
                    if (block.type === 'sql_json_to_sql' || block.type === 'sql_json_dataset_to_sql') {
                        displaySql = "INSERT INTO [Table] ([Columns...]) VALUES ([Values...])";
                    }

                    html += `<div style="border-left: 3px solid #3498db; background: white; padding: 8px; border-radius: 0 4px 4px 0; box-shadow: 0 1px 3px rgba(0,0,0,0.05);">
                        <div style="font-size: 10px; color: #3498db; font-weight: bold; margin-bottom: 4px; text-transform: uppercase;">${block.type.replace('sql_', '').replace(/_/g, ' ')}</div>
                        <code style="background: #f1f3f5; padding: 6px; display: block; white-space: pre-wrap; word-break: break-all; border-radius: 3px; border: 1px solid #e9ecef; font-family: monospace; color: #333;">${displaySql}</code>
                    </div>`;
                }
            } catch (e) {
                console.error("SQL Viewer Error:", e);
            }
        });
        html += '</div>';
        content.innerHTML = html;
    }

    removeDataViewer() {
        const viewer = document.getElementById(this.viewerId);
        if (viewer) viewer.remove();
    }

    setupBlockClickHandlers() {
        this.workspace.addChangeListener((e) => {
            if (e.type === Blockly.Events.CLICK) {
                const block = this.workspace.getBlockById(e.blockId);
                if (block && this.blockTypes.includes(block.type)) {
                    this.showHelp(block.type);
                }
            }
        });
    }

    showHelp(type) {
        this.removeHelpOverlay();
        const helpText = {
            'sql_select': '【SELECT】<br>データを取得します。<br>例: SELECT <b>"name, email"</b> FROM <b>"users"</b> WHERE <b>"id = 1"</b>',
            'sql_insert': '【INSERT】<br>データを追加します。<br>例: INSERT INTO <b>"users"</b> (<b>"name"</b>) VALUES (<b>"\'Taro\'"</b>)',
            'sql_update': '【UPDATE】<br>データを更新します。<br>例: UPDATE <b>"users"</b> SET <b>"age = 21"</b> WHERE <b>"id = 1"</b>',
            'sql_delete': '【DELETE】<br>データを削除します。<br>例: DELETE FROM <b>"users"</b> WHERE <b>"status = \'old\'"</b>',
            'sql_json_to_sql': '【JSON → SQL】<br>変数のJSONデータをSQLのINSERT文に変換します。他ツールへのデータ移行に最適です。',
            'sql_json_dataset_to_sql': '【データセット → SQL】<br>EDBPのデータセット全体をSQL形式で書き出します。PhpMyAdmin等への移行に使えます。',
            'sql_custom_query': '【自由記述】<br>複雑なクエリを直接記述します。'
        };

        const overlay = document.createElement('div');
        overlay.id = this.helpOverlayId;
        overlay.style.cssText = `
            position: fixed; top: 50%; left: 50%; transform: translate(-50%, -50%);
            background: rgba(255,255,255,0.98); border: 2px solid #3498db; padding: 25px;
            border-radius: 16px; z-index: 2000; max-width: 420px; box-shadow: 0 15px 40px rgba(0,0,0,0.25);
            font-family: sans-serif; text-align: center; animation: fadeIn 0.2s ease;
        `;
        overlay.innerHTML = `
            <style>@keyframes fadeIn { from { opacity: 0; transform: translate(-50%, -45%); } to { opacity: 1; transform: translate(-50%, -50%); } }</style>
            <div style="font-size: 24px; margin-bottom: 10px;">💡</div>
            <h3 style="margin: 0 0 15px 0; color: #3498db; border-bottom: 1px solid #eee; padding-bottom: 10px;">SQLブロックの使い方</h3>
            <p style="line-height: 1.8; color: #444; font-size: 14px; text-align: left;">${helpText[type]}</p>
            <button id="close-sql-help" style="background: #3498db; color: white; border: none; padding: 10px 25px; border-radius: 8px; cursor: pointer; margin-top: 20px; font-weight: bold; transition: background 0.2s;">閉じる</button>
        `;
        document.body.appendChild(overlay);
        document.getElementById('close-sql-help').onclick = () => this.removeHelpOverlay();
        document.getElementById('close-sql-help').onmouseover = (e) => e.target.style.background = '#2980b9';
        document.getElementById('close-sql-help').onmouseout = (e) => e.target.style.background = '#3498db';
        
        setTimeout(() => this.removeHelpOverlay(), 10000);
    }

    removeHelpOverlay() {
        const overlay = document.getElementById(this.helpOverlayId);
        if (overlay) overlay.remove();
    }
}

window.SQLGeneratorPlugin = Plugin;