import java.io.File;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.stream.Collectors;

public class PopulateLivestreamFinal {
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/sisvietnam", "sisvietnam", "sisvietnam");

            long tagId = 6351;

            // Delete old tags first, then old posts
            PreparedStatement delTagStmt = conn.prepareStatement(
                "DELETE FROM sis_post_tag WHERE tag_id = ?");
            delTagStmt.setLong(1, tagId);
            delTagStmt.executeUpdate();

            PreparedStatement delStmt = conn.prepareStatement(
                "DELETE FROM sis_post WHERE id BETWEEN 6600 AND 6610");
            delStmt.executeUpdate();

            String baseDir = "/home/x79/sisvietnamvn_01/Media/Livestream";
            String uploadDir = "/home/x79/sisvietnamvn_01/sisvietnamvn_main/src/main/resources/static/upload/livestream";
            new File(uploadDir).mkdirs();

            long currentId = 6600;

            for (int i = 1; i <= 4; i++) {
                String folder = baseDir + "/Tuan" + i;
                File f = new File(folder);
                if (!f.exists()) continue;

                // Title is just the week number for tab display
                String title = String.valueOf(i);
                // Excerpt is the short topic headline (line 2 of content.txt, after the program name)
                String excerpt = "";
                StringBuilder content = new StringBuilder();
                String metaDescRaw = "";
                String imageUrl = "";

                File contentFile = new File(folder, "content.txt");
                if (contentFile.exists()) {
                    List<String> lines = Files.readAllLines(contentFile.toPath())
                        .stream().filter(l -> !l.trim().isEmpty()).collect(Collectors.toList());

                    // Line 0 = full program name (used as excerpt/subtitle)
                    // Extract the topic after "Chủ đề:" if present
                    if (lines.size() > 0) {
                        String fullTitle = lines.get(0);
                        int chuDeIdx = fullTitle.indexOf("Chủ đề:");
                        if (chuDeIdx >= 0) {
                            excerpt = fullTitle.substring(chuDeIdx + "Chủ đề:".length()).trim();
                        } else {
                            chuDeIdx = fullTitle.indexOf("Chủ đề: ");
                            if (chuDeIdx >= 0) {
                                excerpt = fullTitle.substring(chuDeIdx + "Chủ đề: ".length()).trim();
                            } else {
                                excerpt = fullTitle;
                            }
                        }
                    }
                    
                    // Line 1 onward = description content
                    if (lines.size() > 1) {
                        // Take first 2-3 meaningful lines as description
                        int maxLines = Math.min(lines.size(), 4);
                        for (int j = 1; j < maxLines; j++) {
                            content.append(lines.get(j));
                            if (j < maxLines - 1) content.append("<br>");
                        }
                    }
                }

                File embedFile = new File(folder, "embed.txt");
                if (embedFile.exists()) {
                    metaDescRaw = Files.readString(embedFile.toPath()).trim();
                }

                // Find image
                for (File img : f.listFiles()) {
                    String name = img.getName().toLowerCase();
                    if (name.endsWith(".jpg") || name.endsWith(".png") || name.endsWith(".jpeg")) {
                        File dest = new File(uploadDir, "tuan" + i + "_" + img.getName());
                        Files.copy(img.toPath(), dest.toPath(), java.nio.file.StandardCopyOption.REPLACE_EXISTING);
                        imageUrl = "/upload/livestream/" + dest.getName();
                        break;
                    }
                }

                String slug = "livestream-tuan-" + i;

                PreparedStatement insertStmt = conn.prepareStatement(
                    "INSERT INTO sis_post (id, slug, title, excerpt, content, meta_description, featured_image, category_id, created_date, status, layout, created_by) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, NULL, SYSDATE, 'PUBLISHED', 'STANDARD', 'admin')"
                );
                insertStmt.setLong(1, currentId);
                insertStmt.setString(2, slug);
                insertStmt.setString(3, title);
                insertStmt.setString(4, excerpt);
                insertStmt.setString(5, content.toString());
                insertStmt.setString(6, metaDescRaw);
                insertStmt.setString(7, imageUrl);
                insertStmt.executeUpdate();

                PreparedStatement tagStmt = conn.prepareStatement(
                    "INSERT INTO sis_post_tag (post_id, tag_id) VALUES (?, ?)");
                tagStmt.setLong(1, currentId);
                tagStmt.setLong(2, tagId);
                tagStmt.executeUpdate();

                currentId++;
                System.out.println("Inserted Tuan " + i + " | excerpt=" + excerpt + " | image=" + imageUrl);
            }

            conn.close();
            System.out.println("Done populating livestream data.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}