const path = require('path');
// .env 파일에서 환경 변수를 로드합니다. (경로: 상위 디렉토리) - 다른 모듈보다 먼저 로드해야 함!
require('dotenv').config({ path: path.join(__dirname, '../.env') });

const express = require('express');
const puppeteer = require('puppeteer');
const { PuppeteerScreenRecorder } = require('puppeteer-screen-recorder');
const cors = require('cors');
const fs = require('fs');
const axios = require('axios');
const { pipeline } = require('stream');
const { promisify } = require('util');
const { exec } = require('child_process');
const streamPipeline = promisify(pipeline);
const {
  // Legacy functions
  uploadVideo,
  getAuthenticatedClient,
  SCOPES,
  createPlaylist,
  getPlaylists,
  // New multi-user functions
  generateAuthUrl,
  exchangeCodeForTokens,
  getConnectionStatus,
  disconnectYouTube,
  uploadVideoForUser,
  getPlaylistsForUser,
  createPlaylistForUser,
  getUserYouTubeSettings,
  updateUserYouTubeSettings,
  getUploadHistory
} = require('./youtubeService');
const { google } = require('googleapis');
const jwt = require('jsonwebtoken');
const bcrypt = require('bcrypt');
const crypto = require('crypto');
const db = require('./db'); // SQLite DB
const { sendVerificationEmail, sendPasswordResetEmail, sendWelcomeEmail } = require('./emailService');
const {
  generateCreativeContent,
  generateAdvancedSpeech,
  generateAdPoster,
  generateImageGallery,
  filterBestImages,
  enrichDescriptionWithReviews,
  extractTextEffectFromImage,
  generateVideoBackground,
  generateYouTubeSEO
} = require('./geminiBackendService'); // Gemini Backend Service 임포트

// TikTok Service 임포트
const tiktokService = require('./tiktokService');

// Statistics Service 임포트
const statisticsService = require('./statisticsService');

const JWT_SECRET = process.env.JWT_SECRET || 'bizvibe-secret-key-change-this'; 

const app = express();
const PORT = process.env.PORT || 3001;

// 미들웨어 설정
app.use(cors());
app.use(express.json({ limit: '500mb' }));

// 임시 디렉토리 설정 (temp)
const TEMP_DIR = path.join(__dirname, 'temp');
if (!fs.existsSync(TEMP_DIR)) {
    fs.mkdirSync(TEMP_DIR);
}

// 다운로드 저장소 설정 (downloads) - 영구 저장용
const DOWNLOADS_DIR = path.join(__dirname, 'downloads');
if (!fs.existsSync(DOWNLOADS_DIR)) {
    fs.mkdirSync(DOWNLOADS_DIR);
}

// 정적 파일 제공
app.use('/temp', express.static(TEMP_DIR));
app.use('/downloads', express.static(DOWNLOADS_DIR));
app.use(express.static(path.join(__dirname, '../dist'))); // React 빌드 결과물

// DB 마이그레이션: render_status 컬럼 확인 및 추가
const ensureRenderStatusColumn = () => {
    db.all("PRAGMA table_info(history)", [], (err, rows) => {
        if (err) {
            console.error("DB 스키마 확인 실패:", err);
            return;
        }
        const hasColumn = rows.some(row => row.name === 'render_status');
        if (!hasColumn) {
            console.log("DB: render_status 컬럼 추가 중...");
            db.run("ALTER TABLE history ADD COLUMN render_status TEXT DEFAULT 'pending'", (err) => {
                if (err) console.error("컬럼 추가 실패:", err);
                else console.log("DB: render_status 컬럼 추가 완료.");
            });
        }
    });
};
ensureRenderStatusColumn();

// --- AUTH MIDDLEWARE ---
const authenticateToken = (req, res, next) => {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];

    if (!token) return res.sendStatus(401);

    jwt.verify(token, JWT_SECRET, (err, user) => {
        if (err) return res.sendStatus(403);
        req.user = user;
        next();
    });
};

const requireAdmin = (req, res, next) => {
    if (req.user && req.user.role === 'admin') {
        next();
    } else {
        res.status(403).json({ error: "관리자 권한이 필요합니다." });
    }
};

// --- ASSET UPLOAD API (Auto-Save) ---
app.post('/api/assets/upload', authenticateToken, async (req, res) => {
    try {
        const { businessName, posterBase64, audioBase64 } = req.body;
        
        const safeName = (businessName || 'project').replace(/[^a-z0-9가-힣]/gi, '_');
        const timestamp = Date.now();
        const folderName = `${timestamp}_${safeName}`;
        const projectDir = path.join(DOWNLOADS_DIR, folderName);

        if (!fs.existsSync(projectDir)) {
            fs.mkdirSync(projectDir, { recursive: true });
        }

        const result = {
            posterUrl: '',
            audioUrl: '',
            folderName
        };

        if (posterBase64) {
            const posterPath = path.join(projectDir, 'source_poster.jpg');
            fs.writeFileSync(posterPath, Buffer.from(posterBase64, 'base64'));
            result.posterUrl = `/downloads/${folderName}/source_poster.jpg`;
        }

        if (audioBase64) {
            const audioPath = path.join(projectDir, 'source_audio.mp3');
            fs.writeFileSync(audioPath, Buffer.from(audioBase64, 'base64'));
            result.audioUrl = `/downloads/${folderName}/source_audio.mp3`;
        }

        res.json(result);
    } catch (e) {
        console.error("에셋 업로드 실패:", e);
        res.status(500).json({ error: "파일 저장 실패" });
    }
});

// --- AUTH API ---

// 1. 회원가입 (이메일 인증 필요)
app.post('/api/auth/register', async (req, res) => {
    const { username, email, password, name, phone, businessName } = req.body;

    // 필수 필드 검증
    if (!username || !email || !password) {
        return res.status(400).json({ error: "ID, 이메일, 비밀번호는 필수입니다." });
    }

    // 이메일 형식 검증
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(email)) {
        return res.status(400).json({ error: "올바른 이메일 형식이 아닙니다." });
    }

    try {
        const salt = await bcrypt.genSalt(10);
        const hash = await bcrypt.hash(password, salt);
        const verificationToken = crypto.randomBytes(32).toString('hex');

        db.run(
            `INSERT INTO users (username, email, password, name, phone, business_name, approved, email_verified, verification_token)
             VALUES (?, ?, ?, ?, ?, ?, 1, 0, ?)`,
            [username, email, hash, name, phone, businessName, verificationToken],
            async function(err) {
                if (err) {
                    if (err.message.includes('UNIQUE constraint failed')) {
                        if (err.message.includes('email')) {
                            return res.status(400).json({ error: "이미 사용 중인 이메일입니다." });
                        }
                        return res.status(400).json({ error: "이미 존재하는 ID입니다." });
                    }
                    return res.status(500).json({ error: err.message });
                }

                // 인증 이메일 발송
                const emailResult = await sendVerificationEmail(email, name, verificationToken);

                if (emailResult.success) {
                    res.json({
                        message: "회원가입이 완료되었습니다. 이메일을 확인하여 인증을 완료해주세요.",
                        userId: this.lastID,
                        requireVerification: true
                    });
                } else {
                    // 이메일 발송 실패해도 회원가입은 완료 (재발송 가능)
                    console.error('인증 이메일 발송 실패:', emailResult.error);
                    res.json({
                        message: "회원가입이 완료되었습니다. 이메일 발송에 실패했습니다. 로그인 후 인증 메일을 재발송해주세요.",
                        userId: this.lastID,
                        requireVerification: true,
                        emailError: true
                    });
                }
            }
        );
    } catch (error) {
        console.error('회원가입 오류:', error);
        res.status(500).json({ error: "회원가입 처리 중 오류가 발생했습니다." });
    }
});

// 2. 이메일 인증 확인
app.get('/api/auth/verify-email', (req, res) => {
    const { token } = req.query;

    if (!token) {
        return res.status(400).json({ error: "인증 토큰이 필요합니다." });
    }

    db.get("SELECT * FROM users WHERE verification_token = ?", [token], async (err, user) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!user) return res.status(400).json({ error: "유효하지 않은 인증 토큰입니다." });

        if (user.email_verified === 1) {
            return res.json({ message: "이미 인증된 이메일입니다." });
        }

        db.run(
            "UPDATE users SET email_verified = 1, verification_token = NULL WHERE id = ?",
            [user.id],
            async function(err) {
                if (err) return res.status(500).json({ error: err.message });

                // 환영 이메일 발송
                await sendWelcomeEmail(user.email, user.name);

                res.json({ message: "이메일 인증이 완료되었습니다. 이제 로그인할 수 있습니다." });
            }
        );
    });
});

// 3. 인증 이메일 재발송
app.post('/api/auth/resend-verification', async (req, res) => {
    const { email } = req.body;

    if (!email) {
        return res.status(400).json({ error: "이메일이 필요합니다." });
    }

    db.get("SELECT * FROM users WHERE email = ?", [email], async (err, user) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!user) return res.status(400).json({ error: "등록되지 않은 이메일입니다." });

        if (user.email_verified === 1) {
            return res.json({ message: "이미 인증된 이메일입니다." });
        }

        const newToken = crypto.randomBytes(32).toString('hex');

        db.run("UPDATE users SET verification_token = ? WHERE id = ?", [newToken, user.id], async function(err) {
            if (err) return res.status(500).json({ error: err.message });

            const emailResult = await sendVerificationEmail(user.email, user.name, newToken);

            if (emailResult.success) {
                res.json({ message: "인증 이메일을 다시 보냈습니다. 이메일을 확인해주세요." });
            } else {
                res.status(500).json({ error: "이메일 발송에 실패했습니다. 잠시 후 다시 시도해주세요." });
            }
        });
    });
});

// 4. 로그인
app.post('/api/auth/login', (req, res) => {
    const { username, password } = req.body;

    // username 또는 email로 로그인 가능
    db.get(
        "SELECT * FROM users WHERE username = ? OR email = ?",
        [username, username],
        async (err, user) => {
            if (err) return res.status(500).json({ error: err.message });
            if (!user) return res.status(400).json({ error: "사용자를 찾을 수 없습니다." });

            const match = await bcrypt.compare(password, user.password);
            if (!match) return res.status(400).json({ error: "비밀번호가 일치하지 않습니다." });

            if (user.approved !== 1) {
                return res.status(403).json({ error: "계정이 아직 승인되지 않았습니다. 관리자에게 문의하세요." });
            }

            // 이메일 미인증 시 경고 (로그인은 허용하되 제한된 기능)
            const emailVerified = user.email_verified === 1;

            const token = jwt.sign(
                { id: user.id, username: user.username, email: user.email, role: user.role, name: user.name, emailVerified },
                JWT_SECRET,
                { expiresIn: '12h' }
            );
            res.json({
                token,
                user: {
                    id: user.id,
                    username: user.username,
                    email: user.email,
                    role: user.role,
                    name: user.name,
                    emailVerified
                },
                requireVerification: !emailVerified
            });
        }
    );
});

// 5. 비밀번호 재설정 요청
app.post('/api/auth/forgot-password', async (req, res) => {
    const { email } = req.body;

    if (!email) {
        return res.status(400).json({ error: "이메일이 필요합니다." });
    }

    db.get("SELECT * FROM users WHERE email = ?", [email], async (err, user) => {
        if (err) return res.status(500).json({ error: err.message });

        // 보안상 이메일 존재 여부를 알리지 않음
        if (!user) {
            return res.json({ message: "등록된 이메일이면 비밀번호 재설정 링크가 발송됩니다." });
        }

        const resetToken = crypto.randomBytes(32).toString('hex');
        const expiry = new Date(Date.now() + 60 * 60 * 1000); // 1시간 후 만료

        db.run(
            "UPDATE users SET reset_token = ?, reset_token_expiry = ? WHERE id = ?",
            [resetToken, expiry.toISOString(), user.id],
            async function(err) {
                if (err) return res.status(500).json({ error: err.message });

                const emailResult = await sendPasswordResetEmail(user.email, user.name, resetToken);

                // 성공/실패와 관계없이 동일한 응답 (보안)
                res.json({ message: "등록된 이메일이면 비밀번호 재설정 링크가 발송됩니다." });
            }
        );
    });
});

// 6. 비밀번호 재설정 실행
app.post('/api/auth/reset-password', async (req, res) => {
    const { token, newPassword } = req.body;

    if (!token || !newPassword) {
        return res.status(400).json({ error: "토큰과 새 비밀번호가 필요합니다." });
    }

    if (newPassword.length < 6) {
        return res.status(400).json({ error: "비밀번호는 6자 이상이어야 합니다." });
    }

    db.get("SELECT * FROM users WHERE reset_token = ?", [token], async (err, user) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!user) return res.status(400).json({ error: "유효하지 않은 토큰입니다." });

        // 토큰 만료 확인
        if (user.reset_token_expiry && new Date(user.reset_token_expiry) < new Date()) {
            return res.status(400).json({ error: "토큰이 만료되었습니다. 다시 비밀번호 재설정을 요청해주세요." });
        }

        const salt = await bcrypt.genSalt(10);
        const hash = await bcrypt.hash(newPassword, salt);

        db.run(
            "UPDATE users SET password = ?, reset_token = NULL, reset_token_expiry = NULL WHERE id = ?",
            [hash, user.id],
            function(err) {
                if (err) return res.status(500).json({ error: err.message });
                res.json({ message: "비밀번호가 성공적으로 변경되었습니다. 새 비밀번호로 로그인해주세요." });
            }
        );
    });
});

// ============================================
// OAuth 소셜 로그인 (Google, Naver)
// ============================================

const GOOGLE_CLIENT_ID = process.env.GOOGLE_CLIENT_ID;
const GOOGLE_CLIENT_SECRET = process.env.GOOGLE_CLIENT_SECRET;
const NAVER_CLIENT_ID = process.env.NAVER_CLIENT_ID;
const NAVER_CLIENT_SECRET = process.env.NAVER_CLIENT_SECRET;
const FRONTEND_URL = process.env.FRONTEND_URL || 'http://localhost:5173';
const BACKEND_URL = process.env.BACKEND_URL || `http://localhost:${PORT}`;

// Google OAuth 시작
app.get('/api/auth/google', (req, res) => {
    if (!GOOGLE_CLIENT_ID) {
        return res.status(500).json({ error: 'Google OAuth is not configured' });
    }

    const redirectUri = `${BACKEND_URL}/api/auth/google/callback`;
    const scope = encodeURIComponent('openid email profile');
    const state = crypto.randomBytes(16).toString('hex');

    const authUrl = `https://accounts.google.com/o/oauth2/v2/auth?` +
        `client_id=${GOOGLE_CLIENT_ID}&` +
        `redirect_uri=${encodeURIComponent(redirectUri)}&` +
        `response_type=code&` +
        `scope=${scope}&` +
        `state=${state}&` +
        `access_type=offline&` +
        `prompt=consent`;

    res.redirect(authUrl);
});

// Google OAuth 콜백
app.get('/api/auth/google/callback', async (req, res) => {
    const { code, error } = req.query;

    if (error) {
        return res.redirect(`${FRONTEND_URL}/login?error=${encodeURIComponent(error)}`);
    }

    if (!code) {
        return res.redirect(`${FRONTEND_URL}/login?error=no_code`);
    }

    try {
        const redirectUri = `${BACKEND_URL}/api/auth/google/callback`;

        // Exchange code for tokens
        const tokenRes = await axios.post('https://oauth2.googleapis.com/token', {
            code,
            client_id: GOOGLE_CLIENT_ID,
            client_secret: GOOGLE_CLIENT_SECRET,
            redirect_uri: redirectUri,
            grant_type: 'authorization_code'
        });

        const { access_token, id_token } = tokenRes.data;

        // Get user info
        const userInfoRes = await axios.get('https://www.googleapis.com/oauth2/v2/userinfo', {
            headers: { Authorization: `Bearer ${access_token}` }
        });

        const { id: googleId, email, name, picture } = userInfoRes.data;

        // Find or create user
        db.get(
            "SELECT * FROM users WHERE oauth_provider = 'google' AND oauth_provider_id = ?",
            [googleId],
            async (err, existingUser) => {
                if (err) {
                    console.error('DB error:', err);
                    return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                }

                if (existingUser) {
                    // Existing user - generate token
                    const token = jwt.sign(
                        { id: existingUser.id, username: existingUser.username, email: existingUser.email, role: existingUser.role, name: existingUser.name, emailVerified: true },
                        JWT_SECRET,
                        { expiresIn: '12h' }
                    );
                    return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
                }

                // Check if email already exists
                db.get("SELECT * FROM users WHERE email = ?", [email], async (err, emailUser) => {
                    if (err) {
                        console.error('DB error:', err);
                        return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                    }

                    if (emailUser) {
                        // Link Google account to existing user
                        db.run(
                            "UPDATE users SET oauth_provider = 'google', oauth_provider_id = ?, profile_image = ?, email_verified = 1 WHERE id = ?",
                            [googleId, picture, emailUser.id],
                            function(err) {
                                if (err) {
                                    console.error('DB error:', err);
                                    return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                                }

                                const token = jwt.sign(
                                    { id: emailUser.id, username: emailUser.username, email: emailUser.email, role: emailUser.role, name: emailUser.name || name, emailVerified: true },
                                    JWT_SECRET,
                                    { expiresIn: '12h' }
                                );
                                return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
                            }
                        );
                    } else {
                        // Create new user
                        const username = `google_${googleId.substring(0, 8)}`;
                        const randomPassword = crypto.randomBytes(32).toString('hex');
                        const salt = await bcrypt.genSalt(10);
                        const hash = await bcrypt.hash(randomPassword, salt);

                        db.run(
                            `INSERT INTO users (username, email, password, name, role, approved, email_verified, oauth_provider, oauth_provider_id, profile_image)
                             VALUES (?, ?, ?, ?, 'user', 1, 1, 'google', ?, ?)`,
                            [username, email, hash, name, googleId, picture],
                            function(err) {
                                if (err) {
                                    console.error('DB error:', err);
                                    return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                                }

                                const token = jwt.sign(
                                    { id: this.lastID, username, email, role: 'user', name, emailVerified: true },
                                    JWT_SECRET,
                                    { expiresIn: '12h' }
                                );
                                return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
                            }
                        );
                    }
                });
            }
        );
    } catch (error) {
        console.error('Google OAuth error:', error.response?.data || error.message);
        return res.redirect(`${FRONTEND_URL}/login?error=oauth_error`);
    }
});

// Naver OAuth 시작
app.get('/api/auth/naver', (req, res) => {
    if (!NAVER_CLIENT_ID) {
        return res.status(500).json({ error: 'Naver OAuth is not configured' });
    }

    const redirectUri = `${req.protocol}://${req.get('host')}/api/auth/naver/callback`;
    const state = crypto.randomBytes(16).toString('hex');

    const authUrl = `https://nid.naver.com/oauth2.0/authorize?` +
        `client_id=${NAVER_CLIENT_ID}&` +
        `redirect_uri=${encodeURIComponent(redirectUri)}&` +
        `response_type=code&` +
        `state=${state}`;

    res.redirect(authUrl);
});

// Naver OAuth 콜백
app.get('/api/auth/naver/callback', async (req, res) => {
    const { code, error, state } = req.query;

    if (error) {
        return res.redirect(`${FRONTEND_URL}/login?error=${encodeURIComponent(error)}`);
    }

    if (!code) {
        return res.redirect(`${FRONTEND_URL}/login?error=no_code`);
    }

    try {
        const redirectUri = `${req.protocol}://${req.get('host')}/api/auth/naver/callback`;

        // Exchange code for tokens
        const tokenRes = await axios.post('https://nid.naver.com/oauth2.0/token', null, {
            params: {
                grant_type: 'authorization_code',
                client_id: NAVER_CLIENT_ID,
                client_secret: NAVER_CLIENT_SECRET,
                code,
                state
            }
        });

        const { access_token } = tokenRes.data;

        // Get user info
        const userInfoRes = await axios.get('https://openapi.naver.com/v1/nid/me', {
            headers: { Authorization: `Bearer ${access_token}` }
        });

        const { response: naverUser } = userInfoRes.data;
        const { id: naverId, email, name, profile_image } = naverUser;

        // Find or create user
        db.get(
            "SELECT * FROM users WHERE oauth_provider = 'naver' AND oauth_provider_id = ?",
            [naverId],
            async (err, existingUser) => {
                if (err) {
                    console.error('DB error:', err);
                    return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                }

                if (existingUser) {
                    // Existing user - generate token
                    const token = jwt.sign(
                        { id: existingUser.id, username: existingUser.username, email: existingUser.email, role: existingUser.role, name: existingUser.name, emailVerified: true },
                        JWT_SECRET,
                        { expiresIn: '12h' }
                    );
                    return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
                }

                // Check if email already exists (if email is provided)
                if (email) {
                    db.get("SELECT * FROM users WHERE email = ?", [email], async (err, emailUser) => {
                        if (err) {
                            console.error('DB error:', err);
                            return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                        }

                        if (emailUser) {
                            // Link Naver account to existing user
                            db.run(
                                "UPDATE users SET oauth_provider = 'naver', oauth_provider_id = ?, profile_image = ?, email_verified = 1 WHERE id = ?",
                                [naverId, profile_image, emailUser.id],
                                function(err) {
                                    if (err) {
                                        console.error('DB error:', err);
                                        return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
                                    }

                                    const token = jwt.sign(
                                        { id: emailUser.id, username: emailUser.username, email: emailUser.email, role: emailUser.role, name: emailUser.name || name, emailVerified: true },
                                        JWT_SECRET,
                                        { expiresIn: '12h' }
                                    );
                                    return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
                                }
                            );
                            return;
                        }

                        // Create new user with email
                        createNaverUser(naverId, email, name, profile_image, res);
                    });
                } else {
                    // Create new user without email
                    createNaverUser(naverId, null, name, profile_image, res);
                }
            }
        );
    } catch (error) {
        console.error('Naver OAuth error:', error.response?.data || error.message);
        return res.redirect(`${FRONTEND_URL}/login?error=oauth_error`);
    }
});

// Helper function to create Naver user
async function createNaverUser(naverId, email, name, profile_image, res) {
    const username = `naver_${naverId.substring(0, 8)}`;
    const randomPassword = crypto.randomBytes(32).toString('hex');
    const salt = await bcrypt.genSalt(10);
    const hash = await bcrypt.hash(randomPassword, salt);

    db.run(
        `INSERT INTO users (username, email, password, name, role, approved, email_verified, oauth_provider, oauth_provider_id, profile_image)
         VALUES (?, ?, ?, ?, 'user', 1, 1, 'naver', ?, ?)`,
        [username, email, hash, name || 'Naver User', naverId, profile_image],
        function(err) {
            if (err) {
                console.error('DB error:', err);
                return res.redirect(`${FRONTEND_URL}/login?error=db_error`);
            }

            const token = jwt.sign(
                { id: this.lastID, username, email, role: 'user', name: name || 'Naver User', emailVerified: true },
                JWT_SECRET,
                { expiresIn: '12h' }
            );
            return res.redirect(`${FRONTEND_URL}/oauth/callback?token=${token}`);
        }
    );
}

// 7. 내 정보 확인
app.get('/api/auth/me', authenticateToken, (req, res) => {
    db.get(
        "SELECT id, username, email, name, phone, role, email_verified FROM users WHERE id = ?",
        [req.user.id],
        (err, user) => {
            if (err) return res.status(500).json({ error: err.message });
            if (!user) return res.status(404).json({ error: "사용자를 찾을 수 없습니다." });
            res.json(user);
        }
    );
});

// 8. 프로필 업데이트
app.put('/api/auth/profile', authenticateToken, async (req, res) => {
    const { name, phone } = req.body;

    db.run(
        "UPDATE users SET name = ?, phone = ? WHERE id = ?",
        [name, phone, req.user.id],
        function(err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ message: "프로필이 업데이트되었습니다." });
        }
    );
});

// 9. 비밀번호 변경 (로그인 상태)
app.put('/api/auth/change-password', authenticateToken, async (req, res) => {
    const { currentPassword, newPassword } = req.body;

    if (!currentPassword || !newPassword) {
        return res.status(400).json({ error: "현재 비밀번호와 새 비밀번호가 필요합니다." });
    }

    if (newPassword.length < 6) {
        return res.status(400).json({ error: "비밀번호는 6자 이상이어야 합니다." });
    }

    db.get("SELECT * FROM users WHERE id = ?", [req.user.id], async (err, user) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!user) return res.status(404).json({ error: "사용자를 찾을 수 없습니다." });

        const match = await bcrypt.compare(currentPassword, user.password);
        if (!match) return res.status(400).json({ error: "현재 비밀번호가 일치하지 않습니다." });

        const salt = await bcrypt.genSalt(10);
        const hash = await bcrypt.hash(newPassword, salt);

        db.run("UPDATE users SET password = ? WHERE id = ?", [hash, user.id], function(err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ message: "비밀번호가 변경되었습니다." });
        });
    });
});

// --- ADMIN API ---

// 1. 사용자 목록 조회 (승인 대기 포함)
app.get('/api/admin/users', authenticateToken, requireAdmin, (req, res) => {
    db.all("SELECT id, username, name, phone, business_name, role, approved, plan_type, credits, max_pensions, monthly_credits, createdAt FROM users ORDER BY createdAt DESC", [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

// 1.5. 비밀번호 초기화 (New)
app.post('/api/admin/users/:id/reset-password', authenticateToken, requireAdmin, async (req, res) => {
    const userId = req.params.id;
    const defaultPw = 'ado4!!!';
    const salt = await bcrypt.genSalt(10);
    const hash = await bcrypt.hash(defaultPw, salt);

    db.run("UPDATE users SET password = ? WHERE id = ?", [hash, userId], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ message: `비밀번호가 초기화되었습니다.` });
    });
});

// 2. 사용자 승인/반려
app.post('/api/admin/approve', authenticateToken, requireAdmin, (req, res) => {
    const { userId, approve } = req.body; // approve: true(승인), false(반려/미승인)
    const status = approve ? 1 : 0;
    
    db.run("UPDATE users SET approved = ? WHERE id = ?", [status, userId], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ message: `사용자 ID ${userId} 처리 완료 (상태: ${status})` });
    });
});

// 3. 사용자 삭제 (New)
app.delete('/api/admin/users/:id', authenticateToken, requireAdmin, (req, res) => {
    const userId = req.params.id;
    // 관리자 자신은 삭제 불가
    if (parseInt(userId) === req.user.id) {
        return res.status(400).json({ error: "자기 자신은 삭제할 수 없습니다." });
    }

    db.run("DELETE FROM users WHERE id = ?", [userId], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ message: "사용자가 삭제되었습니다." });
    });
});

// 4. 사용자 추가 (New)
app.post('/api/admin/users', authenticateToken, requireAdmin, async (req, res) => {
    const { username, password, name, phone, role, businessName } = req.body;
    if (!username || !password) return res.status(400).json({ error: "ID와 비밀번호는 필수입니다." });

    const salt = await bcrypt.genSalt(10);
    const hash = await bcrypt.hash(password, salt);
    const userRole = role || 'user';

    db.run(`INSERT INTO users (username, password, name, phone, role, approved, business_name) VALUES (?, ?, ?, ?, ?, 1, ?)`, 
        [username, hash, name, phone, userRole, businessName], 
        function(err) {
            if (err) {
                if (err.message.includes('UNIQUE constraint failed')) {
                    return res.status(400).json({ error: "이미 존재하는 ID입니다." });
                }
                return res.status(500).json({ error: err.message });
            }
            res.json({ message: "사용자가 생성되었습니다.", userId: this.lastID });
        }
    );
});

// 5. 전체 히스토리 조회
app.get('/api/admin/history', authenticateToken, requireAdmin, (req, res) => {
    const query = `
        SELECT h.*, u.username, u.name 
        FROM history h 
        JOIN users u ON h.user_id = u.id 
        ORDER BY h.createdAt DESC
    `;
    db.all(query, [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        const parsed = rows.map(row => ({
            ...row,
            details: JSON.parse(row.details)
        }));
        res.json(parsed);
    });
});

// --- USER HISTORY API ---

// 1. 히스토리 저장
app.post('/api/history', authenticateToken, (req, res) => {
    const { businessName, details, pensionId } = req.body;
    const detailsStr = JSON.stringify(details);

    db.run("INSERT INTO history (user_id, business_name, details, render_status, pension_id) VALUES (?, ?, ?, 'pending', ?)",
        [req.user.id, businessName, detailsStr, pensionId || null],
        function(err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ message: "히스토리 저장 완료", id: this.lastID });
        }
    );
});

// 2. 내 히스토리 조회
app.get('/api/history', authenticateToken, (req, res) => {
    db.all("SELECT * FROM history WHERE user_id = ? ORDER BY createdAt DESC", [req.user.id], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        const parsed = rows.map(row => ({
            ...row,
            details: JSON.parse(row.details)
        }));
        res.json(parsed);
    });
});

// 3. 히스토리 삭제 (단일) - 사용자 본인 것만
app.delete('/api/history/:id', authenticateToken, async (req, res) => {
    const historyId = req.params.id;
    const userId = req.user.id;

    try {
        // 먼저 해당 레코드가 사용자의 것인지 확인
        const row = await new Promise((resolve, reject) => {
            db.get("SELECT * FROM history WHERE id = ? AND user_id = ?", [historyId, userId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!row) {
            return res.status(404).json({ error: '히스토리를 찾을 수 없거나 권한이 없습니다.' });
        }

        // 관련 파일 삭제
        await deleteHistoryFiles(row);

        // DB에서 삭제
        await new Promise((resolve, reject) => {
            db.run("DELETE FROM history WHERE id = ?", [historyId], (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        console.log(`[History] 삭제 완료: ID ${historyId} (사용자: ${userId})`);
        res.json({ success: true, message: '삭제되었습니다.', deletedId: historyId });

    } catch (error) {
        console.error('[History] 삭제 오류:', error);
        res.status(500).json({ error: '삭제 중 오류가 발생했습니다.', details: error.message });
    }
});

// 4. 히스토리 일괄 삭제 - 사용자 본인 것만
app.delete('/api/history', authenticateToken, async (req, res) => {
    const { ids } = req.body;
    const userId = req.user.id;

    if (!ids || !Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({ error: '삭제할 ID 목록이 필요합니다.' });
    }

    try {
        // 사용자의 히스토리만 조회
        const placeholders = ids.map(() => '?').join(',');
        const rows = await new Promise((resolve, reject) => {
            db.all(
                `SELECT * FROM history WHERE id IN (${placeholders}) AND user_id = ?`,
                [...ids, userId],
                (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows);
                }
            );
        });

        if (rows.length === 0) {
            return res.status(404).json({ error: '삭제할 히스토리가 없습니다.' });
        }

        // 파일 삭제
        for (const row of rows) {
            await deleteHistoryFiles(row);
        }

        // DB에서 삭제
        const validIds = rows.map(r => r.id);
        const deletePlaceholders = validIds.map(() => '?').join(',');
        await new Promise((resolve, reject) => {
            db.run(`DELETE FROM history WHERE id IN (${deletePlaceholders})`, validIds, (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        console.log(`[History] 일괄 삭제 완료: ${validIds.length}개 (사용자: ${userId})`);
        res.json({ success: true, message: `${validIds.length}개 항목이 삭제되었습니다.`, deletedIds: validIds });

    } catch (error) {
        console.error('[History] 일괄 삭제 오류:', error);
        res.status(500).json({ error: '삭제 중 오류가 발생했습니다.', details: error.message });
    }
});

// 5. 관리자용 히스토리 삭제 (단일) - 모든 사용자
app.delete('/api/admin/history/:id', authenticateToken, requireAdmin, async (req, res) => {
    const historyId = req.params.id;

    try {
        const row = await new Promise((resolve, reject) => {
            db.get("SELECT * FROM history WHERE id = ?", [historyId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!row) {
            return res.status(404).json({ error: '히스토리를 찾을 수 없습니다.' });
        }

        await deleteHistoryFiles(row);

        await new Promise((resolve, reject) => {
            db.run("DELETE FROM history WHERE id = ?", [historyId], (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        console.log(`[Admin] 히스토리 삭제 완료: ID ${historyId}`);
        res.json({ success: true, message: '삭제되었습니다.', deletedId: historyId });

    } catch (error) {
        console.error('[Admin] 히스토리 삭제 오류:', error);
        res.status(500).json({ error: '삭제 중 오류가 발생했습니다.', details: error.message });
    }
});

// 6. 관리자용 히스토리 일괄 삭제 - 모든 사용자
app.delete('/api/admin/history', authenticateToken, requireAdmin, async (req, res) => {
    const { ids } = req.body;

    if (!ids || !Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({ error: '삭제할 ID 목록이 필요합니다.' });
    }

    try {
        const placeholders = ids.map(() => '?').join(',');
        const rows = await new Promise((resolve, reject) => {
            db.all(`SELECT * FROM history WHERE id IN (${placeholders})`, ids, (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });

        if (rows.length === 0) {
            return res.status(404).json({ error: '삭제할 히스토리가 없습니다.' });
        }

        for (const row of rows) {
            await deleteHistoryFiles(row);
        }

        const validIds = rows.map(r => r.id);
        const deletePlaceholders = validIds.map(() => '?').join(',');
        await new Promise((resolve, reject) => {
            db.run(`DELETE FROM history WHERE id IN (${deletePlaceholders})`, validIds, (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        console.log(`[Admin] 히스토리 일괄 삭제 완료: ${validIds.length}개`);
        res.json({ success: true, message: `${validIds.length}개 항목이 삭제되었습니다.`, deletedIds: validIds });

    } catch (error) {
        console.error('[Admin] 히스토리 일괄 삭제 오류:', error);
        res.status(500).json({ error: '삭제 중 오류가 발생했습니다.', details: error.message });
    }
});

/**
 * 유틸리티 함수: 히스토리 관련 파일 삭제
 * DB 레코드와 연관된 모든 파일/폴더를 삭제합니다.
 */
async function deleteHistoryFiles(historyRow) {
    const fsPromises = require('fs').promises;

    try {
        // final_video_path에서 폴더 경로 추출
        if (historyRow.final_video_path) {
            let videoPath = historyRow.final_video_path;

            // URL에서 localhost 제거
            if (videoPath.includes('localhost:3001')) {
                videoPath = videoPath.replace('http://localhost:3001', '');
            }

            // 상대 경로를 절대 경로로
            const absolutePath = path.join(__dirname, '..', videoPath);
            const folderPath = path.dirname(absolutePath);

            // downloads 폴더 내의 프로젝트 폴더 전체 삭제
            if (folderPath.includes('downloads') && fs.existsSync(folderPath)) {
                await fsPromises.rm(folderPath, { recursive: true, force: true });
                console.log(`[File] 폴더 삭제됨: ${folderPath}`);
            } else if (fs.existsSync(absolutePath)) {
                await fsPromises.unlink(absolutePath);
                console.log(`[File] 파일 삭제됨: ${absolutePath}`);
            }
        }

        // poster_path 삭제 (별도 파일인 경우)
        if (historyRow.poster_path && !historyRow.final_video_path?.includes(path.dirname(historyRow.poster_path))) {
            let posterPath = historyRow.poster_path;
            if (posterPath.includes('localhost:3001')) {
                posterPath = posterPath.replace('http://localhost:3001', '');
            }
            const absolutePosterPath = path.join(__dirname, '..', posterPath);
            if (fs.existsSync(absolutePosterPath)) {
                await fsPromises.unlink(absolutePosterPath);
                console.log(`[File] 포스터 삭제됨: ${absolutePosterPath}`);
            }
        }

    } catch (error) {
        console.error('[File] 파일 삭제 오류:', error.message);
        // 파일 삭제 실패해도 계속 진행 (DB 삭제는 수행)
    }
}

/**
 * 유틸리티 함수: 파일 다운로드
 * URL에서 파일을 다운로드하여 로컬 경로에 저장합니다.
 */
async function downloadFile(url, outputPath) {
    if (!url || url.startsWith('blob:')) {
        console.warn(`[다운로드] 유효하지 않거나 Blob URL입니다: ${url}`);
        return false;
    }
    console.log(`[다운로드] 파일 가져오는 중: ${url} -> ${outputPath}`);
    try {
        const controller = new AbortController();
        const timeout = setTimeout(() => controller.abort(), 60000); 

        const response = await fetch(url, {
            headers: {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
            },
            signal: controller.signal
        });
        clearTimeout(timeout);

        if (!response.ok) throw new Error(`실패: ${response.status} ${response.statusText}`);
        
        const stream = fs.createWriteStream(outputPath);
        await streamPipeline(response.body, stream);
        console.log(`[다운로드] 저장 완료.`);
        return true;
    } catch (e) {
        console.error(`[다운로드] 오류 발생 ${url}:`, e);
        return false;
    }
}

// --- NAVER API (크롤링) ---
const GRAPHQL_URL = "https://pcmap-api.place.naver.com/graphql";
const REQUEST_HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36",
    "Referer": "https://map.naver.com/",
    "Origin": "https://map.naver.com",
    "Content-Type": "application/json"
};

async function retry(fn, retries = 3, delay = 1000) {
    try {
        return await fn();
    } catch (error) {
        if (retries > 0 && (error.response?.status === 429 || error.code === 'ECONNABORTED' || error.code === 'ETIMEDOUT')) {
            console.warn(`[Retry] 요청 실패 (상태: ${error.response?.status || error.code}), ${delay / 1000}초 후 재시도... (남은 횟수: ${retries})`);
            await new Promise(resolve => setTimeout(resolve, delay));
            return retry(fn, retries - 1, delay * 2); 
        }
        throw error;
    }
}

const OVERVIEW_QUERY = `
  query getAccommodation($id: String!, $deviceType: String) {
    business: placeDetail(input: {id: $id, isNx: true, deviceType: $deviceType}) {
      base {
        id
        name
        category
        roadAddress
        address
        phone
        virtualPhone
        microReviews
        conveniences
        visitorReviewsTotal
      }
      images { images { origin url } }
      cpImages(source: [ugcImage]) { images { origin url } }
    }
  }
`;

// Fisher-Yates 셔플 알고리즘
function shuffleArray(array) {
    const shuffled = [...array];
    for (let i = shuffled.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [shuffled[i], shuffled[j]] = [shuffled[j], shuffled[i]];
    }
    return shuffled;
}

app.post('/api/naver/crawl', async (req, res) => {
    const { url } = req.body;
    if (!url) return res.status(400).json({ error: "URL이 필요합니다." });

    console.log(`[Naver] 크롤링 시작: ${url}`);

    try {
        let placeId = "";
        const match = url.match(/\/place\/(\d+)/);
        
        if (match && match[1]) {
            placeId = match[1];
        } else if (/^\d+$/.test(url)) {
            placeId = url; 
        } else {
            return res.status(400).json({ error: "URL에서 장소 ID를 찾을 수 없습니다. 예: https://map.naver.com/p/entry/place/12345678" });
        }

        console.log(`[Naver] ID 추출됨: ${placeId}`);

        const headers = { ...REQUEST_HEADERS };
        const naverCookies = getCookie('naver') || process.env.NAVER_COOKIES;
        if (naverCookies) {
            headers['Cookie'] = naverCookies.trim().replace(/^"|"$/g, '');
        }

        const response = await retry(async () => {
            return await axios.post(GRAPHQL_URL, {
                "operationName": "getAccommodation",
                "variables": { "id": placeId, "deviceType": "pc" },
                "query": OVERVIEW_QUERY,
            }, { headers, timeout: 5000 });
        }, 3, 1000);

        const business = response.data.data?.business;
        if (!business) return res.status(404).json({ error: "업체 정보를 찾을 수 없습니다." });

        const base = business.base || {};

        // 이미지 추출 (공식 이미지 + 사용자 제공 이미지)
        const rawImgs = [
            ...(business.images?.images || []),      // 공식 이미지
            ...(business.cpImages?.images || [])     // 사용자 제공 이미지 (UGC)
        ];
        const images = [];
        const seen = new Set();
        // 중복 제거하며 URL 수집
        for (const img of rawImgs) {
            const u = img.origin || img.url;
            if (u && !seen.has(u)) {
                seen.add(u);
                images.push(u);
            }
        }

        // 셔플하여 랜덤 순서로 반환
        const shuffledImages = shuffleArray(images);

        // 상세 설명 구성
        const descParts = [];
        if (base.name) descParts.push(`상호: ${base.name}`);
        if (base.category) descParts.push(`업종: ${base.category}`);
        if (base.roadAddress) descParts.push(`주소: ${base.roadAddress}`);
        if (base.phone || base.virtualPhone) descParts.push(`전화: ${base.phone || base.virtualPhone}`);
        if (base.microReviews) descParts.push(`키워드: ${base.microReviews.slice(0, 5).join(', ')}`);

        console.log(`[Naver] ${base.name}: 총 ${images.length}장 이미지 발견`);

        res.json({
            name: base.name,
            description: descParts.join('\n'),
            images: shuffledImages,  // 모든 이미지 반환 (제한 없음)
            totalImages: images.length,
            place_id: placeId,
            address: base.roadAddress || base.address,
            category: base.category
        });

    } catch (e) {
        console.error("[Naver] 오류:", e.message);
        res.status(500).json({ error: "크롤링 실패", details: e.message });
    }
});

// --- INSTAGRAM API (크롤링) ---
const INSTAGRAM_HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
    "Accept": "*/*",
    "Accept-Language": "ko-KR,ko;q=0.9,en-US;q=0.8,en;q=0.7",
    "X-IG-App-ID": "936619743392459",
    "X-Requested-With": "XMLHttpRequest",
    "Sec-Fetch-Site": "same-origin",
    "Sec-Fetch-Mode": "cors",
    "Sec-Fetch-Dest": "empty",
    "Referer": "https://www.instagram.com/"
};

// 쿠키를 메모리에 캐시 (DB에서 로드)
let cachedCookies = {
    naver: process.env.NAVER_COOKIES || '',
    instagram: process.env.INSTAGRAM_COOKIES || ''
};

// 서버 시작 시 DB에서 쿠키 로드
const loadCookiesFromDB = () => {
    db.all("SELECT setting_key, setting_value FROM system_settings WHERE setting_key IN ('naver_cookies', 'instagram_cookies')", [], (err, rows) => {
        if (!err && rows) {
            rows.forEach(row => {
                if (row.setting_key === 'naver_cookies' && row.setting_value) {
                    cachedCookies.naver = row.setting_value;
                }
                if (row.setting_key === 'instagram_cookies' && row.setting_value) {
                    cachedCookies.instagram = row.setting_value;
                }
            });
            console.log('[System] 쿠키 설정 로드 완료');
        }
    });
};
// 서버 시작 후 약간의 지연을 두고 로드 (DB 초기화 대기)
setTimeout(loadCookiesFromDB, 1000);

// 쿠키 가져오기 헬퍼 함수
const getCookie = (type) => {
    return cachedCookies[type] || '';
};

app.post('/api/instagram/crawl', async (req, res) => {
    const { url } = req.body;
    if (!url) return res.status(400).json({ error: "URL이 필요합니다." });

    console.log(`[Instagram] 크롤링 시작: ${url}`);

    try {
        // URL에서 username 추출
        let username = "";

        // instagram.com/username 또는 instagram.com/username/ 형식
        const match = url.match(/instagram\.com\/([a-zA-Z0-9._]+)/);
        if (match && match[1] && !['p', 'reel', 'stories', 'explore'].includes(match[1])) {
            username = match[1];
        } else {
            return res.status(400).json({ error: "유효한 인스타그램 프로필 URL이 아닙니다. 예: https://instagram.com/username" });
        }

        console.log(`[Instagram] Username 추출됨: ${username}`);

        const headers = { ...INSTAGRAM_HEADERS };
        const instagramCookies = getCookie('instagram');
        if (instagramCookies) {
            headers['Cookie'] = instagramCookies.trim().replace(/^"|"$/g, '');
        } else {
            console.warn('[Instagram] 쿠키가 설정되지 않았습니다. 일부 프로필은 접근이 제한될 수 있습니다.');
        }

        // Instagram GraphQL API 호출
        const apiUrl = `https://www.instagram.com/api/v1/users/web_profile_info/?username=${username}`;

        const response = await retry(async () => {
            return await axios.get(apiUrl, { headers, timeout: 10000 });
        }, 3, 1000);

        const userData = response.data?.data?.user;
        if (!userData) {
            return res.status(404).json({ error: "사용자를 찾을 수 없습니다. 비공개 계정이거나 존재하지 않는 사용자입니다." });
        }

        // 프로필 정보 추출
        const profileName = userData.full_name || username;
        const biography = userData.biography || '';

        // 최근 게시물에서 이미지 추출
        const edges = userData.edge_owner_to_timeline_media?.edges || [];
        const images = [];
        const seen = new Set();

        for (const edge of edges) {
            const node = edge.node;

            // 단일 이미지
            if (node.display_url && !seen.has(node.display_url)) {
                seen.add(node.display_url);
                images.push(node.display_url);
            }

            // 캐러셀 (여러 이미지)
            if (node.edge_sidecar_to_children?.edges) {
                for (const child of node.edge_sidecar_to_children.edges) {
                    const childUrl = child.node?.display_url;
                    if (childUrl && !seen.has(childUrl)) {
                        seen.add(childUrl);
                        images.push(childUrl);
                    }
                }
            }
        }

        // 셔플
        const shuffledImages = shuffleArray(images);

        console.log(`[Instagram] ${profileName}: 총 ${images.length}장 이미지 발견`);

        res.json({
            name: profileName,
            description: biography,
            images: shuffledImages,
            totalImages: images.length,
            username: username,
            address: '',
            category: 'Instagram'
        });

    } catch (e) {
        console.error("[Instagram] 오류:", e.message);
        if (e.response?.status === 401 || e.response?.status === 403) {
            res.status(401).json({ error: "인스타그램 인증이 필요합니다. 관리자에게 쿠키 설정을 요청하세요." });
        } else {
            res.status(500).json({ error: "크롤링 실패", details: e.message });
        }
    }
});

// --- 관리자 쿠키 관리 API ---
// 쿠키 조회 (관리자 전용)
app.get('/api/admin/cookies', authenticateToken, requireAdmin, (req, res) => {
    db.all("SELECT setting_key, setting_value, description, updatedAt FROM system_settings WHERE setting_key IN ('naver_cookies', 'instagram_cookies')", [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });

        // 쿠키 값 마스킹 (앞 20자만 표시)
        const result = {
            naver_cookies: { value: '', masked: '', updatedAt: null },
            instagram_cookies: { value: '', masked: '', updatedAt: null }
        };

        rows?.forEach(row => {
            const val = row.setting_value || '';
            result[row.setting_key] = {
                value: val,
                masked: val.length > 20 ? val.substring(0, 20) + '...' + ` (${val.length}자)` : val,
                updatedAt: row.updatedAt
            };
        });

        // .env에서 로드된 값도 표시 (DB에 없는 경우)
        if (!result.naver_cookies.value && process.env.NAVER_COOKIES) {
            result.naver_cookies.value = process.env.NAVER_COOKIES;
            result.naver_cookies.masked = '.env에서 로드됨';
            result.naver_cookies.source = 'env';
        }
        if (!result.instagram_cookies.value && process.env.INSTAGRAM_COOKIES) {
            result.instagram_cookies.value = process.env.INSTAGRAM_COOKIES;
            result.instagram_cookies.masked = '.env에서 로드됨';
            result.instagram_cookies.source = 'env';
        }

        res.json(result);
    });
});

// 쿠키 수정 (관리자 전용)
app.put('/api/admin/cookies', authenticateToken, requireAdmin, async (req, res) => {
    const { naver_cookies, instagram_cookies } = req.body;
    const userId = req.user.id;

    try {
        const updateCookie = (key, value) => {
            return new Promise((resolve, reject) => {
                db.run(`INSERT INTO system_settings (setting_key, setting_value, updated_by, updatedAt)
                        VALUES (?, ?, ?, datetime('now'))
                        ON CONFLICT(setting_key) DO UPDATE SET
                        setting_value = excluded.setting_value,
                        updated_by = excluded.updated_by,
                        updatedAt = datetime('now')`,
                    [key, value || '', userId],
                    function(err) {
                        if (err) reject(err);
                        else resolve();
                    }
                );
            });
        };

        if (naver_cookies !== undefined) {
            await updateCookie('naver_cookies', naver_cookies);
            cachedCookies.naver = naver_cookies || '';
        }
        if (instagram_cookies !== undefined) {
            await updateCookie('instagram_cookies', instagram_cookies);
            cachedCookies.instagram = instagram_cookies || '';
        }

        console.log(`[Admin] 쿠키 설정 업데이트됨 (by user ${userId})`);
        res.json({ success: true, message: '쿠키가 업데이트되었습니다.' });

    } catch (e) {
        console.error("[Admin] 쿠키 업데이트 오류:", e.message);
        res.status(500).json({ error: "쿠키 업데이트 실패" });
    }
});

// --- GENERIC IMAGE PROXY (for Naver etc.) ---
app.get('/api/proxy/image', async (req, res) => {
    try {
        const imageUrl = req.query.url;
        if (!imageUrl) {
            return res.status(400).send("URL parameter is required");
        }

        const response = await axios({
            method: 'get',
            url: imageUrl,
            responseType: 'stream'
        });

        if (response.headers['content-type']) {
            res.setHeader('Content-Type', response.headers['content-type']);
        }
        response.data.pipe(res);

    } catch (e) {
        console.error("[Proxy] Image download failed:", e.message);
        res.status(500).send("Image Proxy Error");
    }
});

// --- AUDIO PROXY (for Suno music URLs) ---
app.get('/api/proxy/audio', async (req, res) => {
    try {
        const audioUrl = req.query.url;
        if (!audioUrl) {
            return res.status(400).send("URL parameter is required");
        }

        console.log(`[Proxy] Audio download: ${audioUrl}`);
        const response = await axios({
            method: 'get',
            url: audioUrl,
            responseType: 'stream',
            timeout: 60000 // 60초 타임아웃
        });

        if (response.headers['content-type']) {
            res.setHeader('Content-Type', response.headers['content-type']);
        }
        res.setHeader('Content-Disposition', 'attachment; filename="audio.mp3"');
        response.data.pipe(res);

    } catch (e) {
        console.error("[Proxy] Audio download failed:", e.message);
        res.status(500).send("Audio Proxy Error");
    }
});

// --- GOOGLE PLACES API PROXY (여전히 프론트엔드에서 API 키를 보내야 함) ---
// Google Places API는 프론트엔드에서도 직접 호출할 수 있으나, CORS 문제 때문에 백엔드 프록시로 제공합니다.
// API Key는 여전히 프론트엔드에서 헤더로 전달받거나, 서버 .env에서 직접 사용 가능합니다.
// 여기서는 VITE_GEMINI_API_KEY를 Google Places API 키로 활용하고 있습니다.

// 1. 장소 검색 (Text Search)
app.post('/api/google/places/search', authenticateToken, async (req, res) => {
    try {
        const { textQuery, languageCode } = req.body;
        const apiKey = process.env.VITE_GEMINI_API_KEY; // 서버에서 직접 키 사용
        if (!apiKey) return res.status(500).json({ error: "Google Places API Key not configured on server." });

        const response = await axios.post('https://places.googleapis.com/v1/places:searchText', {
            textQuery,
            languageCode
        }, {
            headers: {
                'Content-Type': 'application/json',
                'X-Goog-Api-Key': apiKey,
                'X-Goog-FieldMask': 'places.name,places.displayName,places.formattedAddress'
            }
        });

        res.json(response.data);
    } catch (e) {
        console.error("[Google Proxy] 검색 실패:", e.message);
        res.status(e.response?.status || 500).json(e.response?.data || { error: e.message });
    }
});

// 2. 장소 상세 (Details)
app.post('/api/google/places/details', authenticateToken, async (req, res) => {
    try {
        const { placeId, fieldMask } = req.body;
        const apiKey = process.env.VITE_GEMINI_API_KEY;
        if (!apiKey) return res.status(500).json({ error: "Google Places API Key not configured on server." });

        const response = await axios.get(`https://places.googleapis.com/v1/places/${placeId}`, {
            headers: {
                'X-Goog-Api-Key': apiKey,
                'X-Goog-FieldMask': fieldMask || '*',
                'X-Goog-Place-Language-Code': 'ko'
            }
        });

        res.json(response.data);
    } catch (e) {
        console.error("[Google Proxy] 상세 조회 실패:", e.message);
        res.status(e.response?.status || 500).json(e.response?.data || { error: e.message });
    }
});

// 3. 사진 다운로드 (Photo)
app.post('/api/google/places/photo', authenticateToken, async (req, res) => {
    try {
        const { photoName, maxWidthPx } = req.body;
        const apiKey = process.env.VITE_GEMINI_API_KEY; 
        if (!apiKey) return res.status(500).json({ error: "Google Places API Key not configured on server." });

        const url = `https://places.googleapis.com/v1/${photoName}/media?maxHeightPx=${maxWidthPx || 800}&maxWidthPx=${maxWidthPx || 800}&key=${apiKey}`;
        
        const response = await axios({
            method: 'get',
            url: url,
            responseType: 'stream'
        });

        if (response.headers['content-type']) {
            res.setHeader('Content-Type', response.headers['content-type']);
        }
        response.data.pipe(res);

    } catch (e) {
        console.error("[Google Proxy] 사진 다운로드 실패:", e.message);
        res.status(500).send("Photo Error");
    }
});

// --- SUNO API PROXY ---
const SUNO_API_KEY = process.env.SUNO_API_KEY;
const SUNO_BASE_URL = "https://api.sunoapi.org/api/v1";

app.post('/api/suno/generate', authenticateToken, async (req, res) => {
    const payload = req.body;
    // console.log(`[Suno] 생성 요청: ${payload.title}`); // 디버그 로그 제거
    // console.log(`[Suno] API Key 존재 여부: ${!!SUNO_API_KEY}`); // 디버그 로그 제거

    try {
        if (!SUNO_API_KEY) throw new Error("Suno API Key가 서버에 설정되지 않았습니다.");

        const generateRes = await axios.post(`${SUNO_BASE_URL}/generate`, payload, {
            headers: {
                'Authorization': `Bearer ${SUNO_API_KEY}`,
                'Content-Type': 'application/json'
            }
        });

        if (generateRes.status !== 200) {
            throw new Error(`Suno API 요청 실패: ${generateRes.status} - ${JSON.stringify(generateRes.data)}`);
        }

        const taskId = generateRes.data.data?.taskId;
        if (!taskId) throw new Error("Task ID를 받지 못했습니다.");
        
        // console.log(`[Suno] 작업 시작: ${taskId}`); // 디버그 로그 제거

        let audioUrl = '';
        let attempts = 0;
        const maxAttempts = 100; 

        while (attempts < maxAttempts) {
            await new Promise(r => setTimeout(r, 3000)); 

            try {
                const statusRes = await axios.get(`${SUNO_BASE_URL}/generate/record-info?taskId=${taskId}`, {
                    headers: { 'Authorization': `Bearer ${SUNO_API_KEY}` }
                });

                const statusData = statusRes.data;
                const innerResponse = statusData.data?.response;
                const sunoData = innerResponse?.sunoData;
                const status = statusData.data?.status || "UNKNOWN";

                // console.log(`[Suno] 상태 폴링 [${attempts+1}/${maxAttempts}]: ${status}`); // 디버그 로그 제거

                if (sunoData && Array.isArray(sunoData) && sunoData.length > 0) {
                    const track = sunoData[0];
                    
                    if (['SUCCESS', 'FIRST_SUCCESS', 'TEXT_SUCCESS', 'complete', 'streaming'].includes(status)) {
                        if (track.audioUrl) {
                            audioUrl = track.audioUrl;
                            // console.log(`[Suno] 생성 완료: ${audioUrl}`); // 디버그 로그 제거
                            break;
                        }
                    } else if (['em', 'error', 'REJECTED'].includes(status)) {
                        throw new Error(`Suno 생성 실패 상태: ${status}`);
                    }
                }
            } catch (pollErr) {
                console.error(`[Suno] 폴링 중 일시적 오류:`, pollErr.message);
            }
            attempts++;
        }

        if (!audioUrl) {
            throw new Error("Suno 생성 시간 초과");
        }

        res.json({ audioUrl });

    } catch (e) {
        console.error("[Suno] 최종 실패:", e.message);
        res.status(500).json({ error: e.message, details: e.response?.data || null });
    }
});

// --- Gemini API Proxies (Frontend에서 API Key 숨기기) ---
// 모든 Gemini API 요청은 이 백엔드 프록시를 통해 이루어집니다.

app.post('/api/gemini/creative-content', authenticateToken, async (req, res) => {
    try {
        const result = await generateCreativeContent(req.body);
        res.json(result);
    } catch (error) {
        console.error("[Gemini Proxy] Creative Content Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/speech', authenticateToken, async (req, res) => {
    try {
        const { text, config } = req.body;
        const base64Audio = await generateAdvancedSpeech(text, config);
        res.json({ base64Audio });
    } catch (error) {
        console.error("[Gemini Proxy] TTS Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/ad-poster', authenticateToken, async (req, res) => {
    try {
        const { info } = req.body;
        const { base64, mimeType } = await generateAdPoster(info);
        res.json({ base64, mimeType });
    } catch (error) {
        console.error("[Gemini Proxy] Ad Poster Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/image-gallery', authenticateToken, async (req, res) => {
    try {
        const { info, count } = req.body;
        const images = await generateImageGallery(info, count);
        res.json({ images });
    } catch (error) {
        console.error("[Gemini Proxy] Image Gallery Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/video-background', authenticateToken, async (req, res) => {
    try {
        const { posterBase64, posterMimeType, aspectRatio } = req.body;
        const videoUrl = await generateVideoBackground(posterBase64, posterMimeType, aspectRatio);
        res.json({ videoUrl });
    } catch (error) {
        console.error("[Gemini Proxy] Video Background Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/text-effect', authenticateToken, async (req, res) => {
    try {
        const { imageFile } = req.body;
        const cssCode = await extractTextEffectFromImage(imageFile);
        res.json({ cssCode });
    } catch (error) {
        console.error("[Gemini Proxy] Text Effect Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/filter-images', authenticateToken, async (req, res) => {
    try {
        const { imagesData } = req.body;
        const filteredImages = await filterBestImages(imagesData);
        res.json({ filteredImages });
    } catch (error) {
        console.error("[Gemini Proxy] Filter Images Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/enrich-description', authenticateToken, async (req, res) => {
    try {
        const { name, rawDescription, reviews, rating } = req.body;
        const enrichedDescription = await enrichDescriptionWithReviews(name, rawDescription, reviews, rating);
        res.json({ enrichedDescription });
    } catch (error) {
        console.error("[Gemini Proxy] Enrich Description Error:", error);
        res.status(500).json({ error: error.message });
    }
});

app.post('/api/gemini/search-business', authenticateToken, async (req, res) => {
    try {
        const { query } = req.body;
        const result = await searchBusinessInfo(query, process.env.VITE_GEMINI_API_KEY); // Google Maps Tool은 API 키 필요
        res.json(result);
    } catch (error) {
        console.error("[Gemini Proxy] Search Business Error:", error);
        res.status(500).json({ error: error.message });
    }
});

// DNA 분석 (Google Search Grounding)
app.post('/api/gemini/analyze-dna', authenticateToken, async (req, res) => {
    try {
        const { nameOrUrl, images } = req.body;
        if (!nameOrUrl) {
            return res.status(400).json({ error: '펜션 이름 또는 URL이 필요합니다.' });
        }
        const dna = await geminiService.analyzeBusinessDNA(nameOrUrl, images || [], req.user.id);
        res.json({ dna });
    } catch (error) {
        console.error("[Gemini Proxy] Analyze DNA Error:", error);
        res.status(500).json({ error: error.message });
    }
});

// --- VIDEO RENDER QUEUE API ---
/**
 * 렌더링 작업 큐 시스템
 * - 작업 시작 시 크레딧 선차감
 * - 백그라운드에서 렌더링 처리
 * - 사용자가 로그아웃/페이지 이동해도 작업 계속
 */

// 동시 렌더링 제한
const MAX_CONCURRENT_RENDERS = 3;
let activeRenderCount = 0;

// 작업 ID 생성
function generateJobId() {
    return `job_${Date.now()}_${Math.random().toString(36).slice(2, 8)}`;
}

/**
 * POST /api/render/start
 * 렌더링 작업 시작 (크레딧 선차감, job_id 즉시 반환)
 * 계정당 동시 렌더링 1개로 제한
 */
app.post('/api/render/start', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const jobId = generateJobId();

    console.log(`[RenderQueue] 작업 요청: ${jobId} by user ${userId}`);

    try {
        // 0. 계정당 동시 렌더링 제한 체크 (pending 또는 processing 작업이 있으면 거부)
        const existingJob = await new Promise((resolve, reject) => {
            db.get(
                `SELECT id, status, progress, created_at FROM render_jobs
                 WHERE user_id = ? AND status IN ('pending', 'processing')
                 ORDER BY created_at DESC LIMIT 1`,
                [userId],
                (err, row) => err ? reject(err) : resolve(row)
            );
        });

        if (existingJob) {
            console.log(`[RenderQueue] 사용자 ${userId} 이미 진행 중인 작업 있음: ${existingJob.id} (${existingJob.status})`);
            return res.status(429).json({
                success: false,
                error: '이미 렌더링 작업이 진행 중입니다. 완료 후 다시 시도해주세요.',
                errorCode: 'RENDER_IN_PROGRESS',
                existingJobId: existingJob.id,
                existingJobStatus: existingJob.status,
                existingJobProgress: existingJob.progress
            });
        }

        // 1. 사용자 크레딧 확인
        const user = await new Promise((resolve, reject) => {
            db.get("SELECT credits, role FROM users WHERE id = ?", [userId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        // 관리자가 아니고 크레딧이 부족한 경우
        if (user.role !== 'admin' && (user.credits === null || user.credits <= 0)) {
            return res.status(403).json({
                success: false,
                error: '크레딧이 부족합니다. 추가 크레딧을 요청해주세요.',
                errorCode: 'INSUFFICIENT_CREDITS',
                credits: user.credits || 0
            });
        }

        // 2. 크레딧 선차감 (관리자는 제외)
        let creditsCharged = 0;
        if (user.role !== 'admin') {
            creditsCharged = 1;
            await new Promise((resolve, reject) => {
                db.run(
                    "UPDATE users SET credits = credits - 1 WHERE id = ?",
                    [userId],
                    (err) => err ? reject(err) : resolve()
                );
            });

            // 크레딧 변동 기록
            const newBalance = (user.credits || 0) - 1;
            await new Promise((resolve, reject) => {
                db.run(
                    `INSERT INTO credit_history (user_id, amount, type, description, balance_after)
                     VALUES (?, ?, ?, ?, ?)`,
                    [userId, -1, 'render', `영상 렌더링 (${jobId})`, newBalance],
                    (err) => err ? reject(err) : resolve()
                );
            });

            console.log(`[RenderQueue] 크레딧 차감: user ${userId}, 잔액 ${newBalance}`);
        }

        // 3. 입력 데이터 추출
        const {
            posterBase64,
            audioBase64,
            imagesBase64,
            adCopy = [],
            textEffect = 'effect-fade',
            businessName = 'CastAD',
            aspectRatio = '9:16',
            pensionId
        } = req.body;

        // 4. 작업 큐에 등록
        const inputData = JSON.stringify({
            posterBase64,
            audioBase64,
            imagesBase64,
            adCopy,
            textEffect,
            businessName,
            aspectRatio
        });

        await new Promise((resolve, reject) => {
            db.run(
                `INSERT INTO render_jobs (id, user_id, pension_id, status, input_data, credits_charged, created_at)
                 VALUES (?, ?, ?, 'pending', ?, ?, datetime('now'))`,
                [jobId, userId, pensionId || null, inputData, creditsCharged],
                (err) => err ? reject(err) : resolve()
            );
        });

        console.log(`[RenderQueue] 작업 등록 완료: ${jobId}`);

        // 5. 즉시 응답 (작업 ID 반환)
        res.json({
            success: true,
            jobId,
            message: '렌더링 작업이 시작되었습니다.',
            creditsCharged,
            creditsRemaining: user.role === 'admin' ? 'unlimited' : (user.credits || 0) - creditsCharged
        });

        // 6. 백그라운드에서 작업 처리 시작
        processRenderQueue();

    } catch (error) {
        console.error(`[RenderQueue] 작업 등록 실패: ${jobId}`, error);
        res.status(500).json({
            success: false,
            error: '렌더링 작업 등록에 실패했습니다.',
            details: error.message
        });
    }
});

/**
 * GET /api/render/status/:jobId
 * 작업 상태 조회
 */
app.get('/api/render/status/:jobId', authenticateToken, async (req, res) => {
    const { jobId } = req.params;
    const userId = req.user.id;

    try {
        const job = await new Promise((resolve, reject) => {
            db.get(
                `SELECT id, status, progress, output_path, history_id, error_message,
                        credits_charged, credits_refunded, created_at, started_at, completed_at
                 FROM render_jobs WHERE id = ? AND user_id = ?`,
                [jobId, userId],
                (err, row) => err ? reject(err) : resolve(row)
            );
        });

        if (!job) {
            return res.status(404).json({ success: false, error: '작업을 찾을 수 없습니다.' });
        }

        res.json({
            success: true,
            job: {
                ...job,
                downloadUrl: job.output_path ? `/downloads/${path.basename(path.dirname(job.output_path))}/final.mp4` : null
            }
        });

    } catch (error) {
        console.error(`[RenderQueue] 상태 조회 실패: ${jobId}`, error);
        res.status(500).json({ success: false, error: error.message });
    }
});

/**
 * GET /api/render/jobs
 * 내 작업 목록 조회
 */
app.get('/api/render/jobs', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const { status, limit = 20, offset = 0 } = req.query;

    try {
        let query = `SELECT id, status, progress, output_path, history_id, error_message,
                            credits_charged, credits_refunded, created_at, started_at, completed_at
                     FROM render_jobs WHERE user_id = ?`;
        const params = [userId];

        if (status) {
            query += ` AND status = ?`;
            params.push(status);
        }

        query += ` ORDER BY created_at DESC LIMIT ? OFFSET ?`;
        params.push(parseInt(limit), parseInt(offset));

        const jobs = await new Promise((resolve, reject) => {
            db.all(query, params, (err, rows) => err ? reject(err) : resolve(rows));
        });

        // 진행 중인 작업 수
        const pendingCount = await new Promise((resolve, reject) => {
            db.get(
                `SELECT COUNT(*) as count FROM render_jobs WHERE user_id = ? AND status IN ('pending', 'processing')`,
                [userId],
                (err, row) => err ? reject(err) : resolve(row?.count || 0)
            );
        });

        res.json({
            success: true,
            jobs: jobs.map(job => ({
                ...job,
                downloadUrl: job.output_path ? `/downloads/${path.basename(path.dirname(job.output_path))}/final.mp4` : null
            })),
            pendingCount
        });

    } catch (error) {
        console.error('[RenderQueue] 작업 목록 조회 실패:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

/**
 * 백그라운드 렌더링 Worker
 * pending 상태의 작업을 순차적으로 처리
 */
async function processRenderQueue() {
    // 동시 렌더링 제한 체크
    if (activeRenderCount >= MAX_CONCURRENT_RENDERS) {
        console.log(`[RenderWorker] 최대 동시 렌더링 수 (${MAX_CONCURRENT_RENDERS}) 도달, 대기`);
        return;
    }

    // pending 작업 가져오기
    const job = await new Promise((resolve, reject) => {
        db.get(
            `SELECT * FROM render_jobs WHERE status = 'pending' ORDER BY created_at ASC LIMIT 1`,
            (err, row) => err ? reject(err) : resolve(row)
        );
    }).catch(err => {
        console.error('[RenderWorker] 작업 조회 실패:', err);
        return null;
    });

    if (!job) {
        return; // 대기 중인 작업 없음
    }

    activeRenderCount++;
    console.log(`[RenderWorker] 작업 시작: ${job.id} (활성: ${activeRenderCount}/${MAX_CONCURRENT_RENDERS})`);

    try {
        // 상태 업데이트: processing
        await new Promise((resolve, reject) => {
            db.run(
                `UPDATE render_jobs SET status = 'processing', started_at = datetime('now'), progress = 5 WHERE id = ?`,
                [job.id],
                (err) => err ? reject(err) : resolve()
            );
        });

        // 입력 데이터 파싱
        const inputData = JSON.parse(job.input_data);
        const {
            posterBase64,
            audioBase64,
            imagesBase64,
            adCopy = [],
            textEffect = 'effect-fade',
            businessName = 'CastAD',
            aspectRatio = '9:16'
        } = inputData;

        // 프로젝트 폴더 생성
        const projectFolder = `render_${job.id}`;
        const projectPath = path.join(DOWNLOADS_DIR, projectFolder);
        fs.mkdirSync(projectPath, { recursive: true });

        // 진행률 업데이트 함수
        const updateProgress = async (progress) => {
            await new Promise((resolve) => {
                db.run(`UPDATE render_jobs SET progress = ? WHERE id = ?`, [progress, job.id], resolve);
            });
        };

        await updateProgress(10);

        // 파일 저장
        const audioPath = path.join(projectPath, 'audio.mp3');
        const videoPath = path.join(projectPath, 'video.webm');
        const finalPath = path.join(projectPath, 'final.mp4');

        if (audioBase64) {
            fs.writeFileSync(audioPath, Buffer.from(audioBase64, 'base64'));
        }

        await updateProgress(20);

        // 이미지 저장
        const imagePaths = [];
        if (imagesBase64 && imagesBase64.length > 0) {
            for (let i = 0; i < imagesBase64.length; i++) {
                const imgPath = path.join(projectPath, `image_${i}.jpg`);
                const imgData = imagesBase64[i].replace(/^data:image\/\w+;base64,/, '');
                fs.writeFileSync(imgPath, Buffer.from(imgData, 'base64'));
                imagePaths.push(imgPath);
            }
        }

        // 포스터 저장
        if (posterBase64) {
            const posterPath = path.join(projectPath, 'poster.jpg');
            const posterData = posterBase64.replace(/^data:image\/\w+;base64,/, '');
            fs.writeFileSync(posterPath, Buffer.from(posterData, 'base64'));
            if (imagePaths.length === 0) {
                imagePaths.push(posterPath);
            }
        }

        await updateProgress(30);

        // 비디오 크기 결정
        const isVertical = aspectRatio === '9:16';
        const width = isVertical ? 540 : 960;
        const height = isVertical ? 960 : 540;

        // HTML 템플릿 생성
        const htmlContent = generateRenderHTML({
            imagePaths: imagePaths.map(p => `file://${p}`),
            adCopy,
            textEffect,
            businessName,
            width,
            height
        });

        const htmlPath = path.join(projectPath, 'render.html');
        fs.writeFileSync(htmlPath, htmlContent);

        await updateProgress(40);

        // Puppeteer 녹화
        const browser = await puppeteer.launch({
            executablePath: process.env.PUPPETEER_EXECUTABLE_PATH || '/usr/bin/google-chrome-stable',
            headless: 'new',
            args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-web-security','--disable-dev-shm-usage', '--disable-gpu']
        });

        const page = await browser.newPage();
        await page.setViewport({ width, height });

        const recorder = new PuppeteerScreenRecorder(page, {
            fps: 30,
            ffmpeg_Path: null,
            videoFrame: { width, height },
            aspectRatio: isVertical ? '9:16' : '16:9'
        });

        await page.goto(`file://${htmlPath}`, { waitUntil: 'networkidle0' });
        await recorder.start(videoPath);

        await updateProgress(50);

        const durationMs = Math.max(30000, adCopy.length * 5000, imagePaths.length * 5000);
        await page.evaluate(() => {
            window.startAnimation && window.startAnimation();
        });

        await new Promise(resolve => setTimeout(resolve, durationMs));

        await recorder.stop();
        await browser.close();

        await updateProgress(80);

        // FFmpeg 오디오 합성
        if (fs.existsSync(audioPath)) {
            await new Promise((resolve, reject) => {
                const ffmpegCmd = `ffmpeg -y -i "${videoPath}" -i "${audioPath}" -c:v libx264 -preset fast -crf 23 -c:a aac -b:a 128k -shortest "${finalPath}"`;
                exec(ffmpegCmd, (error) => {
                    if (error) reject(error);
                    else resolve();
                });
            });
        } else {
            await new Promise((resolve, reject) => {
                const ffmpegCmd = `ffmpeg -y -i "${videoPath}" -c:v libx264 -preset fast -crf 23 "${finalPath}"`;
                exec(ffmpegCmd, (error) => {
                    if (error) reject(error);
                    else resolve();
                });
            });
        }

        await updateProgress(90);

        // History 레코드 생성
        const historyId = await new Promise((resolve, reject) => {
            db.run(
                `INSERT INTO history (user_id, business_name, details, final_video_path, pension_id) VALUES (?, ?, ?, ?, ?)`,
                [job.user_id, businessName, JSON.stringify({ adCopy, textEffect }), `/downloads/${projectFolder}/final.mp4`, job.pension_id],
                function(err) {
                    if (err) reject(err);
                    else resolve(this.lastID);
                }
            );
        });

        // 작업 완료 업데이트
        await new Promise((resolve, reject) => {
            db.run(
                `UPDATE render_jobs SET status = 'completed', progress = 100, output_path = ?, history_id = ?, completed_at = datetime('now') WHERE id = ?`,
                [finalPath, historyId, job.id],
                (err) => err ? reject(err) : resolve()
            );
        });

        console.log(`[RenderWorker] 작업 완료: ${job.id}`);

    } catch (error) {
        console.error(`[RenderWorker] 작업 실패: ${job.id}`, error);

        // 작업 실패 처리
        await new Promise((resolve) => {
            db.run(
                `UPDATE render_jobs SET status = 'failed', error_message = ?, completed_at = datetime('now') WHERE id = ?`,
                [error.message, job.id],
                resolve
            );
        });

        // 크레딧 환불 (실패 시)
        if (job.credits_charged > 0) {
            await new Promise((resolve) => {
                db.run(
                    `UPDATE users SET credits = credits + ? WHERE id = ?`,
                    [job.credits_charged, job.user_id],
                    resolve
                );
            });

            await new Promise((resolve) => {
                db.run(
                    `UPDATE render_jobs SET credits_refunded = ? WHERE id = ?`,
                    [job.credits_charged, job.id],
                    resolve
                );
            });

            // 환불 기록
            await new Promise((resolve) => {
                db.run(
                    `INSERT INTO credit_history (user_id, amount, type, description)
                     VALUES (?, ?, ?, ?)`,
                    [job.user_id, job.credits_charged, 'refund', `렌더링 실패 환불 (${job.id})`],
                    resolve
                );
            });

            console.log(`[RenderWorker] 크레딧 환불: user ${job.user_id}, ${job.credits_charged} 크레딧`);
        }

    } finally {
        activeRenderCount--;

        // 다음 작업 처리
        setTimeout(() => processRenderQueue(), 100);
    }
}

// 서버 시작 시 미처리 작업 복구
setTimeout(() => {
    console.log('[RenderWorker] 미처리 작업 확인...');

    // processing 상태로 남은 작업을 pending으로 복구
    db.run(
        `UPDATE render_jobs SET status = 'pending', started_at = NULL, progress = 0 WHERE status = 'processing'`,
        (err) => {
            if (!err) {
                processRenderQueue();
            }
        }
    );
}, 3000);

// --- VIDEO RENDER API (기존 - 하위 호환성 유지) ---
/**
 * 서버 사이드 영상 렌더링 엔드포인트 (동기식 - deprecated)
 * 새로운 /api/render/start 사용 권장
 */
app.post('/render', authenticateToken, async (req, res) => {
    const startTime = Date.now();
    const projectFolder = `render_${Date.now()}_${Math.random().toString(36).slice(2, 8)}`;
    const projectPath = path.join(DOWNLOADS_DIR, projectFolder);

    console.log(`[Render] 시작: ${projectFolder}`);

    // 크레딧 체크 (관리자는 무제한)
    try {
        const user = await new Promise((resolve, reject) => {
            db.get("SELECT credits, role FROM users WHERE id = ?", [req.user.id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (user.role !== 'admin' && (user.credits === null || user.credits <= 0)) {
            return res.status(403).json({
                error: '크레딧이 부족합니다. 추가 크레딧을 요청해주세요.',
                errorCode: 'INSUFFICIENT_CREDITS',
                credits: user.credits || 0
            });
        }
    } catch (creditErr) {
        console.error('[Render] 크레딧 확인 오류:', creditErr);
        return res.status(500).json({ error: '크레딧 확인 중 오류가 발생했습니다.' });
    }

    try {
        const {
            posterBase64,
            audioBase64,
            imagesBase64,
            adCopy = [],
            textEffect = 'effect-fade',
            businessName = 'CastAD',
            aspectRatio = '9:16',
            historyId
        } = req.body;

        // 프로젝트 폴더 생성
        fs.mkdirSync(projectPath, { recursive: true });

        // 1. 파일 저장
        const audioPath = path.join(projectPath, 'audio.mp3');
        const videoPath = path.join(projectPath, 'video.webm');
        const finalPath = path.join(projectPath, 'final.mp4');

        // 오디오 저장
        if (audioBase64) {
            fs.writeFileSync(audioPath, Buffer.from(audioBase64, 'base64'));
            console.log(`[Render] 오디오 저장 완료: ${audioPath}`);
        }

        // 이미지 저장
        const imagePaths = [];
        if (imagesBase64 && imagesBase64.length > 0) {
            for (let i = 0; i < imagesBase64.length; i++) {
                const imgPath = path.join(projectPath, `image_${i}.jpg`);
                const imgData = imagesBase64[i].replace(/^data:image\/\w+;base64,/, '');
                fs.writeFileSync(imgPath, Buffer.from(imgData, 'base64'));
                imagePaths.push(imgPath);
            }
            console.log(`[Render] ${imagePaths.length}개 이미지 저장 완료`);
        }

        // 포스터 저장 (이미지가 없을 경우 대체)
        if (posterBase64) {
            const posterPath = path.join(projectPath, 'poster.jpg');
            const posterData = posterBase64.replace(/^data:image\/\w+;base64,/, '');
            fs.writeFileSync(posterPath, Buffer.from(posterData, 'base64'));
            if (imagePaths.length === 0) {
                imagePaths.push(posterPath);
            }
        }

        // 비디오 크기 결정
        const isVertical = aspectRatio === '9:16';
        const width = isVertical ? 540 : 960;
        const height = isVertical ? 960 : 540;

        // 2. HTML 템플릿 생성
        const htmlContent = generateRenderHTML({
            imagePaths: imagePaths.map(p => `file://${p}`),
            adCopy,
            textEffect,
            businessName,
            width,
            height
        });

        const htmlPath = path.join(projectPath, 'render.html');
        fs.writeFileSync(htmlPath, htmlContent);
        console.log(`[Render] HTML 템플릿 생성 완료`);

        // 3. Puppeteer로 영상 녹화
        const browser = await puppeteer.launch({
            executablePath: process.env.PUPPETEER_EXECUTABLE_PATH || '/usr/bin/google-chrome-stable',
            headless: 'new',
            args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-web-security','--disable-dev-shm-usage', '--disable-gpu']
        });

        const page = await browser.newPage();
        await page.setViewport({ width, height });

        // 녹화 설정
        const recorder = new PuppeteerScreenRecorder(page, {
            fps: 30,
            ffmpeg_Path: null, // 시스템 ffmpeg 사용
            videoFrame: { width, height },
            aspectRatio: isVertical ? '9:16' : '16:9'
        });

        await page.goto(`file://${htmlPath}`, { waitUntil: 'networkidle0' });

        // 애니메이션 시작 및 녹화
        await recorder.start(videoPath);
        console.log(`[Render] 녹화 시작`);

        // 슬라이드쇼 재생 시간 계산 (각 슬라이드 5초 + 텍스트 4초)
        const durationMs = Math.max(30000, adCopy.length * 5000, imagePaths.length * 5000);
        await page.evaluate((duration) => {
            window.startAnimation && window.startAnimation();
        }, durationMs);

        await new Promise(resolve => setTimeout(resolve, durationMs));

        await recorder.stop();
        await browser.close();
        console.log(`[Render] 녹화 완료: ${videoPath}`);

        // 4. FFmpeg로 오디오 합성
        if (fs.existsSync(audioPath)) {
            await new Promise((resolve, reject) => {
                const ffmpegCmd = `ffmpeg -y -i "${videoPath}" -i "${audioPath}" -c:v libx264 -preset fast -crf 23 -c:a aac -b:a 128k -shortest "${finalPath}"`;
                exec(ffmpegCmd, (error, stdout, stderr) => {
                    if (error) {
                        console.error('[FFmpeg] 오류:', stderr);
                        reject(error);
                    } else {
                        console.log('[Render] FFmpeg 합성 완료');
                        resolve();
                    }
                });
            });
        } else {
            // 오디오 없이 비디오만 변환
            await new Promise((resolve, reject) => {
                const ffmpegCmd = `ffmpeg -y -i "${videoPath}" -c:v libx264 -preset fast -crf 23 "${finalPath}"`;
                exec(ffmpegCmd, (error) => {
                    if (error) reject(error);
                    else resolve();
                });
            });
        }

        // 5. DB 업데이트 (히스토리에 파일 경로 저장)
        if (historyId) {
            const relativePath = `/downloads/${projectFolder}/final.mp4`;
            db.run("UPDATE history SET final_video_path = ?, render_status = 'completed' WHERE id = ?",
                [relativePath, historyId]);

            // 5.1 에셋 자동 저장 (렌더링된 영상 + 포스터 + 오디오 + 소스 이미지)
            try {
                let totalAssetSize = 0;
                const videoStats = fs.statSync(finalPath);
                totalAssetSize += videoStats.size;

                // 렌더링된 영상 저장
                db.run(`
                    INSERT INTO user_assets (user_id, history_id, asset_type, source_type, file_name, file_path, file_size, mime_type)
                    VALUES (?, ?, 'video', 'rendered', ?, ?, ?, 'video/mp4')
                `, [req.user.id, historyId, `final.mp4`, relativePath, videoStats.size]);

                // 포스터 저장 (있으면)
                const posterFilePath = path.join(projectPath, 'poster.jpg');
                if (fs.existsSync(posterFilePath)) {
                    const posterStats = fs.statSync(posterFilePath);
                    totalAssetSize += posterStats.size;
                    db.run(`
                        INSERT INTO user_assets (user_id, history_id, asset_type, source_type, file_name, file_path, file_size, mime_type)
                        VALUES (?, ?, 'image', 'ai_generated', ?, ?, ?, 'image/jpeg')
                    `, [req.user.id, historyId, 'poster.jpg', `/downloads/${projectFolder}/poster.jpg`, posterStats.size]);
                }

                // 오디오 저장 (있으면)
                const audioFilePath = path.join(projectPath, 'audio.mp3');
                if (fs.existsSync(audioFilePath)) {
                    const audioStats = fs.statSync(audioFilePath);
                    totalAssetSize += audioStats.size;
                    db.run(`
                        INSERT INTO user_assets (user_id, history_id, asset_type, source_type, file_name, file_path, file_size, mime_type)
                        VALUES (?, ?, 'audio', 'ai_generated', ?, ?, ?, 'audio/mpeg')
                    `, [req.user.id, historyId, 'audio.mp3', `/downloads/${projectFolder}/audio.mp3`, audioStats.size]);
                }

                // 소스 이미지들 저장 (크롤링/업로드된 이미지 - 영상에 사용된 것)
                if (imagePaths && imagePaths.length > 0) {
                    for (let i = 0; i < imagePaths.length; i++) {
                        const imgPath = imagePaths[i];
                        if (fs.existsSync(imgPath)) {
                            const imgStats = fs.statSync(imgPath);
                            totalAssetSize += imgStats.size;
                            db.run(`
                                INSERT INTO user_assets (user_id, history_id, asset_type, source_type, file_name, file_path, file_size, mime_type)
                                VALUES (?, ?, 'image', 'crawl', ?, ?, ?, 'image/jpeg')
                            `, [req.user.id, historyId, `image_${i}.jpg`, `/downloads/${projectFolder}/image_${i}.jpg`, imgStats.size]);
                        }
                    }
                }

                // 스토리지 사용량 업데이트
                db.run(`UPDATE users SET storage_used = storage_used + ? WHERE id = ?`, [totalAssetSize, req.user.id]);

                console.log(`[Render] 에셋 저장 완료: video + poster + audio + ${imagePaths?.length || 0} images (${(totalAssetSize / 1024 / 1024).toFixed(2)} MB)`);
            } catch (assetErr) {
                console.error('[Render] 에셋 저장 오류 (무시):', assetErr);
            }
        }

        const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
        console.log(`[Render] 완료! (${elapsed}초)`);

        // 5.5. 크레딧 차감 (관리자 제외)
        try {
            const userInfo = await new Promise((resolve, reject) => {
                db.get("SELECT credits, role FROM users WHERE id = ?", [req.user.id], (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                });
            });

            if (userInfo.role !== 'admin') {
                const newBalance = Math.max(0, (userInfo.credits || 0) - 1);
                await new Promise((resolve, reject) => {
                    db.run("UPDATE users SET credits = ? WHERE id = ?", [newBalance, req.user.id], (err) => {
                        if (err) reject(err);
                        else resolve();
                    });
                });

                // 크레딧 히스토리 기록
                await new Promise((resolve, reject) => {
                    db.run(`
                        INSERT INTO credit_history (user_id, amount, type, description, balance_after)
                        VALUES (?, -1, 'video_render', ?, ?)
                    `, [req.user.id, `영상 생성: ${businessName}`, newBalance], (err) => {
                        if (err) reject(err);
                        else resolve();
                    });
                });

                console.log(`[Render] 크레딧 차감: ${req.user.username} (잔액: ${newBalance})`);
            }
        } catch (creditErr) {
            console.error('[Render] 크레딧 차감 오류 (무시):', creditErr);
            // 크레딧 차감 실패해도 영상은 전달
        }

        // 6. 파일 응답 (한글 파일명 RFC 5987 인코딩)
        res.setHeader('Content-Type', 'video/mp4');
        const safeFilename = `CastAD_${businessName}.mp4`;
        const encodedFilename = encodeURIComponent(safeFilename);
        res.setHeader('Content-Disposition', `attachment; filename="CastAD_video.mp4"; filename*=UTF-8''${encodedFilename}`);
        res.setHeader('X-Project-Folder', encodeURIComponent(projectFolder));
        res.sendFile(finalPath);

    } catch (error) {
        console.error('[Render] 오류:', error);

        // 실패 시 폴더 정리
        try {
            if (fs.existsSync(projectPath)) {
                fs.rmSync(projectPath, { recursive: true });
            }
        } catch (e) {}

        res.status(500).json({ error: error.message || '영상 렌더링 실패' });
    }
});

/**
 * 렌더링용 HTML 템플릿 생성
 */
function generateRenderHTML({ imagePaths, adCopy, textEffect, businessName, width, height }) {
    const slideCount = Math.max(imagePaths.length, 1);
    const slideDuration = 5; // 각 슬라이드 5초 (천천히)
    const totalDuration = slideCount * slideDuration;

    // 텍스트 이펙트 CSS
    const textEffectCSS = getTextEffectCSS(textEffect);

    return `<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            width: ${width}px;
            height: ${height}px;
            overflow: hidden;
            background: #000;
            font-family: 'Noto Sans KR', sans-serif;
        }
        .slideshow {
            position: relative;
            width: 100%;
            height: 100%;
        }
        .slide {
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            opacity: 0;
            transition: opacity 1.5s ease-in-out;
        }
        .slide.active { opacity: 1; }
        .slide img {
            width: 100%;
            height: 100%;
            object-fit: cover;
        }
        .text-overlay {
            position: absolute;
            bottom: 15%;
            left: 50%;
            transform: translateX(-50%);
            width: 90%;
            text-align: center;
            color: white;
            font-size: ${Math.floor(height / 20)}px;
            font-weight: bold;
            text-shadow: 2px 2px 8px rgba(0,0,0,0.8);
            opacity: 0;
        }
        .text-overlay.show {
            opacity: 1;
            animation: textIn 1.2s ease-out;
        }
        @keyframes textIn {
            from { opacity: 0; transform: translateX(-50%) translateY(30px); }
            to { opacity: 1; transform: translateX(-50%) translateY(0); }
        }
        ${textEffectCSS}
        .brand-watermark {
            position: absolute;
            bottom: 20px;
            right: 20px;
            color: rgba(255,255,255,0.5);
            font-size: 14px;
        }
    </style>
</head>
<body>
    <div class="slideshow">
        ${imagePaths.map((img, i) => `
            <div class="slide ${i === 0 ? 'active' : ''}" data-index="${i}">
                <img src="${img}" alt="slide ${i}">
            </div>
        `).join('')}
        <div class="text-overlay ${textEffect}" id="textOverlay"></div>
        <div class="brand-watermark">CastAD</div>
    </div>
    <script>
        const slides = document.querySelectorAll('.slide');
        const textOverlay = document.getElementById('textOverlay');
        const adCopy = ${JSON.stringify(adCopy)};
        const slideDuration = ${slideDuration * 1000};
        let currentSlide = 0;
        let currentText = 0;

        window.startAnimation = function() {
            // 슬라이드쇼 시작
            setInterval(() => {
                slides[currentSlide].classList.remove('active');
                currentSlide = (currentSlide + 1) % slides.length;
                slides[currentSlide].classList.add('active');
            }, slideDuration);

            // 텍스트 애니메이션
            if (adCopy.length > 0) {
                textOverlay.textContent = adCopy[0];
                textOverlay.classList.add('show');

                setInterval(() => {
                    textOverlay.classList.remove('show');
                    setTimeout(() => {
                        currentText = (currentText + 1) % adCopy.length;
                        textOverlay.textContent = adCopy[currentText];
                        textOverlay.classList.add('show');
                    }, 300);
                }, slideDuration);
            }
        };

        // 자동 시작
        window.startAnimation();
    </script>
</body>
</html>`;
}

/**
 * 텍스트 이펙트별 CSS 반환
 */
function getTextEffectCSS(effect) {
    const effects = {
        'effect-fade': '',
        'effect-bounce': `
            .effect-bounce.show {
                animation: bounceIn 0.6s cubic-bezier(0.68, -0.55, 0.265, 1.55);
            }
            @keyframes bounceIn {
                0% { transform: translateX(-50%) scale(0.3); opacity: 0; }
                50% { transform: translateX(-50%) scale(1.05); }
                100% { transform: translateX(-50%) scale(1); opacity: 1; }
            }
        `,
        'effect-typewriter': `
            .effect-typewriter {
                overflow: hidden;
                white-space: nowrap;
                border-right: 3px solid white;
                animation: typing 2s steps(40, end), blink-caret 0.75s step-end infinite;
            }
            @keyframes typing { from { width: 0 } to { width: 100% } }
            @keyframes blink-caret { from, to { border-color: transparent } 50% { border-color: white } }
        `,
        'effect-glow': `
            .effect-glow {
                text-shadow: 0 0 10px #fff, 0 0 20px #fff, 0 0 30px #a855f7, 0 0 40px #a855f7;
            }
        `,
        'effect-neon': `
            .effect-neon {
                text-shadow: 0 0 5px #fff, 0 0 10px #fff, 0 0 20px #ff00de, 0 0 30px #ff00de, 0 0 40px #ff00de;
                animation: neonPulse 1.5s ease-in-out infinite alternate;
            }
            @keyframes neonPulse {
                from { text-shadow: 0 0 5px #fff, 0 0 10px #fff, 0 0 20px #ff00de; }
                to { text-shadow: 0 0 10px #fff, 0 0 20px #fff, 0 0 40px #ff00de, 0 0 60px #ff00de; }
            }
        `
    };
    return effects[effect] || '';
}

// ==================== PENSION PROFILE API ROUTES (다중 펜션 지원) ====================

/**
 * 모든 펜션 프로필 조회
 * GET /api/profile/pensions
 */
app.get('/api/profile/pensions', authenticateToken, (req, res) => {
    const userId = req.user.id;
    db.all(`SELECT * FROM pension_profiles WHERE user_id = ? ORDER BY is_default DESC, createdAt DESC`, [userId], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows || []);
    });
});

/**
 * 기본 펜션 프로필 조회 (하위 호환)
 * GET /api/profile/pension
 */
app.get('/api/profile/pension', authenticateToken, (req, res) => {
    const userId = req.user.id;
    // 먼저 기본 펜션을 찾고, 없으면 첫 번째 펜션 반환
    db.get(`SELECT * FROM pension_profiles WHERE user_id = ? ORDER BY is_default DESC, createdAt ASC LIMIT 1`, [userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(row || null);
    });
});

/**
 * 특정 펜션 프로필 조회
 * GET /api/profile/pension/:id
 */
app.get('/api/profile/pension/:id', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    db.get(`SELECT * FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });
        res.json(row);
    });
});

/**
 * 새 펜션 프로필 생성
 * POST /api/profile/pension
 */
app.post('/api/profile/pension', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const {
        brand_name, brand_name_en, region, address, pension_types,
        target_customers, key_features, nearby_attractions, booking_url,
        homepage_url, kakao_channel, instagram_handle, languages,
        price_range, description, is_default
    } = req.body;

    // 사용자 플랜 확인 및 펜션 제한 체크
    db.get(`SELECT plan_type, max_pensions FROM users WHERE id = ?`, [userId], (err, userRow) => {
        if (err) return res.status(500).json({ error: err.message });

        const maxPensions = userRow?.max_pensions || 1;

        // 현재 펜션 수 확인
        db.get(`SELECT COUNT(*) as count FROM pension_profiles WHERE user_id = ?`, [userId], (err, countRow) => {
            if (err) return res.status(500).json({ error: err.message });

            // 플랜 제한 체크
            if (countRow.count >= maxPensions) {
                return res.status(403).json({
                    error: '펜션 등록 한도에 도달했습니다.',
                    limit: maxPensions,
                    current: countRow.count,
                    upgrade_required: true,
                    message: `현재 플랜에서는 최대 ${maxPensions}개의 펜션만 등록할 수 있습니다. 더 많은 펜션을 관리하려면 Pro 플랜으로 업그레이드하세요.`
                });
            }

            // 첫 번째 펜션이면 기본값으로 설정
            proceedWithCreation(countRow.count);
        });
    });

    function proceedWithCreation(currentCount) {
        const shouldBeDefault = is_default || currentCount === 0 ? 1 : 0;

        // 만약 이 펜션이 기본값이면 다른 펜션들의 기본값 해제
        const setDefault = () => {
            if (shouldBeDefault) {
                db.run(`UPDATE pension_profiles SET is_default = 0 WHERE user_id = ?`, [userId], () => {
                    insertPension();
                });
            } else {
                insertPension();
            }
        };

        const insertPension = () => {
            db.run(`
                INSERT INTO pension_profiles
                (user_id, is_default, brand_name, brand_name_en, region, address, pension_types,
                 target_customers, key_features, nearby_attractions, booking_url,
                 homepage_url, kakao_channel, instagram_handle, languages, price_range, description)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `, [
                userId,
                shouldBeDefault,
                brand_name || null,
                brand_name_en || null,
                region || null,
                address || null,
                JSON.stringify(pension_types || []),
                JSON.stringify(target_customers || []),
                JSON.stringify(key_features || []),
                JSON.stringify(nearby_attractions || []),
                booking_url || null,
                homepage_url || null,
                kakao_channel || null,
                instagram_handle || null,
                languages || 'KO',
                price_range || null,
                description || null
            ], function(err) {
                if (err) return res.status(500).json({ error: err.message });
                res.json({ success: true, id: this.lastID, is_default: shouldBeDefault });
            });
        };

        setDefault();
    }
});

/**
 * 펜션 프로필 업데이트
 * PUT /api/profile/pension/:id
 */
app.put('/api/profile/pension/:id', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const {
        brand_name, brand_name_en, region, address, pension_types,
        target_customers, key_features, nearby_attractions, booking_url,
        homepage_url, kakao_channel, instagram_handle, languages,
        price_range, description
    } = req.body;

    // 먼저 해당 펜션이 사용자의 것인지 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.run(`
            UPDATE pension_profiles SET
            brand_name = ?,
            brand_name_en = ?,
            region = ?,
            address = ?,
            pension_types = ?,
            target_customers = ?,
            key_features = ?,
            nearby_attractions = ?,
            booking_url = ?,
            homepage_url = ?,
            kakao_channel = ?,
            instagram_handle = ?,
            languages = ?,
            price_range = ?,
            description = ?,
            updatedAt = datetime('now')
            WHERE id = ?
        `, [
            brand_name || null,
            brand_name_en || null,
            region || null,
            address || null,
            JSON.stringify(pension_types || []),
            JSON.stringify(target_customers || []),
            JSON.stringify(key_features || []),
            JSON.stringify(nearby_attractions || []),
            booking_url || null,
            homepage_url || null,
            kakao_channel || null,
            instagram_handle || null,
            languages || 'KO',
            price_range || null,
            description || null,
            pensionId
        ], function(err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ success: true, id: pensionId });
        });
    });
});

/**
 * 펜션 프로필 삭제
 * DELETE /api/profile/pension/:id
 */
app.delete('/api/profile/pension/:id', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    db.get(`SELECT is_default FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.run(`DELETE FROM pension_profiles WHERE id = ?`, [pensionId], function(err) {
            if (err) return res.status(500).json({ error: err.message });

            // 삭제된 펜션이 기본값이었으면 다른 펜션을 기본값으로 설정
            if (row.is_default) {
                db.run(`
                    UPDATE pension_profiles SET is_default = 1
                    WHERE user_id = ? AND id = (SELECT id FROM pension_profiles WHERE user_id = ? ORDER BY createdAt ASC LIMIT 1)
                `, [userId, userId]);
            }

            res.json({ success: true, deletedId: pensionId });
        });
    });
});

/**
 * 기본 펜션 설정
 * POST /api/profile/pension/:id/default
 */
app.post('/api/profile/pension/:id/default', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        // 모든 펜션의 기본값 해제
        db.run(`UPDATE pension_profiles SET is_default = 0 WHERE user_id = ?`, [userId], (err) => {
            if (err) return res.status(500).json({ error: err.message });

            // 선택한 펜션을 기본값으로 설정
            db.run(`UPDATE pension_profiles SET is_default = 1 WHERE id = ?`, [pensionId], function(err) {
                if (err) return res.status(500).json({ error: err.message });
                res.json({ success: true, defaultPensionId: pensionId });
            });
        });
    });
});

// ==================== PENSION IMAGES MANAGEMENT ====================

/**
 * 펜션 이미지 저장 (Base64)
 * POST /api/profile/pension/:id/images
 * body: { images: [{ base64, mimeType, filename, originalUrl, source, is_priority }] }
 * source: 'crawl' | 'manual' | 'upload'
 * is_priority: 1 = 수동 업로드 우선순위 (이벤트성)
 */
app.post('/api/profile/pension/:id/images', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const { images } = req.body;

    if (!images || !Array.isArray(images) || images.length === 0) {
        return res.status(400).json({ error: '이미지가 필요합니다.' });
    }

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], async (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        try {
            // 펜션 이미지 디렉토리 생성
            const pensionImgDir = path.join(DOWNLOADS_DIR, 'pension_images', pensionId.toString());
            if (!fs.existsSync(pensionImgDir)) {
                fs.mkdirSync(pensionImgDir, { recursive: true });
            }

            const savedImages = [];
            for (let i = 0; i < images.length; i++) {
                const img = images[i];
                const ext = img.mimeType?.split('/')[1] || 'jpg';
                const filename = img.filename || `pension_${pensionId}_${Date.now()}_${i}.${ext}`;
                const filePath = path.join(pensionImgDir, filename);

                // Base64 디코딩 및 저장
                const buffer = Buffer.from(img.base64, 'base64');
                fs.writeFileSync(filePath, buffer);

                // 수동 업로드면 우선순위 설정
                const source = img.source || 'crawl';
                const isPriority = img.is_priority || (source === 'manual' || source === 'upload') ? 1 : 0;

                // DB에 저장 (is_priority 포함)
                const relativePath = `/downloads/pension_images/${pensionId}/${filename}`;
                await new Promise((resolve, reject) => {
                    db.run(`INSERT INTO pension_images (pension_id, user_id, filename, original_url, file_path, file_size, mime_type, source, is_priority)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                        [pensionId, userId, filename, img.originalUrl || null, relativePath, buffer.length, img.mimeType || 'image/jpeg', source, isPriority],
                        function(err) {
                            if (err) reject(err);
                            else {
                                savedImages.push({ id: this.lastID, filename, path: relativePath, is_priority: isPriority });
                                resolve();
                            }
                        });
                });
            }

            res.json({ success: true, savedCount: savedImages.length, images: savedImages });
        } catch (error) {
            console.error('펜션 이미지 저장 실패:', error);
            res.status(500).json({ error: '이미지 저장에 실패했습니다.' });
        }
    });
});

/**
 * 펜션 이미지 목록 조회 (소스별 필터링 지원)
 * GET /api/profile/pension/:id/images?source=naver|google|instagram|upload|all
 */
app.get('/api/profile/pension/:id/images', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const sourceFilter = req.query.source || 'all';

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        // 소스별 필터링 쿼리 구성
        let query = `SELECT id, filename, original_url, file_path, file_size, mime_type, source, is_priority, createdAt
                     FROM pension_images WHERE pension_id = ?`;
        const params = [pensionId];

        if (sourceFilter !== 'all') {
            query += ` AND source = ?`;
            params.push(sourceFilter);
        }

        query += ` ORDER BY is_priority DESC, createdAt DESC`;

        db.all(query, params, (err, images) => {
            if (err) return res.status(500).json({ error: err.message });
            res.json(images || []);
        });
    });
});

/**
 * 펜션 이미지 소스별 통계 조회
 * GET /api/profile/pension/:id/images/stats
 */
app.get('/api/profile/pension/:id/images/stats', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.all(`SELECT source, COUNT(*) as count FROM pension_images WHERE pension_id = ? GROUP BY source`,
            [pensionId], (err, stats) => {
                if (err) return res.status(500).json({ error: err.message });

                // 통계 객체로 변환
                const result = {
                    total: 0,
                    naver: 0,
                    google: 0,
                    instagram: 0,
                    upload: 0,
                    crawl: 0,  // 기존 'crawl' 소스도 지원
                    manual: 0  // 기존 'manual' 소스도 지원
                };

                stats?.forEach(s => {
                    result[s.source] = s.count;
                    result.total += s.count;
                });

                res.json(result);
            });
    });
});

/**
 * 펜션 이미지 삭제
 * DELETE /api/profile/pension/:id/images/:imageId
 */
app.delete('/api/profile/pension/:id/images/:imageId', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const imageId = req.params.imageId;

    // 이미지 소유권 확인 및 삭제
    db.get(`SELECT pi.id, pi.file_path FROM pension_images pi
            JOIN pension_profiles pp ON pi.pension_id = pp.id
            WHERE pi.id = ? AND pi.pension_id = ? AND pp.user_id = ?`,
        [imageId, pensionId, userId], (err, row) => {
            if (err) return res.status(500).json({ error: err.message });
            if (!row) return res.status(404).json({ error: '이미지를 찾을 수 없습니다.' });

            // 파일 삭제
            const fullPath = path.join(__dirname, '..', row.file_path);
            if (fs.existsSync(fullPath)) {
                fs.unlinkSync(fullPath);
            }

            // DB에서 삭제
            db.run(`DELETE FROM pension_images WHERE id = ?`, [imageId], function(err) {
                if (err) return res.status(500).json({ error: err.message });
                res.json({ success: true });
            });
        });
});

// ==================== AUTO GENERATION SETTINGS ====================

/**
 * 자동 생성 설정 조회
 * GET /api/profile/pension/:id/auto-generation
 */
app.get('/api/profile/pension/:id/auto-generation', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, pension) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!pension) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.get(`SELECT * FROM daily_auto_generation WHERE pension_id = ?`, [pensionId], (err, settings) => {
            if (err) return res.status(500).json({ error: err.message });

            // 설정이 없으면 기본값 반환
            if (!settings) {
                return res.json({
                    pension_id: parseInt(pensionId),
                    enabled: false,
                    generation_time: '09:00',
                    image_mode: 'priority_first',  // priority_first, random, all
                    random_count: 10,
                    auto_upload_youtube: true,
                    auto_upload_instagram: false,
                    auto_upload_tiktok: false,
                    last_generated_at: null,
                    next_scheduled_at: null
                });
            }

            res.json({
                ...settings,
                enabled: !!settings.enabled,
                auto_upload_youtube: !!settings.auto_upload_youtube,
                auto_upload_instagram: !!settings.auto_upload_instagram,
                auto_upload_tiktok: !!settings.auto_upload_tiktok
            });
        });
    });
});

/**
 * 자동 생성 설정 저장/업데이트
 * POST /api/profile/pension/:id/auto-generation
 */
app.post('/api/profile/pension/:id/auto-generation', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const {
        enabled,
        generation_time,
        image_mode,
        random_count,
        auto_upload_youtube,
        auto_upload_instagram,
        auto_upload_tiktok
    } = req.body;

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, pension) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!pension) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        // 다음 실행 시간 계산
        const now = new Date();
        let nextScheduled = null;
        if (enabled && generation_time) {
            const [hours, minutes] = generation_time.split(':').map(Number);
            nextScheduled = new Date(now);
            nextScheduled.setHours(hours, minutes, 0, 0);
            if (nextScheduled <= now) {
                nextScheduled.setDate(nextScheduled.getDate() + 1);
            }
        }

        // UPSERT
        db.run(`INSERT INTO daily_auto_generation
                (pension_id, user_id, enabled, generation_time, image_mode, random_count,
                 auto_upload_youtube, auto_upload_instagram, auto_upload_tiktok, next_scheduled_at, updatedAt)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                ON CONFLICT(pension_id) DO UPDATE SET
                enabled = excluded.enabled,
                generation_time = excluded.generation_time,
                image_mode = excluded.image_mode,
                random_count = excluded.random_count,
                auto_upload_youtube = excluded.auto_upload_youtube,
                auto_upload_instagram = excluded.auto_upload_instagram,
                auto_upload_tiktok = excluded.auto_upload_tiktok,
                next_scheduled_at = excluded.next_scheduled_at,
                updatedAt = CURRENT_TIMESTAMP`,
            [
                pensionId, userId,
                enabled ? 1 : 0,
                generation_time || '09:00',
                image_mode || 'priority_first',
                random_count || 10,
                auto_upload_youtube ? 1 : 0,
                auto_upload_instagram ? 1 : 0,
                auto_upload_tiktok ? 1 : 0,
                nextScheduled ? nextScheduled.toISOString() : null
            ],
            function(err) {
                if (err) return res.status(500).json({ error: err.message });

                console.log(`[AutoGen] 설정 저장: pension=${pensionId}, enabled=${enabled}, time=${generation_time}`);
                res.json({
                    success: true,
                    pension_id: parseInt(pensionId),
                    enabled: !!enabled,
                    next_scheduled_at: nextScheduled ? nextScheduled.toISOString() : null
                });
            });
    });
});

/**
 * 자동 생성 로그 조회
 * GET /api/profile/pension/:id/auto-generation/logs
 */
app.get('/api/profile/pension/:id/auto-generation/logs', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const limit = parseInt(req.query.limit) || 10;

    // 펜션 소유권 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, pension) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!pension) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.all(`SELECT * FROM auto_generation_logs
                WHERE pension_id = ?
                ORDER BY createdAt DESC
                LIMIT ?`,
            [pensionId, limit], (err, logs) => {
                if (err) return res.status(500).json({ error: err.message });
                res.json(logs || []);
            });
    });
});

/**
 * 우선순위 기반 이미지 선택 로직
 */
function selectImagesForAutoGeneration(pensionId, imageMode, randomCount, callback) {
    // 1. 우선순위 이미지 (수동 업로드) 먼저 가져오기
    db.all(`SELECT * FROM pension_images
            WHERE pension_id = ? AND is_priority = 1
            ORDER BY createdAt DESC`,
        [pensionId], (err, priorityImages) => {
            if (err) return callback(err);

            // 2. 일반 이미지 가져오기 (사용 횟수 적은 순, 마지막 사용 시간 오래된 순)
            db.all(`SELECT * FROM pension_images
                    WHERE pension_id = ? AND is_priority = 0
                    ORDER BY used_count ASC, last_used_at ASC NULLS FIRST, createdAt DESC`,
                [pensionId], (err, normalImages) => {
                    if (err) return callback(err);

                    let selectedImages = [];
                    const targetCount = randomCount || 10;

                    if (imageMode === 'priority_first') {
                        // 우선순위 이미지 먼저, 부족하면 일반 이미지로 채움
                        selectedImages = [...priorityImages];
                        if (selectedImages.length < targetCount) {
                            const remaining = targetCount - selectedImages.length;
                            selectedImages = selectedImages.concat(normalImages.slice(0, remaining));
                        } else {
                            selectedImages = selectedImages.slice(0, targetCount);
                        }
                    } else if (imageMode === 'random') {
                        // 전체 이미지 중 랜덤 선택
                        const allImages = [...priorityImages, ...normalImages];
                        selectedImages = shuffleArray(allImages).slice(0, targetCount);
                    } else if (imageMode === 'all') {
                        // 모든 이미지 사용 (최대 30개)
                        selectedImages = [...priorityImages, ...normalImages].slice(0, 30);
                    }

                    callback(null, selectedImages);
                });
        });
}

// 배열 셔플 함수
function shuffleArray(array) {
    const result = [...array];
    for (let i = result.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [result[i], result[j]] = [result[j], result[i]];
    }
    return result;
}

/**
 * 이미지 사용 기록 업데이트
 */
function updateImageUsage(imageIds) {
    if (!imageIds || imageIds.length === 0) return;

    const placeholders = imageIds.map(() => '?').join(',');
    db.run(`UPDATE pension_images
            SET used_count = used_count + 1, last_used_at = CURRENT_TIMESTAMP
            WHERE id IN (${placeholders})`, imageIds);
}

/**
 * 수동 트리거 (테스트/디버그용)
 * POST /api/profile/pension/:id/auto-generation/trigger
 */
app.post('/api/profile/pension/:id/auto-generation/trigger', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    // 펜션 정보 가져오기
    db.get(`SELECT pp.*, dag.image_mode, dag.random_count, dag.auto_upload_youtube, dag.auto_upload_instagram
            FROM pension_profiles pp
            LEFT JOIN daily_auto_generation dag ON pp.id = dag.pension_id
            WHERE pp.id = ? AND pp.user_id = ?`,
        [pensionId, userId], async (err, pension) => {
            if (err) return res.status(500).json({ error: err.message });
            if (!pension) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

            const imageMode = pension.image_mode || 'priority_first';
            const randomCount = pension.random_count || 10;

            // 이미지 선택
            selectImagesForAutoGeneration(pensionId, imageMode, randomCount, async (err, images) => {
                if (err) return res.status(500).json({ error: err.message });
                if (!images || images.length < 3) {
                    return res.status(400).json({ error: '영상 생성에 필요한 이미지가 부족합니다 (최소 3장)' });
                }

                // 로그 생성
                db.run(`INSERT INTO auto_generation_logs (pension_id, user_id, status, images_used, started_at)
                        VALUES (?, ?, 'processing', ?, CURRENT_TIMESTAMP)`,
                    [pensionId, userId, JSON.stringify(images.map(img => img.id))],
                    function(err) {
                        if (err) return res.status(500).json({ error: err.message });

                        const logId = this.lastID;

                        // 이미지 URL 배열 생성
                        const imageUrls = images.map(img => {
                            // file_path가 상대경로면 절대 URL로 변환
                            if (img.file_path.startsWith('/')) {
                                return `${process.env.FRONTEND_URL || 'http://localhost:5173'}${img.file_path}`;
                            }
                            return img.original_url || img.file_path;
                        });

                        // 이미지 사용 기록 업데이트
                        updateImageUsage(images.map(img => img.id));

                        res.json({
                            success: true,
                            logId,
                            pension: {
                                id: pension.id,
                                name: pension.name,
                                business_type: pension.business_type
                            },
                            images: imageUrls,
                            imageCount: images.length,
                            message: '자동 생성이 트리거되었습니다. 프론트엔드에서 렌더링을 진행해주세요.'
                        });
                    });
            });
        });
});

/**
 * 자동 생성 로그 상태 업데이트
 * PATCH /api/auto-generation/logs/:logId
 */
app.patch('/api/auto-generation/logs/:logId', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const logId = req.params.logId;
    const { status, video_path, youtube_video_id, instagram_media_id, error_message } = req.body;

    db.run(`UPDATE auto_generation_logs
            SET status = ?, video_path = ?, youtube_video_id = ?, instagram_media_id = ?,
                error_message = ?, completed_at = CASE WHEN ? IN ('completed', 'failed') THEN CURRENT_TIMESTAMP ELSE completed_at END
            WHERE id = ? AND user_id = ?`,
        [status, video_path, youtube_video_id, instagram_media_id, error_message, status, logId, userId],
        function(err) {
            if (err) return res.status(500).json({ error: err.message });
            if (this.changes === 0) return res.status(404).json({ error: '로그를 찾을 수 없습니다.' });

            // 성공 시 다음 스케줄 업데이트
            if (status === 'completed') {
                db.get(`SELECT pension_id FROM auto_generation_logs WHERE id = ?`, [logId], (err, log) => {
                    if (!err && log) {
                        updateNextSchedule(log.pension_id);
                    }
                });
            }

            res.json({ success: true });
        });
});

/**
 * 다음 스케줄 시간 업데이트
 */
function updateNextSchedule(pensionId) {
    db.get(`SELECT generation_time, enabled FROM daily_auto_generation WHERE pension_id = ?`,
        [pensionId], (err, settings) => {
            if (err || !settings || !settings.enabled) return;

            const [hours, minutes] = settings.generation_time.split(':').map(Number);
            const nextScheduled = new Date();
            nextScheduled.setDate(nextScheduled.getDate() + 1);
            nextScheduled.setHours(hours, minutes, 0, 0);

            db.run(`UPDATE daily_auto_generation
                    SET last_generated_at = CURRENT_TIMESTAMP,
                        next_scheduled_at = ?,
                        consecutive_failures = 0
                    WHERE pension_id = ?`,
                [nextScheduled.toISOString(), pensionId]);
        });
}

/**
 * 전체 예약된 자동 생성 목록 (스케줄러용)
 * GET /api/auto-generation/scheduled
 */
app.get('/api/auto-generation/scheduled', (req, res) => {
    // 현재 시간 기준으로 실행해야 할 작업 조회
    const now = new Date().toISOString();

    db.all(`SELECT dag.*, pp.name as pension_name, pp.business_type, u.email as user_email
            FROM daily_auto_generation dag
            JOIN pension_profiles pp ON dag.pension_id = pp.id
            JOIN users u ON dag.user_id = u.id
            WHERE dag.enabled = 1
            AND (dag.next_scheduled_at IS NULL OR dag.next_scheduled_at <= ?)
            AND dag.consecutive_failures < 3`,
        [now], (err, jobs) => {
            if (err) return res.status(500).json({ error: err.message });
            res.json(jobs || []);
        });
});

// ==================== USER ASSETS MANAGEMENT ====================

// 사용자별 에셋 디렉토리 생성 함수
const ensureUserAssetDir = (userId) => {
    const userDir = path.join(DOWNLOADS_DIR, 'users', userId.toString());
    if (!fs.existsSync(userDir)) {
        fs.mkdirSync(userDir, { recursive: true });
    }
    return userDir;
};

/**
 * 스토리지 사용량 통계 조회
 * GET /api/user-assets/stats
 */
app.get('/api/user-assets/stats', authenticateToken, (req, res) => {
    const userId = req.user.id;

    db.get(`SELECT storage_limit FROM users WHERE id = ?`, [userId], (err, userRow) => {
        if (err) return res.status(500).json({ error: err.message });

        const storageLimit = userRow?.storage_limit || 500; // MB

        db.all(`
            SELECT
                asset_type,
                COUNT(*) as count,
                SUM(file_size) as total_size
            FROM user_assets
            WHERE user_id = ? AND is_deleted = 0
            GROUP BY asset_type
        `, [userId], (err, rows) => {
            if (err) return res.status(500).json({ error: err.message });

            const stats = {
                totalUsed: 0,
                storageLimit,
                imageCount: 0,
                audioCount: 0,
                videoCount: 0,
                imageSize: 0,
                audioSize: 0,
                videoSize: 0
            };

            rows.forEach(row => {
                const size = row.total_size || 0;
                stats.totalUsed += size;

                if (row.asset_type === 'image') {
                    stats.imageCount = row.count;
                    stats.imageSize = size;
                } else if (row.asset_type === 'audio') {
                    stats.audioCount = row.count;
                    stats.audioSize = size;
                } else if (row.asset_type === 'video') {
                    stats.videoCount = row.count;
                    stats.videoSize = size;
                }
            });

            res.json(stats);
        });
    });
});

/**
 * 에셋 목록 조회
 * GET /api/user-assets?type=image|audio|video&source=upload|crawl|ai_generated|rendered
 */
app.get('/api/user-assets', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const { type, source, pensionId, limit = 100, offset = 0 } = req.query;

    let query = `SELECT * FROM user_assets WHERE user_id = ? AND is_deleted = 0`;
    const params = [userId];

    if (type) {
        query += ` AND asset_type = ?`;
        params.push(type);
    }
    if (source) {
        query += ` AND source_type = ?`;
        params.push(source);
    }
    if (pensionId) {
        query += ` AND pension_id = ?`;
        params.push(pensionId);
    }

    query += ` ORDER BY createdAt DESC LIMIT ? OFFSET ?`;
    params.push(parseInt(limit), parseInt(offset));

    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

/**
 * 에셋 업로드 (이미지)
 * POST /api/user-assets/upload
 */
const multer = require('multer');
const uploadStorage = multer.diskStorage({
    destination: (req, file, cb) => {
        const userDir = ensureUserAssetDir(req.user.id);
        cb(null, userDir);
    },
    filename: (req, file, cb) => {
        const ext = path.extname(file.originalname);
        const safeName = file.originalname.replace(/[^a-z0-9가-힣.]/gi, '_');
        cb(null, `${Date.now()}_${safeName}`);
    }
});
const uploadMiddleware = multer({
    storage: uploadStorage,
    limits: { fileSize: 50 * 1024 * 1024 }, // 50MB limit
    fileFilter: (req, file, cb) => {
        const allowedTypes = ['image/jpeg', 'image/png', 'image/gif', 'image/webp', 'audio/mpeg', 'audio/wav', 'audio/mp3', 'video/mp4', 'video/webm'];
        if (allowedTypes.includes(file.mimetype)) {
            cb(null, true);
        } else {
            cb(new Error('지원하지 않는 파일 형식입니다.'), false);
        }
    }
});

app.post('/api/user-assets/upload', authenticateToken, uploadMiddleware.array('files', 20), (req, res) => {
    const userId = req.user.id;
    const { pensionId } = req.body;

    if (!req.files || req.files.length === 0) {
        return res.status(400).json({ error: '파일이 없습니다.' });
    }

    const savedAssets = [];
    let completed = 0;

    req.files.forEach(file => {
        let assetType = 'image';
        if (file.mimetype.startsWith('audio/')) assetType = 'audio';
        else if (file.mimetype.startsWith('video/')) assetType = 'video';

        const filePath = `/downloads/users/${userId}/${file.filename}`;

        db.run(`
            INSERT INTO user_assets (user_id, pension_id, asset_type, source_type, file_name, file_path, file_size, mime_type)
            VALUES (?, ?, ?, 'upload', ?, ?, ?, ?)
        `, [userId, pensionId || null, assetType, file.originalname, filePath, file.size, file.mimetype], function(err) {
            completed++;
            if (!err) {
                savedAssets.push({
                    id: this.lastID,
                    asset_type: assetType,
                    file_name: file.originalname,
                    file_path: filePath,
                    file_size: file.size
                });
            }

            if (completed === req.files.length) {
                // 사용자 스토리지 사용량 업데이트
                const totalSize = req.files.reduce((sum, f) => sum + f.size, 0);
                db.run(`UPDATE users SET storage_used = storage_used + ? WHERE id = ?`, [totalSize, userId]);

                res.json({ success: true, assets: savedAssets });
            }
        });
    });
});

/**
 * 에셋 삭제
 * DELETE /api/user-assets/:id
 */
app.delete('/api/user-assets/:id', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const assetId = req.params.id;

    db.get(`SELECT * FROM user_assets WHERE id = ? AND user_id = ?`, [assetId, userId], (err, asset) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!asset) return res.status(404).json({ error: '에셋을 찾을 수 없습니다.' });

        // 소프트 삭제 (실제 파일은 유지, 필요시 하드 삭제 구현 가능)
        db.run(`UPDATE user_assets SET is_deleted = 1 WHERE id = ?`, [assetId], function(err) {
            if (err) return res.status(500).json({ error: err.message });

            // 스토리지 사용량 업데이트
            db.run(`UPDATE users SET storage_used = storage_used - ? WHERE id = ?`, [asset.file_size, userId]);

            res.json({ success: true, deletedId: assetId });
        });
    });
});

/**
 * 에셋 저장 (내부 사용 - 크롤링/AI 생성 시 호출)
 * 이 함수는 다른 API에서 호출하는 헬퍼 함수입니다
 */
const saveUserAsset = (userId, assetData) => {
    return new Promise((resolve, reject) => {
        const {
            pensionId,
            historyId,
            assetType,
            sourceType,
            fileName,
            filePath,
            fileSize,
            mimeType,
            thumbnailPath,
            duration,
            width,
            height,
            metadata
        } = assetData;

        db.run(`
            INSERT INTO user_assets (
                user_id, pension_id, history_id, asset_type, source_type,
                file_name, file_path, file_size, mime_type, thumbnail_path,
                duration, width, height, metadata
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
            userId, pensionId || null, historyId || null, assetType, sourceType,
            fileName, filePath, fileSize || 0, mimeType || null, thumbnailPath || null,
            duration || null, width || null, height || null, metadata ? JSON.stringify(metadata) : null
        ], function(err) {
            if (err) reject(err);
            else {
                // 스토리지 사용량 업데이트
                if (fileSize) {
                    db.run(`UPDATE users SET storage_used = storage_used + ? WHERE id = ?`, [fileSize, userId]);
                }
                resolve({ id: this.lastID, ...assetData });
            }
        });
    });
};

// 전역으로 사용할 수 있도록 export (다른 파일에서 require 시)
module.exports = { saveUserAsset };

// ==================== YOUTUBE OAUTH & SETTINGS ROUTES ====================

/**
 * YouTube OAuth 인증 URL 생성
 * GET /api/youtube/oauth/url
 */
app.get('/api/youtube/oauth/url', authenticateToken, (req, res) => {
    try {
        const userId = req.user.id;
        const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:3000';
        const redirectUri = `${frontendUrl.replace(/:\d+$/, '')}:3001/api/youtube/oauth/callback`;

        const authUrl = generateAuthUrl(userId, redirectUri);
        res.json({ authUrl });
    } catch (error) {
        console.error('[YouTube OAuth] URL 생성 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * YouTube OAuth 콜백 처리
 * GET /api/youtube/oauth/callback
 */
app.get('/api/youtube/oauth/callback', async (req, res) => {
    const { code, state, error } = req.query;
    const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:3000';

    if (error) {
        return res.redirect(`${frontendUrl}/settings?youtube_error=${encodeURIComponent(error)}`);
    }

    if (!code || !state) {
        return res.redirect(`${frontendUrl}/settings?youtube_error=missing_params`);
    }

    try {
        const { userId } = JSON.parse(state);
        const redirectUri = `${frontendUrl.replace(/:\d+$/, '')}:3001/api/youtube/oauth/callback`;

        const result = await exchangeCodeForTokens(code, userId, redirectUri);

        // 성공 시 프론트엔드로 리다이렉트
        res.redirect(`${frontendUrl}/settings?youtube_connected=true&channel=${encodeURIComponent(result.channelTitle || '')}`);

    } catch (error) {
        console.error('[YouTube OAuth] 콜백 오류:', error.message);
        res.redirect(`${frontendUrl}/settings?youtube_error=${encodeURIComponent(error.message)}`);
    }
});

/**
 * YouTube 연결 상태 확인
 * GET /api/youtube/connection
 */
app.get('/api/youtube/connection', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const connection = await getConnectionStatus(userId);
        res.json({ connected: !!connection, ...connection });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * YouTube 연결 해제
 * DELETE /api/youtube/connection
 */
app.delete('/api/youtube/connection', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        await disconnectYouTube(userId);
        res.json({ success: true });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * YouTube 설정 조회
 * GET /api/youtube/settings
 */
app.get('/api/youtube/settings', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const settings = await getUserYouTubeSettings(userId);
        res.json(settings);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * YouTube 설정 업데이트
 * POST /api/youtube/settings
 */
app.post('/api/youtube/settings', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        await updateUserYouTubeSettings(userId, req.body);
        res.json({ success: true });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자의 플레이리스트 목록 (새 API)
 * GET /api/youtube/my-playlists
 */
app.get('/api/youtube/my-playlists', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const playlists = await getPlaylistsForUser(userId);
        res.json(playlists);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 플레이리스트 생성 (사용자 채널에)
 * POST /api/youtube/my-playlists
 */
app.post('/api/youtube/my-playlists', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const { title, description, privacyStatus, pensionId } = req.body;
        const playlist = await createPlaylistForUser(userId, title, description, privacyStatus);

        // 펜션에 플레이리스트 연결
        if (pensionId && playlist.id) {
            db.run(`
                INSERT OR REPLACE INTO youtube_playlists (user_id, pension_id, playlist_id, title, item_count, cached_at)
                VALUES (?, ?, ?, ?, 0, datetime('now'))
            `, [userId, pensionId, playlist.id, title]);
        }

        res.json(playlist);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션별 플레이리스트 조회
 * GET /api/youtube/pension/:pensionId/playlists
 */
app.get('/api/youtube/pension/:pensionId/playlists', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.pensionId;

    db.all(`
        SELECT * FROM youtube_playlists
        WHERE user_id = ? AND pension_id = ?
        ORDER BY cached_at DESC
    `, [userId, pensionId], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows || []);
    });
});

/**
 * 플레이리스트를 펜션에 연결
 * POST /api/youtube/pension/:pensionId/playlists
 */
app.post('/api/youtube/pension/:pensionId/playlists', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.pensionId;
    const { playlistId, title } = req.body;

    if (!playlistId) {
        return res.status(400).json({ error: '플레이리스트 ID가 필요합니다.' });
    }

    // 펜션이 사용자의 것인지 확인
    db.get(`SELECT id FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, pension) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!pension) return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });

        db.run(`
            INSERT OR REPLACE INTO youtube_playlists (user_id, pension_id, playlist_id, title, cached_at)
            VALUES (?, ?, ?, ?, datetime('now'))
        `, [userId, pensionId, playlistId, title || ''], function(err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ success: true });
        });
    });
});

/**
 * 플레이리스트 펜션 연결 해제
 * DELETE /api/youtube/pension/:pensionId/playlists/:playlistId
 */
app.delete('/api/youtube/pension/:pensionId/playlists/:playlistId', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const { pensionId, playlistId } = req.params;

    db.run(`
        DELETE FROM youtube_playlists
        WHERE user_id = ? AND pension_id = ? AND playlist_id = ?
    `, [userId, pensionId, playlistId], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true });
    });
});

/**
 * 사용자 채널에 업로드 (새 API)
 * POST /api/youtube/my-upload
 */
app.post('/api/youtube/my-upload', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const { videoPath, seoData, historyId, playlistId, privacyStatus, categoryId } = req.body;

        if (!videoPath) {
            return res.status(400).json({ error: '비디오 경로가 필요합니다.' });
        }

        const fullVideoPath = videoPath.startsWith('/')
            ? path.join(__dirname, '..', videoPath)
            : path.join(__dirname, videoPath);

        if (!fs.existsSync(fullVideoPath)) {
            return res.status(404).json({ error: '비디오 파일을 찾을 수 없습니다.' });
        }

        const result = await uploadVideoForUser(userId, fullVideoPath, seoData || {}, {
            historyId,
            playlistId,
            privacyStatus,
            categoryId
        });

        res.json({ success: true, youtubeUrl: result.url, videoId: result.videoId });
    } catch (error) {
        console.error('[YouTube Upload] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 업로드 히스토리 조회
 * GET /api/youtube/upload-history
 */
app.get('/api/youtube/upload-history', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const limit = parseInt(req.query.limit) || 20;
        const history = await getUploadHistory(userId, limit);
        res.json(history);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ==================== AI AUTO DESCRIPTION GENERATION ====================

/**
 * AI 매직 라이트 - 펜션 컨셉 자동 생성
 * POST /api/ai/auto-description
 */
app.post('/api/ai/auto-description', authenticateToken, async (req, res) => {
    try {
        const {
            name,
            address,
            pensionCategories,
            selectedFestivals,
            existingDescription,
            language
        } = req.body;

        if (!name) {
            return res.status(400).json({ error: '펜션 이름이 필요합니다.' });
        }

        // 카테고리 한글 매핑
        const categoryMap = {
            'PoolVilla': '풀빌라',
            'OceanView': '오션뷰',
            'Mountain': '산/계곡',
            'Glamping': '글램핑',
            'Couple': '커플',
            'Family': '가족',
            'Pet': '반려동물 동반',
            'Traditional': '전통한옥'
        };

        const categoryNames = (pensionCategories || []).map(c => categoryMap[c] || c).join(', ');

        // 축제 정보 정리
        let festivalInfo = '';
        if (selectedFestivals && selectedFestivals.length > 0) {
            const festivalNames = selectedFestivals.map(f => f.title).join(', ');
            const festivalDates = selectedFestivals.map(f => {
                const start = f.eventstartdate || f.event_start_date || '';
                const end = f.eventenddate || f.event_end_date || '';
                if (start && end) {
                    return `${f.title} (${start.slice(4,6)}/${start.slice(6,8)}~${end.slice(4,6)}/${end.slice(6,8)})`;
                }
                return f.title;
            }).join(', ');
            festivalInfo = `\n- 근처 축제: ${festivalDates}`;
        }

        // Gemini 프롬프트 생성
        const prompt = `당신은 펜션/숙소 마케팅 전문가입니다. 아래 정보를 바탕으로 펜션의 매력적인 컨셉 설명을 작성해주세요.

## 펜션 정보
- 이름: ${name}
- 위치: ${address || '정보 없음'}
- 유형: ${categoryNames || '정보 없음'}${festivalInfo}
${existingDescription ? `- 기존 설명 참고: ${existingDescription}` : ''}

## 작성 가이드라인
1. 2-3문장으로 간결하게 작성
2. 펜션의 핵심 매력 포인트를 강조
3. 타겟 고객층(커플, 가족, 반려동물 동반 등)에 맞는 어필 포인트 포함
4. 계절감이나 축제 정보가 있다면 자연스럽게 연결
5. 감성적이고 매력적인 문체 사용
6. 예약을 유도하는 호기심 유발 표현 사용

컨셉 설명만 작성하세요. 다른 설명이나 부연 없이 설명문만 출력하세요.`;

        // Gemini API 호출
        const { GoogleGenerativeAI } = require('@google-cloud/generative-ai') || require('@google/generative-ai');
        const genAI = new GoogleGenerativeAI(process.env.VITE_GEMINI_API_KEY || process.env.GEMINI_API_KEY);
        const model = genAI.getGenerativeModel({ model: 'gemini-2.0-flash' });

        const result = await model.generateContent(prompt);
        const description = result.response.text().trim();

        res.json({
            description,
            success: true
        });

    } catch (error) {
        console.error('[AI Auto Description] 오류:', error.message);
        res.status(500).json({ error: 'AI 컨셉 생성 실패', details: error.message });
    }
});

// ==================== YOUTUBE API ROUTES (Legacy) ====================

/**
 * YouTube SEO 메타데이터 생성 (다국어 지원)
 * POST /api/youtube/seo
 */
app.post('/api/youtube/seo', authenticateToken, async (req, res) => {
    try {
        const {
            businessName,
            businessNameEn,
            description,
            categories,
            address,
            region,
            regionEn,
            targetAudience,
            mainStrengths,
            nearbyAttractions,
            bookingUrl,
            videoDuration,
            seasonTheme,
            priceRange,
            language
        } = req.body;

        if (!businessName) {
            return res.status(400).json({ error: '비즈니스 이름이 필요합니다.' });
        }

        const seoData = await generateYouTubeSEO({
            businessName,
            businessNameEn: businessNameEn || businessName,
            description: description || '',
            categories: categories || [],
            address: address || '',
            region: region || '',
            regionEn: regionEn || '',
            targetAudience: targetAudience || '',
            mainStrengths: mainStrengths || [],
            nearbyAttractions: nearbyAttractions || [],
            bookingUrl: bookingUrl || '',
            videoDuration: videoDuration || 60,
            seasonTheme: seasonTheme || '',
            priceRange: priceRange || '',
            language: language || 'KO'
        });

        res.json(seoData);
    } catch (error) {
        console.error('[YouTube SEO API] 오류:', error.message);
        res.status(500).json({ error: 'SEO 메타데이터 생성 실패', details: error.message });
    }
});

/**
 * YouTube 영상 업로드 (SEO 포함)
 * POST /api/youtube/upload
 */
app.post('/api/youtube/upload', authenticateToken, async (req, res) => {
    try {
        const { videoPath, seoData, businessName, addToPlaylist, privacyStatus } = req.body;

        if (!videoPath) {
            return res.status(400).json({ error: '비디오 경로가 필요합니다.' });
        }

        // 전체 경로 생성
        const fullVideoPath = videoPath.startsWith('/')
            ? path.join(__dirname, '..', videoPath)
            : path.join(__dirname, videoPath);

        if (!fs.existsSync(fullVideoPath)) {
            return res.status(404).json({ error: '비디오 파일을 찾을 수 없습니다.', path: fullVideoPath });
        }

        let playlistId = null;

        // 비즈니스명으로 플레이리스트 자동 생성/연결
        if (addToPlaylist && businessName) {
            const playlistResult = await getOrCreatePlaylistByBusiness(businessName);
            playlistId = playlistResult.playlistId;
            console.log(`[YouTube] 플레이리스트 연결: ${playlistResult.title} (신규: ${playlistResult.isNew})`);
        }

        const result = await uploadVideo(
            fullVideoPath,
            seoData || { title: `${businessName || 'CastAD'} 홍보영상` },
            playlistId,
            privacyStatus || 'public'
        );

        res.json({
            success: true,
            videoId: result.videoId,
            url: result.url,
            youtubeUrl: result.url, // 호환성을 위해 추가
            playlistId: playlistId
        });
    } catch (error) {
        console.error('[YouTube Upload API] 오류:', error.message);
        res.status(500).json({ error: 'YouTube 업로드 실패', details: error.message });
    }
});

/**
 * 플레이리스트 목록 조회
 * GET /api/youtube/playlists
 */
app.get('/api/youtube/playlists', authenticateToken, async (req, res) => {
    try {
        const playlists = await getPlaylists();
        res.json(playlists);
    } catch (error) {
        console.error('[YouTube Playlists API] 오류:', error.message);
        res.status(500).json({ error: '플레이리스트 조회 실패', details: error.message });
    }
});

/**
 * 플레이리스트 생성
 * POST /api/youtube/playlists
 */
app.post('/api/youtube/playlists', authenticateToken, async (req, res) => {
    try {
        const { title, description, privacyStatus } = req.body;

        if (!title) {
            return res.status(400).json({ error: '플레이리스트 제목이 필요합니다.' });
        }

        const result = await createPlaylist(title, description || '', privacyStatus || 'public');
        res.json(result);
    } catch (error) {
        console.error('[YouTube Create Playlist API] 오류:', error.message);
        res.status(500).json({ error: '플레이리스트 생성 실패', details: error.message });
    }
});

/**
 * 플레이리스트의 영상 목록 조회
 * GET /api/youtube/playlists/:playlistId/videos
 */
app.get('/api/youtube/playlists/:playlistId/videos', authenticateToken, async (req, res) => {
    try {
        const { playlistId } = req.params;
        const videos = await getPlaylistVideos(playlistId);
        res.json(videos);
    } catch (error) {
        console.error('[YouTube Playlist Videos API] 오류:', error.message);
        res.status(500).json({ error: '플레이리스트 영상 조회 실패', details: error.message });
    }
});

/**
 * 비즈니스명으로 플레이리스트 찾기/생성
 * POST /api/youtube/playlists/business
 */
app.post('/api/youtube/playlists/business', authenticateToken, async (req, res) => {
    try {
        const { businessName } = req.body;

        if (!businessName) {
            return res.status(400).json({ error: '비즈니스 이름이 필요합니다.' });
        }

        const result = await getOrCreatePlaylistByBusiness(businessName);
        res.json(result);
    } catch (error) {
        console.error('[YouTube Business Playlist API] 오류:', error.message);
        res.status(500).json({ error: '플레이리스트 처리 실패', details: error.message });
    }
});

// ==================== END YOUTUBE API ROUTES ====================


// ==================== INSTAGRAM API ROUTES ====================
/**
 * Instagram 자동 업로드 기능 API
 *
 * 주요 엔드포인트:
 * - POST /api/instagram/connect - 계정 연결
 * - POST /api/instagram/disconnect - 계정 연결 해제
 * - GET  /api/instagram/status - 연결 상태 조회
 * - PUT  /api/instagram/settings - 설정 업데이트
 * - POST /api/instagram/upload - 영상 업로드
 * - GET  /api/instagram/history - 업로드 히스토리
 * - GET  /api/instagram/health - 서비스 상태 확인
 */

const instagramService = require('./instagramService');

/**
 * Instagram 서비스 상태 확인
 * GET /api/instagram/health
 */
app.get('/api/instagram/health', async (req, res) => {
    try {
        const isHealthy = await instagramService.checkServiceHealth();
        res.json({
            service: 'instagram-upload',
            status: isHealthy ? 'ok' : 'unavailable',
            message: isHealthy
                ? 'Instagram 서비스가 정상 작동 중입니다.'
                : 'Instagram 서비스에 연결할 수 없습니다. Python 서비스가 실행 중인지 확인하세요.'
        });
    } catch (error) {
        res.status(500).json({
            service: 'instagram-upload',
            status: 'error',
            message: error.message
        });
    }
});

/**
 * Instagram 계정 연결
 * POST /api/instagram/connect
 *
 * Body: { username, password, verification_code? }
 */
app.post('/api/instagram/connect', authenticateToken, async (req, res) => {
    try {
        const { username, password, verification_code } = req.body;
        const userId = req.user.id;

        if (!username || !password) {
            return res.status(400).json({
                success: false,
                error: 'Instagram 아이디와 비밀번호를 입력해주세요.'
            });
        }

        const result = await instagramService.connectAccount(
            userId,
            username,
            password,
            verification_code
        );

        if (result.success) {
            res.json(result);
        } else {
            res.status(result.requires_2fa ? 200 : 401).json(result);
        }

    } catch (error) {
        console.error('[Instagram Connect API] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: 'Instagram 연결 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * Instagram 계정 연결 해제
 * POST /api/instagram/disconnect
 */
app.post('/api/instagram/disconnect', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const result = await instagramService.disconnectAccount(userId);
        res.json(result);

    } catch (error) {
        console.error('[Instagram Disconnect API] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: '연결 해제 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * Instagram 연결 상태 조회
 * GET /api/instagram/status
 */
app.get('/api/instagram/status', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const status = await instagramService.getConnectionStatus(userId);
        res.json(status);

    } catch (error) {
        console.error('[Instagram Status API] 오류:', error.message);
        res.status(500).json({
            connected: false,
            error: error.message
        });
    }
});

/**
 * Instagram 설정 업데이트
 * PUT /api/instagram/settings
 *
 * Body: {
 *   auto_upload: boolean,
 *   upload_as_reel: boolean,
 *   default_caption_template: string,
 *   default_hashtags: string,
 *   max_uploads_per_week: number,
 *   notify_on_upload: boolean
 * }
 */
app.put('/api/instagram/settings', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const settings = req.body;

        const result = await instagramService.updateSettings(userId, settings);
        res.json(result);

    } catch (error) {
        console.error('[Instagram Settings API] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: '설정 저장 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * Instagram에 영상 업로드
 * POST /api/instagram/upload
 *
 * Body: {
 *   history_id: number,
 *   caption: string,
 *   hashtags?: string,
 *   thumbnail_path?: string,
 *   force_upload?: boolean (주간 제한 무시)
 * }
 */
app.post('/api/instagram/upload', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const {
            history_id,
            caption,
            hashtags,
            thumbnail_path,
            force_upload
        } = req.body;

        if (!history_id) {
            return res.status(400).json({
                success: false,
                error: '업로드할 영상 ID가 필요합니다.'
            });
        }

        // 영상 정보 조회
        const video = await new Promise((resolve, reject) => {
            db.get(
                'SELECT final_video_path FROM history WHERE id = ? AND user_id = ?',
                [history_id, userId],
                (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                }
            );
        });

        if (!video || !video.final_video_path) {
            return res.status(404).json({
                success: false,
                error: '업로드할 영상을 찾을 수 없습니다.'
            });
        }

        // 영상 경로 (상대경로를 절대경로로)
        let videoPath = video.final_video_path;
        if (videoPath.startsWith('/downloads/')) {
            videoPath = path.join(__dirname, videoPath);
        }

        // 캡션 조합
        const fullCaption = hashtags
            ? `${caption}\n\n${hashtags}`
            : caption;

        const result = await instagramService.uploadVideo(
            userId,
            history_id,
            videoPath,
            fullCaption,
            {
                thumbnailPath: thumbnail_path,
                forceUpload: force_upload
            }
        );

        if (result.success) {
            res.json(result);
        } else {
            res.status(400).json(result);
        }

    } catch (error) {
        console.error('[Instagram Upload API] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: '업로드 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * 주간 업로드 통계 조회
 * GET /api/instagram/weekly-stats
 */
app.get('/api/instagram/weekly-stats', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const stats = await instagramService.getWeeklyUploadCount(userId);
        res.json(stats);

    } catch (error) {
        console.error('[Instagram Weekly Stats API] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * Instagram 업로드 히스토리 조회
 * GET /api/instagram/history
 *
 * Query: { limit?: number, offset?: number }
 */
app.get('/api/instagram/history', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const limit = parseInt(req.query.limit) || 20;
        const offset = parseInt(req.query.offset) || 0;

        const history = await instagramService.getUploadHistory(userId, limit, offset);
        res.json(history);

    } catch (error) {
        console.error('[Instagram History API] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END INSTAGRAM API ROUTES ====================


// ==================== ENHANCED ADMIN API ROUTES ====================

/**
 * Admin 대시보드 통계
 * GET /api/admin/stats
 */
app.get('/api/admin/stats', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const stats = {};

        // 사용자 통계
        const userStats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN approved = 1 THEN 1 ELSE 0 END) as active,
                    SUM(CASE WHEN approved = 0 THEN 1 ELSE 0 END) as pending,
                    SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admins,
                    SUM(CASE WHEN email_verified = 1 THEN 1 ELSE 0 END) as verified,
                    SUM(CASE WHEN DATE(createdAt) = DATE('now') THEN 1 ELSE 0 END) as today,
                    SUM(CASE WHEN DATE(createdAt) >= DATE('now', '-7 days') THEN 1 ELSE 0 END) as thisWeek
                FROM users
            `, (err, row) => err ? reject(err) : resolve(row));
        });
        stats.users = userStats;

        // 콘텐츠 통계
        const contentStats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN render_status = 'completed' THEN 1 ELSE 0 END) as completed,
                    SUM(CASE WHEN render_status = 'failed' THEN 1 ELSE 0 END) as failed,
                    SUM(CASE WHEN render_status = 'pending' OR render_status IS NULL THEN 1 ELSE 0 END) as pending,
                    SUM(CASE WHEN DATE(createdAt) = DATE('now') THEN 1 ELSE 0 END) as today,
                    SUM(CASE WHEN DATE(createdAt) >= DATE('now', '-7 days') THEN 1 ELSE 0 END) as thisWeek
                FROM history
            `, (err, row) => err ? reject(err) : resolve(row));
        });
        stats.content = contentStats;

        // YouTube 업로드 통계
        const youtubeStats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
                    SUM(CASE WHEN DATE(uploaded_at) = DATE('now') THEN 1 ELSE 0 END) as today
                FROM upload_history
            `, (err, row) => err ? reject(err) : resolve(row));
        });
        stats.youtube = youtubeStats;

        // Instagram 업로드 통계
        const instagramStats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
                    SUM(CASE WHEN DATE(createdAt) = DATE('now') THEN 1 ELSE 0 END) as today
                FROM instagram_upload_history
            `, (err, row) => err ? reject(err) : resolve(row));
        });
        stats.instagram = instagramStats;

        // 펜션 프로필 통계
        const pensionStats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT COUNT(*) as total FROM pension_profiles
            `, (err, row) => err ? reject(err) : resolve(row));
        });
        stats.pensions = pensionStats;

        // 최근 7일간 일별 생성 추이
        const dailyTrend = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    DATE(createdAt) as date,
                    COUNT(*) as count
                FROM history
                WHERE DATE(createdAt) >= DATE('now', '-7 days')
                GROUP BY DATE(createdAt)
                ORDER BY date ASC
            `, (err, rows) => err ? reject(err) : resolve(rows));
        });
        stats.dailyTrend = dailyTrend;

        res.json(stats);

    } catch (error) {
        console.error('[Admin Stats API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 시스템 헬스 체크
 * GET /api/admin/system-health
 */
app.get('/api/admin/system-health', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const health = {
            server: {
                status: 'healthy',
                uptime: process.uptime(),
                memory: process.memoryUsage(),
                nodeVersion: process.version,
                platform: process.platform
            },
            database: {
                status: 'unknown'
            },
            services: {
                instagram: { status: 'unknown' },
                youtube: { status: 'unknown' }
            },
            storage: {
                downloads: { status: 'unknown' },
                temp: { status: 'unknown' }
            }
        };

        // DB 체크
        try {
            await new Promise((resolve, reject) => {
                db.get("SELECT 1", (err) => err ? reject(err) : resolve());
            });
            health.database.status = 'healthy';
        } catch (e) {
            health.database.status = 'error';
            health.database.error = e.message;
        }

        // Instagram 서비스 체크
        try {
            const instagramHealthResponse = await axios.get(`${process.env.INSTAGRAM_SERVICE_URL || 'http://localhost:5001'}/health`, { timeout: 3000 });
            health.services.instagram.status = instagramHealthResponse.data.status === 'ok' ? 'healthy' : 'degraded';
        } catch (e) {
            health.services.instagram.status = 'offline';
        }

        // YouTube 연결 수
        try {
            const ytConnections = await new Promise((resolve, reject) => {
                db.get("SELECT COUNT(*) as count FROM youtube_connections WHERE access_token IS NOT NULL", (err, row) => {
                    err ? reject(err) : resolve(row?.count || 0);
                });
            });
            health.services.youtube.status = 'healthy';
            health.services.youtube.connections = ytConnections;
        } catch (e) {
            health.services.youtube.status = 'error';
        }

        // 스토리지 체크
        try {
            const downloadsPath = path.join(__dirname, 'downloads');
            const tempPath = path.join(__dirname, 'temp');

            if (fs.existsSync(downloadsPath)) {
                const downloadFiles = fs.readdirSync(downloadsPath);
                health.storage.downloads.status = 'healthy';
                health.storage.downloads.itemCount = downloadFiles.length;
            }

            if (fs.existsSync(tempPath)) {
                const tempFiles = fs.readdirSync(tempPath);
                health.storage.temp.status = 'healthy';
                health.storage.temp.itemCount = tempFiles.length;
            }
        } catch (e) {
            health.storage.downloads.status = 'error';
            health.storage.temp.status = 'error';
        }

        res.json(health);

    } catch (error) {
        console.error('[System Health API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 활동 로그 테이블 생성 (서버 시작 시)
 */
db.run(`CREATE TABLE IF NOT EXISTS activity_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    username TEXT,
    action TEXT NOT NULL,
    target_type TEXT,
    target_id INTEGER,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
)`);

/**
 * 활동 로그 기록 함수
 */
const logActivity = (userId, username, action, targetType = null, targetId = null, details = null, req = null) => {
    const ip = req ? (req.headers['x-forwarded-for'] || req.connection.remoteAddress) : null;
    const userAgent = req ? req.headers['user-agent'] : null;

    db.run(`
        INSERT INTO activity_logs (user_id, username, action, target_type, target_id, details, ip_address, user_agent)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [userId, username, action, targetType, targetId, details ? JSON.stringify(details) : null, ip, userAgent]);
};

/**
 * 활동 로그 조회
 * GET /api/admin/logs
 */
app.get('/api/admin/logs', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { limit = 100, offset = 0, action, userId, startDate, endDate } = req.query;

        let query = `SELECT * FROM activity_logs WHERE 1=1`;
        const params = [];

        if (action) {
            query += ` AND action LIKE ?`;
            params.push(`%${action}%`);
        }
        if (userId) {
            query += ` AND user_id = ?`;
            params.push(userId);
        }
        if (startDate) {
            query += ` AND DATE(createdAt) >= DATE(?)`;
            params.push(startDate);
        }
        if (endDate) {
            query += ` AND DATE(createdAt) <= DATE(?)`;
            params.push(endDate);
        }

        query += ` ORDER BY createdAt DESC LIMIT ? OFFSET ?`;
        params.push(parseInt(limit), parseInt(offset));

        const logs = await new Promise((resolve, reject) => {
            db.all(query, params, (err, rows) => err ? reject(err) : resolve(rows));
        });

        // 총 개수
        let countQuery = `SELECT COUNT(*) as total FROM activity_logs WHERE 1=1`;
        const countParams = [];
        if (action) {
            countQuery += ` AND action LIKE ?`;
            countParams.push(`%${action}%`);
        }
        if (userId) {
            countQuery += ` AND user_id = ?`;
            countParams.push(userId);
        }

        const totalCount = await new Promise((resolve, reject) => {
            db.get(countQuery, countParams, (err, row) => err ? reject(err) : resolve(row?.total || 0));
        });

        res.json({ logs, total: totalCount });

    } catch (error) {
        console.error('[Activity Logs API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자 역할 변경
 * PUT /api/admin/users/:id/role
 */
app.put('/api/admin/users/:id/role', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { role } = req.body;

        if (!['user', 'admin'].includes(role)) {
            return res.status(400).json({ error: '유효하지 않은 역할입니다.' });
        }

        // 자기 자신의 역할은 변경 불가
        if (parseInt(id) === req.user.id) {
            return res.status(400).json({ error: '자신의 역할은 변경할 수 없습니다.' });
        }

        await new Promise((resolve, reject) => {
            db.run("UPDATE users SET role = ? WHERE id = ?", [role, id], function(err) {
                if (err) reject(err);
                else resolve(this.changes);
            });
        });

        // 로그 기록
        logActivity(req.user.id, req.user.username, 'USER_ROLE_CHANGE', 'user', parseInt(id), { newRole: role }, req);

        res.json({ success: true, message: '역할이 변경되었습니다.' });

    } catch (error) {
        console.error('[User Role Change API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자 플랜/크레딧 변경 (어드민 전용)
 * PUT /api/admin/users/:id/plan
 */
app.put('/api/admin/users/:id/plan', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { plan_type, credits, max_pensions, monthly_credits } = req.body;

        // 유효한 플랜 타입 확인
        const validPlans = ['free', 'basic', 'pro', 'business'];
        if (plan_type && !validPlans.includes(plan_type)) {
            return res.status(400).json({ error: '유효하지 않은 플랜입니다.' });
        }

        // 플랜별 기본값 설정
        const planDefaults = {
            free: { max_pensions: 1, monthly_credits: 10 },
            basic: { max_pensions: 1, monthly_credits: 15 },
            pro: { max_pensions: 5, monthly_credits: 75 },
            business: { max_pensions: 999, monthly_credits: 999 }
        };

        // 업데이트할 필드 구성
        const updates = [];
        const values = [];

        if (plan_type) {
            updates.push('plan_type = ?');
            values.push(plan_type);

            // 플랜 변경 시 기본값 적용 (별도 값이 없으면)
            if (max_pensions === undefined) {
                updates.push('max_pensions = ?');
                values.push(planDefaults[plan_type].max_pensions);
            }
            if (monthly_credits === undefined) {
                updates.push('monthly_credits = ?');
                values.push(planDefaults[plan_type].monthly_credits);
            }
        }

        if (credits !== undefined) {
            updates.push('credits = ?');
            values.push(credits);
        }

        if (max_pensions !== undefined) {
            updates.push('max_pensions = ?');
            values.push(max_pensions);
        }

        if (monthly_credits !== undefined) {
            updates.push('monthly_credits = ?');
            values.push(monthly_credits);
        }

        if (updates.length === 0) {
            return res.status(400).json({ error: '변경할 내용이 없습니다.' });
        }

        values.push(id);

        await new Promise((resolve, reject) => {
            db.run(`UPDATE users SET ${updates.join(', ')} WHERE id = ?`, values, function(err) {
                if (err) reject(err);
                else resolve(this.changes);
            });
        });

        // 변경된 사용자 정보 조회
        const updatedUser = await new Promise((resolve, reject) => {
            db.get("SELECT id, username, name, plan_type, credits, max_pensions, monthly_credits FROM users WHERE id = ?", [id],
                (err, row) => err ? reject(err) : resolve(row));
        });

        // 로그 기록
        logActivity(req.user.id, req.user.username, 'USER_PLAN_CHANGE', 'user', parseInt(id),
            { plan_type, credits, max_pensions, monthly_credits }, req);

        res.json({
            success: true,
            message: '플랜 정보가 변경되었습니다.',
            user: updatedUser
        });

    } catch (error) {
        console.error('[User Plan Change API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자 상세 정보 (펜션 프로필, 업로드 내역 포함)
 * GET /api/admin/users/:id/detail
 */
app.get('/api/admin/users/:id/detail', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;

        // 사용자 기본 정보
        const user = await new Promise((resolve, reject) => {
            db.get("SELECT id, username, email, name, phone, role, approved, email_verified, plan_type, credits, max_pensions, monthly_credits, createdAt FROM users WHERE id = ?", [id],
                (err, row) => err ? reject(err) : resolve(row));
        });

        if (!user) {
            return res.status(404).json({ error: '사용자를 찾을 수 없습니다.' });
        }

        // 펜션 프로필
        const pensions = await new Promise((resolve, reject) => {
            db.all("SELECT * FROM pension_profiles WHERE user_id = ?", [id],
                (err, rows) => err ? reject(err) : resolve(rows));
        });

        // 콘텐츠 히스토리 (최근 10개)
        const history = await new Promise((resolve, reject) => {
            db.all("SELECT id, business_name, createdAt, render_status FROM history WHERE user_id = ? ORDER BY createdAt DESC LIMIT 10", [id],
                (err, rows) => err ? reject(err) : resolve(rows));
        });

        // YouTube 연결 상태
        const youtubeConnection = await new Promise((resolve, reject) => {
            db.get("SELECT youtube_channel_title, google_email, connected_at FROM youtube_connections WHERE user_id = ?", [id],
                (err, row) => err ? reject(err) : resolve(row));
        });

        // Instagram 연결 상태
        const instagramConnection = await new Promise((resolve, reject) => {
            db.get("SELECT instagram_username, is_active, connected_at FROM instagram_connections WHERE user_id = ?", [id],
                (err, row) => err ? reject(err) : resolve(row));
        });

        res.json({
            user,
            pensions,
            history,
            youtubeConnection,
            instagramConnection
        });

    } catch (error) {
        console.error('[User Detail API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 전체 업로드 통계 (YouTube + Instagram)
 * GET /api/admin/uploads
 */
app.get('/api/admin/uploads', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { limit = 50, offset = 0, platform, status } = req.query;

        // YouTube 업로드
        let ytQuery = `
            SELECT uh.*, u.username, u.name as user_name, 'youtube' as platform
            FROM upload_history uh
            LEFT JOIN users u ON uh.user_id = u.id
            WHERE 1=1
        `;
        const ytParams = [];

        if (status) {
            ytQuery += ` AND uh.status = ?`;
            ytParams.push(status);
        }

        // Instagram 업로드
        let igQuery = `
            SELECT iuh.*, u.username, u.name as user_name, 'instagram' as platform
            FROM instagram_upload_history iuh
            LEFT JOIN users u ON iuh.user_id = u.id
            WHERE 1=1
        `;
        const igParams = [];

        if (status) {
            igQuery += ` AND iuh.status = ?`;
            igParams.push(status);
        }

        let uploads = [];

        if (!platform || platform === 'youtube') {
            const ytUploads = await new Promise((resolve, reject) => {
                db.all(ytQuery, ytParams, (err, rows) => err ? reject(err) : resolve(rows || []));
            });
            uploads = uploads.concat(ytUploads);
        }

        if (!platform || platform === 'instagram') {
            const igUploads = await new Promise((resolve, reject) => {
                db.all(igQuery, igParams, (err, rows) => err ? reject(err) : resolve(rows || []));
            });
            uploads = uploads.concat(igUploads);
        }

        // 날짜순 정렬
        uploads.sort((a, b) => new Date(b.uploaded_at || b.createdAt) - new Date(a.uploaded_at || a.createdAt));

        // 페이지네이션
        const total = uploads.length;
        uploads = uploads.slice(parseInt(offset), parseInt(offset) + parseInt(limit));

        res.json({ uploads, total });

    } catch (error) {
        console.error('[Admin Uploads API] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 시스템 설정 조회/저장
 * GET/PUT /api/admin/settings
 */
db.run(`CREATE TABLE IF NOT EXISTS system_settings (
    key TEXT PRIMARY KEY,
    value TEXT,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
)`);

app.get('/api/admin/settings', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const settings = await new Promise((resolve, reject) => {
            db.all("SELECT key, value FROM system_settings", (err, rows) => {
                if (err) reject(err);
                else {
                    const obj = {};
                    (rows || []).forEach(row => {
                        try {
                            obj[row.key] = JSON.parse(row.value);
                        } catch {
                            obj[row.key] = row.value;
                        }
                    });
                    resolve(obj);
                }
            });
        });
        res.json(settings);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

app.put('/api/admin/settings', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const settings = req.body;

        for (const [key, value] of Object.entries(settings)) {
            const valueStr = typeof value === 'object' ? JSON.stringify(value) : String(value);
            await new Promise((resolve, reject) => {
                db.run(`
                    INSERT OR REPLACE INTO system_settings (key, value, updatedAt)
                    VALUES (?, ?, CURRENT_TIMESTAMP)
                `, [key, valueStr], (err) => err ? reject(err) : resolve());
            });
        }

        logActivity(req.user.id, req.user.username, 'SETTINGS_UPDATE', 'system', null, settings, req);
        res.json({ success: true });

    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ==================== END ENHANCED ADMIN API ROUTES ====================

// ==================== CREDIT MANAGEMENT API ROUTES ====================

// 사용자 크레딧 조회
app.get('/api/credits', authenticateToken, async (req, res) => {
    try {
        const user = await new Promise((resolve, reject) => {
            db.get("SELECT credits FROM users WHERE id = ?", [req.user.id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        res.json({ credits: user?.credits ?? 10 });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 크레딧 사용 내역 조회
app.get('/api/credits/history', authenticateToken, async (req, res) => {
    try {
        const history = await new Promise((resolve, reject) => {
            db.all(`
                SELECT * FROM credit_history
                WHERE user_id = ?
                ORDER BY createdAt DESC
                LIMIT 50
            `, [req.user.id], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        res.json(history);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 크레딧 요청 생성 (사용자)
app.post('/api/credits/request', authenticateToken, async (req, res) => {
    try {
        const { reason } = req.body;
        const requestedCredits = 10; // 기본 10개

        // 이미 대기 중인 요청이 있는지 확인
        const pendingRequest = await new Promise((resolve, reject) => {
            db.get(`
                SELECT * FROM credit_requests
                WHERE user_id = ? AND status = 'pending'
            `, [req.user.id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (pendingRequest) {
            return res.status(400).json({ error: '이미 대기 중인 크레딧 요청이 있습니다.' });
        }

        // 새 요청 생성
        const result = await new Promise((resolve, reject) => {
            db.run(`
                INSERT INTO credit_requests (user_id, requested_credits, reason, status)
                VALUES (?, ?, ?, 'pending')
            `, [req.user.id, requestedCredits, reason || '추가 크레딧 요청'], function(err) {
                if (err) reject(err);
                else resolve({ id: this.lastID });
            });
        });

        // 활동 로그
        logActivity(req.user.id, req.user.username, 'CREDIT_REQUEST', 'credit_request', result.id,
            { requestedCredits, reason }, req);

        res.json({
            success: true,
            requestId: result.id,
            message: '크레딧 요청이 접수되었습니다. 관리자 승인을 기다려주세요.'
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 내 크레딧 요청 목록 조회
app.get('/api/credits/requests', authenticateToken, async (req, res) => {
    try {
        const requests = await new Promise((resolve, reject) => {
            db.all(`
                SELECT cr.*, u.username as processed_by_username
                FROM credit_requests cr
                LEFT JOIN users u ON cr.processed_by = u.id
                WHERE cr.user_id = ?
                ORDER BY cr.createdAt DESC
            `, [req.user.id], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        res.json(requests);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ===== ADMIN CREDIT MANAGEMENT =====

// 관리자: 모든 크레딧 요청 목록
app.get('/api/admin/credits/requests', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { status, page = 1, limit = 20 } = req.query;
        const offset = (page - 1) * limit;

        let whereClause = '';
        const params = [];

        if (status && status !== 'all') {
            whereClause = 'WHERE cr.status = ?';
            params.push(status);
        }

        const requests = await new Promise((resolve, reject) => {
            db.all(`
                SELECT cr.*,
                       u.username, u.name, u.email, u.credits as current_credits,
                       p.username as processed_by_username
                FROM credit_requests cr
                JOIN users u ON cr.user_id = u.id
                LEFT JOIN users p ON cr.processed_by = p.id
                ${whereClause}
                ORDER BY
                    CASE WHEN cr.status = 'pending' THEN 0 ELSE 1 END,
                    cr.createdAt DESC
                LIMIT ? OFFSET ?
            `, [...params, limit, offset], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        const total = await new Promise((resolve, reject) => {
            db.get(`
                SELECT COUNT(*) as count FROM credit_requests cr
                ${whereClause}
            `, params, (err, row) => {
                if (err) reject(err);
                else resolve(row?.count || 0);
            });
        });

        const pendingCount = await new Promise((resolve, reject) => {
            db.get("SELECT COUNT(*) as count FROM credit_requests WHERE status = 'pending'", (err, row) => {
                if (err) reject(err);
                else resolve(row?.count || 0);
            });
        });

        res.json({ requests, total, pendingCount });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 관리자: 크레딧 요청 승인/거절
app.post('/api/admin/credits/requests/:id/process', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { action, adminNote } = req.body; // action: 'approve' or 'reject'

        if (!['approve', 'reject'].includes(action)) {
            return res.status(400).json({ error: '유효하지 않은 액션입니다.' });
        }

        // 요청 정보 조회
        const request = await new Promise((resolve, reject) => {
            db.get(`
                SELECT cr.*, u.credits as current_credits, u.username
                FROM credit_requests cr
                JOIN users u ON cr.user_id = u.id
                WHERE cr.id = ?
            `, [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!request) {
            return res.status(404).json({ error: '요청을 찾을 수 없습니다.' });
        }

        if (request.status !== 'pending') {
            return res.status(400).json({ error: '이미 처리된 요청입니다.' });
        }

        const newStatus = action === 'approve' ? 'approved' : 'rejected';

        // 요청 상태 업데이트
        await new Promise((resolve, reject) => {
            db.run(`
                UPDATE credit_requests
                SET status = ?, admin_note = ?, processed_by = ?, processed_at = CURRENT_TIMESTAMP
                WHERE id = ?
            `, [newStatus, adminNote, req.user.id, id], (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        if (action === 'approve') {
            // 크레딧 추가
            const newBalance = (request.current_credits || 0) + request.requested_credits;
            await new Promise((resolve, reject) => {
                db.run("UPDATE users SET credits = ? WHERE id = ?", [newBalance, request.user_id], (err) => {
                    if (err) reject(err);
                    else resolve();
                });
            });

            // 크레딧 히스토리 기록
            await new Promise((resolve, reject) => {
                db.run(`
                    INSERT INTO credit_history (user_id, amount, type, description, balance_after, related_request_id)
                    VALUES (?, ?, 'request_approved', ?, ?, ?)
                `, [request.user_id, request.requested_credits, '크레딧 요청 승인', newBalance, id], (err) => {
                    if (err) reject(err);
                    else resolve();
                });
            });
        }

        // 활동 로그
        logActivity(req.user.id, req.user.username,
            action === 'approve' ? 'CREDIT_APPROVE' : 'CREDIT_REJECT',
            'credit_request', id,
            { requestedCredits: request.requested_credits, username: request.username, adminNote },
            req);

        res.json({
            success: true,
            message: action === 'approve' ? '크레딧이 충전되었습니다.' : '요청이 거절되었습니다.'
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 관리자: 사용자 크레딧 직접 조정
app.post('/api/admin/users/:id/credits', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { id } = req.params;
        const { amount, reason } = req.body; // amount: positive to add, negative to deduct

        if (typeof amount !== 'number' || amount === 0) {
            return res.status(400).json({ error: '유효한 크레딧 수량을 입력해주세요.' });
        }

        // 사용자 조회
        const user = await new Promise((resolve, reject) => {
            db.get("SELECT id, username, credits FROM users WHERE id = ?", [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!user) {
            return res.status(404).json({ error: '사용자를 찾을 수 없습니다.' });
        }

        const newBalance = Math.max(0, (user.credits || 0) + amount);

        // 크레딧 업데이트
        await new Promise((resolve, reject) => {
            db.run("UPDATE users SET credits = ? WHERE id = ?", [newBalance, id], (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        // 크레딧 히스토리 기록
        await new Promise((resolve, reject) => {
            db.run(`
                INSERT INTO credit_history (user_id, amount, type, description, balance_after)
                VALUES (?, ?, ?, ?, ?)
            `, [id, amount, amount > 0 ? 'admin_add' : 'admin_deduct', reason || '관리자 조정', newBalance], (err) => {
                if (err) reject(err);
                else resolve();
            });
        });

        // 활동 로그
        logActivity(req.user.id, req.user.username, 'CREDIT_ADJUST', 'user', id,
            { amount, reason, username: user.username, newBalance }, req);

        res.json({
            success: true,
            newBalance,
            message: `${user.username}님의 크레딧이 ${amount > 0 ? amount + '개 추가' : Math.abs(amount) + '개 차감'}되었습니다.`
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 관리자: 크레딧 통계
app.get('/api/admin/credits/stats', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const stats = await new Promise((resolve, reject) => {
            db.get(`
                SELECT
                    SUM(CASE WHEN credits > 0 THEN credits ELSE 0 END) as total_credits,
                    AVG(credits) as avg_credits,
                    COUNT(CASE WHEN credits = 0 THEN 1 END) as zero_credit_users,
                    COUNT(CASE WHEN credits > 0 AND credits <= 3 THEN 1 END) as low_credit_users
                FROM users WHERE role != 'admin'
            `, (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        const pendingRequests = await new Promise((resolve, reject) => {
            db.get("SELECT COUNT(*) as count FROM credit_requests WHERE status = 'pending'", (err, row) => {
                if (err) reject(err);
                else resolve(row?.count || 0);
            });
        });

        const recentActivity = await new Promise((resolve, reject) => {
            db.all(`
                SELECT type, COUNT(*) as count
                FROM credit_history
                WHERE createdAt > datetime('now', '-7 days')
                GROUP BY type
            `, (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        res.json({
            ...stats,
            pendingRequests,
            recentActivity
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ==================== END CREDIT MANAGEMENT API ROUTES ====================

// ==================== PENSION YOUTUBE & ANALYTICS ROUTES ====================

/**
 * 사용자 플랜 정보 조회
 * GET /api/user/plan
 */
app.get('/api/user/plan', authenticateToken, (req, res) => {
    const userId = req.user.id;
    db.get(`
        SELECT plan_type, max_pensions, monthly_credits, credits, subscription_started_at, subscription_expires_at
        FROM users WHERE id = ?
    `, [userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });

        // 현재 펜션 수 조회
        db.get(`SELECT COUNT(*) as pension_count FROM pension_profiles WHERE user_id = ?`, [userId], (err, countRow) => {
            if (err) return res.status(500).json({ error: err.message });

            res.json({
                plan_type: row?.plan_type || 'free',
                max_pensions: row?.max_pensions || 1,
                monthly_credits: row?.monthly_credits || 3,
                current_credits: row?.credits || 0,
                current_pensions: countRow?.pension_count || 0,
                subscription_started_at: row?.subscription_started_at,
                subscription_expires_at: row?.subscription_expires_at
            });
        });
    });
});

/**
 * 사용자 경험 레벨 조회
 * GET /api/user/level
 */
app.get('/api/user/level', authenticateToken, (req, res) => {
    const userId = req.user.id;
    db.get(`SELECT experience_level FROM users WHERE id = ?`, [userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ level: row?.experience_level || 'beginner' });
    });
});

/**
 * 사용자 경험 레벨 변경
 * PUT /api/user/level
 */
app.put('/api/user/level', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const { level } = req.body;

    if (!['beginner', 'intermediate', 'pro'].includes(level)) {
        return res.status(400).json({ error: '유효하지 않은 레벨입니다.' });
    }

    db.run(`UPDATE users SET experience_level = ? WHERE id = ?`, [level, userId], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true, level });
    });
});

/**
 * 자동 생성 설정 조회
 * GET /api/user/auto-generation
 */
app.get('/api/user/auto-generation', authenticateToken, (req, res) => {
    const userId = req.user.id;
    db.get(`SELECT * FROM auto_generation_settings WHERE user_id = ?`, [userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) {
            return res.json({
                enabled: false,
                day_of_week: 1,
                time_of_day: '09:00',
                pension_id: null,
                last_generated_at: null,
                next_scheduled_at: null
            });
        }
        res.json(row);
    });
});

/**
 * 자동 생성 설정 저장
 * PUT /api/user/auto-generation
 */
app.put('/api/user/auto-generation', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const { enabled, day_of_week, time_of_day, pension_id } = req.body;

    // 다음 예약 시간 계산
    const calculateNextScheduled = () => {
        if (!enabled) return null;
        const now = new Date();
        const [hours, minutes] = (time_of_day || '09:00').split(':').map(Number);
        const targetDay = day_of_week ?? 1;

        let next = new Date(now);
        next.setHours(hours, minutes, 0, 0);

        // 다음 요일로 설정
        const daysUntilTarget = (targetDay - now.getDay() + 7) % 7;
        if (daysUntilTarget === 0 && next <= now) {
            next.setDate(next.getDate() + 7);
        } else {
            next.setDate(next.getDate() + daysUntilTarget);
        }
        return next.toISOString();
    };

    const nextScheduled = calculateNextScheduled();

    db.run(`
        INSERT INTO auto_generation_settings (user_id, enabled, day_of_week, time_of_day, pension_id, next_scheduled_at, updatedAt)
        VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(user_id) DO UPDATE SET
            enabled = excluded.enabled,
            day_of_week = excluded.day_of_week,
            time_of_day = excluded.time_of_day,
            pension_id = excluded.pension_id,
            next_scheduled_at = excluded.next_scheduled_at,
            updatedAt = CURRENT_TIMESTAMP
    `, [userId, enabled ? 1 : 0, day_of_week ?? 1, time_of_day ?? '09:00', pension_id, nextScheduled], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true, next_scheduled_at: nextScheduled });
    });
});

/**
 * 자동 업로드 실행
 * POST /api/auto-upload
 */
app.post('/api/auto-upload', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const { videoPath, historyId, businessInfo, language } = req.body;

    try {
        const autoUploadService = require('./services/autoUploadService');

        // 자동 업로드 실행
        const result = await autoUploadService.executeAutoUpload(
            userId,
            videoPath,
            businessInfo,
            language || 'KO'
        );

        // 업로드 이력 저장
        if (historyId && result.results) {
            for (const [platform, platformResult] of Object.entries(result.results)) {
                await autoUploadService.saveUploadHistory(userId, historyId, platform, platformResult);
            }
        }

        res.json(result);
    } catch (error) {
        console.error('자동 업로드 실패:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 자동 업로드 설정 조회/수정 (Pro 사용자용)
 * GET/PUT /api/user/auto-upload-settings
 */
app.get('/api/user/auto-upload-settings', authenticateToken, (req, res) => {
    const userId = req.user.id;

    db.get(`
        SELECT auto_youtube, auto_instagram, auto_tiktok
        FROM auto_generation_settings
        WHERE user_id = ?
    `, [userId], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(row || { auto_youtube: 1, auto_instagram: 1, auto_tiktok: 1 });
    });
});

app.put('/api/user/auto-upload-settings', authenticateToken, (req, res) => {
    const userId = req.user.id;
    const { auto_youtube, auto_instagram, auto_tiktok } = req.body;

    db.run(`
        INSERT INTO auto_generation_settings (user_id, auto_youtube, auto_instagram, auto_tiktok)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(user_id) DO UPDATE SET
            auto_youtube = excluded.auto_youtube,
            auto_instagram = excluded.auto_instagram,
            auto_tiktok = excluded.auto_tiktok,
            updatedAt = CURRENT_TIMESTAMP
    `, [userId, auto_youtube ? 1 : 0, auto_instagram ? 1 : 0, auto_tiktok ? 1 : 0], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true });
    });
});

/**
 * 펜션에 YouTube 플레이리스트 연결
 * POST /api/profile/pension/:id/youtube-playlist
 */
app.post('/api/profile/pension/:id/youtube-playlist', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const { playlist_id, playlist_title, create_new } = req.body;

    try {
        // 펜션 소유권 확인
        const pension = await new Promise((resolve, reject) => {
            db.get(`SELECT * FROM pension_profiles WHERE id = ? AND user_id = ?`, [pensionId, userId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!pension) {
            return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });
        }

        // YouTube 연결 확인
        const ytConnection = await getConnectionStatus(userId);
        if (!ytConnection) {
            return res.status(400).json({ error: 'YouTube 계정이 연결되어 있지 않습니다.' });
        }

        let finalPlaylistId = playlist_id;
        let finalPlaylistTitle = playlist_title;

        // 새 플레이리스트 생성 요청인 경우
        if (create_new && pension.brand_name) {
            const newPlaylist = await createPlaylistForUser(userId, {
                title: `${pension.brand_name} - 홍보영상`,
                description: `${pension.brand_name}의 AI 생성 마케팅 영상 컬렉션`,
                privacyStatus: 'public'
            });
            finalPlaylistId = newPlaylist.id;
            finalPlaylistTitle = newPlaylist.title;
        }

        // 펜션에 플레이리스트 연결
        await new Promise((resolve, reject) => {
            db.run(`
                UPDATE pension_profiles
                SET youtube_playlist_id = ?, youtube_playlist_title = ?, updatedAt = datetime('now')
                WHERE id = ?
            `, [finalPlaylistId, finalPlaylistTitle, pensionId], function(err) {
                if (err) reject(err);
                else resolve(this.changes);
            });
        });

        res.json({
            success: true,
            pension_id: pensionId,
            playlist_id: finalPlaylistId,
            playlist_title: finalPlaylistTitle
        });

    } catch (error) {
        console.error('[Pension YouTube] 플레이리스트 연결 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션의 YouTube 플레이리스트 연결 해제
 * DELETE /api/profile/pension/:id/youtube-playlist
 */
app.delete('/api/profile/pension/:id/youtube-playlist', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;

    try {
        const result = await new Promise((resolve, reject) => {
            db.run(`
                UPDATE pension_profiles
                SET youtube_playlist_id = NULL, youtube_playlist_title = NULL, updatedAt = datetime('now')
                WHERE id = ? AND user_id = ?
            `, [pensionId, userId], function(err) {
                if (err) reject(err);
                else resolve(this.changes);
            });
        });

        if (result === 0) {
            return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });
        }

        res.json({ success: true });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션별 분석 데이터 조회
 * GET /api/profile/pension/:id/analytics
 */
app.get('/api/profile/pension/:id/analytics', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    const pensionId = req.params.id;
    const { start_date, end_date } = req.query;

    try {
        // 펜션 소유권 및 플레이리스트 확인
        const pension = await new Promise((resolve, reject) => {
            db.get(`
                SELECT id, brand_name, youtube_playlist_id, youtube_playlist_title
                FROM pension_profiles WHERE id = ? AND user_id = ?
            `, [pensionId, userId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!pension) {
            return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });
        }

        if (!pension.youtube_playlist_id) {
            return res.json({
                pension_id: pensionId,
                brand_name: pension.brand_name,
                playlist_connected: false,
                message: 'YouTube 플레이리스트가 연결되어 있지 않습니다.',
                analytics: null
            });
        }

        // 캐시된 분석 데이터 조회
        const cachedAnalytics = await new Promise((resolve, reject) => {
            let query = `SELECT * FROM youtube_analytics WHERE pension_id = ?`;
            let params = [pensionId];

            if (start_date && end_date) {
                query += ` AND date BETWEEN ? AND ?`;
                params.push(start_date, end_date);
            }

            query += ` ORDER BY date DESC LIMIT 30`;

            db.all(query, params, (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 월간 통계 조회
        const monthlyStats = await new Promise((resolve, reject) => {
            db.all(`
                SELECT * FROM pension_monthly_stats
                WHERE pension_id = ?
                ORDER BY year_month DESC LIMIT 6
            `, [pensionId], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 업로드 히스토리 조회
        const uploadHistory = await new Promise((resolve, reject) => {
            db.all(`
                SELECT uh.*, h.business_name
                FROM upload_history uh
                LEFT JOIN history h ON uh.history_id = h.id
                WHERE uh.pension_id = ? AND uh.status = 'completed'
                ORDER BY uh.uploaded_at DESC LIMIT 20
            `, [pensionId], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 요약 통계 계산
        const summary = {
            total_views: cachedAnalytics.reduce((sum, a) => sum + (a.views || 0), 0),
            total_watch_time: cachedAnalytics.reduce((sum, a) => sum + (a.estimated_minutes_watched || 0), 0),
            total_likes: cachedAnalytics.reduce((sum, a) => sum + (a.likes || 0), 0),
            total_comments: cachedAnalytics.reduce((sum, a) => sum + (a.comments || 0), 0),
            total_videos: uploadHistory.length,
            avg_views_per_video: uploadHistory.length > 0
                ? Math.round(cachedAnalytics.reduce((sum, a) => sum + (a.views || 0), 0) / uploadHistory.length)
                : 0
        };

        res.json({
            pension_id: pensionId,
            brand_name: pension.brand_name,
            playlist_connected: true,
            playlist_id: pension.youtube_playlist_id,
            playlist_title: pension.youtube_playlist_title,
            summary,
            daily_analytics: cachedAnalytics,
            monthly_stats: monthlyStats,
            recent_uploads: uploadHistory
        });

    } catch (error) {
        console.error('[Pension Analytics] 조회 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 모든 펜션의 분석 요약 (대시보드용)
 * GET /api/profile/pensions/analytics-summary
 */
app.get('/api/profile/pensions/analytics-summary', authenticateToken, async (req, res) => {
    const userId = req.user.id;

    try {
        // 모든 펜션 조회
        const pensions = await new Promise((resolve, reject) => {
            db.all(`
                SELECT id, brand_name, youtube_playlist_id, youtube_playlist_title
                FROM pension_profiles WHERE user_id = ?
                ORDER BY is_default DESC, createdAt ASC
            `, [userId], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 각 펜션별 요약 통계 조회
        const summaries = await Promise.all(pensions.map(async (pension) => {
            // 최근 30일 분석 데이터
            const recentAnalytics = await new Promise((resolve, reject) => {
                db.get(`
                    SELECT
                        SUM(views) as total_views,
                        SUM(estimated_minutes_watched) as total_watch_time,
                        SUM(likes) as total_likes,
                        SUM(subscribers_gained) as total_subscribers_gained
                    FROM youtube_analytics
                    WHERE pension_id = ? AND date >= date('now', '-30 days')
                `, [pension.id], (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                });
            });

            // 업로드된 영상 수
            const uploadCount = await new Promise((resolve, reject) => {
                db.get(`
                    SELECT COUNT(*) as count
                    FROM upload_history
                    WHERE pension_id = ? AND status = 'completed'
                `, [pension.id], (err, row) => {
                    if (err) reject(err);
                    else resolve(row?.count || 0);
                });
            });

            return {
                pension_id: pension.id,
                brand_name: pension.brand_name,
                playlist_connected: !!pension.youtube_playlist_id,
                playlist_title: pension.youtube_playlist_title,
                last_30_days: {
                    views: recentAnalytics?.total_views || 0,
                    watch_time_minutes: Math.round(recentAnalytics?.total_watch_time || 0),
                    likes: recentAnalytics?.total_likes || 0,
                    subscribers_gained: recentAnalytics?.total_subscribers_gained || 0
                },
                total_videos: uploadCount
            };
        }));

        // 전체 요약
        const totalSummary = {
            total_pensions: pensions.length,
            connected_pensions: pensions.filter(p => p.youtube_playlist_id).length,
            total_views: summaries.reduce((sum, s) => sum + s.last_30_days.views, 0),
            total_videos: summaries.reduce((sum, s) => sum + s.total_videos, 0)
        };

        res.json({
            summary: totalSummary,
            pensions: summaries
        });

    } catch (error) {
        console.error('[Pensions Analytics Summary] 조회 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END PENSION YOUTUBE & ANALYTICS ROUTES ====================


// ==================== TIKTOK INTEGRATION ROUTES ====================

/**
 * TikTok OAuth 인증 URL 생성
 * GET /api/tiktok/oauth/url
 */
app.get('/api/tiktok/oauth/url', authenticateToken, (req, res) => {
    try {
        const userId = req.user.id;
        const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
        const redirectUri = `${process.env.BACKEND_URL || 'http://localhost:3001'}/api/tiktok/oauth/callback`;

        const authUrl = tiktokService.generateAuthUrl(userId, redirectUri);

        res.json({ authUrl });
    } catch (error) {
        console.error('[TikTok OAuth URL] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * TikTok OAuth 콜백
 * GET /api/tiktok/oauth/callback
 */
app.get('/api/tiktok/oauth/callback', async (req, res) => {
    try {
        const { code, state, error: authError, error_description } = req.query;

        if (authError) {
            console.error('[TikTok OAuth Callback] 인증 실패:', authError, error_description);
            const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
            return res.redirect(`${frontendUrl}/dashboard?tiktok_error=${encodeURIComponent(error_description || authError)}`);
        }

        if (!code || !state) {
            return res.status(400).json({ error: 'code 또는 state 파라미터가 없습니다.' });
        }

        const { userId } = JSON.parse(state);
        const redirectUri = `${process.env.BACKEND_URL || 'http://localhost:3001'}/api/tiktok/oauth/callback`;

        const result = await tiktokService.exchangeCodeForTokens(code, userId, redirectUri);

        const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
        res.redirect(`${frontendUrl}/dashboard?tiktok_connected=true&tiktok_name=${encodeURIComponent(result.displayName || '')}`);

    } catch (error) {
        console.error('[TikTok OAuth Callback] 오류:', error.message);
        const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
        res.redirect(`${frontendUrl}/dashboard?tiktok_error=${encodeURIComponent(error.message)}`);
    }
});

/**
 * TikTok 연결 상태 조회
 * GET /api/tiktok/status
 */
app.get('/api/tiktok/status', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const status = await tiktokService.getConnectionStatus(userId);

        if (status) {
            res.json({
                connected: true,
                openId: status.open_id,
                displayName: status.display_name,
                avatarUrl: status.avatar_url,
                followerCount: status.follower_count,
                followingCount: status.following_count,
                connectedAt: status.connected_at
            });
        } else {
            res.json({ connected: false });
        }
    } catch (error) {
        console.error('[TikTok Status] 오류:', error.message);
        res.status(500).json({ connected: false, error: error.message });
    }
});

/**
 * TikTok 연결 해제
 * POST /api/tiktok/disconnect
 */
app.post('/api/tiktok/disconnect', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const result = await tiktokService.disconnectTikTok(userId);
        res.json(result);
    } catch (error) {
        console.error('[TikTok Disconnect] 오류:', error.message);
        res.status(500).json({ success: false, error: error.message });
    }
});

/**
 * TikTok 설정 조회
 * GET /api/tiktok/settings
 */
app.get('/api/tiktok/settings', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const settings = await tiktokService.getUserTikTokSettings(userId);
        res.json(settings);
    } catch (error) {
        console.error('[TikTok Settings] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * TikTok 설정 업데이트
 * PUT /api/tiktok/settings
 */
app.put('/api/tiktok/settings', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const result = await tiktokService.updateUserTikTokSettings(userId, req.body);
        res.json(result);
    } catch (error) {
        console.error('[TikTok Settings Update] 오류:', error.message);
        res.status(500).json({ success: false, error: error.message });
    }
});

/**
 * TikTok 비디오 업로드 (Direct Post)
 * POST /api/tiktok/upload
 *
 * Body: { history_id, title, privacy_level, disable_duet, disable_comment, disable_stitch }
 */
app.post('/api/tiktok/upload', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const {
            history_id,
            title,
            privacy_level,
            disable_duet,
            disable_comment,
            disable_stitch
        } = req.body;

        if (!history_id) {
            return res.status(400).json({ success: false, error: '업로드할 영상 ID가 필요합니다.' });
        }

        // 영상 정보 조회
        const video = await new Promise((resolve, reject) => {
            db.get(
                'SELECT final_video_path, business_name FROM history WHERE id = ? AND user_id = ?',
                [history_id, userId],
                (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                }
            );
        });

        if (!video || !video.final_video_path) {
            return res.status(404).json({ success: false, error: '업로드할 영상을 찾을 수 없습니다.' });
        }

        let videoPath = video.final_video_path;
        if (videoPath.startsWith('/downloads/')) {
            videoPath = path.join(__dirname, videoPath);
        }

        const result = await tiktokService.uploadVideo(
            userId,
            videoPath,
            { title: title || video.business_name || 'CaStAD Video' },
            {
                historyId: history_id,
                privacyLevel: privacy_level || 'SELF_ONLY',
                disableDuet: disable_duet || false,
                disableComment: disable_comment || false,
                disableStitch: disable_stitch || false
            }
        );

        res.json({ success: true, ...result });

    } catch (error) {
        console.error('[TikTok Upload] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: 'TikTok 업로드 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * TikTok Inbox 업로드 (Draft 방식)
 * POST /api/tiktok/upload-to-inbox
 *
 * Body: { history_id }
 */
app.post('/api/tiktok/upload-to-inbox', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const { history_id } = req.body;

        if (!history_id) {
            return res.status(400).json({ success: false, error: '업로드할 영상 ID가 필요합니다.' });
        }

        // 영상 정보 조회
        const video = await new Promise((resolve, reject) => {
            db.get(
                'SELECT final_video_path, business_name FROM history WHERE id = ? AND user_id = ?',
                [history_id, userId],
                (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                }
            );
        });

        if (!video || !video.final_video_path) {
            return res.status(404).json({ success: false, error: '업로드할 영상을 찾을 수 없습니다.' });
        }

        let videoPath = video.final_video_path;
        if (videoPath.startsWith('/downloads/')) {
            videoPath = path.join(__dirname, videoPath);
        }

        const result = await tiktokService.uploadVideoToInbox(
            userId,
            videoPath,
            { title: video.business_name || 'CaStAD Video' },
            { historyId: history_id }
        );

        res.json({ success: true, ...result });

    } catch (error) {
        console.error('[TikTok Upload to Inbox] 오류:', error.message);
        res.status(500).json({
            success: false,
            error: 'TikTok 업로드 중 오류가 발생했습니다.',
            details: error.message
        });
    }
});

/**
 * TikTok 업로드 히스토리 조회
 * GET /api/tiktok/history
 */
app.get('/api/tiktok/history', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const limit = parseInt(req.query.limit) || 20;
        const history = await tiktokService.getUploadHistory(userId, limit);
        res.json(history);
    } catch (error) {
        console.error('[TikTok History] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

/**
 * TikTok 통계 조회
 * GET /api/tiktok/stats
 */
app.get('/api/tiktok/stats', authenticateToken, async (req, res) => {
    try {
        const userId = req.user.id;
        const stats = await tiktokService.getTikTokStats(userId);
        res.json(stats);
    } catch (error) {
        console.error('[TikTok Stats] 오류:', error.message);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END TIKTOK ROUTES ====================


// ==================== ADVANCED STATISTICS ROUTES (ADMIN) ====================

/**
 * 대시보드 요약 통계
 * GET /api/admin/analytics/summary
 */
app.get('/api/admin/analytics/summary', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const summary = await statisticsService.getDashboardSummary();
        res.json(summary);
    } catch (error) {
        console.error('[Admin Analytics Summary] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자 성장 트렌드
 * GET /api/admin/analytics/user-growth
 * Query: { days?: number }
 */
app.get('/api/admin/analytics/user-growth', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await statisticsService.getUserGrowthTrend(days);
        res.json(data);
    } catch (error) {
        console.error('[Admin User Growth] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 영상 생성 트렌드
 * GET /api/admin/analytics/video-trend
 * Query: { days?: number }
 */
app.get('/api/admin/analytics/video-trend', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await statisticsService.getVideoGenerationTrend(days);
        res.json(data);
    } catch (error) {
        console.error('[Admin Video Trend] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 플랫폼별 업로드 통계
 * GET /api/admin/analytics/platform-uploads
 * Query: { days?: number }
 */
app.get('/api/admin/analytics/platform-uploads', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await statisticsService.getPlatformUploadStats(days);
        res.json(data);
    } catch (error) {
        console.error('[Admin Platform Uploads] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 크레딧 사용 통계
 * GET /api/admin/analytics/credit-usage
 * Query: { days?: number }
 */
app.get('/api/admin/analytics/credit-usage', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await statisticsService.getCreditUsageStats(days);
        res.json(data);
    } catch (error) {
        console.error('[Admin Credit Usage] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 플랜별 사용자 분포
 * GET /api/admin/analytics/plan-distribution
 */
app.get('/api/admin/analytics/plan-distribution', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const data = await statisticsService.getPlanDistribution();
        res.json(data);
    } catch (error) {
        console.error('[Admin Plan Distribution] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 톱 사용자 (가장 많은 영상 생성)
 * GET /api/admin/analytics/top-users
 * Query: { limit?: number }
 */
app.get('/api/admin/analytics/top-users', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const limit = parseInt(req.query.limit) || 10;
        const data = await statisticsService.getTopUsers(limit);
        res.json(data);
    } catch (error) {
        console.error('[Admin Top Users] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 시간대별 사용 패턴
 * GET /api/admin/analytics/usage-pattern
 */
app.get('/api/admin/analytics/usage-pattern', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const data = await statisticsService.getUsagePattern();
        res.json(data);
    } catch (error) {
        console.error('[Admin Usage Pattern] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 지역별 분포
 * GET /api/admin/analytics/regional
 */
app.get('/api/admin/analytics/regional', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const data = await statisticsService.getRegionalDistribution();
        res.json(data);
    } catch (error) {
        console.error('[Admin Regional] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 수익 예측
 * GET /api/admin/analytics/revenue
 */
app.get('/api/admin/analytics/revenue', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const data = await statisticsService.getRevenueProjection();
        res.json(data);
    } catch (error) {
        console.error('[Admin Revenue] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 최근 활동 로그
 * GET /api/admin/analytics/activity-logs
 * Query: { limit?: number }
 */
app.get('/api/admin/analytics/activity-logs', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const limit = parseInt(req.query.limit) || 50;
        const data = await statisticsService.getRecentActivityLogs(limit);
        res.json(data);
    } catch (error) {
        console.error('[Admin Activity Logs] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 시스템 헬스 체크 (확장)
 * GET /api/admin/analytics/system-health
 */
app.get('/api/admin/analytics/system-health', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const data = await statisticsService.getSystemHealth();
        res.json(data);
    } catch (error) {
        console.error('[Admin System Health] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 일별 통계 업데이트 (수동 트리거)
 * POST /api/admin/analytics/update-daily
 */
app.post('/api/admin/analytics/update-daily', authenticateToken, requireAdmin, async (req, res) => {
    try {
        await statisticsService.updateDailyStats();
        res.json({ success: true, message: '일별 통계가 업데이트되었습니다.' });
    } catch (error) {
        console.error('[Admin Update Daily Stats] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 종합 분석 리포트 (모든 통계 한번에)
 * GET /api/admin/analytics/full-report
 * Query: { days?: number }
 */
app.get('/api/admin/analytics/full-report', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;

        const [
            summary,
            userGrowth,
            videoTrend,
            platformUploads,
            creditUsage,
            planDistribution,
            topUsers,
            usagePattern,
            regional,
            revenue,
            systemHealth
        ] = await Promise.all([
            statisticsService.getDashboardSummary(),
            statisticsService.getUserGrowthTrend(days),
            statisticsService.getVideoGenerationTrend(days),
            statisticsService.getPlatformUploadStats(days),
            statisticsService.getCreditUsageStats(days),
            statisticsService.getPlanDistribution(),
            statisticsService.getTopUsers(10),
            statisticsService.getUsagePattern(),
            statisticsService.getRegionalDistribution(),
            statisticsService.getRevenueProjection(),
            statisticsService.getSystemHealth()
        ]);

        res.json({
            generatedAt: new Date().toISOString(),
            period: `${days}일`,
            summary,
            userGrowth,
            videoTrend,
            platformUploads,
            creditUsage,
            planDistribution,
            topUsers,
            usagePattern,
            regional,
            revenue,
            systemHealth
        });
    } catch (error) {
        console.error('[Admin Full Report] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END ADVANCED STATISTICS ROUTES ====================


// ==================== API USAGE MONITORING ROUTES ====================

/**
 * API 사용량 통계 조회
 * GET /api/admin/api-usage/stats
 * Query: { days?: number, service?: string }
 */
app.get('/api/admin/api-usage/stats', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const service = req.query.service || null;
        const startDate = new Date();
        startDate.setDate(startDate.getDate() - days);

        // 서비스별 요약 통계
        const summaryQuery = `
            SELECT
                service,
                model,
                COUNT(*) as total_calls,
                SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success_count,
                SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as error_count,
                SUM(tokens_input) as total_tokens_input,
                SUM(tokens_output) as total_tokens_output,
                SUM(image_count) as total_images,
                SUM(audio_seconds) as total_audio_seconds,
                SUM(video_seconds) as total_video_seconds,
                SUM(cost_estimate) as total_cost,
                AVG(latency_ms) as avg_latency
            FROM api_usage_logs
            WHERE createdAt >= ?
            ${service ? 'AND service = ?' : ''}
            GROUP BY service, model
            ORDER BY total_calls DESC
        `;

        const params = [startDate.toISOString()];
        if (service) params.push(service);

        const summary = await new Promise((resolve, reject) => {
            db.all(summaryQuery, params, (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 일별 추이
        const dailyQuery = `
            SELECT
                DATE(createdAt) as date,
                service,
                COUNT(*) as calls,
                SUM(cost_estimate) as cost
            FROM api_usage_logs
            WHERE createdAt >= ?
            ${service ? 'AND service = ?' : ''}
            GROUP BY DATE(createdAt), service
            ORDER BY date DESC
        `;

        const daily = await new Promise((resolve, reject) => {
            db.all(dailyQuery, params, (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 최근 에러 목록
        const errorsQuery = `
            SELECT
                id, service, model, endpoint, error_message, latency_ms, createdAt
            FROM api_usage_logs
            WHERE status = 'error'
            AND createdAt >= ?
            ${service ? 'AND service = ?' : ''}
            ORDER BY createdAt DESC
            LIMIT 20
        `;

        const recentErrors = await new Promise((resolve, reject) => {
            db.all(errorsQuery, params, (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 총 비용 계산
        const totalCost = summary.reduce((sum, s) => sum + (s.total_cost || 0), 0);
        const totalCalls = summary.reduce((sum, s) => sum + (s.total_calls || 0), 0);
        const totalErrors = summary.reduce((sum, s) => sum + (s.error_count || 0), 0);
        const avgLatency = summary.length > 0
            ? summary.reduce((sum, s) => sum + (s.avg_latency || 0), 0) / summary.length
            : 0;

        res.json({
            period: `${days}일`,
            overview: {
                totalCalls,
                totalErrors,
                errorRate: totalCalls > 0 ? ((totalErrors / totalCalls) * 100).toFixed(2) : 0,
                totalCostUSD: totalCost.toFixed(4),
                avgLatencyMs: Math.round(avgLatency)
            },
            byServiceModel: summary,
            dailyTrend: daily,
            recentErrors,
            links: {
                googleAiStudio: 'https://aistudio.google.com/app/apikey',
                googleCloudBilling: 'https://console.cloud.google.com/billing'
            }
        });
    } catch (error) {
        console.error('[API Usage Stats] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * API 사용량 상세 로그 조회
 * GET /api/admin/api-usage/logs
 * Query: { page?, limit?, service?, status? }
 */
app.get('/api/admin/api-usage/logs', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 50;
        const offset = (page - 1) * limit;
        const { service, status, model } = req.query;

        let whereClause = '1=1';
        const params = [];

        if (service) {
            whereClause += ' AND service = ?';
            params.push(service);
        }
        if (status) {
            whereClause += ' AND status = ?';
            params.push(status);
        }
        if (model) {
            whereClause += ' AND model LIKE ?';
            params.push(`%${model}%`);
        }

        // 총 개수
        const countResult = await new Promise((resolve, reject) => {
            db.get(
                `SELECT COUNT(*) as total FROM api_usage_logs WHERE ${whereClause}`,
                params,
                (err, row) => {
                    if (err) reject(err);
                    else resolve(row?.total || 0);
                }
            );
        });

        // 로그 조회
        const logs = await new Promise((resolve, reject) => {
            db.all(
                `SELECT
                    l.*,
                    u.username
                FROM api_usage_logs l
                LEFT JOIN users u ON l.user_id = u.id
                WHERE ${whereClause}
                ORDER BY l.createdAt DESC
                LIMIT ? OFFSET ?`,
                [...params, limit, offset],
                (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows || []);
                }
            );
        });

        res.json({
            logs,
            pagination: {
                page,
                limit,
                total: countResult,
                totalPages: Math.ceil(countResult / limit)
            }
        });
    } catch (error) {
        console.error('[API Usage Logs] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 모델별 우선순위 설정 조회
 * GET /api/admin/api-usage/models
 */
app.get('/api/admin/api-usage/models', authenticateToken, requireAdmin, async (req, res) => {
    try {
        // 현재 모델 설정 (geminiBackendService에서 정의된 것)
        const imageModels = [
            { id: 'gemini-2.0-flash-preview-image-generation', name: 'Gemini 2.0 Flash Image (Preview)', priority: 1, costPerImage: 0.02 },
            { id: 'gemini-2.5-flash-image', name: 'Gemini 2.5 Flash Image', priority: 2, costPerImage: 0.015 },
            { id: 'imagen-3.0-generate-002', name: 'Imagen 3', priority: 3, costPerImage: 0.03 }
        ];

        const textModels = [
            { id: 'gemini-2.0-flash', name: 'Gemini 2.0 Flash', type: 'text', inputCostPer1M: 0.10, outputCostPer1M: 0.40 },
            { id: 'gemini-2.5-flash', name: 'Gemini 2.5 Flash', type: 'text', inputCostPer1M: 0.075, outputCostPer1M: 0.30 },
            { id: 'gemini-2.5-flash-preview-tts', name: 'Gemini TTS', type: 'audio', costPerMinute: 0.001 }
        ];

        const videoModels = [
            { id: 'veo-3.1-fast-generate-preview', name: 'Veo 3.1 Fast', type: 'video', costPerSecond: 0.05 }
        ];

        res.json({
            imageModels,
            textModels,
            videoModels,
            note: '모델 우선순위는 순차적으로 시도되며, 실패 시 다음 모델로 자동 폴백됩니다.'
        });
    } catch (error) {
        console.error('[API Models] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 사용자별 API 사용량 통계 (과금용)
 * GET /api/admin/api-usage/by-user
 * Query: { days?: number, limit?: number }
 */
app.get('/api/admin/api-usage/by-user', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const limit = parseInt(req.query.limit) || 50;
        const startDate = new Date();
        startDate.setDate(startDate.getDate() - days);

        // 사용자별 사용량 집계
        const userUsageQuery = `
            SELECT
                l.user_id,
                u.username,
                u.name,
                u.email,
                u.plan_type,
                COUNT(*) as total_calls,
                SUM(CASE WHEN l.status = 'success' THEN 1 ELSE 0 END) as success_count,
                SUM(CASE WHEN l.status = 'error' THEN 1 ELSE 0 END) as error_count,
                SUM(l.tokens_input) as total_tokens_input,
                SUM(l.tokens_output) as total_tokens_output,
                SUM(l.image_count) as total_images,
                SUM(l.audio_seconds) as total_audio_seconds,
                SUM(l.video_seconds) as total_video_seconds,
                SUM(l.cost_estimate) as total_cost_usd,
                AVG(l.latency_ms) as avg_latency
            FROM api_usage_logs l
            LEFT JOIN users u ON l.user_id = u.id
            WHERE l.createdAt >= ?
            AND l.user_id IS NOT NULL
            GROUP BY l.user_id
            ORDER BY total_cost_usd DESC
            LIMIT ?
        `;

        const userUsage = await new Promise((resolve, reject) => {
            db.all(userUsageQuery, [startDate.toISOString(), limit], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 총 비용 계산
        const totalCost = userUsage.reduce((sum, u) => sum + (u.total_cost_usd || 0), 0);

        // KRW 환산 (대략적인 환율)
        const usdToKrw = 1350;

        res.json({
            period: `${days}일`,
            totalUsers: userUsage.length,
            totalCostUSD: totalCost.toFixed(4),
            totalCostKRW: Math.round(totalCost * usdToKrw),
            users: userUsage.map(u => ({
                userId: u.user_id,
                username: u.username || 'Unknown',
                name: u.name || '',
                email: u.email || '',
                planType: u.plan_type || 'free',
                totalCalls: u.total_calls,
                successCount: u.success_count,
                errorCount: u.error_count,
                totalImages: u.total_images || 0,
                totalAudioSeconds: Math.round(u.total_audio_seconds || 0),
                totalVideoSeconds: Math.round(u.total_video_seconds || 0),
                tokensInput: u.total_tokens_input || 0,
                tokensOutput: u.total_tokens_output || 0,
                costUSD: parseFloat((u.total_cost_usd || 0).toFixed(4)),
                costKRW: Math.round((u.total_cost_usd || 0) * usdToKrw),
                avgLatencyMs: Math.round(u.avg_latency || 0)
            })),
            exchangeRate: { USD_KRW: usdToKrw, note: '예상 환율' }
        });
    } catch (error) {
        console.error('[API Usage By User] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 특정 사용자의 API 사용량 상세
 * GET /api/admin/api-usage/user/:userId
 * Query: { days?: number }
 */
app.get('/api/admin/api-usage/user/:userId', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { userId } = req.params;
        const days = parseInt(req.query.days) || 30;
        const startDate = new Date();
        startDate.setDate(startDate.getDate() - days);

        // 사용자 정보
        const user = await new Promise((resolve, reject) => {
            db.get('SELECT id, username, name, email, plan_type, credits FROM users WHERE id = ?', [userId], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!user) {
            return res.status(404).json({ error: '사용자를 찾을 수 없습니다.' });
        }

        // 서비스/모델별 사용량
        const usageByModel = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    service,
                    model,
                    COUNT(*) as calls,
                    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success,
                    SUM(image_count) as images,
                    SUM(audio_seconds) as audio_sec,
                    SUM(video_seconds) as video_sec,
                    SUM(cost_estimate) as cost
                FROM api_usage_logs
                WHERE user_id = ? AND createdAt >= ?
                GROUP BY service, model
                ORDER BY cost DESC
            `, [userId, startDate.toISOString()], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 일별 추이
        const dailyTrend = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    DATE(createdAt) as date,
                    COUNT(*) as calls,
                    SUM(cost_estimate) as cost
                FROM api_usage_logs
                WHERE user_id = ? AND createdAt >= ?
                GROUP BY DATE(createdAt)
                ORDER BY date DESC
            `, [userId, startDate.toISOString()], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 최근 호출 내역
        const recentCalls = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    id, service, model, endpoint, status, error_message,
                    image_count, audio_seconds, video_seconds,
                    cost_estimate, latency_ms, createdAt
                FROM api_usage_logs
                WHERE user_id = ?
                ORDER BY createdAt DESC
                LIMIT 50
            `, [userId], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 총 비용
        const totalCost = usageByModel.reduce((sum, m) => sum + (m.cost || 0), 0);
        const usdToKrw = 1350;

        res.json({
            user: {
                id: user.id,
                username: user.username,
                name: user.name,
                email: user.email,
                planType: user.plan_type,
                credits: user.credits
            },
            period: `${days}일`,
            summary: {
                totalCostUSD: totalCost.toFixed(4),
                totalCostKRW: Math.round(totalCost * usdToKrw),
                totalCalls: usageByModel.reduce((sum, m) => sum + m.calls, 0),
                totalImages: usageByModel.reduce((sum, m) => sum + (m.images || 0), 0),
                totalAudioMinutes: Math.round(usageByModel.reduce((sum, m) => sum + (m.audio_sec || 0), 0) / 60),
                totalVideoSeconds: Math.round(usageByModel.reduce((sum, m) => sum + (m.video_sec || 0), 0))
            },
            usageByModel,
            dailyTrend,
            recentCalls
        });
    } catch (error) {
        console.error('[API Usage User Detail] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 월별 비용 리포트 (과금용)
 * GET /api/admin/api-usage/monthly-report
 * Query: { year?: number, month?: number }
 */
app.get('/api/admin/api-usage/monthly-report', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const now = new Date();
        const year = parseInt(req.query.year) || now.getFullYear();
        const month = parseInt(req.query.month) || (now.getMonth() + 1);

        const startDate = new Date(year, month - 1, 1);
        const endDate = new Date(year, month, 0, 23, 59, 59);

        // 사용자별 월간 비용
        const monthlyByUser = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    l.user_id,
                    u.username,
                    u.name,
                    u.email,
                    u.plan_type,
                    COUNT(*) as total_calls,
                    SUM(l.image_count) as total_images,
                    SUM(l.audio_seconds) as total_audio_seconds,
                    SUM(l.video_seconds) as total_video_seconds,
                    SUM(l.cost_estimate) as total_cost
                FROM api_usage_logs l
                LEFT JOIN users u ON l.user_id = u.id
                WHERE l.createdAt >= ? AND l.createdAt <= ?
                AND l.user_id IS NOT NULL
                GROUP BY l.user_id
                ORDER BY total_cost DESC
            `, [startDate.toISOString(), endDate.toISOString()], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        // 서비스별 총 비용
        const byService = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    service,
                    COUNT(*) as calls,
                    SUM(cost_estimate) as cost
                FROM api_usage_logs
                WHERE createdAt >= ? AND createdAt <= ?
                GROUP BY service
            `, [startDate.toISOString(), endDate.toISOString()], (err, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            });
        });

        const totalCost = monthlyByUser.reduce((sum, u) => sum + (u.total_cost || 0), 0);
        const usdToKrw = 1350;

        res.json({
            period: `${year}년 ${month}월`,
            year,
            month,
            summary: {
                totalUsers: monthlyByUser.length,
                totalCalls: monthlyByUser.reduce((sum, u) => sum + u.total_calls, 0),
                totalCostUSD: totalCost.toFixed(2),
                totalCostKRW: Math.round(totalCost * usdToKrw)
            },
            byService: byService.map(s => ({
                service: s.service,
                calls: s.calls,
                costUSD: parseFloat((s.cost || 0).toFixed(4))
            })),
            byUser: monthlyByUser.map(u => ({
                userId: u.user_id,
                username: u.username,
                name: u.name,
                email: u.email,
                planType: u.plan_type,
                calls: u.total_calls,
                images: u.total_images || 0,
                audioMinutes: Math.round((u.total_audio_seconds || 0) / 60),
                videoSeconds: Math.round(u.total_video_seconds || 0),
                costUSD: parseFloat((u.total_cost || 0).toFixed(4)),
                costKRW: Math.round((u.total_cost || 0) * usdToKrw)
            })),
            exchangeRate: { USD_KRW: usdToKrw }
        });
    } catch (error) {
        console.error('[Monthly Report] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END API USAGE MONITORING ROUTES ====================


// ==================== GOOGLE CLOUD BILLING ROUTES (BigQuery) ====================

const billingService = require('./billingService');

/**
 * 결제 데이터 사용 가능 여부 확인
 * GET /api/admin/billing/status
 */
app.get('/api/admin/billing/status', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const status = await billingService.checkBillingDataAvailable();
        res.json(status);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 전체 결제 대시보드 데이터
 * GET /api/admin/billing/dashboard
 * Query: { days?: number }
 */
app.get('/api/admin/billing/dashboard', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await billingService.getBillingDashboard(days);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 서비스별 비용
 * GET /api/admin/billing/by-service
 * Query: { days?: number }
 */
app.get('/api/admin/billing/by-service', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await billingService.getCostByService(days);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 일별 비용 추이
 * GET /api/admin/billing/daily
 * Query: { days?: number }
 */
app.get('/api/admin/billing/daily', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await billingService.getDailyCostTrend(days);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * SKU별 상세 비용
 * GET /api/admin/billing/by-sku
 * Query: { days?: number, service?: string }
 */
app.get('/api/admin/billing/by-sku', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const service = req.query.service || null;
        const data = await billingService.getCostBySKU(days, service);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * 월별 비용 요약
 * GET /api/admin/billing/monthly
 * Query: { months?: number }
 */
app.get('/api/admin/billing/monthly', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const months = parseInt(req.query.months) || 6;
        const data = await billingService.getMonthlyCost(months);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

/**
 * Gemini/Vertex AI 비용
 * GET /api/admin/billing/gemini
 * Query: { days?: number }
 */
app.get('/api/admin/billing/gemini', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const data = await billingService.getGeminiCost(days);
        res.json(data);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// ==================== END GOOGLE CLOUD BILLING ROUTES ====================


// ==================== FESTIVAL & PENSION API ROUTES ====================

/**
 * 축제 목록 조회
 * GET /api/festivals
 * Query: { sido?, sigungu?, startDate?, endDate?, keyword?, page?, limit? }
 */
app.get('/api/festivals', async (req, res) => {
    try {
        const {
            sido,
            sigungu,
            startDate,
            endDate,
            keyword,
            page = 1,
            limit = 20
        } = req.query;

        let sql = `
            SELECT * FROM festivals
            WHERE is_active = 1
        `;
        const params = [];

        if (sido) {
            sql += ` AND sido = ?`;
            params.push(sido);
        }

        if (sigungu) {
            sql += ` AND sigungu = ?`;
            params.push(sigungu);
        }

        if (startDate) {
            sql += ` AND event_end_date >= ?`;
            params.push(startDate);
        }

        if (endDate) {
            sql += ` AND event_start_date <= ?`;
            params.push(endDate);
        }

        if (keyword) {
            sql += ` AND (title LIKE ? OR addr1 LIKE ?)`;
            params.push(`%${keyword}%`, `%${keyword}%`);
        }

        // 총 개수 조회
        const countSql = sql.replace('SELECT *', 'SELECT COUNT(*) as total');

        sql += ` ORDER BY event_start_date ASC LIMIT ? OFFSET ?`;
        const offset = (parseInt(page) - 1) * parseInt(limit);
        params.push(parseInt(limit), offset);

        const [festivals, countResult] = await Promise.all([
            new Promise((resolve, reject) => {
                db.all(sql, params, (err, rows) => err ? reject(err) : resolve(rows));
            }),
            new Promise((resolve, reject) => {
                db.get(countSql, params.slice(0, -2), (err, row) => err ? reject(err) : resolve(row));
            })
        ]);

        res.json({
            festivals,
            pagination: {
                total: countResult?.total || 0,
                page: parseInt(page),
                limit: parseInt(limit),
                totalPages: Math.ceil((countResult?.total || 0) / parseInt(limit))
            }
        });
    } catch (error) {
        console.error('[Festivals List] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 축제 행정구역별 그룹 조회
 * GET /api/festivals/grouped
 * Query: { primarySido?, limit? }
 * Returns festivals grouped by sido, with primarySido first
 */
app.get('/api/festivals/grouped', async (req, res) => {
    try {
        const { primarySido, limit = 30 } = req.query;

        // Get current date for filtering ongoing/upcoming festivals
        const today = new Date().toISOString().slice(0, 10).replace(/-/g, '');

        // Fetch active festivals that are ongoing or upcoming
        const sql = `
            SELECT * FROM festivals
            WHERE is_active = 1
              AND event_end_date >= ?
            ORDER BY
                CASE WHEN sido = ? THEN 0 ELSE 1 END,
                event_start_date ASC
            LIMIT ?
        `;

        const festivals = await new Promise((resolve, reject) => {
            db.all(sql, [today, primarySido || '', parseInt(limit)], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });

        // Group by sido
        const grouped = {};
        const sidoOrder = [];

        festivals.forEach(festival => {
            const sido = festival.sido || '기타';
            if (!grouped[sido]) {
                grouped[sido] = [];
                sidoOrder.push(sido);
            }
            grouped[sido].push(festival);
        });

        // Ensure primarySido is first if it exists
        if (primarySido && grouped[primarySido]) {
            const idx = sidoOrder.indexOf(primarySido);
            if (idx > 0) {
                sidoOrder.splice(idx, 1);
                sidoOrder.unshift(primarySido);
            }
        }

        res.json({
            grouped,
            sidoOrder,
            primarySido: primarySido || null,
            totalCount: festivals.length
        });
    } catch (error) {
        console.error('[Festivals Grouped] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 축제 상세 조회
 * GET /api/festivals/:id
 */
app.get('/api/festivals/:id', async (req, res) => {
    try {
        const { id } = req.params;

        const festival = await new Promise((resolve, reject) => {
            db.get('SELECT * FROM festivals WHERE id = ?', [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!festival) {
            return res.status(404).json({ error: '축제를 찾을 수 없습니다.' });
        }

        // 조회수 증가
        db.run('UPDATE festivals SET view_count = view_count + 1 WHERE id = ?', [id]);

        res.json(festival);
    } catch (error) {
        console.error('[Festival Detail] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 축제 근처 펜션 조회
 * GET /api/festivals/:id/nearby-pensions
 * Query: { radius?: number (km), limit?: number }
 */
app.get('/api/festivals/:id/nearby-pensions', async (req, res) => {
    try {
        const { id } = req.params;
        const radius = parseFloat(req.query.radius) || 30;
        const limit = parseInt(req.query.limit) || 20;

        // 축제 좌표 조회
        const festival = await new Promise((resolve, reject) => {
            db.get('SELECT mapx, mapy, sido FROM festivals WHERE id = ?', [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!festival || !festival.mapx || !festival.mapy) {
            return res.status(404).json({ error: '축제 좌표 정보가 없습니다.' });
        }

        // Haversine 공식을 사용한 거리 계산 (SQLite에서 직접 계산)
        const sql = `
            SELECT *,
                (6371 * acos(cos(radians(?)) * cos(radians(mapy)) * cos(radians(mapx) - radians(?)) + sin(radians(?)) * sin(radians(mapy)))) AS distance_km
            FROM public_pensions
            WHERE mapx IS NOT NULL AND mapy IS NOT NULL
            HAVING distance_km <= ?
            ORDER BY distance_km ASC
            LIMIT ?
        `;

        const pensions = await new Promise((resolve, reject) => {
            db.all(sql, [festival.mapy, festival.mapx, festival.mapy, radius, limit], (err, rows) => {
                if (err) {
                    // SQLite doesn't have radians/acos, fall back to simple query
                    const fallbackSql = `
                        SELECT * FROM public_pensions
                        WHERE sido = ?
                        ORDER BY name ASC
                        LIMIT ?
                    `;
                    db.all(fallbackSql, [festival.sido, limit], (err2, rows2) => {
                        if (err2) reject(err2);
                        else resolve(rows2);
                    });
                } else {
                    resolve(rows);
                }
            });
        });

        res.json({
            festivalId: id,
            radius,
            pensions,
            total: pensions.length
        });
    } catch (error) {
        console.error('[Nearby Pensions] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션 목록 조회
 * GET /api/pensions
 * Query: { sido?, sigungu?, keyword?, page?, limit? }
 */
app.get('/api/pensions', async (req, res) => {
    try {
        const {
            sido,
            sigungu,
            keyword,
            page = 1,
            limit = 20
        } = req.query;

        let sql = `SELECT * FROM public_pensions WHERE 1=1`;
        const params = [];

        if (sido) {
            sql += ` AND sido = ?`;
            params.push(sido);
        }

        if (sigungu) {
            sql += ` AND sigungu = ?`;
            params.push(sigungu);
        }

        if (keyword) {
            sql += ` AND (name LIKE ? OR address LIKE ?)`;
            params.push(`%${keyword}%`, `%${keyword}%`);
        }

        // 총 개수 조회
        const countSql = sql.replace('SELECT *', 'SELECT COUNT(*) as total');

        sql += ` ORDER BY sido, name ASC LIMIT ? OFFSET ?`;
        const offset = (parseInt(page) - 1) * parseInt(limit);
        params.push(parseInt(limit), offset);

        const [pensions, countResult] = await Promise.all([
            new Promise((resolve, reject) => {
                db.all(sql, params, (err, rows) => err ? reject(err) : resolve(rows));
            }),
            new Promise((resolve, reject) => {
                db.get(countSql, params.slice(0, -2), (err, row) => err ? reject(err) : resolve(row));
            })
        ]);

        res.json({
            pensions,
            pagination: {
                total: countResult?.total || 0,
                page: parseInt(page),
                limit: parseInt(limit),
                totalPages: Math.ceil((countResult?.total || 0) / parseInt(limit))
            }
        });
    } catch (error) {
        console.error('[Pensions List] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션 상세 조회
 * GET /api/pensions/:id
 */
app.get('/api/pensions/:id', async (req, res) => {
    try {
        const { id } = req.params;

        const pension = await new Promise((resolve, reject) => {
            db.get('SELECT * FROM public_pensions WHERE id = ?', [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!pension) {
            return res.status(404).json({ error: '펜션을 찾을 수 없습니다.' });
        }

        // 조회수 증가
        db.run('UPDATE public_pensions SET view_count = view_count + 1 WHERE id = ?', [id]);

        res.json(pension);
    } catch (error) {
        console.error('[Pension Detail] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 펜션 근처 축제 조회
 * GET /api/pensions/:id/nearby-festivals
 * Query: { radius?: number (km), limit?: number }
 */
app.get('/api/pensions/:id/nearby-festivals', async (req, res) => {
    try {
        const { id } = req.params;
        const radius = parseFloat(req.query.radius) || 50;
        const limit = parseInt(req.query.limit) || 10;

        // 펜션 좌표 조회
        const pension = await new Promise((resolve, reject) => {
            db.get('SELECT mapx, mapy, sido FROM public_pensions WHERE id = ?', [id], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });

        if (!pension || !pension.mapx || !pension.mapy) {
            return res.status(404).json({ error: '펜션 좌표 정보가 없습니다.' });
        }

        // 같은 지역의 진행중/예정 축제 조회
        const today = new Date().toISOString().slice(0, 10).replace(/-/g, '');
        const sql = `
            SELECT * FROM festivals
            WHERE sido = ?
            AND is_active = 1
            AND event_end_date >= ?
            ORDER BY event_start_date ASC
            LIMIT ?
        `;

        const festivals = await new Promise((resolve, reject) => {
            db.all(sql, [pension.sido, today, limit], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });

        res.json({
            pensionId: id,
            radius,
            festivals,
            total: festivals.length
        });
    } catch (error) {
        console.error('[Nearby Festivals] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 지역별 펜션 통계
 * GET /api/pensions/stats/by-region
 */
app.get('/api/pensions/stats/by-region', async (req, res) => {
    try {
        const stats = await new Promise((resolve, reject) => {
            db.all(`
                SELECT sido, COUNT(*) as count
                FROM public_pensions
                WHERE sido IS NOT NULL
                GROUP BY sido
                ORDER BY count DESC
            `, [], (err, rows) => err ? reject(err) : resolve(rows));
        });

        const total = stats.reduce((sum, row) => sum + row.count, 0);

        res.json({ stats, total });
    } catch (error) {
        console.error('[Pension Stats] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 지역별 축제 통계
 * GET /api/festivals/stats/by-region
 */
app.get('/api/festivals/stats/by-region', async (req, res) => {
    try {
        const today = new Date().toISOString().slice(0, 10).replace(/-/g, '');

        const stats = await new Promise((resolve, reject) => {
            db.all(`
                SELECT sido, COUNT(*) as count
                FROM festivals
                WHERE sido IS NOT NULL
                AND is_active = 1
                AND event_end_date >= ?
                GROUP BY sido
                ORDER BY count DESC
            `, [today], (err, rows) => err ? reject(err) : resolve(rows));
        });

        const total = stats.reduce((sum, row) => sum + row.count, 0);

        res.json({ stats, total });
    } catch (error) {
        console.error('[Festival Stats] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 월별 축제 통계
 * GET /api/festivals/stats/by-month
 */
app.get('/api/festivals/stats/by-month', async (req, res) => {
    try {
        const today = new Date().toISOString().slice(0, 10).replace(/-/g, '');

        // 월별 축제 수 집계
        const monthlyStats = await new Promise((resolve, reject) => {
            db.all(`
                SELECT
                    substr(event_start_date, 1, 6) as year_month,
                    COUNT(*) as count
                FROM festivals
                WHERE is_active = 1
                AND event_end_date >= ?
                GROUP BY substr(event_start_date, 1, 6)
                ORDER BY year_month ASC
            `, [today], (err, rows) => err ? reject(err) : resolve(rows));
        });

        // 현재 월, 다음 월 계산
        const now = new Date();
        const currentYearMonth = `${now.getFullYear()}${String(now.getMonth() + 1).padStart(2, '0')}`;
        const nextMonth = new Date(now.getFullYear(), now.getMonth() + 1, 1);
        const nextYearMonth = `${nextMonth.getFullYear()}${String(nextMonth.getMonth() + 1).padStart(2, '0')}`;

        // 현재 월 진행중 축제 (시작일이 이번달 이전이고 종료일이 오늘 이후)
        const currentMonthFestivals = await new Promise((resolve, reject) => {
            db.get(`
                SELECT COUNT(*) as count
                FROM festivals
                WHERE is_active = 1
                AND event_start_date <= ?
                AND event_end_date >= ?
            `, [currentYearMonth + '31', today], (err, row) => err ? reject(err) : resolve(row));
        });

        // 다음 월 축제
        const nextMonthFestivals = await new Promise((resolve, reject) => {
            db.get(`
                SELECT COUNT(*) as count
                FROM festivals
                WHERE is_active = 1
                AND substr(event_start_date, 1, 6) = ?
            `, [nextYearMonth], (err, row) => err ? reject(err) : resolve(row));
        });

        // 월 이름 포맷팅
        const formatMonth = (ym) => {
            if (!ym || ym.length !== 6) return ym;
            const year = ym.slice(0, 4);
            const month = parseInt(ym.slice(4, 6));
            return `${year}.${month}월`;
        };

        const formattedStats = monthlyStats.map(item => ({
            yearMonth: item.year_month,
            label: formatMonth(item.year_month),
            count: item.count
        }));

        const total = monthlyStats.reduce((sum, row) => sum + row.count, 0);

        res.json({
            stats: formattedStats,
            total,
            currentMonth: {
                yearMonth: currentYearMonth,
                label: formatMonth(currentYearMonth),
                count: currentMonthFestivals?.count || 0
            },
            nextMonth: {
                yearMonth: nextYearMonth,
                label: formatMonth(nextYearMonth),
                count: nextMonthFestivals?.count || 0
            }
        });
    } catch (error) {
        console.error('[Festival Monthly Stats] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 관리자: 축제 데이터 동기화
 * POST /api/admin/sync/festivals
 * Body: { areaCode?: string }
 */
app.post('/api/admin/sync/festivals', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { areaCode } = req.body;

        // 동기화는 백그라운드에서 실행하고 즉시 응답
        res.json({
            message: '축제 동기화가 시작되었습니다.',
            hint: 'node server/scripts/syncData.js festivals 명령어로 직접 실행도 가능합니다.'
        });

        // Note: 실제 동기화는 별도 프로세스에서 실행 권장
        // const { spawn } = require('child_process');
        // spawn('node', ['scripts/syncData.js', 'festivals'], { cwd: __dirname });
    } catch (error) {
        console.error('[Admin Sync Festivals] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

/**
 * 관리자: 펜션 데이터 동기화
 * POST /api/admin/sync/pensions
 * Body: { areaCode?: string }
 */
app.post('/api/admin/sync/pensions', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { areaCode } = req.body;

        res.json({
            message: '펜션 동기화가 시작되었습니다.',
            hint: 'node server/scripts/syncData.js pensions 명령어로 직접 실행도 가능합니다.'
        });
    } catch (error) {
        console.error('[Admin Sync Pensions] 오류:', error);
        res.status(500).json({ error: error.message });
    }
});

// ==================== END FESTIVAL & PENSION API ROUTES ====================


// 모든 기타 요청은 React 앱으로 전달 (SPA 라우팅 지원)
app.get('*', (req, res) => {
    console.log("unknown req : ", req)
    res.sendFile(path.join(__dirname, '../dist/index.html'));
});

app.listen(PORT, () => {
    console.log(`서버가 http://localhost:${PORT} 에서 실행 중입니다.`);

    // 스케줄러 시작 (주간 자동 생성)
    try {
        const schedulerService = require('./services/schedulerService');
        schedulerService.startScheduler();
    } catch (error) {
        console.error('스케줄러 시작 실패:', error.message);
    }
});