// tdd-guard:skip — DB plumbing; tested via lib/offline/__tests__/db.test.ts
// ── IndexedDB schema via Dexie ───────────────────────────────────────────────
// Single shared database for the entire app.
// To add a new store: create a version(N+1).stores({}) block — never modify
// an existing version. See docs/OFFLINE_ARCHITECTURE.md for guidance.

import Dexie, { type Table } from 'dexie'
import { logError } from '@/lib/logger'
import type { SyncQueueEntry, CacheMetadata, SyncConflict } from './types'

export const OFFLINE_QUEUE_BACKUP_KEY = 'kadi:offline-queue-backup:v1'

// ── Local entity types ────────────────────────────────────────────────────────
// Keep in sync with the Supabase schema. Extra fields (like _syncStatus) are
// stripped before pushing to Supabase by the sync engine.

export type LocalSyncStatus = 'synced' | 'pending' | 'error' | 'conflict'

/** Generic local record with sync metadata — used for read-only stores. */
export type LocalRecord = Record<string, unknown> & {
  id: string
  _syncStatus: LocalSyncStatus
}

// Assessment
export interface LocalAssessmentResponse {
  id: string
  assessment_id: string
  question_id: string
  selected_rating: number | null
  comment: string | null
  is_flagged: boolean
  is_relevant: boolean
  updated_at: string
  updated_by?: string | null
  _syncStatus: LocalSyncStatus
}

export interface LocalAssessment {
  id: string
  title: string
  location: string | null
  assessment_number: string | null
  status: string
  created_at: string
  updated_at: string | null
  _syncStatus: LocalSyncStatus
}

// Projects
export interface LocalProject {
  id: string
  user_id: string
  supplier_name: string
  plant_location: string | null
  product_name: string | null
  visit_date: string
  customer_takt_time_sec: number | null
  planned_oee: number | null
  target_cycle_time_sec: number | null
  notes: string | null
  status: string
  created_at: string
  updated_at: string | null
  _syncStatus: LocalSyncStatus
}

// Planning
export interface LocalAssignment {
  id: string
  consultant_id: string
  date: string          // YYYY-MM-DD
  appointment_type_id: string
  planning_project_id: string | null
  status: string
  notes: string | null
  updated_at: string
  _syncStatus: LocalSyncStatus
}

// LSC
export interface LocalLscShift {
  id: string
  project_id: string
  area_name: string
  shift_number: 1 | 2 | 3
  shift_start: string
  shift_end: string
  planned_breaks_min: number
  planned_downtime_min: number
  setup_min: number
  other_losses_min: number
  created_at: string | null
  updated_at: string | null
  _syncStatus: LocalSyncStatus
}

export interface LocalLscShiftHour {
  id: string
  project_id: string
  shift_date: string
  shift_label: string
  hour_start: number
  plan_output: number
  ist_output: number
  remarks: string | null
  created_at: string | null
  updated_at: string | null
  _syncStatus: LocalSyncStatus
}

// ── Database class ────────────────────────────────────────────────────────────
class SupplierDevDB extends Dexie {
  // Infrastructure
  syncQueue!:    Table<SyncQueueEntry, number>
  cacheMetadata!: Table<CacheMetadata, string>
  syncConflicts!: Table<SyncConflict, number>

  // Assessment
  assessmentResponses!: Table<LocalAssessmentResponse, string>
  assessments!:         Table<LocalAssessment, string>
  assessmentQuestions!: Table<LocalRecord, string>

  // Planning
  assignments!:     Table<LocalAssignment, string>
  consultants!:     Table<LocalRecord, string>
  appointmentTypes!: Table<LocalRecord, string>

  // Projects
  projects!:           Table<LocalProject, string>
  processSteps!:       Table<LocalRecord, string>
  cycleMeasurements!:  Table<LocalRecord, string>
  shiftOutputs!:       Table<LocalRecord, string>
  workshopActions!:    Table<LocalRecord, string>
  qafUploads!:         Table<LocalRecord, string>

  // LSC
  lscShifts!:          Table<LocalLscShift, string>
  lscShiftHours!:      Table<LocalLscShiftHour, string>

  // Account
  userProfiles!:       Table<LocalRecord, string>

  constructor() {
    super('SupplierDevDB')

    // Single consolidated schema — all stores in one version.
    // If a browser has an older version (1–4 from dev), getDB() will detect
    // the VersionError, delete the DB, and recreate it cleanly.
    this.version(1).stores({
      // Infrastructure
      syncQueue:           '++id, table, status, createdAt',
      cacheMetadata:       'key, lastFetchedAt',
      syncConflicts:       '++id, table, recordId, occurredAt',
      // Assessment
      assessmentResponses: 'id, assessment_id, question_id, _syncStatus',
      assessments:         'id, status, _syncStatus',
      assessmentQuestions: 'id, main_category_id, sub_category_id, is_active, _syncStatus',
      // Planning
      assignments:         'id, consultant_id, date, _syncStatus',
      consultants:         'id, team_code, is_active, _syncStatus',
      appointmentTypes:    'id, _syncStatus',
      // Projects
      projects:            'id, user_id, status, visit_date, _syncStatus',
      processSteps:        'id, project_id, step_order, _syncStatus',
      cycleMeasurements:   'id, process_step_id, measurement_date, _syncStatus',
      shiftOutputs:        'id, project_id, shift_date, _syncStatus',
      workshopActions:     'id, project_id, status, _syncStatus',
      qafUploads:          'id, project_id, _syncStatus',
      // Account
      userProfiles:        'id, _syncStatus',
    })

    // version(2) — LSC shift stores (additive; version(1) stores unchanged)
    this.version(2).stores({
      // Infrastructure (carried forward — required by Dexie additive versioning)
      syncQueue:           '++id, table, status, createdAt',
      cacheMetadata:       'key, lastFetchedAt',
      syncConflicts:       '++id, table, recordId, occurredAt',
      // Assessment
      assessmentResponses: 'id, assessment_id, question_id, _syncStatus',
      assessments:         'id, status, _syncStatus',
      assessmentQuestions: 'id, main_category_id, sub_category_id, is_active, _syncStatus',
      // Planning
      assignments:         'id, consultant_id, date, _syncStatus',
      consultants:         'id, team_code, is_active, _syncStatus',
      appointmentTypes:    'id, _syncStatus',
      // Projects
      projects:            'id, user_id, status, visit_date, _syncStatus',
      processSteps:        'id, project_id, step_order, _syncStatus',
      cycleMeasurements:   'id, process_step_id, measurement_date, _syncStatus',
      shiftOutputs:        'id, project_id, shift_date, _syncStatus',
      workshopActions:     'id, project_id, status, _syncStatus',
      qafUploads:          'id, project_id, _syncStatus',
      // Account
      userProfiles:        'id, _syncStatus',
      // LSC — NEW in v2
      lscShifts:           'id, project_id, area_name, _syncStatus',
      lscShiftHours:       'id, project_id, shift_date, shift_label, _syncStatus',
    })
  }
}

// ── Singleton ─────────────────────────────────────────────────────────────────

let _db: SupplierDevDB | null = null

export function getDB(): SupplierDevDB {
  if (typeof window === 'undefined') {
    throw new Error('getDB() must only be called in browser context')
  }
  if (!_db) {
    const db = new SupplierDevDB()
    // If the browser has an older version (e.g. v2/v3/v4 from dev sessions),
    // Dexie will throw a VersionError when it tries to open. Detect that, wipe
    // the database, and create a fresh instance. OfflineSafeWrapper will catch
    // any error that surfaces to the render phase in the meantime.
    //
    // Before deleting, we attempt to back up the pending queue entries to
    // localStorage so they can be restored after the DB is recreated.
    db.open().catch((err: unknown) => {
      const name = err instanceof Error ? err.name : String(err)
      if (name === 'VersionError' || name === 'InvalidStateError') {
        logError('SupplierDevDB:versionConflict', err)
        _db = null

        // Best-effort backup of pending queue entries before deleting the DB
        const backupAndDelete = async () => {
          try {
            const entries = await db.syncQueue
              .where('status')
              .equals('pending')
              .toArray()
            if (entries.length > 0) {
              localStorage.setItem(OFFLINE_QUEUE_BACKUP_KEY, JSON.stringify(entries))
            }
          } catch {
            // Backup failure must not block DB deletion
          }
          Dexie.delete('SupplierDevDB').catch(() => {})
        }
        backupAndDelete()
      }
    })
    _db = db

    // Attempt to restore a backed-up queue from a previous VersionError wipe.
    // Safe to call every time — it's a no-op when no backup exists.
    db.on('ready', () => {
      const raw = typeof localStorage !== 'undefined'
        ? localStorage.getItem(OFFLINE_QUEUE_BACKUP_KEY)
        : null
      if (!raw) return
      try {
        const entries = JSON.parse(raw) as SyncQueueEntry[]
        if (!Array.isArray(entries) || entries.length === 0) return
        const toRestore = entries.map((e) => {
          // Strip the old auto-id so Dexie assigns a new one
          // eslint-disable-next-line @typescript-eslint/no-unused-vars
          const { id: _droppedId, ...rest } = e as SyncQueueEntry & { id?: number }
          return rest as SyncQueueEntry
        })
        db.syncQueue.bulkAdd(toRestore).catch(() => {})
        localStorage.removeItem(OFFLINE_QUEUE_BACKUP_KEY)
      } catch {
        // Restore failure is non-fatal
      }
    })
  }
  return _db
}

/**
 * Access a Dexie store by name. Used by the generic repository so it doesn't
 * need per-table switch statements.
 */
export function getStoreByName(storeName: string): Table<LocalRecord, string> {
  const db = getDB()
  return (db as unknown as Record<string, Table<LocalRecord, string>>)[storeName]
}

export type { SupplierDevDB }