総合演習：データ復旧ミッション

-- 顧客からの注文を管理するテーブル（イメージ）
-- project_members テーブルを「注文データ」として読み替える

-- project_members の role は以下に対応:
--   'PM'        → 注文ステータス: '処理済み'
--   'テックリード' → 注文ステータス: '処理中'
--   'エンジニア'  → 注文ステータス: '未処理'
--   'メンバー'   → 注文ステータス: '確認待ち'

SELECT
    e.name AS 社員名,
    d.name AS 部署名,
    COALESCE(p.name, '未参加') AS プロジェクト名,
    COALESCE(pm.role, '-') AS 役割
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
LEFT JOIN project_members pm ON e.id = pm.employee_id
LEFT JOIN projects p ON pm.project_id = p.id
ORDER BY e.id;

SELECT d.name AS 部署名, d.budget AS 予算
FROM departments d
LEFT JOIN projects p ON d.id = p.department_id
WHERE p.id IS NULL;

SELECT
    e.name AS 社員名,
    d.name AS 部署名,
    COUNT(pm.project_id) AS 参加PJ数
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN project_members pm ON e.id = pm.employee_id
GROUP BY e.name, d.name
HAVING COUNT(pm.project_id) >= 2;

SELECT
    e.name AS 社員名,
    d.name AS 部署名,
    e.salary AS 月給,
    (SELECT ROUND(AVG(e2.salary))
     FROM employees e2
     WHERE e2.department_id = e.department_id) AS 部署平均
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary < (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

SELECT
    p.name AS プロジェクト名,
    p.budget AS 予算,
    SUM(e.salary) AS メンバー月給合計,
    ROUND(CAST(SUM(e.salary) AS FLOAT) / p.budget * 100, 1) AS 月給割合
FROM projects p
INNER JOIN project_members pm ON p.id = pm.project_id
INNER JOIN employees e ON pm.employee_id = e.id
GROUP BY p.name, p.budget
ORDER BY 月給割合 DESC
LIMIT 1;

SELECT
    e.name AS 社員名,
    d.name AS 部署名,
    e.salary AS 月給,
    RANK() OVER (ORDER BY e.salary DESC) AS 全社ランキング,
    RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS 部署内ランキング
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY 全社ランキング;

# Step 1: L3 ネットワーク疎通確認
ping -c 3 192.168.1.100

# Step 2: 経路確認
traceroute 192.168.1.100

# Step 3: L4 ポート到達確認
nc -zv 192.168.1.100 3306

# Step 4: TCP接続状態の確認
ss -tn | grep 192.168.1.100

# Step 5: DNS解決の確認（ドメイン名を使う場合）
dig db.example.com

# Step 6: アプリケーション層の接続テスト
mysql -h 192.168.1.100 -P 3306 -u app_user -p

# DBサーバーへのTCP通信をキャプチャ
sudo tcpdump -i eth0 host 192.168.1.100 and port 3306 -c 20

# 出力の確認ポイント:
# Flags [S]    → SYN 送信（クライアント→サーバー）
# Flags [S.]   → SYN-ACK 受信（サーバー→クライアント）
# Flags [.]    → ACK 送信（クライアント→サーバー）
# この3つが揃えば3ウェイハンドシェイク成功

# SYNだけ送られてSYN-ACKが返ってこない場合:
# → ファイアウォールでブロックされている可能性
# → サーバーのポートがLISTENしていない可能性

# 結果をファイルに保存
sudo tcpdump -i eth0 host 192.168.1.100 and port 3306 -w db_debug.pcap

# 証明書の有効期限を確認
echo | openssl s_client -connect admin.example.com:443 -servername admin.example.com 2>/dev/null | openssl x509 -noout -dates

# 証明書の発行者を確認
echo | openssl s_client -connect admin.example.com:443 -servername admin.example.com 2>/dev/null | openssl x509 -noout -issuer

# 証明書チェーンを確認
openssl s_client -connect admin.example.com:443 -servername admin.example.com

# curl で詳細な接続情報を確認
curl -v https://admin.example.com 2>&1 | head -30

# 1. 証明書の更新
sudo certbot renew

# 2. 更新に失敗した場合、強制更新
sudo certbot renew --force-renewal

# 3. 更新後、nginx をリロード
sudo nginx -t          # 設定の構文チェック
sudo nginx -s reload   # リロード

# 4. 証明書の有効期限を再確認
echo | openssl s_client -connect admin.example.com:443 -servername admin.example.com 2>/dev/null | openssl x509 -noout -dates

# 5. 自動更新が設定されているか確認
sudo systemctl status certbot.timer
# または
crontab -l | grep certbot