import {
  DB_SQL_INITIAL_TEXT,
  EditorLanguage,
  FormComponentType,
  FormItem,
  InputDataType,
  Plugin,
  PluginResponseType,
  PluginType
} from '../../types/index.js';
import { PARAMETERIZED_SQL_DESCRIPTION, PUBLIC_INTEGRATIONS_LOGO_URL, DOCS_BASE_URL } from './constants.js';
import { CONNECTION_METHODS_AND_DISPLAY_NAMES, makeDropdownItem } from './shared/db.js';
import { SqlOperations } from './shared/index.js';

const MicrosoftSQLPluginVersions = {
  V1: '0.0.1',
  V2: '0.0.2',
  V6: '0.0.6',
  V7: '0.0.7',
  V8: '0.0.8',
  V9: '0.0.9',
  V10: '0.0.10',
  V11: '0.0.11'
};

const BASE_HOST = {
  label: 'Host',
  name: 'endpoint.host',
  componentType: FormComponentType.INPUT_TEXT,
  placeholder: 'example.database.windows.net',
  rules: [{ required: true, message: 'Host is required' }]
};

const BASE_PORT = {
  label: 'Port',
  name: 'endpoint.port',
  componentType: FormComponentType.INPUT_TEXT,
  initialValue: '1433',
  rules: [{ required: true, message: 'Port is required' }]
};

const BASE_DATABASE_NAME = {
  label: 'Database name',
  name: 'authentication.custom.databaseName.value',
  componentType: FormComponentType.INPUT_TEXT
};

const BASE_DATABASE_USERNAME = {
  label: 'Database username',
  name: 'authentication.username',
  componentType: FormComponentType.INPUT_TEXT
};

const BASE_DATABASE_PASSWORD = {
  label: 'Database password',
  name: 'authentication.password',
  componentType: FormComponentType.INPUT_TEXT,
  dataType: InputDataType.PASSWORD
};

const BASE_ENABLE_SSL = {
  label: 'Enable SSL',
  name: 'connection.useSsl',
  initialValue: 'checked',
  componentType: FormComponentType.CHECKBOX
};

export const MicrosoftSQLPlugin: Plugin = {
  id: 'mssql',
  name: 'Microsoft SQL Server',
  moduleName: 'MicrosoftSQLPlugin',
  modulePath: 'plugins/mssql/MicrosoftSQLPlugin',
  iconLocation: `${PUBLIC_INTEGRATIONS_LOGO_URL}/mssql.png`,
  docsUrl: `${DOCS_BASE_URL}/mssql`,
  type: PluginType.DB,
  responseType: PluginResponseType.TABLE,
  hasRawRequest: true,
  hasMetadata: true,
  rawRequestName: 'Executed SQL',
  datasourceTemplate: {
    sections: [
      {
        name: 'main',
        items: [
          {
            label: 'Display name',
            name: 'name',
            startVersion: MicrosoftSQLPluginVersions.V1,
            componentType: FormComponentType.INPUT_TEXT,
            placeholder: 'ProdDB',
            rules: [{ required: true, message: 'Display name is required' }]
          },
          {
            label: 'Connection method',
            name: 'connectionType',
            startVersion: MicrosoftSQLPluginVersions.V10,
            componentType: FormComponentType.DROPDOWN,
            initialValue: 'fields',
            rules: [{ required: true }],
            options: Object.entries(CONNECTION_METHODS_AND_DISPLAY_NAMES).map(([value, displayName]) =>
              makeDropdownItem(value, displayName)
            )
          },
          {
            label: 'URI',
            name: 'connectionUrl',
            startVersion: MicrosoftSQLPluginVersions.V10,
            componentType: FormComponentType.INPUT_TEXT,
            dataType: InputDataType.PASSWORD, // since this can sometimes contain a password
            placeholder: `Server=[:serverName],[:port];Database=[:database];User Id=[user];Password=[:password];Encrypt=[true/false]`,
            tooltip: {
              markdownText: '[Read the docs](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax)'
            },
            rules: [{ required: true, message: 'URI is required' }],
            display: {
              show: {
                connectionType: ['url']
              }
            }
          },
          // HOST
          { ...BASE_HOST, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_HOST,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem,
          // PORT
          { ...BASE_PORT, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_PORT,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem,
          // DATABASE NAME
          { ...BASE_DATABASE_NAME, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_DATABASE_NAME,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem,
          // DATABASE USERNAME
          { ...BASE_DATABASE_USERNAME, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_DATABASE_USERNAME,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem,
          // DATABASE PASSWORD
          { ...BASE_DATABASE_PASSWORD, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_DATABASE_PASSWORD,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem,
          // ENABLE SSL
          { ...BASE_ENABLE_SSL, startVersion: MicrosoftSQLPluginVersions.V1, endVersion: MicrosoftSQLPluginVersions.V9 } as FormItem,
          {
            ...BASE_ENABLE_SSL,
            startVersion: MicrosoftSQLPluginVersions.V10,
            display: {
              show: {
                connectionType: ['fields']
              }
            }
          } as FormItem
        ]
      }
    ]
  },
  actionTemplate: {
    sections: [
      {
        name: 'main',
        items: [
          {
            label: 'Operation',
            name: 'operation',
            startVersion: MicrosoftSQLPluginVersions.V7,
            endVersion: MicrosoftSQLPluginVersions.V8,
            componentType: FormComponentType.DROPDOWN,
            options: [
              {
                displayName: 'Run SQL',
                key: SqlOperations.RUN_SQL,
                value: SqlOperations.RUN_SQL
              },
              {
                displayName: 'Update rows with form',
                key: SqlOperations.UPDATE_ROWS,
                value: SqlOperations.UPDATE_ROWS
              }
            ],
            initialValue: SqlOperations.RUN_SQL
          },
          {
            label: 'Operation',
            name: 'operation',
            startVersion: MicrosoftSQLPluginVersions.V9,
            endVersion: MicrosoftSQLPluginVersions.V10,
            componentType: FormComponentType.DROPDOWN,
            options: [
              {
                displayName: 'Run SQL',
                key: SqlOperations.RUN_SQL,
                value: SqlOperations.RUN_SQL
              },
              {
                displayName: 'Insert, update, or delete rows with form',
                key: SqlOperations.UPDATE_ROWS,
                value: SqlOperations.UPDATE_ROWS
              }
            ],
            initialValue: SqlOperations.RUN_SQL
          },
          {
            // Only show operation dropdown if UPDATE_ROWS was previously selected
            // This effectively deprecates UPDATE_ROWS for new actions
            label: 'Operation',
            name: 'operation',
            startVersion: MicrosoftSQLPluginVersions.V11,
            componentType: FormComponentType.DROPDOWN,
            options: [
              {
                displayName: 'Run SQL',
                key: SqlOperations.RUN_SQL,
                value: SqlOperations.RUN_SQL
              },
              {
                displayName: 'Insert, update, or delete rows with form',
                key: SqlOperations.UPDATE_ROWS,
                value: SqlOperations.UPDATE_ROWS
              }
            ],
            initialValue: SqlOperations.RUN_SQL,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Parameters (@PARAM_1, @PARAM_2, ...)',
            name: 'parameters',
            startVersion: MicrosoftSQLPluginVersions.V11,
            ldFlag: 'clark.parameterized-apis.v2.enabled',
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            placeholder: '[param1, param2, ...]',
            tooltip: {
              markdownText:
                'JavaScript expression that evaluates to an array of parameter values. Use `@PARAM_1`, `@PARAM_2`, etc. in your SQL query to reference these parameters.'
            },
            display: {
              show: {
                operation: ['undefined', SqlOperations.RUN_SQL]
              }
            }
          },
          {
            label: '', // Query
            name: 'body',
            startVersion: MicrosoftSQLPluginVersions.V1,
            componentType: FormComponentType.CODE_EDITOR,
            language: EditorLanguage.SQL,
            initialValue: DB_SQL_INITIAL_TEXT,
            display: {
              show: {
                // Frontend is using string equality checks here
                operation: ['undefined', SqlOperations.RUN_SQL]
              }
            }
          },
          {
            label: 'Microsoft SQL schema',
            name: 'schema',
            startVersion: MicrosoftSQLPluginVersions.V8,
            componentType: FormComponentType.METADATA_DROPDOWN,
            placeholder: 'Select a schema',
            keyAccessor: 'name',
            valueAccessor: 'name',
            listAccessor: 'metadata.dbSchema.schemas',
            displayNameAccessor: 'name',
            defaultToFirstOption: true,
            clearDependentFieldsOnChange: ['table'],
            triggerGetMetadata: true,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            },
            rules: [{ required: true, message: 'Schema is required' }],
            showSearch: true,
            optionFilterProp: 'value'
          },
          {
            label: 'Microsoft SQL table',
            name: 'table',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.METADATA_DROPDOWN,
            placeholder: 'Select a table',
            keyAccessor: 'name',
            valueAccessor: 'name',
            listAccessor: 'metadata.dbSchema.tables',
            filterDependency: 'schema',
            filterFieldName: 'schema',
            displayNameAccessor: 'name',
            triggerGetMetadata: true,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            },
            rules: [{ required: true, message: 'Table is required' }],
            showSearch: true,
            optionFilterProp: 'value'
          }
        ]
      },
      {
        name: 'main2',
        sectionHeader: 'Step 1 - Match rows',
        items: [
          {
            label: 'Row matching mode',
            name: 'useAdvancedMatching',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.DROPDOWN,
            options: [
              {
                displayName: 'Automatically match against database table primary key',
                subText: 'Table must have a primary key. Does not allow updates that modify primary key columns.',
                key: 'auto',
                value: 'auto'
              },
              {
                displayName: 'Manually match against any column(s)',
                subText: 'Use with any column. Allows updates that modify primary key columns.',
                key: 'advanced',
                value: 'advanced'
              }
            ],
            initialValue: 'auto',
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Primary keys',
            name: 'primaryKeyDisplay',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.PRIMARY_KEY_DISPLAY,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS],
                useAdvancedMatching: ['undefined', 'auto']
              }
            }
          },
          // Group
          {
            label: 'Array of original rows to be updated',
            name: 'oldValues',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            initialValue: '',
            subHeading: `E.g. for an editable Table, it will be \`{{Table1.editedRows.originalRows}}\``,
            placeholder: '{{ [{name: “OriginalBilly”, email: “billy@joel.com”}, {name: “OriginalAlice”, email: “a@alice.com”}] }}',
            tooltip: {
              markdownText: `Used to identify the Microsoft SQL rows that will be updated.`
            },
            rules: [{ required: true, message: 'Missing required value' }],
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS],
                useAdvancedMatching: ['advanced']
              }
            }
          },
          {
            label: 'Microsoft SQL columns to match on',
            name: 'filterBy',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.FILTER_COLUMNS,
            tooltip: {
              markdownText: `Specify which columns are used to find unique matches.`
            },
            rules: [{ required: true, message: 'At least one filter column is required' }],
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS],
                useAdvancedMatching: ['advanced']
              }
            }
          }
        ]
      },
      {
        name: 'updates',
        sectionHeader: 'Step 2 - Update matched rows',
        items: [
          {
            label: 'Rows to insert',
            name: 'insertedRows',
            startVersion: MicrosoftSQLPluginVersions.V9,
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            initialValue: '',
            placeholder: '{{ [{name: “Billy”, email: “billy@joel.com”}, {name: “Alice”, email: “a@alice.com”}] }}',
            subHeading: `E.g for an editable Table, it will be \`{{Table1.editedRows.insertedRows}}\``,
            tooltip: {
              markdownText: 'These rows will be inserted'
            },
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Rows to update',
            name: 'newValues',
            startVersion: MicrosoftSQLPluginVersions.V7,
            endVersion: MicrosoftSQLPluginVersions.V8,
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            initialValue: '',
            placeholder: '{{ [{name: “Billy”, email: “billy@joel.com”}, {name: “Alice”, email: “a@alice.com”}] }}',
            rules: [{ required: true, message: 'Missing required value' }],
            subHeading: `E.g for an editable Table, it will be \`{{Table1.editedRows.updatedRows}}\``,
            tooltip: {
              markdownText: 'Matched rows from Step 1 will be updated to these values'
            },
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Rows to update',
            name: 'newValues',
            startVersion: MicrosoftSQLPluginVersions.V9,
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            initialValue: '',
            placeholder: '{{ [{name: “Billy”, email: “billy@joel.com”}, {name: “Alice”, email: “a@alice.com”}] }}',
            subHeading: `E.g for an editable Table, it will be \`{{Table1.editedRows.updatedRows}}\``,
            tooltip: {
              markdownText: 'Matched rows from Step 1 will be updated to these values'
            },
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Rows to delete',
            name: 'deletedRows',
            startVersion: MicrosoftSQLPluginVersions.V9,
            componentType: FormComponentType.DYNAMIC_INPUT_TEXT,
            initialValue: '',
            placeholder: '{{ [{name: “Billy”, email: “billy@joel.com”}, {name: “Alice”, email: “a@alice.com”}] }}',
            subHeading: `E.g for an editable Table, it will be \`{{Table1.editedRows.deletedRows}}\``,
            tooltip: {
              markdownText: 'Matched rows from Step 1 will be deleted'
            },
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          }
        ]
      },
      {
        name: 'preview',
        sectionHeader: 'Step 3 - Match columns',
        items: [
          {
            label: 'Map JSON keys to MicrosoftSQL columns',
            name: 'mappingSettingsDisplay',
            startVersion: MicrosoftSQLPluginVersions.V7,
            componentType: FormComponentType.KEY_MAPPING,
            tooltip: {
              markdownText: 'Used to map JSON keys from the previous steps to Microsoft SQL columns'
            },
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Preview',
            name: 'preview',
            startVersion: MicrosoftSQLPluginVersions.V7,
            endVersion: MicrosoftSQLPluginVersions.V8,
            componentType: FormComponentType.SQL_PREVIEW,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          },
          {
            label: 'Preview',
            name: 'preview',
            startVersion: MicrosoftSQLPluginVersions.V9,
            componentType: FormComponentType.SQL_PREVIEW_WITH_INSERT_DELETE,
            display: {
              show: {
                operation: [SqlOperations.UPDATE_ROWS]
              }
            }
          }
        ]
      },
      {
        name: 'advanced:main',
        items: [
          {
            label: 'Use parameterized SQL',
            name: 'usePreparedSql',
            startVersion: MicrosoftSQLPluginVersions.V6,
            endVersion: MicrosoftSQLPluginVersions.V10,
            componentType: FormComponentType.SWITCH,
            initialValue: true,
            tooltip: {
              markdownText: PARAMETERIZED_SQL_DESCRIPTION
            },
            display: {
              show: {
                operation: ['undefined', SqlOperations.RUN_SQL]
              }
            }
          },
          {
            label: 'Use parameterized SQL',
            name: 'usePreparedSql',
            startVersion: MicrosoftSQLPluginVersions.V11,
            componentType: FormComponentType.SWITCH,
            initialValue: true,
            tooltip: {
              markdownText: PARAMETERIZED_SQL_DESCRIPTION
            },
            display: {
              show: {
                operation: [SqlOperations.RUN_SQL],
                parameters: ['undefined']
              }
            }
          }
        ]
      }
    ]
  }
};