<?php
/**
 * TitanCart Todo Manager
 *
 * CRUD + status management for the admin To Do list.
 *
 * @package TitanCart\Modules\Todo
 */

namespace TitanCart\Modules\Todo;

defined( 'ABSPATH' ) || exit;

class TodoManager {

    private $db;

    public function __construct() {
        $this->db = titancart_db();
    }

    private function t( string $name ): string {
        return $this->db->table( $name );
    }

    /**
     * Get all todos with optional filters.
     */
    public function get_list( array $params = [] ): array {
        global $wpdb;

        $where = [ '1=1' ];
        $binds = [];

        if ( ! empty( $params['status'] ) ) {
            $where[] = 't.status = %s';
            $binds[] = $params['status'];
        }
        if ( ! empty( $params['priority'] ) ) {
            $where[] = 't.priority = %s';
            $binds[] = $params['priority'];
        }
        if ( ! empty( $params['assigned_to'] ) ) {
            $where[] = 't.assigned_to = %d';
            $binds[] = (int) $params['assigned_to'];
        }
        if ( ! empty( $params['search'] ) ) {
            $like = '%' . $wpdb->esc_like( $params['search'] ) . '%';
            $where[] = '(t.title LIKE %s OR t.description LIKE %s)';
            $binds[] = $like;
            $binds[] = $like;
        }
        if ( ! empty( $params['overdue'] ) ) {
            $where[] = "t.due_date IS NOT NULL AND t.due_date < CURDATE() AND t.status != 'done'";
        }

        $where_sql = implode( ' AND ', $where );

        $order = 't.position ASC, t.created_at DESC';
        if ( ! empty( $params['sort'] ) ) {
            $allowed_sorts = [
                'priority'   => "FIELD(t.priority, 'urgent', 'high', 'medium', 'low')",
                'due_date'   => 't.due_date IS NULL, t.due_date ASC',
                'created_at' => 't.created_at DESC',
                'status'     => "FIELD(t.status, 'todo', 'in_progress', 'done')",
            ];
            if ( isset( $allowed_sorts[ $params['sort'] ] ) ) {
                $order = $allowed_sorts[ $params['sort'] ];
            }
        }

        $sql = "SELECT t.*
                FROM {$this->t('todos')} t
                WHERE {$where_sql}
                ORDER BY {$order}";

        $rows = $binds
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQL.NotPrepared -- placeholders in $where_sql built in lockstep with $binds; empty-binds case takes the unprepared branch.
            ? $wpdb->get_results( $wpdb->prepare( $sql, ...$binds ), ARRAY_A )
            : $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- empty-binds branch: $sql contains no placeholders and interpolates only tc_ table constants + a whitelisted ORDER BY.

        // Enrich with user display names
        $user_ids = array_unique( array_merge(
            array_column( $rows ?: [], 'assigned_to' ),
            array_column( $rows ?: [], 'created_by' )
        ) );
        $user_ids = array_filter( $user_ids, function( $id ) { return (int) $id > 0; } );

        $user_map = [];
        if ( $user_ids ) {
            $placeholders = implode( ',', array_fill( 0, count( $user_ids ), '%d' ) );
            $sql = "SELECT ID, display_name FROM {$wpdb->users} WHERE ID IN ({$placeholders})";
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQL.NotPrepared -- $placeholders is a %d list built from count($user_ids) (non-empty, guarded above); values passed to prepare().
            $users = $wpdb->get_results( $wpdb->prepare( $sql, ...array_map( 'intval', array_values( $user_ids ) ) ), ARRAY_A );
            foreach ( $users as $u ) {
                $user_map[ (int) $u['ID'] ] = $u['display_name'];
            }
        }

        foreach ( $rows as &$r ) {
            $r['assigned_name'] = $user_map[ (int) $r['assigned_to'] ] ?? '';
            $r['created_by_name'] = $user_map[ (int) $r['created_by'] ] ?? '';
        }

        return $rows ?: [];
    }

    /**
     * Get a single todo.
     */
    public function get( int $id ): ?array {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$this->t('todos')} WHERE id = %d", $id
        ), ARRAY_A );
        if ( ! $row ) return null;

        // Enrich user names
        foreach ( [ 'assigned_to', 'created_by' ] as $field ) {
            $uid = (int) $row[ $field ];
            if ( $uid > 0 ) {
                $u = get_userdata( $uid );
                $row[ str_replace( '_to', '_name', str_replace( 'created_by', 'created_by_name', $field ) ) ] = $u ? $u->display_name : '';
            }
        }
        $row['assigned_name'] = '';
        $row['created_by_name'] = '';
        if ( (int) $row['assigned_to'] > 0 ) {
            $u = get_userdata( (int) $row['assigned_to'] );
            $row['assigned_name'] = $u ? $u->display_name : '';
        }
        if ( (int) $row['created_by'] > 0 ) {
            $u = get_userdata( (int) $row['created_by'] );
            $row['created_by_name'] = $u ? $u->display_name : '';
        }

        return $row;
    }

    /**
     * Create a todo.
     */
    public function create( array $data ): int {
        global $wpdb;

        // Get next position
        $max_pos = (int) $wpdb->get_var( "SELECT MAX(position) FROM {$this->t('todos')}" );

        $row = [
            'title'       => sanitize_text_field( $data['title'] ?? '' ),
            'description' => sanitize_textarea_field( $data['description'] ?? '' ),
            'priority'    => in_array( $data['priority'] ?? 'medium', ['low','medium','high','urgent'], true ) ? ( $data['priority'] ?? 'medium' ) : 'medium',
            'status'      => in_array( $data['status'] ?? 'todo', ['todo','in_progress','done'], true ) ? ( $data['status'] ?? 'todo' ) : 'todo',
            'assigned_to' => (int) ( $data['assigned_to'] ?? 0 ),
            'position'    => $max_pos + 1,
            'created_by'  => get_current_user_id(),
        ];

        // Only include due_date if it has a real value.
        //
        // ⚠️ S388 CORRECTION — THIS IS WHERE A PIECE OF FOLKLORE WAS BORN. This comment
        // used to read "$wpdb->insert() converts PHP null to '' which fails for DATE
        // columns on strict MySQL". THAT IS FALSE, and the false claim escaped this file,
        // reached SESSION_HANDOFF.md L1270, and hardened into a standing rail that
        // constrained five licensing tickets until S388 read the core source.
        //
        // $wpdb->insert() branches on is_null() BEFORE applying the format and emits a
        // bare SQL NULL literal (class-wpdb.php L2599-2602; update() does the same at
        // L2696-2699). The '%s' is IGNORED for a null. Disproof from THIS FILE: line ~185
        // below deliberately passes null to $wpdb->update() with no format array (which
        // defaults to '%s') to clear a due date — the exact construct this comment called
        // fatal. It has always worked.
        //
        // What actually broke in S33: the incoming value was '' (an empty date input
        // posts ""), NOT null. '%s' correctly writes '', and a DATE column rejects ''
        // under strict mode. The ! empty() guard below catches BOTH '' and null, so it
        // never discriminated between them — which is exactly why the wrong diagnosis
        // survived: the fix worked, so nobody re-checked the reason.
        //
        // The guard is still CORRECT and stays. Only its explanation was wrong.
        // See KNOWN_ISSUES.md → KI-80 decision #2, and Pattern #515.
        $due = ! empty( $data['due_date'] ) ? sanitize_text_field( $data['due_date'] ) : null;
        if ( $due ) {
            $row['due_date'] = $due;
        }

        $result = $wpdb->insert( $this->t('todos'), $row );

        if ( $result === false ) {
            titancart_log( '[TitanCart Todo] Insert failed: ' . $wpdb->last_error . ' | Table: ' . $this->t('todos') );
        }

        return (int) $wpdb->insert_id;
    }

    /**
     * Update a todo.
     */
    public function update( int $id, array $data ): bool {
        global $wpdb;
        $update = [];

        if ( isset( $data['title'] ) )       $update['title']       = sanitize_text_field( $data['title'] );
        if ( isset( $data['description'] ) )  $update['description'] = sanitize_textarea_field( $data['description'] );
        if ( isset( $data['priority'] ) )     $update['priority']    = in_array( $data['priority'], ['low','medium','high','urgent'], true ) ? $data['priority'] : 'medium';
        if ( isset( $data['due_date'] ) )     $update['due_date']    = $data['due_date'] ?: null;
        if ( isset( $data['assigned_to'] ) )  $update['assigned_to'] = (int) $data['assigned_to'];
        if ( isset( $data['position'] ) )     $update['position']    = (int) $data['position'];

        if ( isset( $data['status'] ) ) {
            $new_status = in_array( $data['status'], ['todo','in_progress','done'], true ) ? $data['status'] : 'todo';
            $update['status'] = $new_status;

            // Track completion
            if ( $new_status === 'done' ) {
                $update['completed_at'] = current_time( 'mysql' );
            } else {
                $update['completed_at'] = null;
            }
        }

        if ( $update ) {
            $wpdb->update( $this->t('todos'), $update, [ 'id' => $id ] );
        }
        return true;
    }

    /**
     * Delete a todo.
     */
    public function delete( int $id ): bool {
        global $wpdb;
        $wpdb->delete( $this->t('todos'), [ 'id' => $id ] );
        return true;
    }

    /**
     * Batch reorder todos.
     */
    public function reorder( array $id_order ): bool {
        global $wpdb;
        foreach ( $id_order as $pos => $id ) {
            $wpdb->update( $this->t('todos'), [ 'position' => (int) $pos ], [ 'id' => (int) $id ] );
        }
        return true;
    }

    /**
     * Get stats.
     */
    public function get_stats(): array {
        global $wpdb;
        $t = $this->t('todos');
        return [
            'total'       => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t}" ),
            'todo'        => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t} WHERE status='todo'" ),
            'in_progress' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t} WHERE status='in_progress'" ),
            'done'        => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t} WHERE status='done'" ),
            'overdue'     => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t} WHERE due_date IS NOT NULL AND due_date < CURDATE() AND status != 'done'" ),
        ];
    }

    /**
     * Get admin users for the assign dropdown.
     */
    public function get_assignable_users(): array {
        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT ID, display_name, user_email FROM {$wpdb->users} u
             INNER JOIN {$wpdb->usermeta} um ON um.user_id = u.ID AND um.meta_key = '{$wpdb->prefix}capabilities'
             WHERE um.meta_value LIKE '%administrator%' OR um.meta_value LIKE '%editor%'
             ORDER BY display_name ASC",
            ARRAY_A
        );
        return $rows ?: [];
    }
}
