<?php

/**
 * Roadmap Items Schema
 *
 * @package ProRankSEO\Database\Schema
 * @since   1.0.0
 */

declare(strict_types=1);

namespace ProRank\SEO\Database\Schema;

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


/**
 * RoadmapItemsSchema class
 */


class RoadmapItemsSchema {
    
    /**
     * Get table name
     *
     * @return string
     */
    public static function get_table_name(): string {
        global $wpdb;
        return $wpdb->prefix . 'prorank_roadmap_items';
    }
    
    /**
     * Get schema SQL
     *
     * @return string
     */
    public static function get_schema(): string {
        $table_name = self::get_table_name();
        $charset_collate = self::get_charset_collate();
        
        return "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            description text,
            status varchar(50) NOT NULL DEFAULT 'backlog',
            priority varchar(20) NOT NULL DEFAULT 'medium',
            assignee_id bigint(20) unsigned DEFAULT NULL,
            due_date datetime DEFAULT NULL,
            url_target varchar(2048) DEFAULT NULL,
            created_by bigint(20) unsigned NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            completed_at datetime DEFAULT NULL,
            tags_json text,
            meta_json text,
            reach int(11) DEFAULT NULL,
            impact int(11) DEFAULT NULL,
            confidence int(11) DEFAULT NULL,
            effort int(11) DEFAULT NULL,
            score float DEFAULT NULL,
            position int(11) NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_status (status),
            KEY idx_assignee (assignee_id),
            KEY idx_priority (priority),
            KEY idx_due_date (due_date),
            KEY idx_created_by (created_by),
            KEY idx_position (position)
        ) {$charset_collate};";
    }
    
    /**
     * Get charset collate
     *
     * @return string
     */
    private static function get_charset_collate(): string {
        global $wpdb;
        
        $charset_collate = '';
        if (!empty($wpdb->charset)) {
            $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
        }
        if (!empty($wpdb->collate)) {
            $charset_collate .= " COLLATE {$wpdb->collate}";
        }
        
        return $charset_collate;
    }
    
    /**
     * Drop table
     *
     * @return void
     */
    public static function drop_table(): void {
        global $wpdb;
        $table_name = self::get_table_name();
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
    }
}
