<?php
/**
 * DownloadableRepository — DB access layer for the Downloadables module.
 *
 * Pure storage operations — no business rules, no hook firing, no validation
 * beyond column-shape correctness. The DownloadableManager service holds all
 * the "what should happen when" logic and calls this repository to actually
 * touch rows.
 *
 * Tables:
 *   tc_downloadable_files         — files attached to products
 *   tc_downloadable_keys          — per-purchase tokens (key_hash, never raw)
 *   tc_downloadable_log           — every download attempt
 *   tc_downloadable_agreements    — versioned T&C
 *   tc_downloadable_acceptances   — chargeback evidence
 *
 * @package TitanCart\Modules\Downloadables
 */

namespace TitanCart\Modules\Downloadables;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

class DownloadableRepository {

    private Database $db;

    public function __construct() {
        $this->db = titancart_db();
    }

    // ══════════════════════════════════════════════════════════════════════════
    // FILES — tc_downloadable_files
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Admin file list — returns ALL files (including disabled) for a product,
     * with rollup stats joined from tc_downloadable_keys.
     *
     * Stats columns added per row:
     *   keys_issued     — total keys ever issued for this file
     *   keys_active     — keys currently in 'active' status
     *   downloads_total — sum of download_count across all keys for this file
     *
     * @param  int $product_id
     * @return array<int, array>
     */
    public function get_files_for_product_admin( int $product_id ): array {
        $ft = $this->db->table( 'downloadable_files' );
        $kt = $this->db->table( 'downloadable_keys' );

        return $this->db->getArray(
            "SELECT f.*,
                    COALESCE(s.keys_issued, 0)     AS keys_issued,
                    COALESCE(s.keys_active, 0)     AS keys_active,
                    COALESCE(s.downloads_total, 0) AS downloads_total
             FROM {$ft} f
             LEFT JOIN (
                 SELECT file_id,
                        COUNT(*)                                          AS keys_issued,
                        SUM( CASE WHEN status = 'active' THEN 1 ELSE 0 END ) AS keys_active,
                        SUM(download_count)                                AS downloads_total
                 FROM {$kt}
                 GROUP BY file_id
             ) s ON s.file_id = f.id
             WHERE f.product_id = ?i
             ORDER BY f.position ASC, f.id ASC",
            $product_id
        );
    }

    /**
     * Get a single file row by id.
     *
     * @param  int $file_id
     * @return array|null
     */
    public function get_file( int $file_id ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_files')} WHERE id = ?i LIMIT 1",
            $file_id
        );
    }

    /**
     * Get all active files for a product (and optionally a specific variation).
     *
     * When $variation_id is null, returns files where variation_id IS NULL
     * (i.e. product-level files that apply to every variation). When provided,
     * returns BOTH variation-specific files AND product-level files — the
     * variation inherits product-level files unless explicitly overridden.
     *
     * @param  int      $product_id
     * @param  int|null $variation_id
     * @return array<int, array>
     */
    public function get_files_for_product( int $product_id, ?int $variation_id = null ): array {
        if ( $variation_id === null ) {
            return $this->db->getArray(
                "SELECT * FROM {$this->db->table('downloadable_files')}
                 WHERE product_id = ?i AND variation_id IS NULL AND status = 'active'
                 ORDER BY position ASC, id ASC",
                $product_id
            );
        }

        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_files')}
             WHERE product_id = ?i
               AND ( variation_id IS NULL OR variation_id = ?i )
               AND status = 'active'
             ORDER BY position ASC, id ASC",
            $product_id,
            $variation_id
        );
    }

    /**
     * Insert a new file row.
     *
     * @param  array $data Keys: product_id, variation_id, filename, original_name,
     *                     file_path, file_size, mime_type, preview_path, readme,
     *                     position, status.
     * @return int New file_id, or 0 on failure.
     */
    public function create_file( array $data ): int {
        $defaults = [
            'product_id'    => 0,
            'variation_id'  => null,
            'filename'      => '',
            'original_name' => '',
            'file_path'     => '',
            'file_size'     => 0,
            'mime_type'     => '',
            'preview_path'  => null,
            'readme'        => null,
            'position'      => 0,
            'status'        => 'active',
        ];
        $row = array_merge( $defaults, $data );
        return $this->db->insert( 'downloadable_files', $row );
    }

    /**
     * Update a file row.
     *
     * @param  int   $file_id
     * @param  array $data
     * @return bool
     */
    public function update_file( int $file_id, array $data ): bool {
        return false !== $this->db->update( 'downloadable_files', $data, [ 'id' => $file_id ] );
    }

    /**
     * Delete a file row (admin action — orphans any keys pointing to this file).
     *
     * @param  int $file_id
     * @return bool
     */
    public function delete_file( int $file_id ): bool {
        return false !== $this->db->delete( 'downloadable_files', [ 'id' => $file_id ] );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // KEYS — tc_downloadable_keys
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Look up a key row by its sha256 hash.
     *
     * @param  string $key_hash
     * @return array|null
     */
    public function find_key_by_hash( string $key_hash ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_keys')} WHERE key_hash = ?s LIMIT 1",
            $key_hash
        );
    }

    /**
     * Look up a key row by its primary key id. Used by admin-driven revocation
     * (manual revoke button on the order detail panel) where the UI surfaces
     * the key_id but never the raw token.
     *
     * @param  int $key_id
     * @return array|null
     */
    public function find_key_by_id( int $key_id ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_keys')} WHERE id = ?i LIMIT 1",
            $key_id
        );
    }

    /**
     * Get all keys for an order (across all line items + files).
     *
     * @param  int $order_id
     * @return array<int, array>
     */
    public function get_keys_for_order( int $order_id ): array {
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_keys')} WHERE order_id = ?i ORDER BY id ASC",
            $order_id
        );
    }

    /**
     * Admin-flavored keys lookup for the order detail Downloads panel.
     * Joins file + order_item info so each row carries enough data to render
     * a readable list without a second round trip:
     *   - file_original_name, file_size, file_mime, file_status
     *   - product_name (snapshot from order_items.name)
     *
     * @param  int $order_id
     * @return array<int, array>
     */
    public function get_keys_for_order_admin( int $order_id ): array {
        $kt = $this->db->table( 'downloadable_keys' );
        $ft = $this->db->table( 'downloadable_files' );
        $oit = $this->db->table( 'order_items' );

        return $this->db->getArray(
            "SELECT k.*,
                    f.original_name AS file_original_name,
                    f.file_size     AS file_size,
                    f.mime_type     AS file_mime,
                    f.status        AS file_status,
                    oi.name         AS product_name
             FROM {$kt} k
             LEFT JOIN {$ft}  f  ON f.id  = k.file_id
             LEFT JOIN {$oit} oi ON oi.id = k.order_item_id
             WHERE k.order_id = ?i
             ORDER BY k.id ASC",
            $order_id
        );
    }

    /**
     * Get all keys for a single order item.
     *
     * @param  int $order_item_id
     * @return array<int, array>
     */
    public function get_keys_for_order_item( int $order_item_id ): array {
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_keys')} WHERE order_item_id = ?i ORDER BY id ASC",
            $order_item_id
        );
    }

    /**
     * Get all download keys belonging to a customer for the My Account page.
     *
     * Joins:
     *   - downloadable_files (file metadata + on-disk status)
     *   - order_items         (product_name snapshot — survives product rename)
     *
     * Filters out revoked/expired keys when $include_inactive is false; pass
     * true to surface the full history (default for the customer-facing list
     * since customers want to see what they USED to be able to download).
     *
     * Lookup is by customer_id OR email — covers two cases:
     *   - Logged-in customer who places an order → key has customer_id
     *   - Guest checkout that later registers → key still keyed by email,
     *     and we want them to see those keys after they create an account
     *     with the same email.
     *
     * Field shape matches get_keys_for_order_admin() so a single
     * shape_customer_key_row() handles both call sites.
     *
     * @param  int|null    $customer_id     tc_customers.id (NOT wp_users.ID)
     * @param  string|null $email           Used for guest lookups when customer_id is null.
     * @param  bool        $include_inactive
     * @return array<int, array>
     */
    public function get_keys_for_customer( ?int $customer_id, ?string $email = null, bool $include_inactive = false ): array {
        $kt  = $this->db->table( 'downloadable_keys' );
        $ft  = $this->db->table( 'downloadable_files' );
        $oit = $this->db->table( 'order_items' );

        $sql = "SELECT k.*,
                       f.original_name AS file_original_name,
                       f.file_size     AS file_size,
                       f.mime_type     AS file_mime,
                       f.status        AS file_status,
                       oi.name         AS product_name
                FROM {$kt} k
                LEFT JOIN {$ft}  f  ON f.id  = k.file_id
                LEFT JOIN {$oit} oi ON oi.id = k.order_item_id
                WHERE ";

        $args  = [];
        $where = [];
        if ( $customer_id ) {
            $where[] = "k.customer_id = ?i";
            $args[]  = $customer_id;
        }
        if ( $email ) {
            $where[] = "k.email = ?s";
            $args[]  = $email;
        }
        if ( ! $where ) {
            return [];
        }
        $sql .= '(' . implode( ' OR ', $where ) . ')';
        if ( ! $include_inactive ) {
            $sql .= " AND k.status = 'active'";
        }
        $sql .= " ORDER BY k.created_at DESC";

        return $this->db->getArray( $sql, ...$args );
    }

    /**
     * Insert a new key row. Caller computes the sha256 hash; raw token never
     * touches the DB layer.
     *
     * @param  array $data Keys: key_hash, order_id, order_item_id, product_id,
     *                     file_id, customer_id, email, status, expires_at,
     *                     max_downloads.
     * @return int New key_id, or 0 on failure.
     */
    public function create_key( array $data ): int {
        $defaults = [
            'key_hash'       => '',
            'order_id'       => 0,
            'order_item_id'  => 0,
            'product_id'     => 0,
            'file_id'        => 0,
            'customer_id'    => null,
            'email'          => '',
            'status'         => 'active',
            'expires_at'     => null,
            'max_downloads'  => null,
            'download_count' => 0,
        ];
        $row = array_merge( $defaults, $data );
        return $this->db->insert( 'downloadable_keys', $row );
    }

    /**
     * Update arbitrary columns on a key row.
     *
     * @param  int   $key_id
     * @param  array $data
     * @return bool
     */
    public function update_key( int $key_id, array $data ): bool {
        return false !== $this->db->update( 'downloadable_keys', $data, [ 'id' => $key_id ] );
    }

    /**
     * Bulk status update — used by the cron expirer and the refund-revoke flow.
     *
     * @param  array  $key_ids
     * @param  string $new_status  'active' | 'expired' | 'revoked'
     * @return int    Rows affected.
     */
    public function bulk_update_status( array $key_ids, string $new_status ): int {
        if ( ! $key_ids ) {
            return 0;
        }
        if ( ! in_array( $new_status, [ 'active', 'expired', 'revoked' ], true ) ) {
            return 0;
        }
        $count = 0;
        foreach ( $key_ids as $id ) {
            if ( $this->update_key( (int) $id, [ 'status' => $new_status ] ) ) {
                $count++;
            }
        }
        return $count;
    }

    /**
     * Find all active keys whose expires_at has passed. The expirer cron
     * walks this list and flips each one to status='expired'.
     *
     * @param  int $limit
     * @return array<int, array>
     */
    public function find_keys_due_to_expire( int $limit = 500 ): array {
        return $this->db->getArray(
            "SELECT id FROM {$this->db->table('downloadable_keys')}
             WHERE status = 'active' AND expires_at IS NOT NULL AND expires_at < ?s
             ORDER BY expires_at ASC
             LIMIT ?i",
            titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
            $limit
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // LOG — tc_downloadable_log
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Append a log row. Append-only — the log is immutable in normal operation.
     *
     * @param  array $data Keys: key_id, event, ip_address, user_agent,
     *                     bytes_served, meta (array, encoded to JSON here).
     * @return int New log_id.
     */
    public function log_event( array $data ): int {
        $defaults = [
            'key_id'       => null,
            'event'        => 'downloaded',
            'ip_address'   => null,
            'user_agent'   => null,
            'bytes_served' => 0,
            'meta'         => null,
        ];
        $row = array_merge( $defaults, $data );

        // Encode meta to JSON if caller passed an array.
        if ( is_array( $row['meta'] ) ) {
            $row['meta'] = wp_json_encode( $row['meta'] );
        }

        return $this->db->insert( 'downloadable_log', $row );
    }

    /**
     * Fetch log entries for a single key — used by the admin order detail
     * page to show "this customer downloaded this file at these times."
     *
     * @param  int $key_id
     * @param  int $limit
     * @return array<int, array>
     */
    public function get_log_for_key( int $key_id, int $limit = 100 ): array {
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_log')}
             WHERE key_id = ?i
             ORDER BY created_at DESC
             LIMIT ?i",
            $key_id,
            $limit
        );
    }

    /**
     * Prune log rows older than N days. Run by daily cron.
     *
     * @param  int $days
     * @return int Rows deleted.
     */
    public function prune_log( int $days ): int {
        $result = $this->db->query(
            "DELETE FROM {$this->db->table('downloadable_log')} WHERE created_at < ( ?s - INTERVAL ?i DAY )",
            titancart_now_utc(), // KI-24: UTC base, not MySQL-tz NOW()
            $days
        );
        return is_int( $result ) ? $result : 0;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // AGREEMENTS — tc_downloadable_agreements
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get the active global default agreement (is_global=1, status='active').
     * Returns null when global agreements are disabled — the default state on
     * a fresh install per Sean's call.
     *
     * @param  string $language_code
     * @return array|null
     */
    public function get_global_agreement( string $language_code = 'en' ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_agreements')}
             WHERE is_global = 1 AND status = 'active' AND language_code = ?s
             ORDER BY version DESC, id DESC
             LIMIT 1",
            $language_code
        );
    }

    /**
     * Get the active product-specific agreement, if any. When this returns
     * null the caller falls back to get_global_agreement().
     *
     * @param  int    $product_id
     * @param  string $language_code
     * @return array|null
     */
    public function get_product_agreement( int $product_id, string $language_code = 'en' ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_agreements')}
             WHERE product_id = ?i AND is_global = 0 AND status = 'active' AND language_code = ?s
             ORDER BY version DESC, id DESC
             LIMIT 1",
            $product_id,
            $language_code
        );
    }

    /**
     * Get a specific agreement row by id.
     *
     * @param  int $agreement_id
     * @return array|null
     */
    public function get_agreement( int $agreement_id ): ?array {
        return $this->db->getRow(
            "SELECT * FROM {$this->db->table('downloadable_agreements')} WHERE id = ?i LIMIT 1",
            $agreement_id
        );
    }

    /**
     * Admin agreement list — every agreement, optionally filtered, with
     * an acceptance_count rollup joined from tc_downloadable_acceptances.
     *
     * Used by the Settings → Download Agreements admin page.
     *
     * @param  array $filters  Supported keys:
     *                            - include_archived (bool, default false)
     *                            - product_id (int|null)
     *                            - is_global (int 0|1|null)
     *                            - language_code (string|null)
     * @return array<int, array>
     */
    public function list_agreements_admin( array $filters = [] ): array {
        $at = $this->db->table( 'downloadable_agreements' );
        $ct = $this->db->table( 'downloadable_acceptances' );

        $where = [];
        $args  = [];
        if ( empty( $filters['include_archived'] ) ) {
            $where[] = "a.status = 'active'";
        }
        if ( isset( $filters['product_id'] ) && $filters['product_id'] !== null ) {
            $where[] = "a.product_id = ?i";
            $args[]  = (int) $filters['product_id'];
        }
        if ( isset( $filters['is_global'] ) && $filters['is_global'] !== null ) {
            $where[] = "a.is_global = ?i";
            $args[]  = (int) $filters['is_global'];
        }
        if ( ! empty( $filters['language_code'] ) ) {
            $where[] = "a.language_code = ?s";
            $args[]  = (string) $filters['language_code'];
        }

        $where_sql = $where ? ( ' WHERE ' . implode( ' AND ', $where ) ) : '';

        $sql = "SELECT a.*,
                       COALESCE(ac.acceptance_count, 0) AS acceptance_count
                FROM {$at} a
                LEFT JOIN (
                    SELECT agreement_id, COUNT(*) AS acceptance_count
                    FROM {$ct}
                    GROUP BY agreement_id
                ) ac ON ac.agreement_id = a.id
                {$where_sql}
                ORDER BY a.is_global DESC, a.product_id ASC, a.version DESC, a.id DESC";

        return $this->db->getArray( $sql, ...$args );
    }

    /**
     * Insert a new agreement row.
     *
     * @param  array $data Keys: product_id, is_global, version, title, content,
     *                     language_code, required, status.
     * @return int New agreement_id.
     */
    public function create_agreement( array $data ): int {
        $defaults = [
            'product_id'    => null,
            'is_global'     => 0,
            'version'       => 1,
            'title'         => '',
            'content'       => '',
            'language_code' => 'en',
            'required'      => 1,
            'status'        => 'active',
        ];
        $row = array_merge( $defaults, $data );
        return $this->db->insert( 'downloadable_agreements', $row );
    }

    /**
     * Archive an agreement (soft delete — keeps acceptances valid but stops
     * new acceptances from being prompted with this version).
     *
     * @param  int $agreement_id
     * @return bool
     */
    public function archive_agreement( int $agreement_id ): bool {
        return false !== $this->db->update(
            'downloadable_agreements',
            [ 'status' => 'archived' ],
            [ 'id' => $agreement_id ]
        );
    }

    /**
     * Look up the highest version number used for a given scope (global or
     * product-specific). Used by the "edit creates new version" flow so we
     * know what version+1 should be.
     *
     * @param  int|null $product_id    null + is_global=1 → global default scope
     * @param  bool     $is_global
     * @param  string   $language_code
     * @return int      Highest version found, or 0 if no rows exist for the scope.
     */
    public function get_max_version_for_scope( ?int $product_id, bool $is_global, string $language_code = 'en' ): int {
        if ( $is_global ) {
            $row = $this->db->getField(
                "SELECT MAX(version) FROM {$this->db->table('downloadable_agreements')}
                 WHERE is_global = 1 AND language_code = ?s",
                $language_code
            );
        } else {
            $row = $this->db->getField(
                "SELECT MAX(version) FROM {$this->db->table('downloadable_agreements')}
                 WHERE is_global = 0 AND product_id = ?i AND language_code = ?s",
                (int) $product_id,
                $language_code
            );
        }
        return (int) $row;
    }

    /**
     * Get the full version history (all rows, including archived) for a given
     * agreement scope. Used by the admin agreement editor's "Version history"
     * panel.
     *
     * @param  int|null $product_id
     * @param  bool     $is_global
     * @param  string   $language_code
     * @return array<int, array>
     */
    public function get_version_history( ?int $product_id, bool $is_global, string $language_code = 'en' ): array {
        if ( $is_global ) {
            return $this->db->getArray(
                "SELECT * FROM {$this->db->table('downloadable_agreements')}
                 WHERE is_global = 1 AND language_code = ?s
                 ORDER BY version DESC, id DESC",
                $language_code
            );
        }
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_agreements')}
             WHERE is_global = 0 AND product_id = ?i AND language_code = ?s
             ORDER BY version DESC, id DESC",
            (int) $product_id,
            $language_code
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // ACCEPTANCES — tc_downloadable_acceptances
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Insert an acceptance record.
     *
     * @param  array $data Keys: agreement_id, agreement_version, customer_id, email,
     *                     order_id, order_item_id, ip_address, user_agent,
     *                     accepted_content_hash.
     * @return int New acceptance_id.
     */
    public function create_acceptance( array $data ): int {
        $defaults = [
            'agreement_id'          => 0,
            'agreement_version'     => 1,
            'customer_id'           => null,
            'email'                 => '',
            'order_id'              => null,
            'order_item_id'         => null,
            'ip_address'            => null,
            'user_agent'            => null,
            'accepted_content_hash' => '',
        ];
        $row = array_merge( $defaults, $data );
        return $this->db->insert( 'downloadable_acceptances', $row );
    }

    /**
     * Find acceptances tied to an order — used by the admin chargeback evidence
     * panel (Phase 2) and by the email confirmation block (Phase 4).
     *
     * @param  int $order_id
     * @return array<int, array>
     */
    public function get_acceptances_for_order( int $order_id ): array {
        return $this->db->getArray(
            "SELECT * FROM {$this->db->table('downloadable_acceptances')}
             WHERE order_id = ?i
             ORDER BY accepted_at ASC",
            $order_id
        );
    }

    /**
     * Admin-flavored acceptance lookup — joins each acceptance to the
     * agreement title and product_id so the chargeback evidence panel can
     * render readable rows without a second round trip.
     *
     * @param  int $order_id
     * @return array<int, array>
     */
    public function get_acceptances_for_order_admin( int $order_id ): array {
        $ct = $this->db->table( 'downloadable_acceptances' );
        $at = $this->db->table( 'downloadable_agreements' );
        return $this->db->getArray(
            "SELECT ac.*, a.title AS agreement_title, a.is_global, a.product_id AS agreement_product_id
             FROM {$ct} ac
             LEFT JOIN {$at} a ON a.id = ac.agreement_id
             WHERE ac.order_id = ?i
             ORDER BY ac.accepted_at ASC",
            $order_id
        );
    }

    /**
     * Has this user/email already accepted this specific agreement version?
     * Phase 5 PDP gating uses this to decide whether to re-prompt.
     *
     * @param  int         $agreement_id
     * @param  int         $version
     * @param  int|null    $customer_id   tc_customers.id
     * @param  string|null $email
     * @return bool
     */
    public function has_acceptance( int $agreement_id, int $version, ?int $customer_id, ?string $email ): bool {
        $sql = "SELECT id FROM {$this->db->table('downloadable_acceptances')}
                WHERE agreement_id = ?i AND agreement_version = ?i AND ";
        $where = [];
        $args  = [ $agreement_id, $version ];

        if ( $customer_id ) {
            $where[] = "customer_id = ?i";
            $args[]  = $customer_id;
        }
        if ( $email ) {
            $where[] = "email = ?s";
            $args[]  = $email;
        }
        if ( ! $where ) {
            return false;
        }
        $sql .= '(' . implode( ' OR ', $where ) . ') LIMIT 1';

        return (bool) $this->db->getField( $sql, ...$args );
    }
}
