const pool = require("../db/pool.js");
const env = process.env.NODE_ENV;
const tbData = env === 'production' ? 'tb_data' : '_test_tb_data';

async function runAutoClean() {
    const client = await pool.connect();
    try {
        console.log("⏰ Running auto clean batch job...");
        
        // 1. 글로벌 보관 정책 조회
        const policyRes = await client.query("SELECT * FROM ver4.tb_system_policy WHERE policy_key = 'GLOBAL_DELETE_POLICY'");
        if (policyRes.rows.length === 0 || !policyRes.rows[0].is_active) {
            console.log("⏰ Auto clean policy is disabled or not found.");
            return;
        }

        const { limit_file_count, limit_days } = policyRes.rows[0];

        // 2. 삭제 대상 3단계 폴더 검색
        // - is_folder = true 이고 depth = 3 이며 아직 삭제되지 않은 폴더
        // - 하위의 유효 파일 개수가 limit_file_count 미만
        // - last_folder_act_date 가 limit_days 일 이상 경과
        const targetQuery = `
            SELECT data_id, project_id, path1, path2, path3, last_folder_act_date
            FROM ver4.${tbData} f
            WHERE f.is_folder = true 
              AND f.data_depth = 3 
              AND f.is_removed = false
              AND f.last_folder_act_date < NOW() - CAST($1 || ' days' AS INTERVAL)
              AND (
                  SELECT COUNT(*)
                  FROM ver4.${tbData} files
                  WHERE files.project_id = f.project_id
                    AND files.path1 = f.path1
                    AND files.path2 = f.path2
                    AND files.path3 = f.path3
                    AND files.is_folder = false
                    AND files.is_removed = false
              ) < $2;
        `;
        const targets = await client.query(targetQuery, [limit_days, limit_file_count]);

        console.log(`⏰ Found ${targets.rows.length} folders to clean up.`);

        for (const folder of targets.rows) {
            let success = true;
            try {
                await client.query('BEGIN');
                
                // 폴더 자체 및 그 하위 파일/폴더들을 전부 Soft-Delete (is_removed = true)
                const updateQuery = `
                    UPDATE ver4.${tbData}
                    SET is_removed = true,
                        mod_date = CURRENT_TIMESTAMP,
                        mod_user_id = 'SYSTEM'
                    WHERE project_id = $1
                      AND path1 = $2
                      AND path2 = $3
                      AND path3 = $4
                      AND is_removed = false;
                `;
                await client.query(updateQuery, [folder.project_id, folder.path1, folder.path2, folder.path3]);
                
                await client.query('COMMIT');
            } catch (err) {
                await client.query('ROLLBACK');
                console.error(`❌ Auto Clean Folder Error [Folder ID: ${folder.data_id}]:`, err.message);
                success = false;
            }

            // 삭제 경로 조합 (/path1/path2/path3)
            const cleanPath = `/${folder.path1}/${folder.path2}/${folder.path3}`;
            const criteria = `보관수량 ${limit_file_count}개 미만 / 기한 ${limit_days}일 경과`;
            
            // 로그 기록
            await client.query(`
                INSERT INTO ver4.tb_auto_clean_log (clean_date, project_id, clean_path, criteria_info, result_status)
                VALUES (CURRENT_TIMESTAMP, $1, $2, $3, $4);
            `, [folder.project_id, cleanPath, criteria, success ? 'SUCCESS' : 'FAILED']);
        }
        console.log("⏰ Auto clean batch job finished successfully.");
    } catch (err) {
        console.error("❌ Auto Clean Batch Error:", err);
    } finally {
        client.release();
    }
}

// 매일 자정에 한 번 가동하는 타이머 등록 함수
function startScheduler() {
    const now = new Date();
    const target = new Date();
    target.setHours(0, 0, 0, 0);
    if (now > target) {
        target.setDate(target.getDate() + 1);
    }
    const delay = target - now;

    console.log(`⏰ Scheduler loaded. Next run in ${Math.floor(delay / 1000 / 60)} minutes.`);
    setTimeout(async () => {
        await runAutoClean();
        // 실행 후 다음날 자정으로 다시 예약
        startScheduler();
    }, delay);
}

module.exports = {
    start: startScheduler,
    runNow: runAutoClean
};