# fanfarm.rb - MITライセンス同意機能追加版
require 'sinatra'
require 'sinatra/cors'
require 'json'
require 'sqlite3'
require 'bcrypt'
require 'logger'
require 'securerandom'
require 'time'

# 保護機能を無効化
set :protection, false

# 手動でCORSヘッダーを設定
before do
  headers['Access-Control-Allow-Origin'] = '*'
  headers['Access-Control-Allow-Methods'] = 'GET, POST, PUT, DELETE, OPTIONS'
  headers['Access-Control-Allow-Headers'] = 'Content-Type, Authorization'
  
  # ログ設定を修正 - 絶対パスまたは適切な相対パスを使用
  # env["rack.logger"] = Logger.new("../logs/api_server.log")  # この行を削除またはコメントアウト
end

# OPTIONSリクエストへの対応
options "*" do
  response.headers["Allow"] = "GET, POST, PUT, DELETE, OPTIONS"
  response.headers["Access-Control-Allow-Headers"] = "Content-Type, Authorization"
  response.headers["Access-Control-Allow-Origin"] = "*"
  200
end

# バインド設定
set :bind, '0.0.0.0'
set :port, 4567

# データベース接続
def db
  @db ||= SQLite3::Database.new("fanfarm-db")
  @db.results_as_hash = true
  @db
end

# ヘルパーメソッド（ライセンス同意記録用）
helpers do
  def record_license_agreement(user_id, ip_address = nil, user_agent = nil)
    db.execute(
      'INSERT INTO license_agreements (user_id, license_type, license_version, ip_address, user_agent, agreed_at) 
       VALUES (?, ?, ?, ?, ?, ?)',
      [user_id, 'MIT', 'MIT-1.0', ip_address || request.ip, user_agent || request.user_agent, Time.now.to_s]
    )
  end
end

# エラーハンドリング
error do
  content_type :json
  status 500
  { error: env['sinatra.error'].message }.to_json
end

# ルートエンドポイント
get '/' do
  { message: 'Welcome to FanFarm API!' }.to_json
end

#################################################
# ユーザー関連のエンドポイント（ライセンス同意機能追加）
#################################################

# ユーザー登録（ライセンス同意機能付き）
post '/users' do
  data = JSON.parse(request.body.read)
  
  # 入力チェック
  required_fields = ['name', 'email', 'password', 'user_type']
  missing_fields = required_fields.select { |field| data[field].nil? || data[field].empty? }
  
  if !missing_fields.empty?
    status 400
    return { error: "Missing required fields: #{missing_fields.join(', ')}" }.to_json
  end
  
  # ライセンス同意チェック（新規登録時は必須）
  if data['license_agreed'] != true && data['license_agreed'] != 'true'
    status 400
    return { error: 'ライセンスへの同意が必要です' }.to_json
  end
  
  # パスワードのハッシュ化
  hashed_password = BCrypt::Password.create(data['password'])
  
  begin
    db.transaction
    
    # ユーザー登録（ライセンス同意情報を含む）
    db.execute(
      'INSERT INTO users (name, email, password, user_type, phone, address, license_agreed, license_agreed_at, license_version) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [data['name'], data['email'], hashed_password, data['user_type'], 
       data['phone'], data['address'], 1, Time.now.to_s, 'MIT-1.0']
    )
    
    user_id = db.last_insert_row_id
    
    # ライセンス同意履歴の記録
    record_license_agreement(user_id, request.ip, request.user_agent)
    
    db.commit
    
    user = db.execute('SELECT id, name, email, user_type, phone, address FROM users WHERE id = ?', [user_id]).first
    
    status 201
    user.to_json
  rescue SQLite3::Exception => e
    db.rollback
    if e.message.include?('UNIQUE constraint failed')
      status 409
      { error: 'Email already exists' }.to_json
    else
      status 500
      { error: e.message }.to_json
    end
  end
end

# ログイン（ライセンス同意チェック付き）
post '/login' do
  data = JSON.parse(request.body.read)
  
  # 入力チェック
  if data['email'].nil? || data['password'].nil?
    status 400
    return { error: 'Email and password are required' }.to_json
  end
  
  # ユーザーの検索
  user = db.execute('SELECT * FROM users WHERE email = ?', [data['email']]).first
  
  if user.nil?
    status 401
    return { error: 'Invalid email or password' }.to_json
  end
  
  # パスワードの検証
  if BCrypt::Password.new(user['password']) == data['password']
    # ライセンス未同意ユーザーのチェック（既存ユーザー対応）
    if user['license_agreed'] != 1
      # ライセンス同意付きログインの場合
      if data['license_agreed'] == true || data['license_agreed'] == 'true'
        # ライセンス同意を更新
        db.transaction
        db.execute(
          'UPDATE users SET license_agreed = 1, license_agreed_at = ?, license_version = ? WHERE id = ?',
          [Time.now.to_s, 'MIT-1.0', user['id']]
        )
        record_license_agreement(user['id'], request.ip, request.user_agent)
        db.commit
        
        # 更新後のユーザー情報を取得
        user = db.execute('SELECT * FROM users WHERE id = ?', [user['id']]).first
      else
        # ライセンス同意が必要
        status 403
        temp_token = SecureRandom.hex(32)
        return { 
          error: 'ライセンスへの同意が必要です',
          require_license_agreement: true,
          temp_token: temp_token,
          message: '利用を続けるには、MITライセンスへの同意が必要です。'
        }.to_json
      end
    end
    
    # セッションまたはトークンの生成
    token = SecureRandom.hex(64)
    
    # パスワードを除外したユーザー情報を返す
    user.delete('password')
    
    { token: token, user: user }.to_json
  else
    status 401
    { error: 'Invalid email or password' }.to_json
  end
end

# ライセンス同意更新エンドポイント（既存ユーザー用）
post '/update-license-agreement' do
  data = JSON.parse(request.body.read)
  
  # Authorizationヘッダーからトークンを取得
  auth_header = request.env['HTTP_AUTHORIZATION']
  
  if auth_header.nil?
    status 401
    return { error: '認証が必要です' }.to_json
  end
  
  # トークンからユーザーを特定（簡易実装）
  # 実際の実装では適切なトークン検証が必要
  token = auth_header.split(' ').last
  
  # ライセンス同意の検証
  if data['license_agreed'] != true && data['license_agreed'] != 'true'
    status 400
    return { error: 'ライセンスへの同意が必要です' }.to_json
  end
  
  # データからemailを取得してユーザーを特定
  email = data['email']
  if email.nil?
    status 400
    return { error: 'メールアドレスが必要です' }.to_json
  end
  
  user = db.execute('SELECT * FROM users WHERE email = ?', [email]).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  begin
    db.transaction
    
    # ユーザー情報の更新
    db.execute(
      'UPDATE users SET license_agreed = 1, license_agreed_at = ?, license_version = ? WHERE id = ?',
      [Time.now.to_s, 'MIT-1.0', user['id']]
    )
    
    # 同意履歴の記録
    record_license_agreement(user['id'], request.ip, request.user_agent)
    
    db.commit
    
    { success: true, message: 'ライセンス同意が記録されました' }.to_json
  rescue SQLite3::Exception => e
    db.rollback
    status 500
    { error: "更新エラー: #{e.message}" }.to_json
  end
end

# ライセンス同意状態の確認エンドポイント
get '/check-license-agreement' do
  # Authorizationヘッダーからトークンを取得
  auth_header = request.env['HTTP_AUTHORIZATION']
  
  if auth_header.nil?
    status 401
    return { error: '認証が必要です' }.to_json
  end
  
  # 簡易的にメールアドレスをクエリパラメータから取得
  email = params['email']
  if email.nil?
    status 400
    return { error: 'メールアドレスが必要です' }.to_json
  end
  
  user = db.execute('SELECT * FROM users WHERE email = ?', [email]).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  {
    license_agreed: user['license_agreed'] == 1,
    license_version: user['license_version'],
    agreed_at: user['license_agreed_at']
  }.to_json
end

# 管理者用：ライセンス同意状況の確認
get '/admin/license-agreements' do
  begin
    # ユーザー毎のライセンス同意状況を取得
    users_with_license = db.execute(
      'SELECT u.id, u.name, u.email, u.user_type, u.license_agreed, 
              u.license_agreed_at, u.license_version,
              COUNT(la.id) as agreement_count
       FROM users u
       LEFT JOIN license_agreements la ON u.id = la.user_id
       GROUP BY u.id, u.name, u.email, u.user_type, u.license_agreed, 
                u.license_agreed_at, u.license_version
       ORDER BY u.license_agreed_at DESC'
    )
    
    { agreements: users_with_license }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

#################################################
# 求人募集関連のエンドポイント（変更なし）
#################################################

# 求人募集の投稿 - 修正版
post '/job_postings' do
  data = JSON.parse(request.body.read)
  
  # 入力チェック（必須フィールド）
  required_fields = ['user_id', 'company_name', 'location', 'contact', 'hourly_wage', 
                     'work_days', 'work_hours', 'job_description', 'positions_available']
  missing_fields = required_fields.select { |field| data[field].nil? || data[field].to_s.empty? }
  
  if !missing_fields.empty?
    status 400
    return { error: "Missing required fields: #{missing_fields.join(', ')}" }.to_json
  end
  
  begin
    # 新フィールドを含めたINSERT文
    # 求人投稿のINSERT文を修正（work_daysカラムを削除またはwork_datesで代用）
    db.execute(
      'INSERT INTO job_postings (
        user_id, company_name, location, meeting_place, contact, hourly_wage, 
        work_days, work_dates, work_hours, seasonal_vegetables, belongings, 
        job_description, remarks, positions_available
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [
        data['user_id'], 
        data['company_name'], 
        data['location'], 
        data['meeting_place'],
        data['contact'], 
        data['hourly_wage'], 
        data['work_dates'] || '',  # work_daysの代わりにwork_datesを使用
        data['work_dates'],
        data['work_hours'], 
        data['seasonal_vegetables'], 
        data['belongings'],
        data['job_description'], 
        data['remarks'],
        data['positions_available']
      ]
    )   

    job_id = db.last_insert_row_id
    job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
    
    status 201
    job.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# 求人一覧の取得
get '/job_postings' do
  # アクティブな求人のみ取得（positions_available > positions_filled かつ is_active = 1）
  jobs = db.execute('SELECT * FROM job_postings WHERE is_active = 1 AND positions_available > positions_filled ORDER BY created_at DESC')
  jobs.to_json
end

# 特定の求人詳細の取得
get '/job_postings/:id' do
  job_id = params['id']
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  job.to_json
end

# 求人の更新 - 修正版
put '/job_postings/:id' do
  job_id = params['id']
  data = JSON.parse(request.body.read)
  
  # 求人の存在チェック
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  begin
    db.transaction
    
    # 入力値の設定（新フィールドを含む）
    company_name = data['company_name'] || job['company_name']
    location = data['location'] || job['location']
    meeting_place = data['meeting_place'] || job['meeting_place']
    contact = data['contact'] || job['contact']
    hourly_wage = data['hourly_wage'] || job['hourly_wage']
    work_days = data['work_days'] || job['work_days']
    work_dates = data['work_dates'] || job['work_dates']
    work_hours = data['work_hours'] || job['work_hours']
    seasonal_vegetables = data['seasonal_vegetables'] || job['seasonal_vegetables']
    belongings = data['belongings'] || job['belongings']
    job_description = data['job_description'] || job['job_description']
    remarks = data['remarks'] || job['remarks']
    positions_available = data['positions_available'] || job['positions_available']
    is_active = data.key?('is_active') ? data['is_active'] : job['is_active']
    
    update_sql = <<-SQL
      UPDATE job_postings 
      SET company_name = ?, 
          location = ?, 
          meeting_place = ?,
          contact = ?, 
          hourly_wage = ?, 
          work_days = ?, 
          work_dates = ?,
          work_hours = ?, 
          seasonal_vegetables = ?, 
          belongings = ?,
          job_description = ?, 
          remarks = ?,
          positions_available = ?, 
          is_active = ?, 
          updated_at = CURRENT_TIMESTAMP 
      WHERE id = ?
    SQL
    
    result = db.execute(
      update_sql,
      [company_name, location, meeting_place, contact, hourly_wage, work_days, 
       work_dates, work_hours, seasonal_vegetables, belongings, job_description, 
       remarks, positions_available, is_active, job_id]
    )
    
    if result.nil?
      raise SQLite3::Exception.new("Failed to update job posting")
    end
    
    db.commit
    
    # 更新後のデータを取得
    updated_job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
    
    updated_job.to_json
    
  rescue SQLite3::Exception => e
    db.rollback
    puts "❌ 求人更新エラー (ID: #{job_id}): #{e.message}"
    status 500
    { error: "求人の更新に失敗しました: #{e.message}" }.to_json
  end
end

# 🔧 修正: 求人の削除（非アクティブ化）- より安全な処理
delete '/job_postings/:id' do
  job_id = params['id']
  
  # 求人の存在チェック
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  begin
    db.transaction
    
    # 🔧 修正: 関連データの整合性チェック
    applications_count = db.execute(
      "SELECT COUNT(*) as count FROM applications WHERE job_posting_id = ? AND status != 'cancelled'",
      [job_id]
    ).first['count']
    
    # 実際には削除せず、非アクティブにする
    result = db.execute(
      'UPDATE job_postings SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?', 
      [job_id]
    )
    
    if result.nil?
      raise SQLite3::Exception.new("Failed to deactivate job posting")
    end
    
    db.commit
    
    puts "📝 求人ID #{job_id} (#{job['company_name']}) が削除（非アクティブ化）されました (応募数: #{applications_count})"
    
    { 
      success: true, 
      message: 'Job posting deactivated successfully',
      applications_affected: applications_count
    }.to_json
    
  rescue SQLite3::Exception => e
    db.rollback
    puts "❌ 求人削除エラー (ID: #{job_id}): #{e.message}"
    status 500
    { error: "求人の削除に失敗しました: #{e.message}" }.to_json
  end
end

# ユーザーの求人一覧を取得
get '/users/:id/job_postings' do
  user_id = params['id']
  
  # ユーザーの存在チェック
  user = db.execute('SELECT * FROM users WHERE id = ?', [user_id]).first
  
  if user.nil?
    status 404
    return { error: 'User not found' }.to_json
  end
  
  # ユーザーの求人一覧を取得
  jobs = db.execute('SELECT * FROM job_postings WHERE user_id = ? ORDER BY created_at DESC', [user_id])
  
  jobs.to_json
end

#################################################
# 応募関連のエンドポイント
#################################################

# 求人への応募
post '/applications' do
  data = JSON.parse(request.body.read)
  
  # 入力チェック
  if data['job_posting_id'].nil? || data['applicant_id'].nil?
    status 400
    return { error: 'Job posting ID and applicant ID are required' }.to_json
  end
  
  # 求人の存在チェック
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [data['job_posting_id']]).first
  
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  # 応募可能かチェック
  if job['positions_filled'] >= job['positions_available'] || job['is_active'] != 1
    status 400
    return { error: 'This job posting is no longer accepting applications' }.to_json
  end
  
  # 既に応募済みかチェック
  existing_application = db.execute(
    'SELECT * FROM applications WHERE job_posting_id = ? AND applicant_id = ?', 
    [data['job_posting_id'], data['applicant_id']]
  ).first
  
  if existing_application
    status 409
    return { error: 'You have already applied to this job posting' }.to_json
  end
  
  begin
    db.transaction
    
    db.execute(
      'INSERT INTO applications (job_posting_id, applicant_id) VALUES (?, ?)',
      [data['job_posting_id'], data['applicant_id']]
    )
    
    application_id = db.last_insert_row_id
    application = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
    
    # 応募数の更新
    db.execute(
      'UPDATE job_postings SET positions_filled = positions_filled + 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [data['job_posting_id']]
    )
    
    # positions_filledがpositions_availableに達した場合は自動的に非アクティブ化
    db.execute(
      'UPDATE job_postings SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND positions_filled >= positions_available',
      [data['job_posting_id']]
    )
    
    db.commit
    
    status 201
    application.to_json
  rescue SQLite3::Exception => e
    db.rollback
    status 500
    { error: e.message }.to_json
  end
end

# 応募のキャンセル - 修正版
delete '/applications/:id' do
  application_id = params['id']
  
  # 応募の存在チェック
  application = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
  
  if application.nil?
    status 404
    return { error: 'Application not found' }.to_json
  end
  
  begin
    # トランザクション開始
    db.transaction
    
    # 🔧 修正: シングルクォートを使用
    db.execute(
      "UPDATE applications SET status = 'cancelled', updated_at = CURRENT_TIMESTAMP WHERE id = ?",
      [application_id]
    )
    
    # 求人の応募数を減らす
    db.execute(
      'UPDATE job_postings SET positions_filled = positions_filled - 1, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND positions_filled > 0',
      [application['job_posting_id']]
    )
    
    # 求人を再度アクティブ化（必要であれば）
    db.execute(
      'UPDATE job_postings SET is_active = 1 WHERE id = ? AND positions_filled < positions_available',
      [application['job_posting_id']]
    )
    
    # トランザクション終了
    db.commit
    
    puts "✅ 応募ID #{application_id} のキャンセルが完了しました"
    
    { success: true, message: 'Application cancelled successfully' }.to_json
  rescue SQLite3::Exception => e
    # エラー時はロールバック
    db.rollback
    puts "❌ 応募キャンセルエラー (ID: #{application_id}): #{e.message}"
    status 500
    { error: e.message }.to_json
  end
end 

# 特定の応募の詳細を取得
get '/applications/:id' do
  application_id = params['id']
  
  # 応募の存在チェック
  application = db.execute('SELECT a.*, j.company_name, j.location, j.hourly_wage, j.work_days, j.work_hours, j.contact 
                           FROM applications a 
                           JOIN job_postings j ON a.job_posting_id = j.id 
                           WHERE a.id = ?', [application_id]).first
  
  if application.nil?
    status 404
    return { error: 'Application not found' }.to_json
  end
  
  application.to_json
end

# ユーザーの応募一覧を取得
get '/users/:id/applications' do
  user_id = params['id']
  
  # ユーザーの存在チェック
  user = db.execute('SELECT * FROM users WHERE id = ?', [user_id]).first
  
  if user.nil?
    status 404
    return { error: 'User not found' }.to_json
  end
  
  # 応募一覧の取得（関連する求人情報も取得）
  applications = db.execute(
    'SELECT a.*, j.company_name, j.location, j.hourly_wage, j.work_days, j.work_hours, j.contact 
     FROM applications a 
     JOIN job_postings j ON a.job_posting_id = j.id 
     WHERE a.applicant_id = ? 
     ORDER BY a.created_at DESC',
    [user_id]
  )
  
  applications.to_json
end

# 求人への応募一覧を取得
# 修正後（匿名名も取得）
get '/job_postings/:id/applications' do
  job_id = params['id']
  
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  # LEFT JOINでグループチャットの匿名名も取得
  applications = db.execute(
    'SELECT a.*, u.name, u.email, u.phone, gcm.anonymous_name
     FROM applications a 
     JOIN users u ON a.applicant_id = u.id 
     LEFT JOIN group_chat_members gcm ON a.job_posting_id = gcm.job_posting_id AND a.applicant_id = gcm.user_id
     WHERE a.job_posting_id = ? 
     ORDER BY a.created_at DESC',
    [job_id]
  )
  
  applications.to_json
end

#################################################
# メッセージ関連のエンドポイント
#################################################

# メッセージの送信
post '/applications/:id/messages' do
  application_id = params['id']
  data = JSON.parse(request.body.read)
  
  # 入力チェック
  if data['sender_id'].nil? || data['message'].nil? || data['message'].empty?
    status 400
    return { error: 'Sender ID and message are required' }.to_json
  end
  
  # 応募の存在チェック
  application = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
  
  if application.nil?
    status 404
    return { error: 'Application not found' }.to_json
  end
  
  # 権限チェック（応募者または求人主のみメッセージ送信可能）
  job = db.execute('SELECT user_id FROM job_postings WHERE id = ?', [application['job_posting_id']]).first
  
  if data['sender_id'].to_i != application['applicant_id'] && data['sender_id'].to_i != job['user_id']
    status 403
    return { error: 'You do not have permission to send messages for this application' }.to_json
  end
  
begin
    current_time_jst = Time.now.localtime("+09:00").strftime("%Y-%m-%d %H:%M:%S")
    db.execute(
      'INSERT INTO messages (application_id, sender_id, message, created_at) VALUES (?, ?, ?, ?)',
      [application_id, data['sender_id'], data['message'], current_time_jst]
    )
    
    message_id = db.last_insert_row_id
    message = db.execute('SELECT * FROM messages WHERE id = ?', [message_id]).first
    
    status 201
    message.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# メッセージ一覧の取得
get '/applications/:id/messages' do
  application_id = params['id']
  
  # 応募の存在チェック
  application = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
  
  if application.nil?
    status 404
    return { error: 'Application not found' }.to_json
  end
  
  # メッセージ一覧の取得（送信者情報も取得）
  messages = db.execute(
    'SELECT m.*, u.name, u.user_type 
     FROM messages m 
     JOIN users u ON m.sender_id = u.id 
     WHERE m.application_id = ? 
     ORDER BY m.created_at ASC',
    [application_id]
  )
  
  messages.to_json
end

# 募集者（農家）が受けた応募一覧とその応募に関連するメッセージを取得するエンド ポイント
get '/users/:id/received_applications' do
  user_id = params['id']
  
  # ユーザーの存在チェック
  user = db.execute('SELECT * FROM users WHERE id = ?', [user_id]).first
  
  if user.nil?
    status 404
    return { error: 'User not found' }.to_json
  end
  
  # ユーザーが農家（求人掲載者）かチェック
  if user['user_type'] != 'farmer'
    status 403
    return { error: 'Only farmers can access this endpoint' }.to_json
  end
  
  # ユーザーの求人に対する応募一覧を取得
  applications = db.execute(
    'SELECT a.*, u.name AS applicant_name, u.email AS applicant_email, u.phone AS applicant_phone, 
            j.company_name, j.location, j.hourly_wage 
     FROM applications a 
     JOIN users u ON a.applicant_id = u.id 
     JOIN job_postings j ON a.job_posting_id = j.id 
     WHERE j.user_id = ? 
     ORDER BY a.created_at DESC',
    [user_id]
  )
  
  applications.to_json
end

# 特定の応募に関するメッセージと応募情報を取得するエンドポイント
get '/applications/:id/details' do
  application_id = params['id']
  
  # 応募の存在チェック
  application = db.execute(
    'SELECT a.*, 
            u.name AS applicant_name, u.email AS applicant_email, u.phone AS applicant_phone,
            j.company_name, j.location, j.hourly_wage, j.work_days, j.work_hours, j.user_id AS job_owner_id, j.contact
     FROM applications a 
     JOIN users u ON a.applicant_id = u.id 
     JOIN job_postings j ON a.job_posting_id = j.id 
     WHERE a.id = ?', 
    [application_id]
  ).first
  
  if application.nil?
    status 404
    return { error: 'Application not found' }.to_json
  end
  
  # メッセージ一覧を取得
  messages = db.execute(
    'SELECT m.*, u.name, u.user_type 
     FROM messages m 
     JOIN users u ON m.sender_id = u.id 
     WHERE m.application_id = ? 
     ORDER BY m.created_at ASC',
    [application_id]
  )
  
  # 応募情報とメッセージを返す
  {
    application: application,
    messages: messages
  }.to_json
end

#################################################
# データベース管理用エンドポイント（ライセンス情報を含む）
#################################################

# すべてのユーザーを取得（ライセンス同意情報を含む）
get '/admin/users' do
  users = db.execute('SELECT id, name, email, user_type, phone, address, license_agreed, license_agreed_at, license_version, created_at FROM users')
  users.to_json
end

# すべての求人を取得
get '/admin/job_postings' do
  jobs = db.execute('SELECT * FROM job_postings')
  jobs.to_json
end

# すべての応募を取得
get '/admin/applications' do
  applications = db.execute('SELECT * FROM applications')
  applications.to_json
end

# すべてのメッセージを取得
get '/admin/messages' do
  messages = db.execute('SELECT * FROM messages')
  messages.to_json
end

# データベースのテーブルを削除
delete '/admin/reset_database' do
  begin
    db.execute('DELETE FROM messages')
    db.execute('DELETE FROM applications')
    db.execute('DELETE FROM job_postings')
    db.execute('DELETE FROM users')
    db.execute('DELETE FROM license_agreements')  # ライセンス同意履歴も削除
    
    { success: true, message: 'Database reset successfully' }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# 🔧 修正: データベースの統計情報を取得 - ライセンス同意情報付き
get '/admin/stats' do
  begin
    stats = {
      users_count: db.execute('SELECT COUNT(*) as count FROM users').first['count'],
      users_with_license: db.execute('SELECT COUNT(*) as count FROM users WHERE license_agreed = 1').first['count'],
      users_without_license: db.execute('SELECT COUNT(*) as count FROM users WHERE license_agreed = 0 OR license_agreed IS NULL').first['count'],
      job_postings_count: db.execute('SELECT COUNT(*) as count FROM job_postings').first['count'],
      active_job_postings_count: db.execute('SELECT COUNT(*) as count FROM job_postings WHERE is_active = 1').first['count'],
      inactive_job_postings_count: db.execute('SELECT COUNT(*) as count FROM job_postings WHERE is_active = 0').first['count'],
      applications_count: db.execute('SELECT COUNT(*) as count FROM applications').first['count'],
      pending_applications_count: db.execute('SELECT COUNT(*) as count FROM applications WHERE status = "pending"').first['count'],
      cancelled_applications_count: db.execute('SELECT COUNT(*) as count FROM applications WHERE status = "cancelled"').first['count'],
      messages_count: db.execute('SELECT COUNT(*) as count FROM messages').first['count'],
      license_agreements_count: db.execute('SELECT COUNT(*) as count FROM license_agreements').first['count']
    }
    
    stats.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# 🔧 新規追加: ヘルスチェックエンドポイント
get '/health' do
  begin
    # データベース接続テスト
    db.execute('SELECT 1').first
    
    {
      status: 'healthy',
      timestamp: Time.now.iso8601,
      database: 'connected',
      api_version: '1.1',  # バージョンアップ（ライセンス機能追加）
      license_feature: 'enabled'
    }.to_json
  rescue => e
    status 500
    {
      status: 'unhealthy',
      timestamp: Time.now.iso8601,
      error: e.message
    }.to_json
  end
end

#################################################
# パスワードリセット関連エンドポイント
#################################################

# パスワードリセット要求（メールアドレス確認）
post '/password-reset/request' do
  data = JSON.parse(request.body.read)
  email = data['email']
  
  if email.nil? || email.empty?
    status 400
    return { error: 'メールアドレスを入力してください' }.to_json
  end
  
  user = db.execute('SELECT id, email FROM users WHERE email = ?', [email]).first
  
  if user.nil?
    status 404
    return { error: 'このメールアドレスは登録されていません' }.to_json
  end
  
  { success: true, message: 'アカウントが確認されました' }.to_json
end

# パスワードリセット実行
post '/password-reset/confirm' do
  data = JSON.parse(request.body.read)
  email = data['email']
  new_password = data['new_password']
  
  if email.nil? || new_password.nil?
    status 400
    return { error: '必要な情報が不足しています' }.to_json
  end
  
  if new_password.length < 6
    status 400
    return { error: 'パスワードは6文字以上で設定してください' }.to_json
  end
  
  user = db.execute('SELECT id FROM users WHERE email = ?', [email]).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  begin
    hashed_password = BCrypt::Password.create(new_password)
    
    db.execute(
      'UPDATE users SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE email = ?',
      [hashed_password, email]
    )
    
    puts "✅ パスワードリセット完了: #{email}"
    
    { success: true, message: 'パスワードが変更されました' }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: "パスワード変更エラー: #{e.message}" }.to_json
  end
end

#################################################
# ユーザープロフィール更新エンドポイント
#################################################

# プロフィール更新
put '/users/:id' do
  user_id = params['id']
  data = JSON.parse(request.body.read)
  
  user = db.execute('SELECT * FROM users WHERE id = ?', [user_id]).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  begin
    name = data['name'] || user['name']
    phone = data['phone'] || user['phone']
    address = data['address'] || user['address']
    
    db.execute(
      'UPDATE users SET name = ?, phone = ?, address = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [name, phone, address, user_id]
    )
    
    updated_user = db.execute(
      'SELECT id, name, email, user_type, phone, address, license_agreed FROM users WHERE id = ?',
      [user_id]
    ).first
    
    puts "✅ プロフィール更新完了: ユーザーID #{user_id}"
    
    updated_user.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: "更新エラー: #{e.message}" }.to_json
  end
end

# パスワード変更
put '/users/:id/password' do
  user_id = params['id']
  data = JSON.parse(request.body.read)
  
  current_password = data['current_password']
  new_password = data['new_password']
  
  if current_password.nil? || new_password.nil?
    status 400
    return { error: '現在のパスワードと新しいパスワードを入力してください' }.to_json
  end
  
  if new_password.length < 6
    status 400
    return { error: 'パスワードは6文字以上で設定してください' }.to_json
  end
  
  user = db.execute('SELECT * FROM users WHERE id = ?', [user_id]).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  unless BCrypt::Password.new(user['password']) == current_password
    status 401
    return { error: '現在のパスワードが正しくありません' }.to_json
  end
  
  begin
    hashed_password = BCrypt::Password.create(new_password)
    
    db.execute(
      'UPDATE users SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [hashed_password, user_id]
    )
    
    puts "✅ パスワード変更完了: ユーザーID #{user_id}"
    
    { success: true, message: 'パスワードが変更されました' }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: "パスワード変更エラー: #{e.message}" }.to_json
  end
end

# ユーザー情報取得
get '/users/:id' do
  user_id = params['id']
  
  user = db.execute(
    'SELECT id, name, email, user_type, phone, address, license_agreed, created_at FROM users WHERE id = ?',
    [user_id]
  ).first
  
  if user.nil?
    status 404
    return { error: 'ユーザーが見つかりません' }.to_json
  end
  
  user.to_json
end

# 未読メッセージ数取得（既読機能対応版）
get '/users/:id/messages/unread_count' do
  user_id = params['id']
  
  begin
    user = db.execute('SELECT user_type, last_message_read_at FROM users WHERE id = ?', [user_id]).first
    
    if user.nil?
      status 404
      return { error: 'ユーザーが見つかりません' }.to_json
    end
    
    # 最終既読時刻（未設定の場合は24時間前）
    last_read = user['last_message_read_at']
    if last_read.nil? || last_read.empty?
      last_read = (Time.now - 86400).strftime("%Y-%m-%d %H:%M:%S")
    end
    
    count = 0
    
    if user['user_type'] == 'farmer'
      # 農家: 自分の求人への応募に対するメッセージ（最終既読以降）
      result = db.execute(
        "SELECT COUNT(*) as count FROM messages m
         JOIN applications a ON m.application_id = a.id
         JOIN job_postings j ON a.job_posting_id = j.id
         WHERE j.user_id = ? AND m.sender_id != ?
         AND m.created_at > ?",
        [user_id, user_id, last_read]
      ).first
      count = result['count'] || 0
    else
      # 応募者: 自分の応募への返信メッセージ（最終既読以降）
      result = db.execute(
        "SELECT COUNT(*) as count FROM messages m
         JOIN applications a ON m.application_id = a.id
         WHERE a.applicant_id = ? AND m.sender_id != ?
         AND m.created_at > ?",
        [user_id, user_id, last_read]
      ).first
      count = result['count'] || 0
    end
    
    { unread_count: count, last_read_at: last_read }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# メッセージ既読マーク
post '/users/:id/messages/mark_read' do
  user_id = params['id']
  
  begin
    current_time = Time.now.localtime("+09:00").strftime("%Y-%m-%d %H:%M:%S")
    
    db.execute(
      'UPDATE users SET last_message_read_at = ? WHERE id = ?',
      [current_time, user_id]
    )
    
    puts "✅ メッセージ既読マーク: ユーザーID #{user_id} at #{current_time}"
    
    { success: true, read_at: current_time }.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# 応募ステータス更新
put '/applications/:id/status' do
  application_id = params['id']
  data = JSON.parse(request.body.read)
  new_status = data['status']
  
  valid_statuses = ['pending', 'accepted', 'rejected', 'cancelled']
  
  unless valid_statuses.include?(new_status)
    status 400
    return { error: '無効なステータスです' }.to_json
  end
  
  application = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
  
  if application.nil?
    status 404
    return { error: '応募が見つかりません' }.to_json
  end
  
  begin
    db.execute(
      'UPDATE applications SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [new_status, application_id]
    )
    
    updated = db.execute('SELECT * FROM applications WHERE id = ?', [application_id]).first
    
    puts "✅ 応募ステータス更新: ID #{application_id} → #{new_status}"
    
    updated.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: "ステータス更新エラー: #{e.message}" }.to_json
  end
end

#################################################
# グループチャット関連のエンドポイント
#################################################

# 動物の名前リスト（匿名アカウント用）
ANIMAL_NAMES = [
  'キツネ', 'タヌキ', 'ウサギ', 'シカ', 'クマ', 'サル', 
  'イノシシ', 'リス', 'ハクビシン', 'アナグマ', 'カモシカ', 
  'ムササビ', 'テン', 'イタチ', 'ノウサギ', 'ヤマネ', 
  'モモンガ', 'カワウソ', 'キジ', 'ヤマドリ'
].freeze

# 利用可能な動物名を取得
get '/job_postings/:id/group_chat/available_names' do
  job_id = params['id']
  
  # 求人の存在チェック
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  # 既に使用されている名前を取得
  used_names = db.execute(
    'SELECT anonymous_name FROM group_chat_members WHERE job_posting_id = ?',
    [job_id]
  ).map { |row| row['anonymous_name'] }
  
  # 利用可能な名前を返す
  available_names = ANIMAL_NAMES - used_names
  
  { available_names: available_names }.to_json
end

# 自分のグループチャットメンバーシップを確認
get '/job_postings/:id/group_chat/membership' do
  job_id = params['id']
  user_id = params['user_id']
  
  if user_id.nil?
    status 400
    return { error: 'User ID is required' }.to_json
  end
  
  # メンバーシップを検索
  membership = db.execute(
    'SELECT * FROM group_chat_members WHERE job_posting_id = ? AND user_id = ?',
    [job_id, user_id]
  ).first
  
  if membership
    { 
      is_member: true, 
      membership: membership 
    }.to_json
  else
    # 求人主かどうかをチェック
    job = db.execute('SELECT user_id FROM job_postings WHERE id = ?', [job_id]).first
    if job && job['user_id'].to_i == user_id.to_i
      { 
        is_member: false, 
        is_owner: true,
        message: 'Owner not yet joined group chat'
      }.to_json
    else
      { 
        is_member: false, 
        is_owner: false 
      }.to_json
    end
  end
end

# グループチャットに参加（匿名名を設定）
post '/job_postings/:id/group_chat/join' do
  job_id = params['id']
  data = JSON.parse(request.body.read)
  
  user_id = data['user_id']
  anonymous_name = data['anonymous_name']
  
  # 入力チェック
  if user_id.nil? || anonymous_name.nil? || anonymous_name.empty?
    status 400
    return { error: 'User ID and anonymous name are required' }.to_json
  end
  
  # 求人の存在チェック
  job = db.execute('SELECT * FROM job_postings WHERE id = ?', [job_id]).first
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  # 求人主かどうか判定
  is_owner = (job['user_id'].to_i == user_id.to_i) ? 1 : 0
  
  # 応募者の場合、応募済みかチェック
  if is_owner == 0
    application = db.execute(
      "SELECT * FROM applications WHERE job_posting_id = ? AND applicant_id = ? AND status != 'cancelled'",
      [job_id, user_id]
    ).first
    
    if application.nil?
      status 403
      return { error: 'You must apply to this job before joining the group chat' }.to_json
    end
  end
  
  # 動物名の有効性チェック
  unless ANIMAL_NAMES.include?(anonymous_name)
    status 400
    return { error: 'Invalid anonymous name' }.to_json
  end
  
  # 既に使用されている名前かチェック
  existing_name = db.execute(
    'SELECT * FROM group_chat_members WHERE job_posting_id = ? AND anonymous_name = ?',
    [job_id, anonymous_name]
  ).first
  
  if existing_name
    status 409
    return { error: 'This name is already taken' }.to_json
  end
  
  begin
    db.execute(
      'INSERT INTO group_chat_members (job_posting_id, user_id, anonymous_name, is_owner) VALUES (?, ?, ?, ?)',
      [job_id, user_id, anonymous_name, is_owner]
    )
    
    member_id = db.last_insert_row_id
    member = db.execute('SELECT * FROM group_chat_members WHERE id = ?', [member_id]).first
    
    status 201
    member.to_json
  rescue SQLite3::Exception => e
    if e.message.include?('UNIQUE constraint failed')
      status 409
      { error: 'You have already joined this group chat' }.to_json
    else
      status 500
      { error: e.message }.to_json
    end
  end
end

# グループチャットメンバー一覧を取得（求人主のみ全員表示、応募者は自分のみ）
get '/job_postings/:id/group_chat/members' do
  job_id = params['id']
  user_id = params['user_id']
  
  if user_id.nil?
    status 400
    return { error: 'User ID is required' }.to_json
  end
  
  # 求人主かどうか判定
  job = db.execute('SELECT user_id FROM job_postings WHERE id = ?', [job_id]).first
  if job.nil?
    status 404
    return { error: 'Job posting not found' }.to_json
  end
  
  is_owner = (job['user_id'].to_i == user_id.to_i)
  
  if is_owner
    # 求人主は全メンバーを確認可能（実名も含む）
    members = db.execute(
      'SELECT gcm.*, u.name as real_name, u.email, u.phone 
       FROM group_chat_members gcm
       JOIN users u ON gcm.user_id = u.id
       WHERE gcm.job_posting_id = ?
       ORDER BY gcm.joined_at ASC',
      [job_id]
    )
  else
    # 応募者は匿名名のみ（実名なし）
    members = db.execute(
      'SELECT id, job_posting_id, anonymous_name, is_owner, joined_at
       FROM group_chat_members
       WHERE job_posting_id = ?
       ORDER BY joined_at ASC',
      [job_id]
    )
  end
  
  { members: members, is_owner: is_owner }.to_json
end

# グループチャットにメッセージを送信
post '/job_postings/:id/group_chat/messages' do
  job_id = params['id']
  data = JSON.parse(request.body.read)
  
  sender_id = data['sender_id']
  message = data['message']
  mentioned_user_id = data['mentioned_user_id']  # オプション
  
  # 入力チェック
  if sender_id.nil? || message.nil? || message.empty?
    status 400
    return { error: 'Sender ID and message are required' }.to_json
  end
  
  # グループチャットメンバーかチェック
  membership = db.execute(
    'SELECT * FROM group_chat_members WHERE job_posting_id = ? AND user_id = ?',
    [job_id, sender_id]
  ).first
  
  if membership.nil?
    status 403
    return { error: 'You must join the group chat first' }.to_json
  end
  
  # メンション先の検証（指定がある場合）
  if mentioned_user_id
    mentioned_membership = db.execute(
      'SELECT * FROM group_chat_members WHERE job_posting_id = ? AND user_id = ?',
      [job_id, mentioned_user_id]
    ).first
    
    if mentioned_membership.nil?
      status 400
      return { error: 'Mentioned user is not a member of this group chat' }.to_json
    end
  end
  
  begin
    db.execute(
      'INSERT INTO group_messages (job_posting_id, sender_id, message, mentioned_user_id) VALUES (?, ?, ?, ?)',
      [job_id, sender_id, message, mentioned_user_id]
    )
    
    message_id = db.last_insert_row_id
    
    # 送信者の匿名名を取得
    new_message = db.execute(
      'SELECT gm.*, gcm.anonymous_name as sender_name, gcm.is_owner as sender_is_owner
       FROM group_messages gm
       JOIN group_chat_members gcm ON gm.sender_id = gcm.user_id AND gm.job_posting_id = gcm.job_posting_id
       WHERE gm.id = ?',
      [message_id]
    ).first
    
    status 201
    new_message.to_json
  rescue SQLite3::Exception => e
    status 500
    { error: e.message }.to_json
  end
end

# グループチャットのメッセージ一覧を取得（可視性フィルタ付き）
get '/job_postings/:id/group_chat/messages' do
  job_id = params['id']
  user_id = params['user_id']
  
  if user_id.nil?
    status 400
    return { error: 'User ID is required' }.to_json
  end
  
  # グループチャットメンバーかチェック
  membership = db.execute(
    'SELECT * FROM group_chat_members WHERE job_posting_id = ? AND user_id = ?',
    [job_id, user_id]
  ).first
  
  if membership.nil?
    status 403
    return { error: 'You must join the group chat first' }.to_json
  end
  
  is_owner = membership['is_owner'] == 1
  
  # 求人主のuser_idを取得
  job = db.execute('SELECT user_id FROM job_postings WHERE id = ?', [job_id]).first
  owner_user_id = job['user_id']
  
  if is_owner
    # 求人主は全メッセージを閲覧可能
    messages = db.execute(
      'SELECT gm.*, gcm.anonymous_name as sender_name, gcm.is_owner as sender_is_owner,
              mentioned_gcm.anonymous_name as mentioned_name
       FROM group_messages gm
       JOIN group_chat_members gcm ON gm.sender_id = gcm.user_id AND gm.job_posting_id = gcm.job_posting_id
       LEFT JOIN group_chat_members mentioned_gcm ON gm.mentioned_user_id = mentioned_gcm.user_id AND gm.job_posting_id = mentioned_gcm.job_posting_id
       WHERE gm.job_posting_id = ?
       ORDER BY gm.created_at ASC',
      [job_id]
    )
  else
    # 応募者の場合：
    # 1. 求人主が送信したメンションなしメッセージ
    # 2. 求人主が自分にメンションしたメッセージ
    # 3. 自分が送信したメッセージ
    messages = db.execute(
      'SELECT gm.*, gcm.anonymous_name as sender_name, gcm.is_owner as sender_is_owner,
              mentioned_gcm.anonymous_name as mentioned_name
       FROM group_messages gm
       JOIN group_chat_members gcm ON gm.sender_id = gcm.user_id AND gm.job_posting_id = gcm.job_posting_id
       LEFT JOIN group_chat_members mentioned_gcm ON gm.mentioned_user_id = mentioned_gcm.user_id AND gm.job_posting_id = mentioned_gcm.job_posting_id
       WHERE gm.job_posting_id = ?
       AND (
         (gm.sender_id = ? AND gcm.is_owner = 1 AND gm.mentioned_user_id IS NULL) OR
         (gm.sender_id = ? AND gcm.is_owner = 1 AND gm.mentioned_user_id = ?) OR
         (gm.sender_id = ?)
       )
       ORDER BY gm.created_at ASC',
      [job_id, owner_user_id, owner_user_id, user_id, user_id]
    )
  end
  
  { messages: messages, is_owner: is_owner }.to_json
end