CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  location VARCHAR(200),
  phone VARCHAR(20),
  line_id VARCHAR(100),
  role VARCHAR(20), -- 'requester' or 'helper'
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE requests (
  request_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  shop_name VARCHAR(200),
  items TEXT,
  preferred_date VARCHAR(100),
  status VARCHAR(20) DEFAULT 'waiting', -- 'waiting', 'matched', 'completed'
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE matches (
  match_id SERIAL PRIMARY KEY,
  request_id INT REFERENCES requests(request_id),
  helper_id INT REFERENCES users(user_id),
  matched_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP
);

const express = require('express');
const { Pool } = require('pg');
const app = express();

// データベース接続
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false }
});

app.use(express.json());

// 買い物依頼の投稿API
app.post('/api/requests', async (req, res) => {
  const { user_id, shop_name, items, preferred_date } = req.body;
  
  try {
    const result = await pool.query(
      'INSERT INTO requests (user_id, shop_name, items, preferred_date) VALUES ($1, $2, $3, $4) RETURNING *',
      [user_id, shop_name, items, preferred_date]
    );
    
    // LINE通知を送信（後述）
    await sendLineNotification(result.rows[0]);
    
    res.json({ success: true, request: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// 依頼一覧取得API
app.get('/api/requests', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM requests WHERE status = $1 ORDER BY created_at DESC',
      ['waiting']
    );
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(process.env.PORT || 3000);

const line = require('@line/bot-sdk');

const client = new line.Client({
  channelAccessToken: process.env.LINE_CHANNEL_ACCESS_TOKEN
});

// 新規依頼をサポーターに通知
async function sendLineNotification(request) {
  // サポーター全員のLINE IDを取得
  const helpers = await pool.query(
    "SELECT line_id FROM users WHERE role = 'helper' AND line_id IS NOT NULL"
  );
  
  const message = {
    type: 'text',
    text: `🛒 新しい買い物依頼が届きました！\n\n📍 ${request.shop_name}\n📝 ${request.items}\n\n依頼を確認: https://yourapp.com/requests/${request.request_id}`
  };
  
  // 全サポーターに通知
  for (const helper of helpers.rows) {
    try {
      await client.pushMessage(helper.line_id, message);
    } catch (err) {
      console.error('LINE通知エラー:', err);
    }
  }
}

// サポーター受諾API
app.post('/api/match', async (req, res) => {
  const { request_id, helper_id } = req.body;
  
  try {
    // トランザクション開始
    await pool.query('BEGIN');
    
    // マッチング記録を作成
    await pool.query(
      'INSERT INTO matches (request_id, helper_id) VALUES ($1, $2)',
      [request_id, helper_id]
    );
    
    // 依頼のステータスを更新
    await pool.query(
      "UPDATE requests SET status = 'matched' WHERE request_id = $1",
      [request_id]
    );
    
    // 依頼者にLINE通知
    const request = await pool.query(
      'SELECT * FROM requests WHERE request_id = $1',
      [request_id]
    );
    
    const requester = await pool.query(
      'SELECT * FROM users WHERE user_id = $1',
      [request.rows[0].user_id]
    );
    
    if (requester.rows[0].line_id) {
      await client.pushMessage(requester.rows[0].line_id, {
        type: 'text',
        text: `✅ サポーターが見つかりました！\n\n間もなく連絡があります。`
      });
    }
    
    await pool.query('COMMIT');
    res.json({ success: true });
  } catch (err) {
    await pool.query('ROLLBACK');
    res.status(500).json({ error: err.message });
  }
});

CREATE TABLE local_currency (
  transaction_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  amount INT, -- プラスが受取、マイナスが支払い
  transaction_type VARCHAR(50), -- 'reward', 'payment', 'bonus'
  related_match_id INT REFERENCES matches(match_id),
  created_at TIMESTAMP DEFAULT NOW()
);

-- サポーターへの謝礼付与
INSERT INTO local_currency (user_id, amount, transaction_type, related_match_id)
VALUES (helper_id, 200, 'reward', match_id);

-- 残高確認
SELECT SUM(amount) as balance 
FROM local_currency 
WHERE user_id = $1;