<?php
/**
 * Rank Writer Database Operations
 * 
 * Handles database table creation and queries for tracking
 * which Morningscore articles have been uploaded to WordPress.
 */

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

class Rank_Writer_DB {
    
    const TABLE_NAME = 'rank_writer_uploads';
    
    /**
     * Create the uploads tracking table
     */
    public static function create_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            morningscore_article_id VARCHAR(50) NOT NULL,
            wp_post_id BIGINT(20) UNSIGNED NOT NULL,
            article_title VARCHAR(255) NOT NULL,
            uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY unique_article (morningscore_article_id),
            KEY idx_wp_post (wp_post_id)
        ) $charset_collate;";
        
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );
    }
    
    /**
     * Check if an article has already been uploaded
     * 
     * @param string $article_id Morningscore article ID
     * @return bool True if already uploaded
     */
    public static function is_article_uploaded( $article_id ) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        $count = $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM $table_name WHERE morningscore_article_id = %s",
            $article_id
        ) );
        
        return $count > 0;
    }
    
    /**
     * Record an article upload
     * 
     * @param string $article_id Morningscore article ID
     * @param int $wp_post_id WordPress post ID
     * @param string $title Article title
     * @return bool|int False on failure, insert ID on success
     */
    public static function record_upload( $article_id, $wp_post_id, $title ) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        $result = $wpdb->insert(
            $table_name,
            array(
                'morningscore_article_id' => $article_id,
                'wp_post_id' => $wp_post_id,
                'article_title' => $title,
            ),
            array( '%s', '%d', '%s' )
        );
        
        return $result ? $wpdb->insert_id : false;
    }
    
    /**
     * Get all uploaded article IDs
     * 
     * @return array Array of Morningscore article IDs that have been uploaded
     */
    public static function get_uploaded_article_ids() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        $results = $wpdb->get_col(
            "SELECT morningscore_article_id FROM $table_name"
        );
        
        return $results ? $results : array();
    }
    
    /**
     * Get upload info for a specific article
     * 
     * @param string $article_id Morningscore article ID
     * @return object|null Upload record or null if not found
     */
    public static function get_upload_info( $article_id ) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM $table_name WHERE morningscore_article_id = %s",
            $article_id
        ) );
    }
    
    /**
     * Delete upload record (used if WP post is deleted)
     * 
     * @param string $article_id Morningscore article ID
     * @return bool True on success
     */
    public static function delete_upload_record( $article_id ) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        return $wpdb->delete(
            $table_name,
            array( 'morningscore_article_id' => $article_id ),
            array( '%s' )
        ) !== false;
    }
    
    /**
     * Check if the table exists
     * 
     * @return bool True if table exists
     */
    public static function table_exists() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        return $wpdb->get_var( $wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $table_name
        ) ) === $table_name;
    }
}