<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Repositories;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// Custom plugin tables (prli_*): table names interpolated from $wpdb->prefix (trusted),
// user values bind through $wpdb->prepare(). No caching: these tables are the source
// of truth for click/redirect data and must read-through. "meta_key"/"meta_value" here
// refer to our own prli_link_metas table, not wp_postmeta.

/**
 * CRUD for v3's `prli_reports` + `prli_report_links` join table.
 *
 * V4 reuses the v3 schema in place — both tables already exist with the
 * v3 column shape (the migrator carried them forward verbatim). This
 * class is the only Pro-side writer for those tables; the
 * `\PrettyLinks\Pro\Reports\CustomReports` query helper reads source
 * link ids back through `find()` for analytics.
 *
 * Drop-in compat:
 *  - `name`, `goal_link_id`, `created_at` are the v3 columns. v4 added
 *    `description`, `options`, `updated_at` (additive) — we don't write
 *    them yet; they're reserved for a future "report options" surface.
 *  - `prli_report_links` carries the source-link membership. v3 has no
 *    `role` column; v4 added `role VARCHAR(32) DEFAULT 'link'` so future
 *    extensions can mark e.g. a row as a "goal" alias. Today we only
 *    write the default role.
 *
 * Atomicity: write paths (`create`, `update`, `delete`) issue multi-row
 * sequences. Wrap them in a manual transaction since `dbDelta`-managed
 * tables on InnoDB support it; on MyISAM the BEGIN/COMMIT is a no-op
 * which matches the v3 behavior (v3 had no transaction either).
 */
final class Reports
{
    /**
     * Allow list of columns clients can sort the list by. Anything else
     * falls back to `created_at`. The allow list prevents SQL injection
     * via the `$sortBy` parameter on `all()`.
     *
     * @var array<int, string>
     */
    private const SORT_COLUMNS = ['created_at', 'name', 'id'];

    /**
     * Load a single report. Returns the v3 columns plus a derived
     * `link_ids` array of source link ids in row insertion order.
     * Returns null when the report id doesn't exist.
     *
     * @param  integer $id The report id to load.
     * @return array{
     *     id: int,
     *     name: string,
     *     goal_link_id: int|null,
     *     created_at: string,
     *     link_ids: array<int, int>
     * }|null
     */
    public function find(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }

        global $wpdb;
        $reports = $wpdb->prefix . 'prli_reports';
        $links   = $wpdb->prefix . 'prli_report_links';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, name, goal_link_id, created_at FROM {$reports} WHERE id = %d",
                $id
            ),
            ARRAY_A
        );
        if ($row === null) {
            return null;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT link_id FROM {$links} WHERE report_id = %d ORDER BY id ASC",
                $id
            )
        ) ?: [];

        $linkIds = array_values(array_map('intval', $rows));

        return [
            'id'           => (int) $row['id'],
            'name'         => (string) $row['name'],
            'goal_link_id' => $row['goal_link_id'] === null ? null : (int) $row['goal_link_id'],
            'created_at'   => (string) $row['created_at'],
            'link_ids'     => $linkIds,
        ];
    }

    /**
     * List all reports for the list view. Each row carries a `link_count`
     * derived from a LEFT JOIN so reports with zero source links still
     * show up. `goal_link_name` is filled in by the controller layer (a
     * single batched query against `prli_links`) to avoid N+1 here.
     *
     * @param  string $sortBy    Column to sort by; falls back to `created_at` if not allow-listed.
     * @param  string $direction Sort direction, `asc` or `desc`.
     * @return array<int, array{
     *     id: int,
     *     name: string,
     *     goal_link_id: int|null,
     *     created_at: string,
     *     link_count: int
     * }>
     */
    public function all(string $sortBy = 'created_at', string $direction = 'desc'): array
    {
        global $wpdb;
        $reports = $wpdb->prefix . 'prli_reports';
        $links   = $wpdb->prefix . 'prli_report_links';

        $sortBy    = in_array($sortBy, self::SORT_COLUMNS, true) ? $sortBy : 'created_at';
        $direction = strtolower($direction) === 'asc' ? 'ASC' : 'DESC';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results(
            "SELECT r.id, r.name, r.goal_link_id, r.created_at,
                    COUNT(rl.id) AS link_count
               FROM {$reports} r
          LEFT JOIN {$links} rl ON rl.report_id = r.id
           GROUP BY r.id
           ORDER BY r.{$sortBy} {$direction}, r.id DESC",
            ARRAY_A
        ) ?: [];

        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'id'           => (int) $row['id'],
                'name'         => (string) $row['name'],
                'goal_link_id' => $row['goal_link_id'] === null ? null : (int) $row['goal_link_id'],
                'created_at'   => (string) $row['created_at'],
                'link_count'   => (int) $row['link_count'],
            ];
        }
        return $out;
    }

    /**
     * Insert a new report row + the membership rows for its source links.
     * Returns the new report id, or 0 when the parent insert failed.
     *
     * Expected `$data` shape:
     *  - `name` (string, required, sanitized via `sanitize_text_field`)
     *  - `link_ids` (array<int>, required, deduped + filtered to positive ints)
     *  - `goal_link_id` (int|null, optional)
     *
     * @param  array<string, mixed> $data The report data (see shape above).
     * @return integer
     */
    public function create(array $data): int
    {
        global $wpdb;
        $reports = $wpdb->prefix . 'prli_reports';
        $links   = $wpdb->prefix . 'prli_report_links';

        $name       = sanitize_text_field((string) ($data['name'] ?? ''));
        $linkIds    = $this->normalizeLinkIds($data['link_ids'] ?? []);
        $goalLinkId = isset($data['goal_link_id']) ? (int) $data['goal_link_id'] : 0;
        $now        = current_time('mysql', true);

        if ($name === '' || $linkIds === []) {
            return 0;
        }

        $wpdb->query('START TRANSACTION');

        $inserted = $wpdb->insert(
            $reports,
            [
                'name'         => $name,
                'goal_link_id' => $goalLinkId > 0 ? $goalLinkId : null,
                'created_at'   => $now,
            ],
            ['%s', $goalLinkId > 0 ? '%d' : null, '%s']
        );
        if ($inserted === false) {
            $wpdb->query('ROLLBACK');
            return 0;
        }
        $reportId = (int) $wpdb->insert_id;
        if ($reportId <= 0) {
            $wpdb->query('ROLLBACK');
            return 0;
        }

        foreach ($linkIds as $linkId) {
            $wpdb->insert(
                $links,
                [
                    'report_id'  => $reportId,
                    'link_id'    => $linkId,
                    'created_at' => $now,
                ],
                ['%d', '%d', '%s']
            );
        }

        $wpdb->query('COMMIT');
        return $reportId;
    }

    /**
     * Update report metadata + replace the full source-link membership.
     * Wrapped in a transaction so a partial failure leaves the report
     * untouched. Returns false when the report doesn't exist or the
     * inputs are invalid (empty name / empty link list).
     *
     * @param  integer              $id   The report id to update.
     * @param  array<string, mixed> $data The report data to write.
     * @return boolean
     */
    public function update(int $id, array $data): bool
    {
        if ($id <= 0) {
            return false;
        }

        $existing = $this->find($id);
        if ($existing === null) {
            return false;
        }

        global $wpdb;
        $reports = $wpdb->prefix . 'prli_reports';
        $links   = $wpdb->prefix . 'prli_report_links';

        $name       = sanitize_text_field((string) ($data['name'] ?? ''));
        $linkIds    = $this->normalizeLinkIds($data['link_ids'] ?? []);
        $goalLinkId = isset($data['goal_link_id']) ? (int) $data['goal_link_id'] : 0;
        $now        = current_time('mysql', true);

        if ($name === '' || $linkIds === []) {
            return false;
        }

        $wpdb->query('START TRANSACTION');

        $updated = $wpdb->update(
            $reports,
            [
                'name'         => $name,
                'goal_link_id' => $goalLinkId > 0 ? $goalLinkId : null,
                'updated_at'   => $now,
            ],
            ['id' => $id],
            ['%s', $goalLinkId > 0 ? '%d' : null, '%s'],
            ['%d']
        );
        if ($updated === false) {
            $wpdb->query('ROLLBACK');
            return false;
        }

        $deleted = $wpdb->delete($links, ['report_id' => $id], ['%d']);
        if ($deleted === false) {
            $wpdb->query('ROLLBACK');
            return false;
        }

        foreach ($linkIds as $linkId) {
            $wpdb->insert(
                $links,
                [
                    'report_id'  => $id,
                    'link_id'    => $linkId,
                    'created_at' => $now,
                ],
                ['%d', '%d', '%s']
            );
        }

        $wpdb->query('COMMIT');
        return true;
    }

    /**
     * Delete a report and cascade-delete its source-link rows. Wrapped
     * in a transaction so the orphan rows can never outlive the parent
     * (v3 had no FK; v4 enforces the cascade in app code).
     *
     * @param  integer $id The report id to delete.
     * @return boolean
     */
    public function delete(int $id): bool
    {
        if ($id <= 0) {
            return false;
        }

        global $wpdb;
        $reports = $wpdb->prefix . 'prli_reports';
        $links   = $wpdb->prefix . 'prli_report_links';

        $wpdb->query('START TRANSACTION');

        $childResult = $wpdb->delete($links, ['report_id' => $id], ['%d']);
        if ($childResult === false) {
            $wpdb->query('ROLLBACK');
            return false;
        }

        $parentResult = $wpdb->delete($reports, ['id' => $id], ['%d']);
        if ($parentResult === false) {
            $wpdb->query('ROLLBACK');
            return false;
        }

        $wpdb->query('COMMIT');
        return $parentResult > 0;
    }

    /**
     * Coerce a raw `link_ids` input into a deduped, ordered list of
     * positive ints. Preserves the caller's order so the saved
     * membership is stable across reads.
     *
     * @param  mixed $raw The raw link ids input to normalize.
     * @return array<int, int>
     */
    private function normalizeLinkIds($raw): array
    {
        if (!is_array($raw)) {
            return [];
        }
        $seen = [];
        $out  = [];
        foreach ($raw as $value) {
            $id = (int) $value;
            if ($id <= 0 || isset($seen[$id])) {
                continue;
            }
            $seen[$id] = true;
            $out[]     = $id;
        }
        return $out;
    }
}
