<?php
/**
 * pc-service.php — لایه بهینه کنترل پروژه (PC/CPM)
 */
if ( ! defined( 'ABSPATH' ) ) exit;

class PC_Service {

    /**
     * لیست پروژه‌ها با aggregate یک‌مرحله‌ای (بدون subquery همبسته)
     */
    public static function list_projects( string $where_sql, array $args ): array {
        global $wpdb;
        $p = $wpdb->prefix . 'pc_projects';
        $l = $wpdb->prefix . 'pc_lands';
        $c = $wpdb->prefix . 'ai_sale_customers';
        $t = $wpdb->prefix . 'pc_tasks';
        $m = $wpdb->prefix . 'pc_members';
        $a = $wpdb->prefix . 'pc_assignments';

        $sql = "SELECT p.*,
                       l.name  AS land_name,
                       cu.name AS client_name,
                       COALESCE(ts.task_count, 0)   AS task_count,
                       COALESCE(ms.member_count, 0) AS member_count,
                       COALESCE(ac.actual_cost, 0)  AS actual_cost
                FROM {$p} p
                LEFT JOIN {$l}  l  ON l.id  = p.land_id
                LEFT JOIN {$c}  cu ON cu.id = p.client_id
                LEFT JOIN (
                    SELECT project_id, COUNT(*) AS task_count
                    FROM {$t} GROUP BY project_id
                ) ts ON ts.project_id = p.id
                LEFT JOIN (
                    SELECT project_id, COUNT(*) AS member_count
                    FROM {$m} WHERE is_active = 1 GROUP BY project_id
                ) ms ON ms.project_id = p.id
                LEFT JOIN (
                    SELECT project_id, SUM(total_cost) AS actual_cost
                    FROM {$a} GROUP BY project_id
                ) ac ON ac.project_id = p.id
                {$where_sql}
                ORDER BY p.id DESC";

        return $args
            ? ( $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A ) ?: [] )
            : ( $wpdb->get_results( $sql, ARRAY_A ) ?: [] );
    }

    /**
     * بارگذاری یکپارچه داده‌های پروژه بر اساس scope
     *
     * scopes: tasks | gantt | resources | dashboard | full
     */
    public static function workspace( int $project_id, string $scope = 'full' ): array {
        global $wpdb;
        $pre = $wpdb->prefix;
        $out = [ 'project_id' => $project_id, 'scope' => $scope ];

        $need_project = in_array( $scope, [ 'gantt', 'dashboard', 'full' ], true );
        $need_wbs     = in_array( $scope, [ 'tasks', 'gantt', 'dashboard', 'full' ], true );
        $need_tasks   = in_array( $scope, [ 'tasks', 'gantt', 'resources', 'dashboard', 'full' ], true );
        $need_deps    = in_array( $scope, [ 'tasks', 'dashboard', 'full' ], true );
        $need_members = in_array( $scope, [ 'tasks', 'dashboard', 'full' ], true );
        $need_res     = in_array( $scope, [ 'resources', 'dashboard', 'full' ], true );
        $need_assign  = in_array( $scope, [ 'resources', 'dashboard', 'full' ], true );

        if ( $need_project ) {
            $out['project'] = $wpdb->get_row( $wpdb->prepare(
                "SELECT p.*, l.name AS land_name
                 FROM {$pre}pc_projects p
                 LEFT JOIN {$pre}pc_lands l ON l.id = p.land_id
                 WHERE p.id = %d",
                $project_id
            ), ARRAY_A );
        }

        if ( $need_wbs ) {
            $out['wbs'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$pre}pc_wbs WHERE project_id = %d ORDER BY sort_index ASC, id ASC",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $need_tasks ) {
            $out['tasks'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT t.*, w.name AS wbs_name, m.name AS assignee_name
                 FROM {$pre}pc_tasks t
                 LEFT JOIN {$pre}pc_wbs     w ON w.id = t.wbs_id
                 LEFT JOIN {$pre}pc_members m ON m.id = t.assigned_member_id
                 WHERE t.project_id = %d
                 ORDER BY t.sort_index ASC, t.id ASC",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $need_deps ) {
            $out['dependencies'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT d.*, p.name AS predecessor_name, s.name AS successor_name
                 FROM {$pre}pc_task_dependencies d
                 JOIN {$pre}pc_tasks p ON p.id = d.predecessor_id
                 JOIN {$pre}pc_tasks s ON s.id = d.successor_id
                 WHERE d.project_id = %d",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $need_members ) {
            $out['members'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$pre}pc_members
                 WHERE project_id = %d AND is_active = 1
                 ORDER BY role ASC, name ASC",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $need_res ) {
            $out['resources'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$pre}pc_resources WHERE project_id = %d ORDER BY type ASC, name ASC",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $need_assign ) {
            $out['assignments'] = $wpdb->get_results( $wpdb->prepare(
                "SELECT a.*, t.name AS task_name, r.name AS resource_name, r.type AS resource_type, r.unit
                 FROM {$pre}pc_assignments a
                 JOIN {$pre}pc_tasks     t ON t.id = a.task_id
                 JOIN {$pre}pc_resources r ON r.id = a.resource_id
                 WHERE a.project_id = %d ORDER BY t.sort_index ASC",
                $project_id
            ), ARRAY_A ) ?: [];
        }

        if ( $scope === 'dashboard' || $scope === 'full' ) {
            $tasks = $out['tasks'] ?? [];
            $assignments = $out['assignments'] ?? [];
            $out['stats'] = [
                'task_count'     => count( $tasks ),
                'done_count'     => count( array_filter( $tasks, fn( $t ) => ( $t['status'] ?? '' ) === 'completed' ) ),
                'critical_count' => count( array_filter( $tasks, fn( $t ) => ! empty( $t['is_critical'] ) ) ),
                'member_count'   => count( $out['members'] ?? [] ),
                'total_cost'     => array_sum( array_column( $assignments, 'total_cost' ) ),
                'avg_progress'   => count( $tasks )
                    ? round( array_sum( array_column( $tasks, 'progress' ) ) / count( $tasks ), 1 )
                    : 0,
                'unread_msgs'    => (int) $wpdb->get_var( $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$pre}pc_messages WHERE project_id = %d AND is_read = 0",
                    $project_id
                ) ),
            ];
        }

        return $out;
    }

    /**
     * بروزرسانی دسته‌ای نتایج CPM (یک query به‌ازای هر chunk)
     */
    public static function apply_cpm_results( int $project_id, array $results ): int {
        global $wpdb;
        if ( empty( $results ) ) {
            return 0;
        }

        $table = $wpdb->prefix . 'pc_tasks';
        $updated = 0;
        $chunks  = array_chunk( $results, 40, true );

        foreach ( $chunks as $chunk ) {
            $ids = array_map( 'intval', array_keys( $chunk ) );
            if ( ! $ids ) {
                continue;
            }

            $fields = [ 'early_start', 'early_finish', 'late_start', 'late_finish', 'total_float', 'free_float', 'is_critical' ];
            $set_parts = [];
            foreach ( $fields as $field ) {
                $cases = [];
                foreach ( $chunk as $task_id => $row ) {
                    $tid = (int) $task_id;
                    $val = $row[ $field ] ?? null;
                    if ( in_array( $field, [ 'total_float', 'free_float' ], true ) ) {
                        $cases[] = $wpdb->prepare( 'WHEN %d THEN %f', $tid, (float) $val );
                    } elseif ( $field === 'is_critical' ) {
                        $cases[] = $wpdb->prepare( 'WHEN %d THEN %d', $tid, (int) $val );
                    } else {
                        $cases[] = $wpdb->prepare( 'WHEN %d THEN %s', $tid, (string) $val );
                    }
                }
                if ( $cases ) {
                    $set_parts[] = "`{$field}` = CASE `id` " . implode( ' ', $cases ) . ' END';
                }
            }

            if ( ! $set_parts ) {
                continue;
            }

            $in_list = implode( ',', $ids );
            $sql = "UPDATE {$table} SET " . implode( ', ', $set_parts )
                . " WHERE project_id = {$project_id} AND id IN ({$in_list})";
            $wpdb->query( $sql );
            $updated += (int) $wpdb->rows_affected;
        }

        return $updated;
    }

    /**
     * ایندکس‌های عملکردی PC
     */
    public static function ensure_performance_indexes(): array {
        global $wpdb;
        $pre = $wpdb->prefix;
        $applied = [];

        $indexes = [
            [ 'pc_tasks',             'idx_pc_tasks_proj_sort',     '(project_id, sort_index)' ],
            [ 'pc_tasks',             'idx_pc_tasks_proj_status',   '(project_id, status)' ],
            [ 'pc_wbs',               'idx_pc_wbs_proj_sort',       '(project_id, sort_index)' ],
            [ 'pc_members',           'idx_pc_members_proj_active', '(project_id, is_active)' ],
            [ 'pc_assignments',       'idx_pc_assign_proj',         '(project_id, task_id)' ],
            [ 'pc_task_dependencies', 'idx_pc_dep_proj',            '(project_id, predecessor_id)' ],
            [ 'pc_messages',          'idx_pc_msg_proj_read',       '(project_id, is_read)' ],
            [ 'pc_resources',         'idx_pc_res_proj_type',       '(project_id, type)' ],
        ];

        foreach ( $indexes as [ $table, $idx_name, $cols ] ) {
            $tbl = $pre . $table;
            $exists_tbl = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl ) );
            if ( ! $exists_tbl ) {
                continue;
            }
            $exists = $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
                $tbl, $idx_name
            ) );
            if ( $exists ) {
                continue;
            }
            $wpdb->query( "ALTER TABLE {$tbl} ADD INDEX {$idx_name} {$cols}" );
            $applied[] = "{$table}.{$idx_name}";
        }

        return $applied;
    }
}