import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PopulateNewsEventsEducation {
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/sisvietnam", "sisvietnam", "sisvietnam");
            
            // Delete old duplicate posts
            PreparedStatement delTagStmt = conn.prepareStatement("DELETE FROM sis_post_tag WHERE post_id >= 7000");
            delTagStmt.executeUpdate();

            PreparedStatement delStmt = conn.prepareStatement("DELETE FROM sis_post WHERE id >= 7000");
            delStmt.executeUpdate();
            
            // Get post 4151 to duplicate
            PreparedStatement getPost = conn.prepareStatement("SELECT title, excerpt, content, meta_description, featured_image, category_id, layout, created_by FROM sis_post WHERE id = 4151");
            ResultSet rs = getPost.executeQuery();
            if (!rs.next()) {
                System.out.println("Post 4151 not found!");
                return;
            }
            
            String originalTitle = rs.getString("title");
            String excerpt = rs.getString("excerpt");
            String content = rs.getString("content");
            String metaDesc = rs.getString("meta_description");
            String featuredImage = rs.getString("featured_image");
            long categoryId = rs.getLong("category_id");
            boolean catIsNull = rs.wasNull();
            
            String layout = rs.getString("layout");
            String createdBy = rs.getString("created_by");
            
            long currentId = 7000;
            
            long[] tagIds = {4251, 4252, 6151}; // news, event, education
            String[] prefixes = {"[Tin Tức] ", "[Sự Kiện] ", "[Đào Tạo] "};
            String[] slugPrefixes = {"tin-tuc-", "su-kien-", "dao-tao-"};
            
            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 (?, ?, ?, ?, ?, ?, ?, ?, SYSDATE - ?, 'PUBLISHED', ?, ?)"
            );
            PreparedStatement tagStmt = conn.prepareStatement("INSERT INTO sis_post_tag (post_id, tag_id) VALUES (?, ?)");

            for (int t = 0; t < 3; t++) {
                for (int i = 1; i <= 4; i++) {
                    String title = prefixes[t] + originalTitle + " " + i;
                    String slug = slugPrefixes[t] + currentId;
                    
                    insertStmt.setLong(1, currentId);
                    insertStmt.setString(2, slug);
                    insertStmt.setString(3, title);
                    insertStmt.setString(4, excerpt);
                    insertStmt.setString(5, content);
                    insertStmt.setString(6, metaDesc);
                    insertStmt.setString(7, featuredImage);
                    
                    if (catIsNull) {
                        insertStmt.setNull(8, java.sql.Types.BIGINT);
                    } else {
                        insertStmt.setLong(8, categoryId);
                    }
                    
                    insertStmt.setInt(9, i); // Subtract i days to vary dates
                    insertStmt.setString(10, layout);
                    insertStmt.setString(11, createdBy);
                    insertStmt.executeUpdate();
                    
                    tagStmt.setLong(1, currentId);
                    tagStmt.setLong(2, tagIds[t]);
                    tagStmt.executeUpdate();
                    
                    currentId++;
                }
            }
            
            conn.close();
            System.out.println("Done duplicating posts.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}