/**
 * @license Copyright © HatioLab Inc. All rights reserved.
 */

import '@material/web/icon/icon.js'

import '@operato/i18n/ox-i18n.js'
import { css, html } from 'lit'
import { customElement, property, queryAll, state } from 'lit/decorators.js'

import { OxFormField } from '@operato/input'

type ProcedureParameterType = {
  name: string
  dir: string
  type: string
  val?: any
  accessor?: string
  maxSize?: number
}

type ValueType = {
  code?: string
  procedure?: string
  parameters?: ProcedureParameterType[]
}

const NUMBERS = ['TINYINT', 'SMALLINT', 'INT', 'BIGINT', 'FLOAT', 'REAL', 'DECIMAL', 'NUMERIC', 'MONEY', 'SMALLMONEY']
const PARAMETERIZED_TYPES = ['NCHAR', 'VARCHAR', 'NVARCHAR', 'TEXT', 'NTEXT', 'DECIMAL', 'NUMERIC']

/**
 input component for procedure-parameters
 
 Example:
 
   <things-editor-db-procedure 
     value=${map}
   </things-editor-db-procedure>
 */
@customElement('things-editor-db-procedure')
export class ThingsEditorProcedureParameters extends OxFormField {
  static styles = [
    css`
      :host {
        display: flex;
        flex-direction: column;
        overflow: hidden;
        margin-bottom: var(--spacing-large);
      }

      div {
        display: flex;
        flex-flow: row nowrap;
        gap: var(--spacing-medium);
        margin-bottom: var(--spacing-small);
      }

      pre {
        flex: 1;
        background-color: #333;
        color: white;
        margin: 0;
        padding: 4px 6px;
        font-size: 1.5em;
        display: flex;
        border-radius: var(--spacing-small);
      }

      code {
        flex: 1;
        white-space: pre;
        min-height: 32px;
      }

      button {
        border: var(--button-border);
        border-radius: var(--border-radius);
        background-color: var(--button-background-color);
        padding: var(--spacing-small) var(--spacing-medium);
        line-height: 0.8;
        color: var(--button-color);
        cursor: pointer;
      }

      button + button {
        margin-left: -5px;
      }

      button md-icon {
        --md-icon-size: var(--fontsize-default);
      }

      button:focus,
      button:hover,
      button:active {
        border: var(--button-activ-border);
        background-color: var(--button-background-focus-color);
        color: var(--md-sys-color-on-primary);
      }

      input {
        flex: 1;
        border: 0;
        border-bottom: var(--border-dim-color);
        padding: var(--input-padding);
        font: var(--input-font);
        min-width: 50px;
      }
      /* 체크박스·라디오는 제외 — 표면색을 주면 컨트롤 모양이 깨진다 */
      input:not([type='checkbox']):not([type='radio']) {
        background-color: var(--input-field-background, var(--md-sys-color-surface-container-highest));
        color: var(--input-field-color, var(--md-sys-color-on-surface));
      }


      input:focus {
        outline: none;
        border-bottom: 1px solid var(--md-sys-color-primary);
      }

      button.hidden {
        opacity: 0;
        cursor: default;
      }

      select,
      ox-select,
      input:not([type='checkbox']) {
        border: 1px solid rgba(0, 0, 0, 0.2);
        border-radius: 4px;
      }
    `
  ]

  @property({ type: Object }) value: ValueType = {}
  @property({ type: Array }) steps: string[] = []
  @property({ type: String }) dbtype: 'oracle' | 'mssql' = 'oracle'

  @state() private procedure?: string = ''
  @state() private parameters?: ProcedureParameterType[] = []
  private _changingNow: boolean = false

  @queryAll('[data-record]') records!: NodeListOf<HTMLElement>

  firstUpdated() {
    this.renderRoot.addEventListener('change', this._onChange.bind(this))
  }

  render() {
    const code = this.value?.code || ''
    const parameters = this.parameters || []
    const procedure = this.procedure || ''
    const dbtype = this.dbtype || 'oracle'

    const steps = this.steps || []

    return html`
      <div>
        <label><ox-i18n msgid="label.procedure-code"></ox-i18n></label>
      </div>

      <div>
        <pre>
          <code>${code}</code>
        </pre
        >
      </div>

      <div>
        <label><ox-i18n msgid="label.procedure-name"></ox-i18n></label>
      </div>

      <div>
        <input type="text" data-procedure .value=${procedure} />
      </div>

      <div>
        <label><ox-i18n msgid="label.parameters"></ox-i18n></label>
      </div>

      ${parameters.map(
        item => html`
          <div data-record>
            <input type="text" data-name placeholder="name" .value=${item.name} />
            <select data-dir placeholder="dir" .value=${item.dir}>
              <option value="" ?selected=${item.dir == ''}>&nbsp;</option>
              <option value="In" ?selected=${item.dir == 'In'}>In</option>
              <option value="Inout" ?selected=${item.dir == 'Inout'}>Inout</option>
              <option value="Out" ?selected=${item.dir == 'Out'}>Out</option>
            </select>
            <select data-type placeholder="type" .value=${item.type}>
              ${dbtype == 'oracle'
                ? html`
                    <option value="" ?selected=${item.type == ''}>&nbsp;</option>
                    <option value="String" ?selected=${item.type == 'String'}>String</option>
                    <option value="Number" ?selected=${item.type == 'Number'}>Number</option>
                    <option value="Date" ?selected=${item.type == 'Date'}>Date</option>
                    <!-- 
                  <option value="Buffer" ?selected=${item.type == 'Buffer'}>Buffer</option>
                  <option value="Blob" ?selected=${item.type == 'Blob'}>Blob</option>
                  <option value="Clob" ?selected=${item.type == 'Clob'}>Clob</option>
                -->
                    <option value="Cursor" ?selected=${item.type == 'Cursor'}>Cursor</option>
                  `
                : html`
                    <option value="" ?selected=${item.type == ''}>&nbsp;</option>
                    ${[
                      'CHAR',
                      'VARCHAR',
                      'NCHAR',
                      'NVARCHAR',
                      'TEXT',
                      'NTEXT',
                      'TINYINT',
                      'SMALLINT',
                      'INT',
                      'BIGINT',
                      'FLOAT',
                      'REAL',
                      'DECIMAL',
                      'NUMERIC',
                      'MONEY',
                      'SMALLMONEY',
                      'SMALLDATETIME',
                      'DATE',
                      'TIME',
                      'DATETIME',
                      'DATETIME2',
                      'DATETIMEOFFSET'
                    ].map(type => html`<option value=${type} ?selected=${item.type == type}>${type}</option>`)}
                  `}
            </select>
            <input type="text" data-accessor placeholder="accessor" .value=${item.accessor || ''} list="step-list" />
            <input type="text" data-val placeholder="val" .value=${item.val || ''} />
            <input type="number" data-max-size placeholder="max-size" .value=${String(item.maxSize)} />

            <button class="record-action" @click=${(e: MouseEvent) => this._delete(e)} tabindex="-1">
              <md-icon>remove</md-icon>
            </button>
            <button class="record-action" @click=${(e: MouseEvent) => this._up(e)} tabindex="-1">
              <md-icon>arrow_upward</md-icon>
            </button>
            <button class="record-action" @click=${(e: MouseEvent) => this._down(e)} tabindex="-1">
              <md-icon>arrow_downward</md-icon>
            </button>
          </div>
        `
      )}

      <div data-record-new>
        <input type="text" data-name placeholder="name" value="" />
        <select data-dir placeholder="dir">
          <option value="" selected>&nbsp;</option>
          <option value="In">In</option>
          <option value="Inout">Inout</option>
          <option value="Out">Out</option>
        </select>
        <select data-type placeholder="type" value="">
          ${dbtype == 'oracle'
            ? html`
                <option value="" selected>&nbsp;</option>
                <option value="String">String</option>
                <option value="Number">Number</option>
                <option value="Date">Date</option>
                <!-- 
          <option value="Buffer">Buffer</option>
          <option value="Blob">Blob</option>
          <option value="Clob">Clob</option>
                -->
                <option value="Cursor">Cursor</option>
              `
            : html`
                <option value="" selected>&nbsp;</option>
                ${[
                  'CHAR',
                  'VARCHAR',
                  'NCHAR',
                  'NVARCHAR',
                  'TEXT',
                  'NTEXT',
                  'TINYINT',
                  'SMALLINT',
                  'INT',
                  'BIGINT',
                  'FLOAT',
                  'REAL',
                  'DECIMAL',
                  'NUMERIC',
                  'MONEY',
                  'SMALLMONEY',
                  'SMALLDATETIME',
                  'DATE',
                  'TIME',
                  'DATETIME',
                  'DATETIME2',
                  'DATETIMEOFFSET'
                ].map(type => html`<option value=${type}>${type}</option>`)}
              `}
        </select>
        <input type="text" data-accessor placeholder="accessor" value="" list="step-list" />
        <input type="text" data-val placeholder="val" value="" />
        <input type="number" data-max-size placeholder="max-size" value="" />

        <button class="record-action" @click=${(e: MouseEvent) => this._add()} tabindex="-1">
          <md-icon>add</md-icon>
        </button>
        <button class="hidden"><md-icon>add</md-icon></button>
        <button class="hidden"><md-icon>add</md-icon></button>
      </div>

      <datalist id="step-list">${steps.map(id => html` <option value=${id}></option> `)}</datalist>
    `
  }

  updated(changes: any) {
    if (changes.has('value')) {
      /* 하위 호환성때문에, Array타입 값을 처리하도록 함. 다음 마이너 업그레이드시에 제거할 것. */
      const value = (Array.isArray(this.value) ? { parameters: this.value } : this.value) as ValueType

      const { procedure, parameters } = value || {}

      this.procedure = procedure
      this.parameters = parameters
    }
  }

  _onChange(e: Event) {
    if (this._changingNow) {
      return
    }

    this._changingNow = true

    const input = e.target as HTMLInputElement

    if (input.hasAttribute('data-procedure')) {
      this.procedure = input.value
      this._updateValue()
    } else {
      const record = (e.target as Element).closest('[data-record],[data-record-new]') as HTMLElement

      if (record.hasAttribute('data-record')) {
        this._build()
      } else if (record.hasAttribute('data-record-new') && input.hasAttribute('data-type')) {
        this._add()
      }
    }

    this._changingNow = false
  }

  _adjust({ name, type, dir, maxSize, val, accessor }: ProcedureParameterType): ProcedureParameterType {
    const entry = {
      name: name && String(name).trim(),
      type,
      dir,
      accessor: accessor && String(accessor).trim()
    } as ProcedureParameterType

    if (
      /* for oracle */
      dir != 'In' &&
      (type == 'String' || type == 'Buffer') &&
      maxSize !== null &&
      maxSize !== undefined &&
      !isNaN(maxSize)
    ) {
      entry.maxSize = maxSize
    } else if (
      /* for mssql */
      PARAMETERIZED_TYPES.includes(type) &&
      maxSize !== null &&
      maxSize !== undefined &&
      !isNaN(maxSize)
    ) {
      entry.maxSize = maxSize
    }

    if (dir != 'Out' && val !== null && val !== undefined && val != '') {
      entry.val = type == 'Number' || NUMBERS.includes(type) ? Number(val) : val
    }

    return entry
  }

  _build(includeNewRecord?: boolean) {
    if (includeNewRecord) {
      var records = this.renderRoot.querySelectorAll('[data-record],[data-record-new]') as NodeListOf<HTMLElement>
    } else {
      var records = this.renderRoot.querySelectorAll('[data-record]') as NodeListOf<HTMLElement>
    }

    var newmap: ProcedureParameterType[] = []

    for (var i = 0; i < records.length; i++) {
      var record = records[i]

      const name = (record.querySelector('[data-name]') as HTMLInputElement).value
      const type = (record.querySelector('[data-type]') as HTMLInputElement).value
      const dir = (record.querySelector('[data-dir]') as HTMLInputElement).value
      const val = (record.querySelector('[data-val]') as HTMLInputElement).value
      const accessor = (record.querySelector('[data-accessor]') as HTMLInputElement).value
      const maxSize = (record.querySelector('[data-max-size]') as HTMLInputElement).valueAsNumber

      const inputs = record.querySelectorAll(
        '[data-type]:not([style*="display: none"])'
      ) as NodeListOf<HTMLInputElement>

      if (!inputs || inputs.length == 0) {
        continue
      }

      if (name) {
        newmap.push(this._adjust({ name, type, dir, val, accessor, maxSize }))
      }
    }

    this.parameters = newmap
    this._updateValue()
  }

  _buildOracleCode() {
    const args = (this.parameters || []).map(p => ':' + p.name).join(', ')

    return `${this.procedure}(${args});`
  }

  _buildMssqlCode() {
    const outParams = this.parameters?.filter(({ dir }) => dir !== 'In') || []
    
    const declareClauses = this.parameters
      ?.map(({ name, dir, type, val, maxSize = 0 }) => {
        const ptype = PARAMETERIZED_TYPES.includes(type) ? `${type}(${maxSize})` : type

        if (dir == 'Out') {
          return `DECLARE @${name} ${ptype};`
        } else {
          const pvalue = NUMBERS.includes(type) ? `${val}` : `'${val}'`
          return `DECLARE @${name} ${ptype} = ${pvalue};`
        }
      })
      .join('\n')

    const execClause =
      [
        `EXEC ${this.procedure}`,
        ...(this.parameters?.map(({ name, dir }, index, array) => {
          const period = index === array.length - 1 ? '' : ','

          if (dir == 'In') {
            return `    @${name} = @${name}${period}`
          } else {
            return `    @${name} = @${name} OUTPUT${period}`
          }
        }) || [])
      ].join('\n') + ';'

    // OUT 파라미터가 있을 때만 SELECT 절 생성
    const seleceClause = outParams.length > 0
      ? `SELECT ` +
        outParams
          .map(({ name }, index, array) => {
            const period = index === array.length - 1 ? '' : ','
            return `@${name} AS ${name}${period}`
          })
          .join(' ') +
        ';'
      : ''

    return [declareClauses, execClause, seleceClause].filter(Boolean).join('\n\n')
  }

  _updateValue() {
    this.value = {
      code: this.dbtype == 'oracle' ? this._buildOracleCode() : this._buildMssqlCode(),
      procedure: this.procedure,
      parameters: this.parameters
    }

    this.dispatchEvent(new CustomEvent('change', { bubbles: true, composed: true, detail: this.value }))
  }

  _add() {
    this._build(true)

    const inputs = this.renderRoot.querySelectorAll(
      '[data-record-new] input:not([style*="display: none"])'
    ) as NodeListOf<HTMLInputElement & { value: any }>

    for (var i = 0; i < inputs.length; i++) {
      let input = inputs[i]

      input.value = ''
    }

    inputs[0].focus()
  }

  _delete(e: MouseEvent) {
    const record = (e.target as Element).closest('[data-record]') as HTMLElement

    ;(record!.querySelector('[data-name]') as HTMLInputElement)!.value = ''

    this._build()
  }

  _up(e: MouseEvent) {
    const record = (e.target as Element).closest('[data-record]') as HTMLElement
    const array = Array.from(this.records)
    const index = array.indexOf(record) - 1

    if (index < 0) {
      return
    }

    const deleted = array.splice(index, 1)
    array.splice(index + 1, 0, ...deleted)

    this.parameters = array.map(record => {
      const name = (record.querySelector('[data-name]') as HTMLInputElement).value
      const dir = (record.querySelector('[data-dir]') as HTMLInputElement).value
      const type = (record.querySelector('[data-type]') as HTMLInputElement).value
      const val = (record.querySelector('[data-val]') as HTMLInputElement).value
      const accessor = (record.querySelector('[data-accessor]') as HTMLInputElement).value
      const maxSize = (record.querySelector('[data-max-size]') as HTMLInputElement).valueAsNumber

      return this._adjust({ name, dir, type, val, accessor, maxSize })
    })

    this._updateValue()
  }

  _down(e: MouseEvent) {
    const record = (e.target as Element).closest('[data-record]') as HTMLElement
    const array = Array.from(this.records)
    const index = array.indexOf(record)

    if (index > array.length) {
      return
    }

    array.splice(index, 1)
    array.splice(index + 1, 0, record)

    this.parameters = array.map(record => {
      const name = (record.querySelector('[data-name]') as HTMLInputElement).value
      const dir = (record.querySelector('[data-dir]') as HTMLInputElement).value
      const type = (record.querySelector('[data-type]') as HTMLInputElement).value
      const val = (record.querySelector('[data-val]') as HTMLInputElement).value
      const accessor = (record.querySelector('[data-accessor]') as HTMLInputElement).value
      const maxSize = (record.querySelector('[data-max-size]') as HTMLInputElement).valueAsNumber

      return this._adjust({ name, dir, type, val, accessor, maxSize })
    })

    this._updateValue()
  }
}