import Foundation
import HealthKit
import SQLite3

enum LocalHealthKitSQLiteError: LocalizedError {
  case databaseNotFound
  case databaseOpenFailed(String)
  case queryFailed(String)

  var errorDescription: String? {
    switch self {
    case .databaseNotFound:
      return "未找到本地健康数据 export.sqlite"
    case .databaseOpenFailed(let message):
      return "打开本地健康数据库失败：\(message)"
    case .queryFailed(let message):
      return "查询本地健康数据库失败：\(message)"
    }
  }
}

final class LocalHealthKitSQLiteReader {
  private let databaseURL: URL

  init(databaseURL: URL? = nil) throws {
    guard let databaseURL = databaseURL ?? Self.defaultDatabaseURL() else {
      throw LocalHealthKitSQLiteError.databaseNotFound
    }
    self.databaseURL = databaseURL
  }

  func fetchData(
    for dataType: NativeHealthDataType,
    startDate: Date,
    endDate: Date
  ) async throws -> [NativeHealthDataPoint] {
    switch dataType {
    case .sleep:
      return []
    case .activeEnergy:
      return try await fetchActivitySummaryData(
        dataType: .activeEnergy,
        valueColumn: "active_energy_burned",
        multiplier: 1,
        startDate: startDate,
        endDate: endDate
      )
    case .exercise:
      return try await fetchActivitySummaryData(
        dataType: .exercise,
        valueColumn: "apple_exercise_time",
        multiplier: 60,
        startDate: startDate,
        endDate: endDate
      )
    case .stand:
      return try await fetchActivitySummaryData(
        dataType: .stand,
        valueColumn: "apple_stand_hours",
        multiplier: 1,
        startDate: startDate,
        endDate: endDate
      )
    case .irregularHeartRhythm:
      return try await fetchRawCategoryData(
        type: healthKitIdentifier(for: dataType),
        dataType: dataType,
        startDate: startDate,
        endDate: endDate
      ).map { NativeHealthDataPoint(dataType: $0.dataType, time: $0.endTime, value: $0.value) }
    default:
      return try await fetchRawQuantityData(
        type: healthKitIdentifier(for: dataType),
        dataType: dataType,
        startDate: startDate,
        endDate: endDate
      ).map { point in
        let value = dataType == .oxygenSaturation ? point.value * 100 : point.value
        return NativeHealthDataPoint(dataType: point.dataType, time: point.endTime, value: value)
      }
    }
  }

  func fetchRawData(
    for dataType: NativeHealthDataType,
    startDate: Date,
    endDate: Date
  ) async throws -> [NativeHealthRawDataPoint] {
    switch dataType {
    case .sleep:
      return []
    case .irregularHeartRhythm:
      return try await fetchRawCategoryData(
        type: healthKitIdentifier(for: dataType),
        dataType: dataType,
        startDate: startDate,
        endDate: endDate
      )
    default:
      let points = try await fetchRawQuantityData(
        type: healthKitIdentifier(for: dataType),
        dataType: dataType,
        startDate: startDate,
        endDate: endDate
      )
      guard dataType == .oxygenSaturation else {
        return points
      }
      return points.map {
        NativeHealthRawDataPoint(
          dataType: $0.dataType,
          startTime: $0.startTime,
          endTime: $0.endTime,
          value: $0.value * 100,
          isMotionLike: nil
        )
      }
    }
  }

  func fetchSleepData(startDate: Date, endDate: Date) async throws -> [NativeSleepInterval] {
    try await fetchSleepIntervals(startDate: startDate, endDate: endDate)
  }

  func fetchActivityTargetData(startDate: Date, endDate: Date) async throws -> NativeActivityTarget? {
    try await fetchActivityTargetDataList(startDate: startDate, endDate: endDate).last
  }

  func fetchActivityTargetDataList(startDate: Date, endDate: Date) async throws -> [NativeActivityTarget] {
    try await fetchActivitySummaryRows(startDate: startDate, endDate: endDate)
      .map { row in
        NativeActivityTarget(
          healthValueTimestamp: row.day.timeIntervalSince1970,
          move: row.activeEnergyBurnedGoal.map(Int.init),
          stand: row.appleStandHoursGoal.map(Int.init),
          activityMoveMode: nil,
          activeEnergyBurned: row.activeEnergyBurned,
          activeEnergyBurnedGoal: row.activeEnergyBurnedGoal,
          appleMoveTime: row.appleMoveTime.map { $0 * 60 },
          appleMoveTimeGoal: row.appleMoveTimeGoal.map { $0 * 60 },
          appleExerciseTime: row.appleExerciseTime.map { $0 * 60 },
          exerciseTimeGoal: row.appleExerciseTimeGoal.map { $0 * 60 },
          appleStandHours: row.appleStandHours,
          standHoursGoal: row.appleStandHoursGoal
        )
      }
  }

  func fetchWorkoutDataList(startDate: Date, endDate: Date) async throws -> [NativeWorkoutInterval] {
    let endDateText = Self.makeDateFormatter().string(from: endDate)
    let startDateText = Self.makeDateFormatter().string(from: startDate)

    return try await Task.detached(priority: .utility) { [databaseURL] in
      let dateFormatter = Self.makeDateFormatter()
      let database = try Self.openDatabase(at: databaseURL)
      defer { sqlite3_close(database) }

      let sql = """
      SELECT workout_activity_type, start_date, end_date
      FROM workouts
      WHERE start_date <= ?
        AND end_date >= ?
      ORDER BY end_date ASC
      """
      return try Self.readRows(
        database: database,
        sql: sql,
        values: [endDateText, startDateText]
      ) { statement in
        guard
          let workoutTypeText = Self.textColumn(statement, index: 0),
          let startText = Self.textColumn(statement, index: 1),
          let endText = Self.textColumn(statement, index: 2),
          let start = dateFormatter.date(from: startText),
          let end = dateFormatter.date(from: endText)
        else {
          return nil
        }
        return NativeWorkoutInterval(
          workoutType: Self.workoutActivityTypeRawValue(for: workoutTypeText),
          startTime: start.timeIntervalSince1970,
          endTime: end.timeIntervalSince1970
        )
      }
    }.value
  }

  private func fetchRawQuantityData(
    type: String,
    dataType: NativeHealthDataType,
    startDate: Date,
    endDate: Date
  ) async throws -> [NativeHealthRawDataPoint] {
    let records: [NativeHealthRawDataPoint] = try await fetchRecords(
      type: type,
      startDate: startDate,
      endDate: endDate,
      columns: "value, start_date, end_date"
    ) { statement, dateFormatter in
      guard
        let valueText = Self.textColumn(statement, index: 0),
        let value = Double(valueText),
        let startText = Self.textColumn(statement, index: 1),
        let endText = Self.textColumn(statement, index: 2),
        let start = dateFormatter.date(from: startText),
        let end = dateFormatter.date(from: endText)
      else {
        return nil
      }
      return NativeHealthRawDataPoint(
        dataType: dataType,
        startTime: start.timeIntervalSince1970,
        endTime: end.timeIntervalSince1970,
        value: value,
        isMotionLike: nil
      )
    }
    return records
  }

  private func fetchRawCategoryData(
    type: String,
    dataType: NativeHealthDataType,
    startDate: Date,
    endDate: Date
  ) async throws -> [NativeHealthRawDataPoint] {
    try await fetchRecords(
      type: type,
      startDate: startDate,
      endDate: endDate,
      columns: "value, start_date, end_date"
    ) { statement, dateFormatter in
      guard
        let startText = Self.textColumn(statement, index: 1),
        let endText = Self.textColumn(statement, index: 2),
        let start = dateFormatter.date(from: startText),
        let end = dateFormatter.date(from: endText)
      else {
        return nil
      }
      return NativeHealthRawDataPoint(
        dataType: dataType,
        startTime: start.timeIntervalSince1970,
        endTime: end.timeIntervalSince1970,
        value: Double(Self.textColumn(statement, index: 0) ?? "") ?? 1,
        isMotionLike: nil
      )
    }
  }

  private func fetchSleepIntervals(startDate: Date, endDate: Date) async throws -> [NativeSleepInterval] {
    try await fetchRecords(
      type: "HKCategoryTypeIdentifierSleepAnalysis",
      startDate: startDate,
      endDate: endDate,
      columns: "value, start_date, end_date"
    ) { statement, dateFormatter in
      guard
        let startText = Self.textColumn(statement, index: 1),
        let endText = Self.textColumn(statement, index: 2),
        let start = dateFormatter.date(from: startText),
        let end = dateFormatter.date(from: endText)
      else {
        return nil
      }
      return NativeSleepInterval(
        dataType: Int(Double(Self.textColumn(statement, index: 0) ?? "") ?? 0),
        fromTime: start.timeIntervalSince1970,
        toTime: end.timeIntervalSince1970
      )
    }
  }

  private func fetchRecords<T>(
    type: String,
    startDate: Date,
    endDate: Date,
    columns: String,
    transform: @escaping (OpaquePointer?, DateFormatter) -> T?
  ) async throws -> [T] {
    let endDateText = Self.makeDateFormatter().string(from: endDate)
    let startDateText = Self.makeDateFormatter().string(from: startDate)

    return try await Task.detached(priority: .utility) { [databaseURL] in
      let dateFormatter = Self.makeDateFormatter()
      let database = try Self.openDatabase(at: databaseURL)
      defer { sqlite3_close(database) }

      let sql = """
      SELECT \(columns)
      FROM records
      WHERE type = ?
        AND start_date <= ?
        AND end_date >= ?
      ORDER BY end_date ASC
      """
      return try Self.readRows(
        database: database,
        sql: sql,
        values: [type, endDateText, startDateText]
      ) { statement in
        transform(statement, dateFormatter)
      }
    }.value
  }

  private func fetchActivitySummaryData(
    dataType: NativeHealthDataType,
    valueColumn: String,
    multiplier: Double,
    startDate: Date,
    endDate: Date
  ) async throws -> [NativeHealthDataPoint] {
    try await fetchActivitySummaryRows(startDate: startDate, endDate: endDate)
      .compactMap { row in
        guard let value = row.value(for: valueColumn) else {
          return nil
        }
        return NativeHealthDataPoint(
          dataType: dataType,
          time: Self.uploadTimeForDailyPoint(day: row.day, endDate: endDate).timeIntervalSince1970,
          value: value * multiplier
        )
      }
  }

  private func fetchActivitySummaryRows(
    startDate: Date,
    endDate: Date
  ) async throws -> [LocalActivitySummaryRow] {
    let startDayText = Self.makeDayFormatter().string(from: Calendar.current.startOfDay(for: startDate))
    let endDayText = Self.makeDayFormatter().string(from: Calendar.current.startOfDay(for: endDate))

    return try await Task.detached(priority: .utility) { [databaseURL] in
      let dayFormatter = Self.makeDayFormatter()
      let database = try Self.openDatabase(at: databaseURL)
      defer { sqlite3_close(database) }

      let sql = """
      SELECT date_components,
             active_energy_burned,
             active_energy_burned_goal,
             apple_move_time,
             apple_move_time_goal,
             apple_exercise_time,
             apple_exercise_time_goal,
             apple_stand_hours,
             apple_stand_hours_goal
      FROM activity_summaries
      WHERE date_components >= ?
        AND date_components <= ?
      ORDER BY date_components ASC
      """
      return try Self.readRows(database: database, sql: sql, values: [startDayText, endDayText]) { statement in
        guard
          let dayText = Self.textColumn(statement, index: 0),
          let day = dayFormatter.date(from: dayText)
        else {
          return nil
        }
        return LocalActivitySummaryRow(
          day: day,
          activeEnergyBurned: Self.doubleColumn(statement, index: 1),
          activeEnergyBurnedGoal: Self.doubleColumn(statement, index: 2),
          appleMoveTime: Self.doubleColumn(statement, index: 3),
          appleMoveTimeGoal: Self.doubleColumn(statement, index: 4),
          appleExerciseTime: Self.doubleColumn(statement, index: 5),
          appleExerciseTimeGoal: Self.doubleColumn(statement, index: 6),
          appleStandHours: Self.doubleColumn(statement, index: 7),
          appleStandHoursGoal: Self.doubleColumn(statement, index: 8)
        )
      }
    }.value
  }

  private func healthKitIdentifier(for dataType: NativeHealthDataType) -> String {
    switch dataType {
    case .hrv: return "HKQuantityTypeIdentifierHeartRateVariabilitySDNN"
    case .heartRate, .sleepingHeartRate: return "HKQuantityTypeIdentifierHeartRate"
    case .oxygenSaturation: return "HKQuantityTypeIdentifierOxygenSaturation"
    case .activeEnergy: return "HKQuantityTypeIdentifierActiveEnergyBurned"
    case .exercise: return "HKQuantityTypeIdentifierAppleExerciseTime"
    case .stand: return "HKQuantityTypeIdentifierAppleStandTime"
    case .steps: return "HKQuantityTypeIdentifierStepCount"
    case .walkingHeartRate: return "HKQuantityTypeIdentifierWalkingHeartRateAverage"
    case .restingHeartRate: return "HKQuantityTypeIdentifierRestingHeartRate"
    case .sleepingWristTemperature: return "HKQuantityTypeIdentifierAppleSleepingWristTemperature"
    case .respiratoryRate: return "HKQuantityTypeIdentifierRespiratoryRate"
    case .irregularHeartRhythm: return "HKCategoryTypeIdentifierIrregularHeartRhythmEvent"
    case .sleep: return "HKCategoryTypeIdentifierSleepAnalysis"
    }
  }

  private static func defaultDatabaseURL() -> URL? {
    let bundle = Bundle.main
    if let url = bundle.url(forResource: "export", withExtension: "sqlite") {
      return url
    }
    let sourceURL = URL(fileURLWithPath: #filePath)
      .deletingLastPathComponent()
      .appendingPathComponent("export.sqlite")
    if FileManager.default.fileExists(atPath: sourceURL.path) {
      return sourceURL
    }
    return nil
  }

  private nonisolated static func makeDateFormatter() -> DateFormatter {
    let formatter = DateFormatter()
    formatter.locale = Locale(identifier: "en_US_POSIX")
    formatter.dateFormat = "yyyy-MM-dd HH:mm:ss Z"
    return formatter
  }

  private nonisolated static func makeDayFormatter() -> DateFormatter {
    let formatter = DateFormatter()
    formatter.locale = Locale(identifier: "en_US_POSIX")
    formatter.dateFormat = "yyyy-MM-dd"
    return formatter
  }

  private nonisolated static func uploadTimeForDailyPoint(day: Date, endDate: Date) -> Date {
    let calendar = Calendar.current
    let start = calendar.startOfDay(for: day)
    let nextDay = calendar.date(byAdding: .day, value: 1, to: start) ?? endDate
    return min(nextDay.addingTimeInterval(-1), endDate)
  }

  private nonisolated static func workoutActivityTypeRawValue(for identifier: String) -> Int {
    if let rawValue = Int(identifier) {
      return rawValue
    }
    if let type = workoutActivityTypeByIdentifier[identifier] {
      return Int(type.rawValue)
    }
    return 0
  }

  private nonisolated static let workoutActivityTypeByIdentifier: [String: HKWorkoutActivityType] = [
    "HKWorkoutActivityTypeAmericanFootball": .americanFootball,
    "HKWorkoutActivityTypeArchery": .archery,
    "HKWorkoutActivityTypeAustralianFootball": .australianFootball,
    "HKWorkoutActivityTypeBadminton": .badminton,
    "HKWorkoutActivityTypeBaseball": .baseball,
    "HKWorkoutActivityTypeBasketball": .basketball,
    "HKWorkoutActivityTypeBowling": .bowling,
    "HKWorkoutActivityTypeBoxing": .boxing,
    "HKWorkoutActivityTypeClimbing": .climbing,
    "HKWorkoutActivityTypeCricket": .cricket,
    "HKWorkoutActivityTypeCrossTraining": .crossTraining,
    "HKWorkoutActivityTypeCurling": .curling,
    "HKWorkoutActivityTypeCycling": .cycling,
    "HKWorkoutActivityTypeDance": .dance,
    "HKWorkoutActivityTypeDanceInspiredTraining": .danceInspiredTraining,
    "HKWorkoutActivityTypeElliptical": .elliptical,
    "HKWorkoutActivityTypeEquestrianSports": .equestrianSports,
    "HKWorkoutActivityTypeFencing": .fencing,
    "HKWorkoutActivityTypeFishing": .fishing,
    "HKWorkoutActivityTypeFunctionalStrengthTraining": .functionalStrengthTraining,
    "HKWorkoutActivityTypeGolf": .golf,
    "HKWorkoutActivityTypeGymnastics": .gymnastics,
    "HKWorkoutActivityTypeHandball": .handball,
    "HKWorkoutActivityTypeHiking": .hiking,
    "HKWorkoutActivityTypeHockey": .hockey,
    "HKWorkoutActivityTypeHunting": .hunting,
    "HKWorkoutActivityTypeLacrosse": .lacrosse,
    "HKWorkoutActivityTypeMartialArts": .martialArts,
    "HKWorkoutActivityTypeMindAndBody": .mindAndBody,
    "HKWorkoutActivityTypeMixedMetabolicCardioTraining": .mixedMetabolicCardioTraining,
    "HKWorkoutActivityTypePaddleSports": .paddleSports,
    "HKWorkoutActivityTypePlay": .play,
    "HKWorkoutActivityTypePreparationAndRecovery": .preparationAndRecovery,
    "HKWorkoutActivityTypeRacquetball": .racquetball,
    "HKWorkoutActivityTypeRowing": .rowing,
    "HKWorkoutActivityTypeRugby": .rugby,
    "HKWorkoutActivityTypeRunning": .running,
    "HKWorkoutActivityTypeSailing": .sailing,
    "HKWorkoutActivityTypeSkatingSports": .skatingSports,
    "HKWorkoutActivityTypeSnowSports": .snowSports,
    "HKWorkoutActivityTypeSoccer": .soccer,
    "HKWorkoutActivityTypeSoftball": .softball,
    "HKWorkoutActivityTypeSquash": .squash,
    "HKWorkoutActivityTypeStairClimbing": .stairClimbing,
    "HKWorkoutActivityTypeSurfingSports": .surfingSports,
    "HKWorkoutActivityTypeSwimming": .swimming,
    "HKWorkoutActivityTypeTableTennis": .tableTennis,
    "HKWorkoutActivityTypeTennis": .tennis,
    "HKWorkoutActivityTypeTrackAndField": .trackAndField,
    "HKWorkoutActivityTypeTraditionalStrengthTraining": .traditionalStrengthTraining,
    "HKWorkoutActivityTypeVolleyball": .volleyball,
    "HKWorkoutActivityTypeWalking": .walking,
    "HKWorkoutActivityTypeWaterFitness": .waterFitness,
    "HKWorkoutActivityTypeWaterPolo": .waterPolo,
    "HKWorkoutActivityTypeWaterSports": .waterSports,
    "HKWorkoutActivityTypeWrestling": .wrestling,
    "HKWorkoutActivityTypeYoga": .yoga,
    "HKWorkoutActivityTypeBarre": .barre,
    "HKWorkoutActivityTypeCoreTraining": .coreTraining,
    "HKWorkoutActivityTypeCrossCountrySkiing": .crossCountrySkiing,
    "HKWorkoutActivityTypeDownhillSkiing": .downhillSkiing,
    "HKWorkoutActivityTypeFlexibility": .flexibility,
    "HKWorkoutActivityTypeHighIntensityIntervalTraining": .highIntensityIntervalTraining,
    "HKWorkoutActivityTypeJumpRope": .jumpRope,
    "HKWorkoutActivityTypeKickboxing": .kickboxing,
    "HKWorkoutActivityTypePilates": .pilates,
    "HKWorkoutActivityTypeSnowboarding": .snowboarding,
    "HKWorkoutActivityTypeStairs": .stairs,
    "HKWorkoutActivityTypeStepTraining": .stepTraining,
    "HKWorkoutActivityTypeWheelchairWalkPace": .wheelchairWalkPace,
    "HKWorkoutActivityTypeWheelchairRunPace": .wheelchairRunPace,
    "HKWorkoutActivityTypeTaiChi": .taiChi,
    "HKWorkoutActivityTypeMixedCardio": .mixedCardio,
    "HKWorkoutActivityTypeHandCycling": .handCycling,
    "HKWorkoutActivityTypeDiscSports": .discSports,
    "HKWorkoutActivityTypeFitnessGaming": .fitnessGaming,
    "HKWorkoutActivityTypeCardioDance": .cardioDance,
    "HKWorkoutActivityTypeSocialDance": .socialDance,
    "HKWorkoutActivityTypePickleball": .pickleball,
    "HKWorkoutActivityTypeCooldown": .cooldown,
    "HKWorkoutActivityTypeSwimBikeRun": .swimBikeRun,
    "HKWorkoutActivityTypeTransition": .transition,
    "HKWorkoutActivityTypeUnderwaterDiving": .underwaterDiving,
    "HKWorkoutActivityTypeOther": .other,
  ]

  private nonisolated static func openDatabase(at url: URL) throws -> OpaquePointer? {
    var database: OpaquePointer?
    let uri = url.absoluteString + "?immutable=1"
    let flags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI
    guard sqlite3_open_v2(uri, &database, flags, nil) == SQLITE_OK else {
      let message = database.map { String(cString: sqlite3_errmsg($0)) } ?? "unknown"
      sqlite3_close(database)
      throw LocalHealthKitSQLiteError.databaseOpenFailed("\(message), path=\(url.path)")
    }
    return database
  }

  private nonisolated static func readRows<T>(
    database: OpaquePointer?,
    sql: String,
    values: [String],
    transform: (OpaquePointer?) -> T?
  ) throws -> [T] {
    var statement: OpaquePointer?
    guard sqlite3_prepare_v2(database, sql, -1, &statement, nil) == SQLITE_OK else {
      throw LocalHealthKitSQLiteError.queryFailed(String(cString: sqlite3_errmsg(database)))
    }
    defer { sqlite3_finalize(statement) }

    let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    for (index, value) in values.enumerated() {
      sqlite3_bind_text(statement, Int32(index + 1), value, -1, transient)
    }

    var rows: [T] = []
    while true {
      let step = sqlite3_step(statement)
      if step == SQLITE_ROW {
        if let row = transform(statement) {
          rows.append(row)
        }
      } else if step == SQLITE_DONE {
        return rows
      } else {
        throw LocalHealthKitSQLiteError.queryFailed(String(cString: sqlite3_errmsg(database)))
      }
    }
  }

  private nonisolated static func textColumn(_ statement: OpaquePointer?, index: Int32) -> String? {
    guard let text = sqlite3_column_text(statement, index) else {
      return nil
    }
    return String(cString: text)
  }

  private nonisolated static func doubleColumn(_ statement: OpaquePointer?, index: Int32) -> Double? {
    guard let text = textColumn(statement, index: index) else {
      return nil
    }
    return Double(text)
  }
}

private struct LocalActivitySummaryRow {
  let day: Date
  let activeEnergyBurned: Double?
  let activeEnergyBurnedGoal: Double?
  let appleMoveTime: Double?
  let appleMoveTimeGoal: Double?
  let appleExerciseTime: Double?
  let appleExerciseTimeGoal: Double?
  let appleStandHours: Double?
  let appleStandHoursGoal: Double?

  func value(for column: String) -> Double? {
    switch column {
    case "active_energy_burned":
      return activeEnergyBurned
    case "apple_exercise_time":
      return appleExerciseTime
    case "apple_stand_hours":
      return appleStandHours
    default:
      return nil
    }
  }
}