<script setup lang="ts">
import { ref, computed, watch, onMounted } from 'vue'
import { useConfigStore } from '../../stores/config'
import { useUiStore } from '../../stores/ui'
import { configApi } from '../../services/configApi'
import ConfirmModal from '../ui/ConfirmModal.vue'
import TableBrowser from './TableBrowser.vue'
import type { DiscoveredConnection, DbPreset } from '../../types'

const configStore = useConfigStore()
const ui = useUiStore()

// Presets
const presets = ref<DbPreset[]>([])
const presetsLoaded = ref(false)

// Fallback presets if API call fails
const FALLBACK_PRESETS: DbPreset[] = [
  { key: 'sqlserver', label: 'SQL Server', default_port: 1433, drivers: ['ODBC Driver 17 for SQL Server'], auth_modes: ['sql', 'windows'], placeholder: { host: 'server\\instance or IP', database: 'master' }, notes: '' },
  { key: 'sybase', label: 'SAP Sybase ASE', default_port: 5000, drivers: ['Adaptive Server Enterprise', 'FreeTDS'], auth_modes: ['sql'], placeholder: { host: 'hostname or IP', database: 'master' }, notes: 'SAP ECC on Sybase uses TDS 5.0.' },
  { key: 'hana', label: 'SAP HANA', default_port: 30015, drivers: ['HDBODBC'], auth_modes: ['sql'], placeholder: { host: 'hostname or IP', database: '(schema)' }, notes: '' },
  { key: 'postgresql', label: 'PostgreSQL', default_port: 5432, drivers: [], auth_modes: ['sql'], placeholder: { host: 'localhost', database: 'postgres' }, notes: '' },
  { key: 'mysql', label: 'MySQL / MariaDB', default_port: 3306, drivers: [], auth_modes: ['sql'], placeholder: { host: 'localhost', database: 'mysql' }, notes: '' },
  { key: 'oracle', label: 'Oracle', default_port: 1521, drivers: [], auth_modes: ['sql'], placeholder: { host: 'hostname or IP', database: 'ORCL' }, notes: '' },
  { key: 'db2', label: 'IBM Db2', default_port: 50000, drivers: [], auth_modes: ['sql'], placeholder: { host: 'hostname or IP', database: 'SAMPLE' }, notes: '' },
]

onMounted(async () => {
  try {
    const { data } = await configApi.getDbPresets()
    presets.value = data
  } catch {
    presets.value = FALLBACK_PRESETS
  }
  presetsLoaded.value = true
})

// Discovery state
const discoveredConnections = ref<DiscoveredConnection[]>([])
const scanning = ref(false)
const selectedDiscovered = ref<DiscoveredConnection | null>(null)

// Manual form state
const showManualForm = ref(false)
const rawMode = ref(false)
const showAdvanced = ref(false)
const manualForm = ref({
  name: '',
  db_type: 'sqlserver',
  source_system: '',
  host: '',
  port: '' as string | number,
  database: '',
  username: '',
  password: '',
  driver: '',
  extra_params: '',
  connection_string: '',
})

// Credentials form (for discovered connections — editable fields)
const credForm = ref({
  username: '',
  password: '',
  server: '',
  port: '',
  database: '',
})

const testing = ref<string | null>(null)
const browsingConnectionId = ref<string | null>(null)
const browsingConnectionName = ref<string>('')

// Confirmation modal state
const confirmAction = ref<{
  title: string
  message: string
  detail?: string
  variant: 'warning' | 'danger' | 'info'
  onConfirm: () => void
} | null>(null)

function showConfirm(opts: typeof confirmAction.value) {
  confirmAction.value = opts
}

function onConfirmApproved() {
  const action = confirmAction.value
  confirmAction.value = null
  if (action?.onConfirm) action.onConfirm()
}

// Current preset for the selected db_type
const currentPreset = computed(() =>
  presets.value.find(p => p.key === manualForm.value.db_type) || null
)

// Auto-fill defaults when db_type changes
watch(() => manualForm.value.db_type, (newType) => {
  const preset = presets.value.find(p => p.key === newType)
  if (preset) {
    manualForm.value.port = preset.default_port
    manualForm.value.driver = ''  // reset to auto-detect
  }
})

// Connection string preview (structured mode)
const connectionPreview = computed(() => {
  const f = manualForm.value
  if (rawMode.value || !f.host) return ''

  const drv = f.driver || currentPreset.value?.drivers[0] || 'auto'
  const port = f.port || currentPreset.value?.default_port || ''

  switch (f.db_type) {
    case 'sqlserver': {
      const server = f.host.includes('\\') ? f.host : `${f.host},${port}`
      return `DRIVER={${drv}};SERVER=${server};DATABASE=${f.database};UID=${f.username || '***'};PWD=***`
    }
    case 'sybase':
      return `DRIVER={${drv}};SERVER=${f.host};PORT=${port};DATABASE=${f.database};UID=${f.username || '***'};PWD=***`
    case 'hana':
      return `DRIVER={${drv}};SERVERNODE=${f.host}:${port};UID=${f.username || '***'};PWD=***`
    case 'postgresql':
      return `postgresql://${f.username || '***'}:***@${f.host}:${port}/${f.database}`
    case 'mysql':
      return `mysql://${f.username || '***'}:***@${f.host}:${port}/${f.database}`
    case 'oracle':
      return `DRIVER={${drv}};DBQ=${f.host}:${port}/${f.database};UID=${f.username || '***'};PWD=***`
    case 'db2':
      return `DRIVER={${drv}};HOSTNAME=${f.host};PORT=${port};DATABASE=${f.database};UID=${f.username || '***'};PWD=***;PROTOCOL=TCPIP`
    default:
      return `${f.host}:${port}/${f.database}`
  }
})

async function discoverConnections() {
  scanning.value = true
  try {
    const { data } = await configApi.discoverConnections()
    discoveredConnections.value = data
    if (data.length === 0) {
      ui.showToast('No connections discovered. You can add one manually.', 'info')
    } else {
      ui.showToast(`Found ${data.length} connection(s)`, 'success')
    }
  } catch {
    ui.showToast('Discovery failed', 'error')
  } finally {
    scanning.value = false
  }
}

function selectDiscovered(conn: DiscoveredConnection) {
  selectedDiscovered.value = conn
  credForm.value = {
    username: '',
    password: '',
    server: conn.server,
    port: conn.port,
    database: conn.database,
  }
}

async function addFromDiscovered() {
  if (!selectedDiscovered.value) return
  const conn = selectedDiscovered.value
  try {
    // Use structured creation so port/host edits are respected
    await configApi.createConnection({
      name: conn.name,
      db_type: conn.db_type,
      source_system: conn.source_system || undefined,
      host: credForm.value.server,
      port: credForm.value.port,
      database: credForm.value.database,
      username: credForm.value.username,
      password: credForm.value.password,
    })
    await configStore.fetchConnections()
    selectedDiscovered.value = null
    ui.showToast('Connection added', 'success')
  } catch (e: any) {
    ui.showToast(e.response?.data?.detail || 'Failed to add connection', 'error')
  }
}

function resetManualForm() {
  const preset = presets.value.find(p => p.key === 'sqlserver')
  manualForm.value = {
    name: '',
    db_type: 'sqlserver',
    source_system: '',
    host: '',
    port: preset?.default_port || 1433,
    database: '',
    username: '',
    password: '',
    driver: '',
    extra_params: '',
    connection_string: '',
  }
  rawMode.value = false
  showAdvanced.value = false
}

async function addManual() {
  const f = manualForm.value
  try {
    if (rawMode.value) {
      await configApi.createConnection({
        name: f.name,
        db_type: f.db_type,
        source_system: f.source_system || undefined,
        connection_string: f.connection_string,
      })
    } else {
      await configApi.createConnection({
        name: f.name,
        db_type: f.db_type,
        source_system: f.source_system || undefined,
        host: f.host,
        port: f.port,
        database: f.database,
        username: f.username,
        password: f.password,
        driver: f.driver || undefined,
        extra_params: f.extra_params || undefined,
      })
    }
    await configStore.fetchConnections()
    showManualForm.value = false
    resetManualForm()
    ui.showToast('Connection added', 'success')
  } catch (e: any) {
    ui.showToast(e.response?.data?.detail || 'Failed to add connection', 'error')
  }
}

function requestTestConnection(conn: any) {
  showConfirm({
    title: 'Test Remote Database Connection',
    message: `This will send a test query (SELECT 1) to the remote database "${conn.name}".`,
    detail: `Database type: ${dbTypeLabel(conn.db_type)}\nSource system: ${conn.source_system || 'N/A'}\n\nOnly a read-only test query will be executed on the remote server.`,
    variant: 'warning',
    onConfirm: () => doTestConnection(conn.id),
  })
}

async function doTestConnection(id: string) {
  testing.value = id
  try {
    const { data } = await configApi.testConnection(id)
    if (data.status === 'ok') {
      ui.showToast('Connection successful', 'success')
    } else {
      ui.showToast(data.error || 'Connection failed', 'error')
    }
  } catch (e: any) {
    ui.showToast(e.response?.data?.detail || 'Connection test failed', 'error')
  } finally {
    testing.value = null
  }
}

function requestBrowseTables(conn: any) {
  browsingConnectionId.value = conn.id
  browsingConnectionName.value = conn.name
}

function onBrowseRegistered() {
  browsingConnectionId.value = null
  configStore.fetchTables()
  ui.showToast('Tables registered successfully', 'success')
}

function requestDelete(conn: any) {
  showConfirm({
    title: 'Delete Connection',
    message: `Are you sure you want to delete the connection "${conn.name}"?`,
    detail: 'This will remove the connection configuration. Synced data will remain in the local database.',
    variant: 'danger',
    onConfirm: () => doDelete(conn.id),
  })
}

async function doDelete(id: string) {
  await configApi.deleteConnection(id)
  await configStore.fetchConnections()
  ui.showToast('Connection deleted', 'info')
}

function sourceIcon(source: string) {
  switch (source) {
    case 'ODBC DSN': return 'ODBC'
    case 'DBeaver': return 'DB'
    case 'SSMS': return 'SSMS'
    case 'SQL Server Browser': return 'NET'
    default: return '?'
  }
}

function dbTypeLabel(t: string) {
  const p = presets.value.find(pr => pr.key === t)
  return p?.label || t
}
</script>

<template>
  <div class="max-w-4xl">
    <div class="flex items-center justify-between mb-4">
      <h2 class="text-lg font-semibold">Database Connections</h2>
      <div class="flex gap-2">
        <button @click="discoverConnections" :disabled="scanning" class="btn-primary text-sm">
          {{ scanning ? 'Scanning...' : 'Auto-Discover' }}
        </button>
        <button @click="showManualForm = !showManualForm; if (showManualForm) resetManualForm()" class="btn-secondary text-sm">
          {{ showManualForm ? 'Cancel' : 'Manual' }}
        </button>
      </div>
    </div>

    <!-- Discovered connections -->
    <div v-if="discoveredConnections.length > 0" class="mb-6">
      <h3 class="text-sm font-medium text-dark-400 mb-2">Discovered Connections</h3>
      <div class="grid grid-cols-1 md:grid-cols-2 gap-3">
        <button
          v-for="(dc, idx) in discoveredConnections"
          :key="idx"
          @click="selectDiscovered(dc)"
          :class="[
            'text-left p-3 rounded-lg border transition-colors',
            selectedDiscovered === dc
              ? 'border-primary-500 bg-primary-500/10'
              : 'border-dark-600 bg-dark-800 hover:border-dark-400'
          ]"
        >
          <div class="flex items-center gap-2 mb-1">
            <span class="inline-flex items-center justify-center w-8 h-5 bg-dark-700 rounded text-[10px] font-bold text-dark-300">{{ sourceIcon(dc.source) }}</span>
            <span class="font-medium text-sm truncate">{{ dc.name }}</span>
          </div>
          <div class="text-xs text-dark-500 space-y-0.5 ml-10">
            <div>{{ dbTypeLabel(dc.db_type) }}<span v-if="dc.server"> &middot; {{ dc.server }}</span><span v-if="dc.port">:{{ dc.port }}</span></div>
            <div v-if="dc.database">Database: {{ dc.database }}</div>
            <div class="text-dark-600">via {{ dc.source }}</div>
          </div>
        </button>
      </div>
    </div>

    <!-- Credentials form for selected discovered connection -->
    <div v-if="selectedDiscovered" class="card mb-4 border-primary-500/30">
      <h3 class="font-medium text-sm mb-1">Connect to: {{ selectedDiscovered.name }}</h3>
      <p class="text-xs text-dark-500 mb-3">
        {{ dbTypeLabel(selectedDiscovered.db_type) }}
        <span v-if="selectedDiscovered.driver" class="text-dark-600"> &middot; {{ selectedDiscovered.driver }}</span>
      </p>
      <!-- Editable connection details -->
      <div class="grid grid-cols-3 gap-3 mb-3">
        <input v-model="credForm.server" placeholder="Host / Server" class="input-field text-sm" />
        <input v-model="credForm.port" placeholder="Port" class="input-field text-sm" />
        <input v-model="credForm.database" placeholder="Database" class="input-field text-sm" />
      </div>
      <div class="grid grid-cols-2 gap-3 mb-3">
        <input v-model="credForm.username" placeholder="Username" class="input-field" autocomplete="off" />
        <input v-model="credForm.password" type="password" placeholder="Password" class="input-field" autocomplete="off" />
      </div>
      <div class="flex gap-2">
        <button @click="addFromDiscovered" class="btn-primary text-sm">Add Connection</button>
        <button @click="selectedDiscovered = null" class="btn-secondary text-sm">Cancel</button>
      </div>
    </div>

    <!-- Manual add form -->
    <div v-if="showManualForm" class="card mb-4 space-y-3">
      <div class="flex items-center justify-between">
        <h3 class="text-sm font-medium text-dark-400">Manual Connection</h3>
        <button @click="rawMode = !rawMode" class="text-xs text-dark-500 hover:text-dark-300 transition-colors">
          {{ rawMode ? 'Structured mode' : 'Raw connection string' }}
        </button>
      </div>

      <!-- DB Type + Name -->
      <div class="grid grid-cols-2 gap-3">
        <select v-model="manualForm.db_type" class="input-field">
          <option v-for="p in presets" :key="p.key" :value="p.key">{{ p.label }}</option>
        </select>
        <input v-model="manualForm.name" placeholder="Connection name" class="input-field" />
      </div>

      <!-- Preset hint -->
      <p v-if="currentPreset?.notes && !rawMode" class="text-xs text-dark-600 -mt-1">
        {{ currentPreset.notes }}
      </p>

      <!-- Raw mode: just a textbox -->
      <div v-if="rawMode">
        <textarea
          v-model="manualForm.connection_string"
          placeholder="DRIVER={...};SERVER=...;DATABASE=...;UID=...;PWD=..."
          rows="3"
          class="input-field font-mono text-xs w-full"
        />
      </div>

      <!-- Structured mode -->
      <template v-else>
        <div class="grid grid-cols-6 gap-3">
          <input
            v-model="manualForm.host"
            :placeholder="currentPreset?.placeholder?.host || 'Host'"
            class="input-field col-span-3"
          />
          <input
            v-model="manualForm.port"
            :placeholder="String(currentPreset?.default_port || 'Port')"
            class="input-field col-span-1"
            type="number"
          />
          <input
            v-model="manualForm.database"
            :placeholder="currentPreset?.placeholder?.database || 'Database'"
            class="input-field col-span-2"
          />
        </div>
        <div class="grid grid-cols-2 gap-3">
          <input v-model="manualForm.username" placeholder="Username" class="input-field" autocomplete="off" />
          <input v-model="manualForm.password" type="password" placeholder="Password" class="input-field" autocomplete="off" />
        </div>
      </template>

      <!-- Source system -->
      <select v-model="manualForm.source_system" class="input-field">
        <option value="">-- Source System (optional) --</option>
        <option value="sap_ecc">SAP ECC</option>
        <option value="sap_b1">SAP Business One</option>
        <option value="hubspot">HubSpot</option>
        <option value="infor">Infor</option>
        <option value="other">Other</option>
      </select>

      <!-- Advanced toggle (structured mode only) -->
      <div v-if="!rawMode">
        <button @click="showAdvanced = !showAdvanced" class="text-xs text-dark-500 hover:text-dark-300 transition-colors">
          {{ showAdvanced ? 'Hide advanced' : 'Advanced options' }}
        </button>
        <div v-if="showAdvanced" class="mt-2 space-y-2">
          <div class="grid grid-cols-2 gap-3">
            <div>
              <label class="text-xs text-dark-500 mb-1 block">ODBC Driver (blank = auto-detect)</label>
              <input v-model="manualForm.driver" :placeholder="currentPreset?.drivers?.[0] || 'auto'" class="input-field font-mono text-xs" />
            </div>
            <div>
              <label class="text-xs text-dark-500 mb-1 block">Extra ODBC params</label>
              <input v-model="manualForm.extra_params" placeholder="TDS_Version=5.0;Charset=utf8" class="input-field font-mono text-xs" />
            </div>
          </div>
          <div v-if="currentPreset?.drivers?.length" class="text-xs text-dark-600">
            Known drivers: {{ currentPreset.drivers.join(', ') }}
          </div>
        </div>
      </div>

      <!-- Connection string preview -->
      <div v-if="!rawMode && connectionPreview" class="mt-1">
        <label class="text-xs text-dark-500 block mb-1">Connection string preview</label>
        <div class="bg-dark-900 border border-dark-700 rounded px-3 py-2 font-mono text-xs text-dark-400 break-all select-all">
          {{ connectionPreview }}
        </div>
      </div>

      <button @click="addManual" class="btn-primary" :disabled="rawMode ? !manualForm.connection_string : !manualForm.host">
        Save Connection
      </button>
    </div>

    <!-- Existing connections -->
    <div v-for="conn in configStore.connections" :key="conn.id" class="card mb-3">
      <div class="flex items-center justify-between">
        <div>
          <h3 class="font-medium">{{ conn.name }}</h3>
          <span class="text-xs text-dark-400">{{ dbTypeLabel(conn.db_type) }} | {{ conn.source_system || 'N/A' }}</span>
        </div>
        <div class="flex gap-2">
          <button @click="requestTestConnection(conn)" :disabled="testing === conn.id" class="btn-secondary text-xs">
            {{ testing === conn.id ? 'Testing...' : 'Test' }}
          </button>
          <button @click="requestBrowseTables(conn)" class="btn-secondary text-xs">
            Browse Tables
          </button>
          <button @click="requestDelete(conn)" class="btn-danger text-xs">Delete</button>
        </div>
      </div>
    </div>

    <!-- Table Browser (inline below connections) -->
    <TableBrowser
      v-if="browsingConnectionId"
      :connection-id="browsingConnectionId"
      :connection-name="browsingConnectionName"
      @close="browsingConnectionId = null"
      @registered="onBrowseRegistered"
    />

    <p v-if="!configStore.connections.length && !discoveredConnections.length" class="text-dark-500 text-center py-8">
      No connections yet. Click "Auto-Discover" to find existing connections, or "Manual" to add one.
    </p>

    <!-- Confirmation modal -->
    <ConfirmModal
      v-if="confirmAction"
      :title="confirmAction.title"
      :message="confirmAction.message"
      :detail="confirmAction.detail"
      :variant="confirmAction.variant"
      @confirm="onConfirmApproved"
      @cancel="confirmAction = null"
    />
  </div>
</template>
