require "sqlite3"
require "../models/user"
require "../models/list"

class UserStore
  @db : DB::Database

  def initialize(db_path : String)
    @db = DB.open("sqlite3://#{db_path}")
    init_schema
  end

  def close
    @db.close
  end

  private def init_schema
    schema = {{ read_file("#{__DIR__}/../db/schema.sql") }}
    schema.split(";").each do |statement|
      stmt = statement.strip
      next if stmt.empty?
      @db.exec(stmt)
    end
  end

  # ============================================
  # User CRUD
  # ============================================

  def create_user(user : User) : User
    @db.exec(
      <<-SQL,
      INSERT INTO users (did, handle, pds_url, display_name, avatar_url, token, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      SQL
      user.did, user.handle, user.pds_url, user.display_name, user.avatar_url,
      user.token, user.created_at.to_unix, user.updated_at.to_unix
    )
    user
  end

  def find_by_did(did : String) : User?
    @db.query_one?(
      "SELECT did, handle, pds_url, display_name, avatar_url, token, created_at, updated_at FROM users WHERE did = ?",
      did
    ) do |rs|
      user_from_row(rs)
    end
  end

  def find_by_handle(handle : String) : User?
    @db.query_one?(
      "SELECT did, handle, pds_url, display_name, avatar_url, token, created_at, updated_at FROM users WHERE handle = ?",
      handle
    ) do |rs|
      user_from_row(rs)
    end
  end

  def find_by_token(token : String) : User?
    @db.query_one?(
      "SELECT did, handle, pds_url, display_name, avatar_url, token, created_at, updated_at FROM users WHERE token = ?",
      token
    ) do |rs|
      user_from_row(rs)
    end
  end

  def upsert_user(user : User) : User
    existing = find_by_did(user.did)
    if existing
      @db.exec(
        <<-SQL,
        UPDATE users SET handle = ?, pds_url = ?, display_name = ?, avatar_url = ?, updated_at = ?
        WHERE did = ?
        SQL
        user.handle, user.pds_url, user.display_name, user.avatar_url,
        Time.utc.to_unix, user.did
      )
      find_by_did(user.did).not_nil!
    else
      create_user(user)
    end
  end

  private def user_from_row(rs) : User
    User.new(
      did: rs.read(String),
      handle: rs.read(String),
      pds_url: rs.read(String?),
      display_name: rs.read(String?),
      avatar_url: rs.read(String?),
      token: rs.read(String),
      created_at: Time.unix(rs.read(Int64)),
      updated_at: Time.unix(rs.read(Int64))
    )
  end

  # ============================================
  # AT Proto Session Storage
  # ============================================

  def save_atproto_session(did : String, access_token : String, refresh_token : String?, dpop_private_key : String, expires_at : Time)
    @db.exec(
      <<-SQL,
      INSERT OR REPLACE INTO atproto_sessions (did, access_token, refresh_token, dpop_private_key, expires_at)
      VALUES (?, ?, ?, ?, ?)
      SQL
      did, access_token, refresh_token, dpop_private_key, expires_at.to_unix
    )
  end

  def get_atproto_session(did : String) : NamedTuple(access_token: String, refresh_token: String?, dpop_private_key: String, expires_at: Time)?
    @db.query_one?(
      "SELECT access_token, refresh_token, dpop_private_key, expires_at FROM atproto_sessions WHERE did = ?",
      did
    ) do |rs|
      {
        access_token:    rs.read(String),
        refresh_token:   rs.read(String?),
        dpop_private_key: rs.read(String),
        expires_at:      Time.unix(rs.read(Int64)),
      }
    end
  end

  # ============================================
  # OAuth State Management
  # ============================================

  def save_oauth_state(state : String, handle : String, code_verifier : String, dpop_private_key : String, auth_server_metadata_json : String, is_native : Bool = false)
    @db.exec(
      <<-SQL,
      INSERT INTO oauth_states (state, handle, code_verifier, dpop_private_key, auth_server_metadata_json, is_native, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      SQL
      state, handle, code_verifier, dpop_private_key, auth_server_metadata_json,
      is_native ? 1 : 0, Time.utc.to_unix
    )
  end

  def get_oauth_state(state : String) : NamedTuple(state: String, handle: String, code_verifier: String, dpop_private_key: String, auth_server_metadata_json: String, is_native: Bool, created_at: Time)?
    @db.query_one?(
      "SELECT state, handle, code_verifier, dpop_private_key, auth_server_metadata_json, is_native, created_at FROM oauth_states WHERE state = ?",
      state
    ) do |rs|
      {
        state:                      rs.read(String),
        handle:                     rs.read(String),
        code_verifier:              rs.read(String),
        dpop_private_key:           rs.read(String),
        auth_server_metadata_json:  rs.read(String),
        is_native:                  rs.read(Int64) == 1,
        created_at:                 Time.unix(rs.read(Int64)),
      }
    end
  end

  def delete_oauth_state(state : String)
    @db.exec("DELETE FROM oauth_states WHERE state = ?", state)
  end

  def cleanup_old_oauth_states(max_age : Time::Span = 10.minutes)
    cutoff = (Time.utc - max_age).to_unix
    @db.exec("DELETE FROM oauth_states WHERE created_at < ?", cutoff)
  end

  # ============================================
  # Invite Management
  # ============================================

  def create_invite(code : String)
    @db.exec(
      "INSERT INTO invites (code, created_at) VALUES (?, ?)",
      code, Time.utc.to_unix
    )
  end

  def use_invite(code : String, did : String) : Bool
    result = @db.exec(
      "UPDATE invites SET used_by = ?, used_at = ? WHERE code = ? AND used_by IS NULL",
      did, Time.utc.to_unix, code
    )
    result.rows_affected > 0
  end

  def invite_valid?(code : String) : Bool
    count = @db.query_one(
      "SELECT COUNT(*) FROM invites WHERE code = ? AND used_by IS NULL",
      code
    ) do |rs|
      rs.read(Int64)
    end
    count > 0
  end

  # ============================================
  # Location Sharing Lists
  # ============================================

  def create_list(list : LocationList) : LocationList
    @db.exec(
      <<-SQL,
      INSERT INTO lists (id, owner_did, name, update_interval_seconds, accuracy, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      SQL
      list.id, list.owner_did, list.name, list.update_interval_seconds,
      list.accuracy.to_s.downcase, list.created_at.to_unix, list.updated_at.to_unix
    )
    list
  end

  def get_list(id : String) : LocationList?
    @db.query_one?(
      "SELECT id, owner_did, name, update_interval_seconds, accuracy, created_at, updated_at FROM lists WHERE id = ?",
      id
    ) do |rs|
      list_from_row(rs)
    end
  end

  def get_lists_for_user(did : String) : Array(LocationList)
    lists = [] of LocationList
    @db.query(
      "SELECT id, owner_did, name, update_interval_seconds, accuracy, created_at, updated_at FROM lists WHERE owner_did = ? ORDER BY name",
      did
    ) do |rs|
      rs.each do
        lists << list_from_row(rs)
      end
    end
    lists
  end

  def update_list(list : LocationList) : LocationList
    @db.exec(
      <<-SQL,
      UPDATE lists SET name = ?, update_interval_seconds = ?, accuracy = ?, updated_at = ?
      WHERE id = ?
      SQL
      list.name, list.update_interval_seconds, list.accuracy.to_s.downcase,
      Time.utc.to_unix, list.id
    )
    list
  end

  def delete_list(id : String)
    @db.exec("DELETE FROM lists WHERE id = ?", id)
  end

  private def list_from_row(rs) : LocationList
    LocationList.new(
      id: rs.read(String),
      owner_did: rs.read(String),
      name: rs.read(String),
      update_interval_seconds: rs.read(Int64).to_i32,
      accuracy: LocationAccuracy.parse(rs.read(String)),
      created_at: Time.unix(rs.read(Int64)),
      updated_at: Time.unix(rs.read(Int64))
    )
  end

  # ============================================
  # List Members
  # ============================================

  def add_list_member(member : ListMember) : ListMember
    @db.exec(
      <<-SQL,
      INSERT INTO list_members (id, list_id, member_did, member_handle, status, created_at, accepted_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      SQL
      member.id, member.list_id, member.member_did, member.member_handle,
      member.status, member.created_at.to_unix, member.accepted_at.try(&.to_unix)
    )
    member
  end

  def get_list_member(id : String) : ListMember?
    @db.query_one?(
      "SELECT id, list_id, member_did, member_handle, status, created_at, accepted_at FROM list_members WHERE id = ?",
      id
    ) do |rs|
      list_member_from_row(rs)
    end
  end

  def get_list_members(list_id : String) : Array(ListMember)
    members = [] of ListMember
    @db.query(
      "SELECT id, list_id, member_did, member_handle, status, created_at, accepted_at FROM list_members WHERE list_id = ?",
      list_id
    ) do |rs|
      rs.each do
        members << list_member_from_row(rs)
      end
    end
    members
  end

  def find_list_member_by_did(list_id : String, member_did : String) : ListMember?
    @db.query_one?(
      "SELECT id, list_id, member_did, member_handle, status, created_at, accepted_at FROM list_members WHERE list_id = ? AND member_did = ?",
      list_id, member_did
    ) do |rs|
      list_member_from_row(rs)
    end
  end

  def update_list_member_status(id : String, status : String, accepted_at : Time? = nil)
    @db.exec(
      "UPDATE list_members SET status = ?, accepted_at = ? WHERE id = ?",
      status, accepted_at.try(&.to_unix), id
    )
  end

  def delete_list_member(id : String)
    @db.exec("DELETE FROM list_members WHERE id = ?", id)
  end

  private def list_member_from_row(rs) : ListMember
    ListMember.new(
      id: rs.read(String),
      list_id: rs.read(String),
      member_did: rs.read(String),
      member_handle: rs.read(String),
      status: rs.read(String),
      created_at: Time.unix(rs.read(Int64)),
      accepted_at: rs.read(Int64?).try { |ts| Time.unix(ts) }
    )
  end

  # ============================================
  # Incoming Shares
  # ============================================

  def create_incoming_share(share : IncomingShare) : IncomingShare
    @db.exec(
      <<-SQL,
      INSERT INTO incoming_shares (id, local_did, remote_did, remote_handle, status, created_at, accepted_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      SQL
      share.id, share.local_did, share.remote_did, share.remote_handle,
      share.status, share.created_at.to_unix, share.accepted_at.try(&.to_unix)
    )
    share
  end

  def get_incoming_share(id : String) : IncomingShare?
    @db.query_one?(
      "SELECT id, local_did, remote_did, remote_handle, status, created_at, accepted_at FROM incoming_shares WHERE id = ?",
      id
    ) do |rs|
      incoming_share_from_row(rs)
    end
  end

  def get_incoming_shares_for_user(did : String) : Array(IncomingShare)
    shares = [] of IncomingShare
    @db.query(
      "SELECT id, local_did, remote_did, remote_handle, status, created_at, accepted_at FROM incoming_shares WHERE local_did = ? ORDER BY created_at DESC",
      did
    ) do |rs|
      rs.each do
        shares << incoming_share_from_row(rs)
      end
    end
    shares
  end

  def find_incoming_share_by_did(local_did : String, remote_did : String) : IncomingShare?
    @db.query_one?(
      "SELECT id, local_did, remote_did, remote_handle, status, created_at, accepted_at FROM incoming_shares WHERE local_did = ? AND remote_did = ?",
      local_did, remote_did
    ) do |rs|
      incoming_share_from_row(rs)
    end
  end

  def update_incoming_share_status(id : String, status : String, accepted_at : Time? = nil)
    @db.exec(
      "UPDATE incoming_shares SET status = ?, accepted_at = ? WHERE id = ?",
      status, accepted_at.try(&.to_unix), id
    )
  end

  private def incoming_share_from_row(rs) : IncomingShare
    IncomingShare.new(
      id: rs.read(String),
      local_did: rs.read(String),
      remote_did: rs.read(String),
      remote_handle: rs.read(String),
      status: rs.read(String),
      created_at: Time.unix(rs.read(Int64)),
      accepted_at: rs.read(Int64?).try { |ts| Time.unix(ts) }
    )
  end

  # ============================================
  # Symmetric sharing check
  # ============================================

  def can_share_with?(local_did : String, remote_did : String) : Bool
    we_added_them = @db.query_one?(
      <<-SQL,
      SELECT 1 FROM list_members lm
      JOIN lists l ON lm.list_id = l.id
      WHERE l.owner_did = ? AND lm.member_did = ? AND lm.status = 'active'
      LIMIT 1
      SQL
      local_did, remote_did
    ) { |rs| rs.read(Int32) }

    they_added_us = @db.query_one?(
      "SELECT 1 FROM incoming_shares WHERE local_did = ? AND remote_did = ? AND status = 'accepted' LIMIT 1",
      local_did, remote_did
    ) { |rs| rs.read(Int32) }

    !we_added_them.nil? && !they_added_us.nil?
  end

  def get_shareable_actors(local_did : String, list_ids : Array(String)) : Array({String, String, Int32, LocationAccuracy})
    return [] of {String, String, Int32, LocationAccuracy} if list_ids.empty?

    placeholders = list_ids.map { "?" }.join(", ")
    results = [] of {String, String, Int32, LocationAccuracy}
    args = [] of DB::Any
    list_ids.each { |id| args << id.as(DB::Any) }
    args << local_did.as(DB::Any)
    args << local_did.as(DB::Any)

    @db.query(
      <<-SQL,
      SELECT DISTINCT lm.member_did, lm.member_handle, l.update_interval_seconds, l.accuracy
      FROM list_members lm
      JOIN lists l ON lm.list_id = l.id
      WHERE l.id IN (#{placeholders})
        AND l.owner_did = ?
        AND lm.status = 'active'
        AND EXISTS (
          SELECT 1 FROM incoming_shares ish
          WHERE ish.local_did = ?
            AND ish.remote_did = lm.member_did
            AND ish.status = 'accepted'
        )
      SQL
      args: args
    ) do |rs|
      rs.each do
        results << {
          rs.read(String),
          rs.read(String),
          rs.read(Int64).to_i32,
          LocationAccuracy.parse(rs.read(String)),
        }
      end
    end
    results
  end
end
