<?php

namespace WPQT\Stage;

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

if (!class_exists('WPQT\Stage\StageRepository')) {
    class StageRepository
    {
        /**
         * Retrieves all pipeline stages from the database.
         *
         * @param int $pipelineId The ID of the pipeline.
         * @return array|null Array of stage objects, empty array if no stages found, or null on database error.
         */
        public function getStagesByPipelineId($pipelineId)
        {
            global $wpdb;

            return $wpdb->get_results($wpdb->prepare(
                'SELECT a.*, b.stage_order FROM ' . TABLE_WP_QUICKTASKER_PIPELINE_STAGES . ' AS a
                INNER JOIN ' . TABLE_WP_QUICKTASKER_STAGES_LOCATION . ' AS b
                ON a.id = b.stage_id
                WHERE a.pipeline_id = %d
                ORDER BY b.stage_order',
                $pipelineId
            ));
        }

        /**
         * Retrieves a stage by its ID.
         *
         * @param int $stageId The ID of the stage to retrieve.
         * @return object|null The stage object if found, null otherwise.
         */
        public function getStageById($stageId)
        {
            global $wpdb;

            return $wpdb->get_row($wpdb->prepare(
                'SELECT a.*, b.stage_order FROM ' . TABLE_WP_QUICKTASKER_PIPELINE_STAGES . ' AS a
                INNER JOIN ' . TABLE_WP_QUICKTASKER_STAGES_LOCATION . ' AS b
                ON a.id = b.stage_id
                WHERE a.id = %d',
                $stageId
            ));
        }

        /**
         * Retrieves the next stage order for a given pipeline ID.
         *
         * @param int $pipelineId The ID of the pipeline.
         * @return int The next stage order.
         */
        public function getNextStageOrder($pipelineId)
        {
            $lastStageOrder = $this->getLastStageOrder($pipelineId);

            return 0 === $lastStageOrder ? 0 : $lastStageOrder + 1;
        }

        /**
         * Retrieves the highest stage order for a given pipeline.
         *
         * @param int $pipelineId The ID of the pipeline.
         * @return int The highest stage order in the pipeline, or 0 if no stages are found.
         */
        public function getLastStageOrder($pipelineId)
        {
            global $wpdb;

            $result = $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT MAX(stage_order) FROM ' . TABLE_WP_QUICKTASKER_STAGES_LOCATION . ' WHERE pipeline_id = %d',
                    $pipelineId
                )
            );

            return null === $result ? 0 : $result;
        }

        /**
         * Retrieves the first stage of a pipeline.
         *
         * @param int $pipelineId The ID of the pipeline.

         * @return object|null The first stage object if found, null otherwise.
         */
        public function getFirstStage($pipelineId)
        {
            global $wpdb;

            return $wpdb->get_row(
                $wpdb->prepare(
                    'SELECT a.*, b.stage_order FROM ' . TABLE_WP_QUICKTASKER_PIPELINE_STAGES . ' AS a
                    INNER JOIN ' . TABLE_WP_QUICKTASKER_STAGES_LOCATION . ' AS b
                    ON a.id = b.stage_id
                    WHERE a.pipeline_id = %d
                    ORDER BY b.stage_order ASC',
                    $pipelineId
                )
            );
        }
    }
}
