<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * Anbaarak | Best Inventory management plugin for WooCommerce.
 * All rights reserved for WP LEGEND team (wplegend.ir)
 */


use Anbarak\Tables;

function anbarak_get_expenses_tag_by_name($name)
{
    global $wpdb;
    $tags_table = $wpdb->prefix . Tables::EXPENSES_TAGS;
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is fixed from $wpdb->prefix and Tables.
    $tag = $wpdb->get_row($wpdb->prepare("
        SELECT * 
        FROM {$tags_table} 
        WHERE name=%s
        ORDER BY ID DESC
    ", $name));
    // phpcs:enable

    return $tag ?: false;
}

function anbarak_get_expenses_tag($id)
{
    global $wpdb;
    $tags_table = $wpdb->prefix . Tables::EXPENSES_TAGS;
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is fixed from $wpdb->prefix and Tables.
    $tag = $wpdb->get_row($wpdb->prepare("
        SELECT * 
        FROM {$tags_table} 
        WHERE id=%d
        ORDER BY ID DESC
    ", (int) $id));
    // phpcs:enable

    return $tag ?: false;
}

function anbarak_define_expenses_tag($name, $color = '#202020')
{
    global $wpdb;
    $wpdb->insert(
        $wpdb->prefix . Tables::EXPENSES_TAGS,
        [
            'name'  => sanitize_text_field(trim($name)),
            'color' => sanitize_text_field(trim($color))
        ]
    );

    return $wpdb->insert_id;
}

function anbarak_get_expenses_tags()
{
    global $wpdb;
    $tags_table = $wpdb->prefix . Tables::EXPENSES_TAGS;
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is fixed from $wpdb->prefix and Tables; query contains no user input.
    return $wpdb->get_results("
        SELECT * 
        FROM {$tags_table} 
        WHERE deleted_at IS NULL
        ORDER BY ID DESC
    ");
    // phpcs:enable
}

function anbarak_remove_expense_tag($id)
{
    $tagToBeDeleted = anbarak_get_expenses_tag($id);

    // system tag cannot be removed
    if (!$tagToBeDeleted || ANBARAK_PURCHASES_EXPENSE_TAG_NAME == $tagToBeDeleted->name) {
        return false;
    }

    global $wpdb;
    $wpdb->update(
        $wpdb->prefix . Tables::EXPENSES_TAGS,
        ['deleted_at' => time()],
        ['ID' => $id]
    );
}

function anbarak_update_expenses_tag($id, $name, $color = '#202020')
{
    $tag = anbarak_get_expenses_tag($id);
    if (!$tag) return false;

    global $wpdb;
    $wpdb->update(
        $wpdb->prefix . Tables::EXPENSES_TAGS,
        [
            'name'  => sanitize_text_field(trim($name)),
            'color' => sanitize_text_field(trim($color)),
        ],
        ['ID' => $id]
    );

    return true;
}

function anbarak_get_expenses_count_by_tag($tag_id)
{
    global $wpdb;
    $expenses_table = $wpdb->prefix . Tables::EXPENSES;
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is fixed from $wpdb->prefix and Tables.
    return (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM {$expenses_table} 
         WHERE tag_id = %d AND deleted_at IS NULL",
        $tag_id
    ));
    // phpcs:enable
}

function anbarak_render_expenses_tags()
{
    $tags = anbarak_get_expenses_tags();
?>
    <div class="table-container">
        <table class="table">
            <thead>
                <tr>
                    <th>#</th>
                    <th><?php esc_html_e('Tag Name', 'anbarak') ?></th>
                    <th><?php esc_html_e('Color', 'anbarak') ?></th>
                    <th><?php esc_html_e('Expenses', 'anbarak') ?></th>
                    <th><?php esc_html_e('Actions', 'anbarak') ?></th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($tags)) : ?>
                    <tr>
                        <td colspan="5" class="text-center text-gray-400 py-8">
                            <?php esc_html_e('No tags defined yet.', 'anbarak') ?>
                        </td>
                    </tr>
                <?php else : $i = 1; ?>
                    <?php foreach ($tags as $tag) : ?>
                        <?php $is_system = ANBARAK_PURCHASES_EXPENSE_TAG_NAME == $tag->name; ?>
                        <tr class="tag-row" data-id="<?php echo (int) $tag->id ?>" data-name="<?php echo esc_attr($tag->name) ?>" data-color="<?php echo esc_attr($tag->color) ?>" data-system="<?php echo $is_system ? '1' : '0' ?>">
                            <td><?php echo esc_html($i++) ?></td>
                            <td>
                                <?php echo esc_html($tag->name) ?>
                                <?php if ($is_system) : ?>
                                    <span class="badge"><?php esc_html_e('system', 'anbarak') ?></span>
                                <?php endif ?>
                            </td>
                            <td>
                                <span class="inline-block w-8 h-8 rounded-2xl border border-gray-200 align-middle" style="background-color: <?php echo esc_attr($tag->color) ?>"></span>
                            </td>
                            <td><?php echo esc_html(anbarak_get_expenses_count_by_tag($tag->id)) ?></td>
                            <td>
                                <div class="flex gap-2">
                                    <button class="button btn-edit-tag" data-id="<?php echo (int) $tag->id ?>" <?php echo $is_system ? 'disabled' : '' ?>>
                                        <i class="icon icon-edit"></i>
                                    </button>
                                    <button class="button button--danger-outline btn-remove-tag" data-id="<?php echo (int) $tag->id ?>" <?php echo $is_system ? 'disabled' : '' ?>>
                                        <i class="icon icon-trash"></i>
                                    </button>
                                </div>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
<?php
}

function anbarak_render_expenses_tags_dropdown($selected_id = 0, $required = false)
{
    $tags = anbarak_get_expenses_tags();
    $selected_id = (int) $selected_id;
    ?>
    <select name="tag" class="w-full" <?php echo $required ? 'required' : ''; ?>>
        <option value=""><?php esc_html_e('Select Tag', 'anbarak') ?></option>
        <?php foreach ($tags as $tag) : ?>
            <option value="<?php echo (int) $tag->id ?>" <?php selected($selected_id, (int) $tag->id) ?>>
                <?php echo esc_html($tag->name) ?>
            </option>
        <?php endforeach; ?>
    </select>
<?php
}

function anbarak_rebuild_product_expenses_tags()
{
    global $wpdb;

    $tag = anbarak_get_expenses_tag_by_name(ANBARAK_PURCHASES_EXPENSE_TAG_NAME);
    if (!$tag) {
        $tag_id = anbarak_define_expenses_tag(ANBARAK_PURCHASES_EXPENSE_TAG_NAME, '#5eba7d');
        $tag = $tag_id ? anbarak_get_expenses_tag((int) $tag_id) : false;
    }

    if (!$tag || empty($tag->id)) {
        return false;
    }

    $tag_id = (int) $tag->id;
    $expenses_table = $wpdb->prefix . Tables::EXPENSES;
    $tags_table = $wpdb->prefix . Tables::EXPENSES_TAGS;

    // assign product purchases without a tag
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table name is fixed from $wpdb->prefix and Tables.
    $wpdb->query($wpdb->prepare("
        UPDATE {$expenses_table}
        SET tag_id=%d
        WHERE product_id IS NOT NULL
        AND tag_id IS NULL
    ", $tag_id));
    // phpcs:enable

    // migrate expenses from any legacy duplicate purchase tag into the canonical one
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table names are fixed from $wpdb->prefix and Tables.
    $duplicates = $wpdb->get_col($wpdb->prepare("
        SELECT t.id
        FROM {$tags_table} t
        INNER JOIN {$expenses_table} e ON e.tag_id = t.id
        WHERE t.id != %d
        AND e.product_id IS NOT NULL
    ", $tag_id));
    // phpcs:enable

    if ($duplicates) {
        $duplicates = array_map('intval', $duplicates);
        $ids_placeholders = implode(',', array_fill(0, count($duplicates), '%d'));
        $update_args = array_merge([$tag_id], $duplicates);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Custom table name is fixed from $wpdb->prefix and Tables; IN placeholders are generated from sanitized IDs.
        $wpdb->query($wpdb->prepare("
            UPDATE {$expenses_table}
            SET tag_id=%d
            WHERE tag_id IN ({$ids_placeholders})
            AND product_id IS NOT NULL
        ", ...$update_args));
        // phpcs:enable

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Custom table name is fixed from $wpdb->prefix and Tables; IN placeholders are generated from sanitized IDs.
        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$tags_table} WHERE id IN ({$ids_placeholders})",
            ...$duplicates
        ));
        // phpcs:enable
    }

    return true;
}


function anbarak_get_purchase_tag_id()
{
    $tag = anbarak_get_expenses_tag_by_name(ANBARAK_PURCHASES_EXPENSE_TAG_NAME);

    if (!$tag || !$tag->id) {
        anbarak_rebuild_product_expenses_tags();
        $tag = anbarak_get_expenses_tag_by_name(ANBARAK_PURCHASES_EXPENSE_TAG_NAME);
    }

    return $tag->id ?? 0;
}
