<?php
/**
 * Category Repository.
 *
 * All category read/write operations flow through here.
 * Uses a nested-set tree (lft/rgt/depth) for efficient subtree queries,
 * using a standard nested-set category tree.
 *
 * @package TitanCart\Modules\Products
 */

namespace TitanCart\Modules\Products;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

class CategoryRepository {

    private Database $db;
    private string   $table;
    private string   $trans_table;

    public function __construct() {
        $this->db          = titancart_db();
        $this->table       = $this->db->table( 'categories' );
        $this->trans_table = $this->db->table( 'category_translations' );
    }

    // ── READ ──────────────────────────────────────────────────────────────────

    /**
     * Retrieve a single category with its translation.
     *
     * @param  int    $category_id
     * @param  string $lang  Language code (default 'en').
     * @return array<string,mixed>|null
     */
    public function get( int $category_id, string $lang = 'en' ): ?array {
        $pc_table = $this->db->table( 'product_categories' );
        $p_table  = $this->db->table( 'products' );
        $row = $this->db->getRow(
            "SELECT c.*, ct.name, ct.description, ct.meta_title, ct.meta_desc,
                    (SELECT COUNT(*) FROM {$pc_table} pc
                     INNER JOIN {$p_table} p2 ON p2.id = pc.product_id AND p2.status = 'active' AND p2.deleted_at IS NULL
                     WHERE pc.category_id = c.id) AS product_count
             FROM {$this->table} c
             LEFT JOIN {$this->trans_table} ct
                ON ct.category_id = c.id AND ct.language_code = ?s
             WHERE c.id = ?i AND c.deleted_at IS NULL",
            $lang,
            $category_id
        );

        if ( ! $row ) {
            return null;
        }

        $row['breadcrumb'] = $this->get_breadcrumb( (int) $row['lft'], (int) $row['rgt'], $lang );
        return $row;
    }

    /**
     * Retrieve a flat list of categories with optional filters.
     *
     * @param  array<string,mixed> $params {
     *   status        string   'active'|'disabled'|'hidden'|'' (default 'active')
     *   parent_id     int|null  Filter by direct parent (null = all)
     *   ids           int[]     Filter to specific IDs
     *   search        string    Name search (LIKE)
     *   items_per_page int      0 = no limit
     *   page          int       1-based page number
     *   sort_by       string    'position'|'name'|'id'
     *   sort_order    string    'asc'|'desc'
     * }
     * @param  string $lang
     * @return array{ categories: array[], params: array }
     */
    public function get_list( array $params = [], string $lang = 'en' ): array {
        $params = array_merge( [
            'status'         => 'active',
            'parent_id'      => null,
            'ids'            => [],
            'search'         => '',
            'items_per_page' => 0,
            'page'           => 1,
            'sort_by'        => 'position',
            'sort_order'     => 'asc',
        ], $params );

        // Build conditions.
        $conditions = [ "c.deleted_at IS NULL" ];
        $args       = [];

        if ( ! empty( $params['status'] ) ) {
            $conditions[] = "c.status = ?s";
            $args[]       = $params['status'];
        }

        if ( $params['parent_id'] !== null ) {
            $conditions[] = "c.parent_id = ?i";
            $args[]       = (int) $params['parent_id'];
        }

        if ( ! empty( $params['ids'] ) ) {
            $conditions[] = "c.id IN (?a)";
            $args[]       = array_map( 'intval', $params['ids'] );
        }

        if ( ! empty( $params['search'] ) ) {
            $conditions[] = "ct.name LIKE ?s";
            $args[]       = '%' . $params['search'] . '%';
        }

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

        // Sorting.
        $sort_map  = [ 'position' => 'c.position', 'name' => 'ct.name', 'id' => 'c.id' ];
        $sort_col  = $sort_map[ $params['sort_by'] ] ?? 'c.position';
        $sort_dir  = strtoupper( $params['sort_order'] ) === 'DESC' ? 'DESC' : 'ASC';

        // Count total.
        $count_args   = array_merge( [ $lang ], $args );
        $total        = (int) $this->db->getField(
            "SELECT COUNT(*) FROM {$this->table} c
             LEFT JOIN {$this->trans_table} ct ON ct.category_id = c.id AND ct.language_code = ?s
             WHERE {$where}",
            ...$count_args
        );

        // Pagination.
        $limit  = '';
        $offset = 0;
        if ( $params['items_per_page'] > 0 ) {
            $offset = ( max( 1, (int) $params['page'] ) - 1 ) * (int) $params['items_per_page'];
            $limit  = "LIMIT {$params['items_per_page']} OFFSET {$offset}";
        }

        $query_args = array_merge( [ $lang ], $args );

        $pc_table = $this->db->table( 'product_categories' );
        $p_table  = $this->db->table( 'products' );

        $rows = $this->db->getArray(
            "SELECT c.id, c.parent_id, c.lft, c.rgt, c.depth, c.slug, c.position, c.status,
                    c.age_restricted, c.age_limit,
                    ct.name, ct.description, ct.meta_title, ct.meta_desc,
                    (SELECT COUNT(*) FROM {$pc_table} pc
                     INNER JOIN {$p_table} p2 ON p2.id = pc.product_id AND p2.status = 'active' AND p2.deleted_at IS NULL
                     WHERE pc.category_id = c.id) AS product_count
             FROM {$this->table} c
             LEFT JOIN {$this->trans_table} ct ON ct.category_id = c.id AND ct.language_code = ?s
             WHERE {$where}
             ORDER BY {$sort_col} {$sort_dir}
             {$limit}",
            ...$query_args
        );

        $params['total_items']  = $total;
        $params['total_pages']  = $params['items_per_page'] > 0
            ? (int) ceil( $total / $params['items_per_page'] )
            : 1;

        return [ 'categories' => $rows, 'params' => $params ];
    }

    /**
     * Return the full nested tree, ready for a React tree component.
     * Each category gets a `children` key.
     *
     * @param  string $lang
     * @param  string $status  '' = all, otherwise filter by status.
     * @return array<int, array<string,mixed>>
     */
    public function get_tree( string $lang = 'en', string $status = 'active' ): array {
        $args       = [ $lang ];
        $status_sql = '';

        if ( $status !== '' ) {
            $status_sql = "AND c.status = ?s";
            $args[]     = $status;
        }

        $rows = $this->db->getArray(
            "SELECT c.id, c.parent_id, c.depth, c.position, c.slug, c.status,
                    ct.name
             FROM {$this->table} c
             LEFT JOIN {$this->trans_table} ct ON ct.category_id = c.id AND ct.language_code = ?s
             WHERE c.deleted_at IS NULL {$status_sql}
             ORDER BY c.lft ASC",
            ...$args
        );

        return $this->build_tree( $rows );
    }

    // ── WRITE ─────────────────────────────────────────────────────────────────

    /**
     * Create or update a category.
     *
     * @param  array<string,mixed> $data         Category fields + 'translation' sub-array.
     * @param  int                 $category_id  0 = create new.
     * @param  string              $lang
     * @return int Category ID (new or existing).
     */
    public function save( array $data, int $category_id = 0, string $lang = 'en' ): int {
        // Separate translation data.
        $translation = $data['translation'] ?? [];
        unset( $data['translation'] );

        // Generate or de-duplicate the slug. A slug supplied by the caller (the admin
        // pre-fills it from the name) is still run through unique_slug() so two
        // categories can never share one /shop/{slug}/ URL, which would make all but
        // one of them unreachable on the storefront. Mirrors ProductRepository::save()
        // (L737) — same module, same contract; before this the else branch was missing
        // and a supplied slug was written raw, un-sanitized and un-de-duplicated. [KI-54]
        // [KI-55] On UPDATE, distinguish an ABSENT slug from an explicitly-empty
        // one — mirrors ProductRepository::save() (L747). CategoriesController
        // (L543) only sets 'slug' in $data when the caller sent the param, so
        // array_key_exists() tells "omitted" from "sent empty". A partial PUT that
        // changes the name but omits slug must NOT silently regenerate — and thus
        // move — the category's public URL; an omitted slug on update is left
        // untouched (the column falls out of $row below, so the UPDATE never writes
        // it and no 301 is minted). An explicitly-empty slug ('') still means
        // "regenerate from the name". On CREATE an omitted slug regenerates as before.
        if ( $category_id > 0 && ! array_key_exists( 'slug', $data ) ) {
            unset( $data['slug'] );
        } elseif ( empty( $data['slug'] ) ) {
            if ( ! empty( $translation['name'] ) ) {
                $data['slug'] = $this->unique_slug(
                    sanitize_title( $translation['name'] ),
                    $category_id
                );
            } elseif ( $category_id > 0 ) {
                // [KI-76] Explicitly-empty slug with no name in this payload — fall
                // back to the category's STORED name rather than writing an empty
                // slug (a broken/empty public URL); unique_slug() with $category_id
                // excluded keeps a no-op re-save on the same slug. If there is no
                // stored name, keep the existing slug untouched (unset the key).
                $stored_name = (string) $this->db->getField(
                    "SELECT name FROM {$this->trans_table}
                     WHERE category_id = ?i AND language_code = ?s",
                    $category_id,
                    $lang
                );
                $fallback = $stored_name !== '' ? sanitize_title( $stored_name ) : '';
                if ( $fallback !== '' ) {
                    $data['slug'] = $this->unique_slug( $fallback, $category_id );
                } else {
                    unset( $data['slug'] );
                }
            }
            // CREATE with empty slug and no name — nothing to regenerate from.
        } else {
            $data['slug'] = $this->unique_slug(
                sanitize_title( $data['slug'] ),
                $category_id
            );
        }

        // Fields allowed in main table.
        $allowed = [ 'parent_id', 'slug', 'image_id', 'position', 'status', 'age_restricted', 'age_limit' ];
        $row     = array_intersect_key( $data, array_flip( $allowed ) );

        if ( $category_id === 0 ) {
            // Insert.
            $row['lft']   = 0;
            $row['rgt']   = 0;
            $row['depth'] = 0;
            $category_id  = $this->db->insert( 'categories', $row );

            if ( $category_id ) {
                $this->rebuild_nested_set();
            }
        } else {
            // Capture old slug for auto SEO redirect.
            $old_slug = '';
            if ( isset( $row['slug'] ) ) {
                $old_slug = (string) $this->db->getField(
                    "SELECT slug FROM {$this->table} WHERE id = ?i",
                    $category_id
                );
            }

            // Update. Guard against an empty $row: every allow-listed column can
            // legitimately be filtered out — e.g. a partial PUT that changes only
            // the translation name and omits slug now leaves $row empty after the
            // [KI-55] absent-slug guard above. $wpdb->update with an empty data
            // array emits `UPDATE wp_tc_categories SET WHERE id = N`, a MySQL syntax
            // error (the S183 defense ProductRepository::save() L840 already carries).
            if ( ! empty( $row ) ) {
                $this->db->update( 'categories', $row, [ 'id' => $category_id ] );
            }

            // Auto-create 301 redirect if slug changed.
            if ( $old_slug !== '' && isset( $row['slug'] ) && $row['slug'] !== $old_slug ) {
                $pfx = \TitanCart\Services\SeoService::get_url_patterns()['category_prefix'];
                $base = $pfx !== '' ? '/' . $pfx : '';
                $this->create_seo_redirect(
                    $base . '/' . $old_slug . '/',
                    $base . '/' . $row['slug'] . '/'
                );
            }

            if ( isset( $data['parent_id'] ) ) {
                $this->rebuild_nested_set();
            }
        }

        // Save translation.
        if ( $category_id && ! empty( $translation ) ) {
            $this->save_translation( $category_id, $translation, $lang );
        }

        do_action( 'titancart_category_saved', $category_id, $data );

        return $category_id;
    }

    /**
     * Soft-delete a category and optionally re-parent its children.
     *
     * @param  int  $category_id
     * @param  bool $recurse  If true, also delete child categories.
     * @return bool
     */
    public function delete( int $category_id, bool $recurse = true ): bool {
        if ( $recurse ) {
            $cat = $this->db->getRow(
                "SELECT lft, rgt FROM {$this->table} WHERE id = ?i",
                $category_id
            );

            if ( $cat ) {
                $this->db->query(
                    "UPDATE {$this->table}
                     SET deleted_at = ?s
                     WHERE lft >= ?i AND rgt <= ?i",
                    titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
                    (int) $cat['lft'],
                    (int) $cat['rgt']
                );
            }
        } else {
            $this->db->query(
                "UPDATE {$this->table} SET deleted_at = ?s WHERE id = ?i",
                titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
                $category_id
            );
            // Re-parent direct children to this category's parent.
            $parent = $this->db->getField(
                "SELECT parent_id FROM {$this->table} WHERE id = ?i",
                $category_id
            );
            $this->db->query(
                "UPDATE {$this->table} SET parent_id = ?i WHERE parent_id = ?i",
                (int) $parent,
                $category_id
            );
        }

        $this->rebuild_nested_set();
        do_action( 'titancart_category_deleted', $category_id );
        return true;
    }

    // ── NESTED SET ────────────────────────────────────────────────────────────

    /**
     * Persist a sibling reorder for one parent.
     *
     * Sets `position` = array index for each id in $ordered_ids, but ONLY for
     * ids that are genuine, non-deleted children of $parent_id. Stray ids are
     * silently ignored (not an error — frontend may submit stale data).
     *
     * Always rebuilds the nested set at the end so `lft`/`rgt`/`depth` reflect
     * the new sibling order. Caller is responsible for transaction wrapping.
     *
     * @param  int   $parent_id    Parent category whose children are being ordered (0 = root).
     * @param  int[] $ordered_ids  Child ids in their desired left-to-right / top-to-bottom order.
     * @return int                 Number of rows actually updated.
     */
    public function reorder( int $parent_id, array $ordered_ids ): int {
        if ( empty( $ordered_ids ) ) {
            return 0;
        }

        // Snapshot the actual children of this parent so we can ignore stray ids
        // (e.g. a child that was just re-parented or deleted in another tab).
        $valid_children = $this->db->getColumn(
            "SELECT id FROM {$this->table}
             WHERE parent_id = ?i AND deleted_at IS NULL",
            $parent_id
        );
        $valid_set = array_flip( array_map( 'intval', $valid_children ) );

        $updated = 0;
        $position = 0;
        foreach ( $ordered_ids as $id ) {
            $id = (int) $id;
            if ( ! isset( $valid_set[ $id ] ) ) {
                continue; // not a child of $parent_id, or deleted — skip
            }
            $this->db->query(
                "UPDATE {$this->table}
                 SET position = ?i
                 WHERE id = ?i AND parent_id = ?i AND deleted_at IS NULL",
                $position,
                $id,
                $parent_id
            );
            $position++;
            $updated++;
        }

        if ( $updated > 0 ) {
            $this->rebuild_nested_set();
            do_action( 'titancart_categories_reordered', $parent_id, $ordered_ids );
        }

        return $updated;
    }

    /**
     * Rebuild the entire lft/rgt/depth nested set from parent_id relationships.
     * Called after any structural change (insert, delete, re-parent).
     */
    public function rebuild_nested_set(): void {
        // Fetch all non-deleted categories ordered by parent and position.
        $all = $this->db->getArray(
            "SELECT id, parent_id, position FROM {$this->table}
             WHERE deleted_at IS NULL
             ORDER BY parent_id ASC, position ASC, id ASC"
        );

        if ( empty( $all ) ) {
            return;
        }

        // Index by id for fast lookup.
        $by_id     = [];
        $children  = [];
        foreach ( $all as $row ) {
            $by_id[ $row['id'] ] = $row;
            $children[ (int) $row['parent_id'] ][] = $row['id'];
        }

        // Traverse and assign lft/rgt/depth.
        $counter  = 0;
        $updates  = [];
        $traverse = function ( int $parent_id, int $depth ) use (
            &$traverse, &$counter, &$updates, $children
        ): void {
            foreach ( $children[ $parent_id ] ?? [] as $id ) {
                $lft            = ++$counter;
                $traverse( (int) $id, $depth + 1 );
                $rgt            = ++$counter;
                $updates[ $id ] = [ 'lft' => $lft, 'rgt' => $rgt, 'depth' => $depth ];
            }
        };

        $traverse( 0, 0 );

        // Batch-update each row.
        foreach ( $updates as $id => $vals ) {
            $this->db->query(
                "UPDATE {$this->table} SET lft = ?i, rgt = ?i, depth = ?i WHERE id = ?i",
                $vals['lft'],
                $vals['rgt'],
                $vals['depth'],
                $id
            );
        }
    }

    // ── HELPERS ───────────────────────────────────────────────────────────────

    /**
     * Upsert a translation row.
     *
     * @param int    $category_id
     * @param array<string,mixed> $data
     * @param string $lang
     */
    private function save_translation( int $category_id, array $data, string $lang ): void {
        $existing = $this->db->getField(
            "SELECT id FROM {$this->trans_table}
             WHERE category_id = ?i AND language_code = ?s",
            $category_id,
            $lang
        );

        $allowed = [ 'name', 'description', 'meta_title', 'meta_desc' ];
        $row     = array_intersect_key( $data, array_flip( $allowed ) );
        $row['category_id']   = $category_id;
        $row['language_code'] = $lang;

        if ( $existing ) {
            unset( $row['category_id'], $row['language_code'] );
            $this->db->update( 'category_translations', $row, [
                'category_id'   => $category_id,
                'language_code' => $lang,
            ] );
        } else {
            $this->db->insert( 'category_translations', $row );
        }
    }

    /**
     * Build breadcrumb trail using nested set (lft/rgt).
     *
     * @param int    $lft
     * @param int    $rgt
     * @param string $lang
     * @return array<int, array{id:int, name:string}>
     */
    private function get_breadcrumb( int $lft, int $rgt, string $lang ): array {
        return $this->db->getArray(
            "SELECT c.id, ct.name
             FROM {$this->table} c
             LEFT JOIN {$this->trans_table} ct ON ct.category_id = c.id AND ct.language_code = ?s
             WHERE c.lft < ?i AND c.rgt > ?i AND c.deleted_at IS NULL
             ORDER BY c.lft ASC",
            $lang,
            $lft,
            $rgt
        );
    }

    /**
     * Generate a unique slug, appending a numeric suffix if necessary.
     *
     * @param  string $base        Starting slug.
     * @param  int    $exclude_id  Current record's ID (when updating).
     * @return string
     */
    private function unique_slug( string $base, int $exclude_id = 0 ): string {
        $slug    = $base;
        $suffix  = 1;
        $exclude = $exclude_id > 0 ? " AND id != {$exclude_id}" : '';

        while ( true ) {
            // S362 / KI-52 — exclude soft-deleted rows. Categories are soft-deleted
            // (`deleted_at`, L289) and every read filters it (L50, L92, L189), so a
            // deleted category's slug is unreachable and must be reusable.
            $count = (int) $this->db->getField(
                "SELECT COUNT(*) FROM {$this->table} WHERE slug = ?s AND deleted_at IS NULL{$exclude}",
                $slug
            );

            if ( $count === 0 ) {
                break;
            }

            $slug = "{$base}-{$suffix}";
            $suffix++;
        }

        return $slug;
    }

    /**
     * Convert a flat rows array into a nested children tree.
     *
     * @param  array<int, array<string,mixed>> $rows
     * @return array<int, array<string,mixed>>
     */
    private function build_tree( array $rows ): array {
        $indexed = [];
        foreach ( $rows as $row ) {
            $row['children']         = [];
            $indexed[ $row['id'] ]   = $row;
        }

        $tree = [];
        foreach ( $indexed as $id => &$node ) {
            $parent = (int) $node['parent_id'];
            if ( $parent === 0 || ! isset( $indexed[ $parent ] ) ) {
                $tree[] = &$node;
            } else {
                $indexed[ $parent ]['children'][] = &$node;
            }
        }

        return $tree;
    }

    /**
     * Auto-create a 301 SEO redirect when a category slug changes.
     */
    private function create_seo_redirect( string $from_url, string $to_url ): void {
        $table = $this->db->table( 'seo_redirects' );

        try {
            $exists = $this->db->getField(
                "SELECT id FROM {$table} WHERE from_url = ?s AND to_url = ?s LIMIT 1",
                $from_url, $to_url
            );
            if ( $exists ) {
                return;
            }

            // Collapse redirect chains: A→B becomes A→C when B→C is created.
            $this->db->query(
                "UPDATE {$table} SET to_url = ?s WHERE to_url = ?s AND status = 'active'",
                $to_url, $from_url
            );

            $this->db->insert( 'seo_redirects', [
                'from_url' => $from_url,
                'to_url'   => $to_url,
                'type'     => 301,
                'status'   => 'active',
            ] );
        } catch ( \Throwable $e ) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                titancart_log( '[TitanCart] SEO redirect create failed: ' . $e->getMessage() );
            }
        }
    }
}
