<?php
/**
 * Class LSC_DB
 * Database abstraction layer for chats and messages.
 */
class LSC_DB {

    /**
     * Get chats table name.
     */
    public static function chats_table() {
        global $wpdb;
        return $wpdb->prefix . 'lsc_chats';
    }

    /**
     * Get messages table name.
     */
    public static function messages_table() {
        global $wpdb;
        return $wpdb->prefix . 'lsc_messages';
    }

    /* ------------------------------------------------------------------ */
    /* CHATS                                                                */
    /* ------------------------------------------------------------------ */

    /**
     * Create a new chat session.
     *
     * @param string $visitor_id   Unique visitor identifier (cookie/session).
     * @param string $visitor_name Visitor display name.
     * @param string $visitor_email Visitor email.
     * @return int|false Inserted chat ID or false on failure.
     */
    public static function create_chat( $visitor_id, $visitor_name = '', $visitor_email = '' ) {
        global $wpdb;

        $result = $wpdb->insert(
            self::chats_table(),
            [
                'visitor_id'    => sanitize_text_field( $visitor_id ),
                'visitor_name'  => sanitize_text_field( $visitor_name ),
                'visitor_email' => sanitize_email( $visitor_email ),
                'status'        => 'open',
            ],
            [ '%s', '%s', '%s', '%s' ]
        );

        return $result ? (int) $wpdb->insert_id : false;
    }

    /**
     * Get a chat by visitor ID (returns most recent open chat).
     *
     * @param string $visitor_id
     * @return object|null
     */
    public static function get_chat_by_visitor( $visitor_id ) {
        global $wpdb;

        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . self::chats_table() . ' WHERE visitor_id = %s AND status = %s ORDER BY created_at DESC LIMIT 1',
                sanitize_text_field( $visitor_id ),
                'open'
            )
        );
    }

    /**
     * Get a single chat by ID.
     *
     * @param int $chat_id
     * @return object|null
     */
    public static function get_chat( $chat_id ) {
        global $wpdb;

        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . self::chats_table() . ' WHERE id = %d',
                (int) $chat_id
            )
        );
    }

    /**
     * Get all chats with optional filters.
     *
     * @param array $args
     * @return array
     */
    public static function get_chats( $args = [] ) {
        global $wpdb;

        $defaults = [
            'status'  => '',
            'limit'   => 50,
            'offset'  => 0,
            'orderby' => 'updated_at',
            'order'   => 'DESC',
        ];
        $args = wp_parse_args( $args, $defaults );

        $where  = '';
        $values = [];

        if ( ! empty( $args['status'] ) ) {
            $where    = 'WHERE status = %s';
            $values[] = sanitize_text_field( $args['status'] );
        }

        $orderby = in_array( $args['orderby'], [ 'id', 'created_at', 'updated_at' ], true )
            ? $args['orderby'] : 'updated_at';
        $order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

        $values[] = (int) $args['limit'];
        $values[] = (int) $args['offset'];

        $sql = 'SELECT * FROM ' . self::chats_table() . " {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results( $wpdb->prepare( $sql, $values ) );
    }

    /**
     * Update chat status.
     *
     * @param int    $chat_id
     * @param string $status open|closed
     * @return bool
     */
    public static function update_chat_status( $chat_id, $status ) {
        global $wpdb;

        $allowed = [ 'open', 'closed' ];
        if ( ! in_array( $status, $allowed, true ) ) {
            return false;
        }

        return (bool) $wpdb->update(
            self::chats_table(),
            [ 'status' => $status ],
            [ 'id'     => (int) $chat_id ],
            [ '%s' ],
            [ '%d' ]
        );
    }

    /**
     * Delete a chat and all its messages.
     *
     * @param int $chat_id
     * @return bool
     */
    public static function delete_chat( $chat_id ) {
        global $wpdb;

        $wpdb->delete( self::messages_table(), [ 'chat_id' => (int) $chat_id ], [ '%d' ] );
        return (bool) $wpdb->delete( self::chats_table(), [ 'id' => (int) $chat_id ], [ '%d' ] );
    }

    /* ------------------------------------------------------------------ */
    /* MESSAGES                                                             */
    /* ------------------------------------------------------------------ */

    /**
     * Insert a new message.
     *
     * @param int    $chat_id
     * @param string $sender  'visitor' or 'chatter'
     * @param string $content
     * @return int|false
     */
    public static function add_message( $chat_id, $sender, $content ) {
        global $wpdb;

        $allowed_senders = [ 'visitor', 'chatter' ];
        if ( ! in_array( $sender, $allowed_senders, true ) ) {
            return false;
        }

        $result = $wpdb->insert(
            self::messages_table(),
            [
                'chat_id' => (int) $chat_id,
                'sender'  => $sender,
                'content' => wp_kses_post( $content ),
                'is_read' => 0,
            ],
            [ '%d', '%s', '%s', '%d' ]
        );

        return $result ? (int) $wpdb->insert_id : false;
    }

    /**
     * Get messages for a chat.
     *
     * @param int $chat_id
     * @param int $since_id  Only return messages with ID > since_id (for polling).
     * @return array
     */
    public static function get_messages( $chat_id, $since_id = 0 ) {
        global $wpdb;

        $since_clause = $since_id > 0 ? $wpdb->prepare( ' AND id > %d', (int) $since_id ) : '';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM ' . self::messages_table() . ' WHERE chat_id = %d' . $since_clause . ' ORDER BY id ASC',
                (int) $chat_id
            )
        );
    }

    /**
     * Mark messages as read for a chat (by the chatter side).
     *
     * @param int $chat_id
     * @return bool
     */
    public static function mark_messages_read( $chat_id ) {
        global $wpdb;

        return (bool) $wpdb->update(
            self::messages_table(),
            [ 'is_read' => 1 ],
            [ 'chat_id' => (int) $chat_id, 'sender' => 'visitor' ],
            [ '%d' ],
            [ '%d', '%s' ]
        );
    }

    /**
     * Count total messages in a chat.
     *
     * @param int $chat_id
     * @return int
     */
    public static function count_chat_messages( $chat_id ) {
        global $wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . self::messages_table() . ' WHERE chat_id = %d',
                (int) $chat_id
            )
        );
    }

    /**
     * Count unread visitor messages across all chats.
     *
     * @return int
     */
    public static function count_unread() {
        global $wpdb;

        return (int) $wpdb->get_var(
            'SELECT COUNT(*) FROM ' . self::messages_table() . " WHERE sender = 'visitor' AND is_read = 0"
        );
    }

    /**
     * Count unread visitor messages for a specific chat.
     *
     * @param int $chat_id
     * @return int
     */
    public static function count_unread_for_chat( $chat_id ) {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM " . self::messages_table() . " WHERE chat_id = %d AND sender = 'visitor' AND is_read = 0",
                (int) $chat_id
            )
        );
    }
}
