<?php
namespace Zadex\Team;

/**
 * Repository for editor notes & comments (T169): table `{$wpdb->prefix}zadex_notes`.
 *
 *   id, post_id, element_id ('' = the page itself), parent_id (0 = thread root, else the root it replies to),
 *   author_id, content (plain text with @mentions), mentions (JSON user ids), status (open | resolved; roots only),
 *   position (JSON {x, y}: offset inside the anchor element, 0..1), created_at / updated_at (UTC)
 *
 * Created by dbDelta on activation and whenever the `zadex_notes_db_version` option differs from DB_VERSION
 * (checked on plugins_loaded, like Forms\Submissions). Content is user input: callers escape on output.
 */
final class Notes
{
    public const TABLE = 'zadex_notes';
    public const DB_VERSION = '1';
    public const OPTION_DB = 'zadex_notes_db_version';
    public const STATUSES = ['open', 'resolved'];
    public const MAX_LENGTH = 5000;

    /** @var \wpdb|object */
    private $db;

    /** @param \wpdb|object|null $wpdb */
    public function __construct($wpdb = null)
    {
        $this->db = $wpdb ?? ($GLOBALS['wpdb'] ?? null);
    }

    public function table(): string
    {
        return (string) ($this->db->prefix ?? 'wp_') . self::TABLE;
    }

    public function schema(): string
    {
        $collate = method_exists($this->db, 'get_charset_collate') ? (string) $this->db->get_charset_collate() : '';
        return "CREATE TABLE {$this->table()} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  post_id bigint(20) unsigned NOT NULL DEFAULT 0,
  element_id varchar(64) NOT NULL DEFAULT '',
  parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
  author_id bigint(20) unsigned NOT NULL DEFAULT 0,
  content longtext NOT NULL,
  mentions text NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'open',
  position text NOT NULL,
  created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  updated_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY  (id),
  KEY post_id (post_id),
  KEY parent_id (parent_id)
) {$collate};";
    }

    public function install(): void
    {
        if (!function_exists('dbDelta') && defined('ABSPATH') && is_file(ABSPATH . 'wp-admin/includes/upgrade.php')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        if (function_exists('dbDelta')) {
            dbDelta($this->schema());
        }
        update_option(self::OPTION_DB, self::DB_VERSION);
    }

    public function maybe_upgrade(): bool
    {
        if ((string) get_option(self::OPTION_DB, '') === self::DB_VERSION) {
            return false;
        }
        $this->install();
        return true;
    }

    public static function clean_content($content): string
    {
        $text = sanitize_textarea_field(is_scalar($content) ? (string) $content : '');
        return trim(mb_substr($text, 0, self::MAX_LENGTH));
    }

    public static function clean_element_id($id): string
    {
        $id = is_string($id) ? $id : '';
        return preg_match('/^[a-z][a-z0-9]{2,15}$/', $id) ? $id : '';
    }

    /** @return array{x: float, y: float}|null */
    public static function clean_position($pos): ?array
    {
        if (!is_array($pos) || !isset($pos['x'], $pos['y']) || !is_numeric($pos['x']) || !is_numeric($pos['y'])) {
            return null;
        }
        $clamp = static fn($v): float => round(max(0.0, min(1.0, (float) $v)), 4);
        return ['x' => $clamp($pos['x']), 'y' => $clamp($pos['y'])];
    }

    /** @param array<string, mixed> $row post_id, element_id, parent_id, author_id, content, mentions, position */
    public function insert(array $row): int
    {
        $now = (string) ($row['created_at'] ?? current_time('mysql', true));
        $ok = $this->db->insert($this->table(), [
            'post_id'    => max(0, (int) ($row['post_id'] ?? 0)),
            'element_id' => self::clean_element_id($row['element_id'] ?? ''),
            'parent_id'  => max(0, (int) ($row['parent_id'] ?? 0)),
            'author_id'  => max(0, (int) ($row['author_id'] ?? 0)),
            'content'    => self::clean_content($row['content'] ?? ''),
            'mentions'   => (string) wp_json_encode(array_values(array_map('intval', (array) ($row['mentions'] ?? [])))),
            'status'     => 'open',
            'position'   => (string) wp_json_encode(self::clean_position($row['position'] ?? null)),
            'created_at' => $now,
            'updated_at' => $now,
        ], ['%d', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s']);
        return $ok ? (int) $this->db->insert_id : 0;
    }

    public function get(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }
        $row = $this->db->get_row($this->db->prepare("SELECT * FROM {$this->table()} WHERE id = %d", $id), ARRAY_A);
        return is_array($row) ? self::decode($row) : null;
    }

    /** Every note of a post (roots and replies), oldest first. */
    public function for_post(int $post_id): array
    {
        $rows = $this->db->get_results($this->db->prepare("SELECT * FROM {$this->table()} WHERE post_id = %d ORDER BY created_at ASC, id ASC", $post_id), ARRAY_A);
        return array_map([self::class, 'decode'], is_array($rows) ? $rows : []);
    }

    public function replies(int $id): array
    {
        $rows = $this->db->get_results($this->db->prepare("SELECT * FROM {$this->table()} WHERE parent_id = %d ORDER BY created_at ASC, id ASC", $id), ARRAY_A);
        return array_map([self::class, 'decode'], is_array($rows) ? $rows : []);
    }

    /** @param array<string, mixed> $fields content, mentions, status */
    public function update(int $id, array $fields): bool
    {
        $data = [];
        $format = [];
        if (array_key_exists('content', $fields)) {
            $data['content'] = self::clean_content($fields['content']);
            $format[] = '%s';
        }
        if (array_key_exists('mentions', $fields)) {
            $data['mentions'] = (string) wp_json_encode(array_values(array_map('intval', (array) $fields['mentions'])));
            $format[] = '%s';
        }
        if (isset($fields['status']) && in_array($fields['status'], self::STATUSES, true)) {
            $data['status'] = $fields['status'];
            $format[] = '%s';
        }
        if (!$data) {
            return false;
        }
        $data['updated_at'] = current_time('mysql', true);
        $format[] = '%s';
        return (bool) $this->db->update($this->table(), $data, ['id' => $id], $format, ['%d']);
    }

    /** Deletes a note and, for a thread root, its replies. Returns the deleted ids. */
    public function delete(int $id): array
    {
        $note = $this->get($id);
        if (!$note) {
            return [];
        }
        $ids = [];
        if ($note['parent_id'] === 0) {
            foreach ($this->replies($id) as $reply) {
                if ($this->db->delete($this->table(), ['id' => $reply['id']], ['%d'])) {
                    $ids[] = $reply['id'];
                }
            }
        }
        if ($this->db->delete($this->table(), ['id' => $id], ['%d'])) {
            $ids[] = $id;
        }
        return $ids;
    }

    /** `deleted_post`: notes never outlive their post. */
    public function delete_for_post(int $post_id): int
    {
        $n = 0;
        foreach ($this->for_post($post_id) as $note) {
            $n += (int) $this->db->delete($this->table(), ['id' => $note['id']], ['%d']);
        }
        return $n;
    }

    /** @param array<string, mixed> $row */
    public static function decode(array $row): array
    {
        $mentions = json_decode((string) ($row['mentions'] ?? '[]'), true);
        $position = json_decode((string) ($row['position'] ?? 'null'), true);
        return [
            'id'         => (int) ($row['id'] ?? 0),
            'post_id'    => (int) ($row['post_id'] ?? 0),
            'element_id' => (string) ($row['element_id'] ?? ''),
            'parent_id'  => (int) ($row['parent_id'] ?? 0),
            'author_id'  => (int) ($row['author_id'] ?? 0),
            'content'    => (string) ($row['content'] ?? ''),
            'mentions'   => is_array($mentions) ? array_values(array_map('intval', $mentions)) : [],
            'status'     => in_array($row['status'] ?? '', self::STATUSES, true) ? (string) $row['status'] : 'open',
            'position'   => self::clean_position($position),
            'created_at' => (string) ($row['created_at'] ?? ''),
            'updated_at' => (string) ($row['updated_at'] ?? ''),
        ];
    }
}