<?php
namespace Blogibot\Feed;

if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * Feed Database Handler
 * Manages database operations for feed sources
 */
class FeedDatabase {
    
    private static $table_name = 'blogibot_feed_sources';
    
    /**
     * Create database tables on plugin activation
     */
    public static function create_tables() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::$table_name;
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            feed_name varchar(255) NOT NULL,
            feed_url varchar(500) NOT NULL,
            feed_type varchar(20) DEFAULT 'rss' COMMENT 'rss or atom',
            category varchar(100) DEFAULT NULL,
            fetch_frequency varchar(20) DEFAULT 'daily',
            max_posts int(11) DEFAULT 10,
            status varchar(20) DEFAULT 'active',
            auto_fetch tinyint(1) DEFAULT 1 COMMENT 'Enable automatic fetching',
            last_fetch_time datetime DEFAULT NULL,
            last_fetch_status varchar(20) DEFAULT NULL,
            last_fetch_message text DEFAULT NULL,
            posts_imported_total int(11) DEFAULT 0,
            posts_imported_today int(11) DEFAULT 0,
            auto_featured_image tinyint(1) DEFAULT 1,
            auto_publish tinyint(1) DEFAULT 0,
            excerpt_length int(11) DEFAULT 150,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY feed_url (feed_url),
            KEY status (status),
            KEY category (category),
            KEY last_fetch_time (last_fetch_time)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Create fetched posts table
        self::create_fetched_posts_table();
    }
    
    /**
     * Create fetched posts tracking table
     */
    private static function create_fetched_posts_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'blogibot_fetched_posts';
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            feed_source_id bigint(20) UNSIGNED NOT NULL,
            post_id bigint(20) UNSIGNED DEFAULT NULL,
            feed_item_guid varchar(500) NOT NULL,
            feed_item_title varchar(500) NOT NULL,
            feed_item_url varchar(500) NOT NULL,
            feed_item_date datetime DEFAULT NULL,
            import_status varchar(20) DEFAULT 'pending',
            import_date datetime DEFAULT NULL,
            error_message text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY feed_item_guid (feed_item_guid),
            KEY feed_source_id (feed_source_id),
            KEY post_id (post_id),
            KEY import_status (import_status),
            KEY import_date (import_date)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    
    /**
     * Get table name with prefix
     */
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::$table_name;
    }
    
    /**
     * Get fetched posts table name with prefix
     */
    public static function get_fetched_posts_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'blogibot_fetched_posts';
    }
    
    /**
     * Drop tables on plugin uninstall
     */
    public static function drop_tables() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::$table_name;
        $fetched_posts_table = $wpdb->prefix . 'blogibot_fetched_posts';
        
        $wpdb->query("DROP TABLE IF EXISTS {$fetched_posts_table}");
        $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
    }
}
