<?php
/**
 * Costudio_Planning — CRUD helper for wp_costudio_planning.
 *
 * The table name is kept as 'costudio_planning' (not renamed) for backward
 * compatibility with existing data from the legacy plugin.
 *
 * @package Contentosapp_Studio
 * @since   3.0.0
 */

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- CRUD on plugin-owned costudio_planning table; $table = $wpdb->prefix . constant, all user values pass through $wpdb->prepare placeholders.

class Costudio_Planning {

    const TABLE = 'costudio_planning';

    public static function create( array $data ): int {
        global $wpdb;

        $wpdb->insert( $wpdb->prefix . self::TABLE, [
            'title'        => sanitize_text_field( $data['title']        ?? '' ),
            'keyword'      => sanitize_text_field( $data['keyword']      ?? '' ),
            'site_key'     => sanitize_text_field( $data['site_key']     ?? '' ),
            'category'     => sanitize_text_field( $data['category']     ?? '' ),
            'planned_date' => ! empty( $data['planned_date'] ) ? sanitize_text_field( $data['planned_date'] ) : null,
            'priority'     => sanitize_text_field( $data['priority']     ?? 'medium' ),
            'status'       => sanitize_text_field( $data['status']       ?? 'planned' ),
            'created_at'   => current_time( 'mysql' ),
        ] );

        return (int) $wpdb->insert_id;
    }

    public static function update( int $id, array $data ): bool {
        global $wpdb;

        $fields = [];
        foreach ( [ 'title', 'keyword', 'site_key', 'category', 'planned_date', 'priority', 'status' ] as $key ) {
            if ( isset( $data[ $key ] ) ) {
                $fields[ $key ] = sanitize_text_field( $data[ $key ] );
            }
        }

        if ( empty( $fields ) ) return false;

        return $wpdb->update( $wpdb->prefix . self::TABLE, $fields, [ 'id' => $id ] ) !== false;
    }

    public static function update_status( int $id, string $status ): void {
        global $wpdb;
        $wpdb->update( $wpdb->prefix . self::TABLE, [ 'status' => $status ], [ 'id' => $id ] );
    }

    public static function find( int $id ): ?object {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM ' . $wpdb->prefix . self::TABLE . ' WHERE id = %d', $id )
        ) ?: null;
    }

    public static function delete( int $id ): void {
        global $wpdb;
        $wpdb->delete( $wpdb->prefix . self::TABLE, [ 'id' => $id ] );
    }

    /** @return object[] */
    public static function all( array $args = [] ): array {
        global $wpdb;

        $table  = $wpdb->prefix . self::TABLE;
        $wheres = [ '1=1' ];
        $params = [];

        if ( ! empty( $args['site_key'] ) ) {
            $wheres[] = 'site_key = %s';
            $params[]  = $args['site_key'];
        }
        if ( ! empty( $args['status'] ) ) {
            $wheres[] = 'status = %s';
            $params[]  = $args['status'];
        }
        if ( ! empty( $args['search'] ) ) {
            $wheres[] = '(title LIKE %s OR keyword LIKE %s)';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
        }
        if ( ! empty( $args['priority'] ) && in_array( $args['priority'], [ 'high', 'medium', 'low' ], true ) ) {
            $wheres[] = 'priority = %s';
            $params[] = $args['priority'];
        }

        $where  = implode( ' AND ', $wheres );
        $limit  = (int) ( $args['limit']  ?? 50 );
        $offset = (int) ( $args['offset'] ?? 0 );
        
        $allowed_orderby = [
            'id'           => 'id',
            'title'        => 'title',
            'keyword'      => 'keyword',
            'site_key'     => 'site_key',
            'category'     => 'category',
            'planned_date' => 'planned_date',
            'priority'     => 'priority',
            'status'       => 'status',
            'created_at'   => 'created_at',
        ];
        
        $orderby = 'planned_date ASC, created_at DESC';
        if ( ! empty( $args['orderby'] ) && array_key_exists( $args['orderby'], $allowed_orderby ) ) {
            $order_col = $allowed_orderby[ $args['orderby'] ];
            $order_dir = strtoupper( $args['order'] ?? 'ASC' );
            if ( ! in_array( $order_dir, [ 'ASC', 'DESC' ], true ) ) {
                $order_dir = 'ASC';
            }
            $orderby = "{$order_col} {$order_dir}";
        }

        $sql = "SELECT * FROM {$table} WHERE {$where} "
             . "ORDER BY {$orderby} "
             . 'LIMIT %d OFFSET %d';

        return $wpdb->get_results(
            $wpdb->prepare( $sql, array_merge( $params, [ $limit, $offset ] ) )
        ) ?: [];
    }

    public static function count( array $args = [] ): int {
        global $wpdb;

        $table  = $wpdb->prefix . self::TABLE;
        $wheres = [ '1=1' ];
        $params = [];

        if ( ! empty( $args['site_key'] ) ) {
            $wheres[] = 'site_key = %s';
            $params[]  = $args['site_key'];
        }
        if ( ! empty( $args['status'] ) ) {
            $wheres[] = 'status = %s';
            $params[]  = $args['status'];
        }
        if ( ! empty( $args['search'] ) ) {
            $wheres[] = '(title LIKE %s OR keyword LIKE %s)';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $params[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
        }
        if ( ! empty( $args['priority'] ) && in_array( $args['priority'], [ 'high', 'medium', 'low' ], true ) ) {
            $wheres[] = 'priority = %s';
            $params[] = $args['priority'];
        }

        $where = implode( ' AND ', $wheres );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is $wpdb->prefix . constant; $where is built from hardcoded placeholders only.
        $sql   = "SELECT COUNT(*) FROM {$table} WHERE {$where}";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- always routed through prepare(); variable SQL is safe: table = constant prefix, where = hardcoded placeholders.
        return (int) $wpdb->get_var( empty( $params ) ? $wpdb->prepare( $sql ) : $wpdb->prepare( $sql, ...$params ) );
    }
}
