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

class TMNL_DB {

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

    /**
     * Get X embeds table name
     */
    public static function x_embeds_table() {
        global $wpdb;
        return $wpdb->prefix . 'tmnl_x_embeds';
    }

    /**
     * Get source settings table name
     */
    public static function source_settings_table() {
        global $wpdb;
        return $wpdb->prefix . 'tmnl_source_settings';
    }

    /**
     * Create/update all tables on plugin activation or version upgrade
     */
    public static function create_table() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Main testimonials table (with new columns)
        $table = self::table_name();
        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            author_name varchar(255) NOT NULL DEFAULT '',
            rating tinyint(1) NOT NULL DEFAULT 5,
            text text NOT NULL,
            review_date date DEFAULT NULL,
            source_label varchar(100) NOT NULL DEFAULT '',
            source_type varchar(50) NOT NULL DEFAULT 'manual',
            external_id varchar(255) DEFAULT NULL,
            is_visible tinyint(1) NOT NULL DEFAULT 1,
            sort_order int(11) NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'published',
            source_url varchar(500) DEFAULT NULL,
            sns_tags varchar(255) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY is_visible (is_visible),
            KEY sort_order (sort_order),
            KEY source_type (source_type),
            KEY status (status)
        ) {$charset_collate};";
        dbDelta( $sql );

        // X embeds table
        $x_table = self::x_embeds_table();
        $sql2 = "CREATE TABLE {$x_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            tweet_url varchar(500) NOT NULL,
            author varchar(255) NOT NULL DEFAULT '',
            is_visible tinyint(1) NOT NULL DEFAULT 1,
            sort_order int(11) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) {$charset_collate};";
        dbDelta( $sql2 );

        // Source settings table
        $ss_table = self::source_settings_table();
        $sql3 = "CREATE TABLE {$ss_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            source_type varchar(50) NOT NULL,
            is_enabled tinyint(1) NOT NULL DEFAULT 0,
            settings longtext,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY source_type (source_type)
        ) {$charset_collate};";
        dbDelta( $sql3 );

        // Migrate existing options to source_settings table
        self::migrate_settings();

        update_option( 'tmnl_db_version', TMNL_VERSION );
    }

    /**
     * Migrate v1 options to source_settings table
     */
    private static function migrate_settings() {
        global $wpdb;
        $table = self::source_settings_table();

        // Migrate Google settings
        $api_key  = get_option( 'tmnl_google_api_key', '' );
        $place_id = get_option( 'tmnl_google_place_id', '' );
        if ( $api_key || $place_id ) {
            $existing = $wpdb->get_var(
                $wpdb->prepare( "SELECT id FROM {$table} WHERE source_type = %s", 'google' )
            );
            if ( ! $existing ) {
                $wpdb->insert( $table, array(
                    'source_type' => 'google',
                    'is_enabled'  => 1,
                    'settings'    => wp_json_encode( array(
                        'api_key'  => $api_key,
                        'place_id' => $place_id,
                    )),
                    'updated_at' => current_time( 'mysql' ),
                ));
            }
        }

        // Ensure manual source is enabled
        $manual_exists = $wpdb->get_var(
            $wpdb->prepare( "SELECT id FROM {$table} WHERE source_type = %s", 'manual' )
        );
        if ( ! $manual_exists ) {
            $wpdb->insert( $table, array(
                'source_type' => 'manual',
                'is_enabled'  => 1,
                'settings'    => wp_json_encode( array() ),
                'updated_at'  => current_time( 'mysql' ),
            ));
        }
    }

    /**
     * Get testimonials
     */
    public static function get_testimonials( $args = array() ) {
        global $wpdb;
        $table = self::table_name();

        $defaults = array(
            'visible_only' => true,
            'limit'        => 0,
            'min_rating'   => 1,
            'source_type'  => '',
            'orderby'      => 'sort_order ASC, created_at DESC',
        );
        $args = wp_parse_args( $args, $defaults );

        $where = array( '1=1' );

        if ( $args['visible_only'] ) {
            $where[] = 'is_visible = 1';
            $where[] = "status != 'pending'";
        }

        if ( $args['min_rating'] > 1 ) {
            $where[] = $wpdb->prepare( 'rating >= %d', $args['min_rating'] );
        }

        if ( ! empty( $args['source_type'] ) ) {
            if ( is_array( $args['source_type'] ) ) {
                $placeholders = implode( ',', array_fill( 0, count( $args['source_type'] ), '%s' ) );
                $where[] = $wpdb->prepare( "source_type IN ({$placeholders})", $args['source_type'] );
            } else {
                $where[] = $wpdb->prepare( 'source_type = %s', $args['source_type'] );
            }
        }

        $where_sql = implode( ' AND ', $where );
        $order_sql = sanitize_sql_orderby( $args['orderby'] ) ?: 'sort_order ASC, created_at DESC';

        $sql = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$order_sql}";

        if ( $args['limit'] > 0 ) {
            $sql .= $wpdb->prepare( ' LIMIT %d', $args['limit'] );
        }

        return $wpdb->get_results( $sql );
    }

    /**
     * Get single testimonial
     */
    public static function get( $id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) );
    }

    /**
     * Insert testimonial
     */
    public static function insert( $data ) {
        global $wpdb;
        $table = self::table_name();

        $result = $wpdb->insert( $table, array(
            'author_name'  => sanitize_text_field( $data['author_name'] ?? '' ),
            'rating'       => intval( $data['rating'] ?? 5 ),
            'text'         => sanitize_textarea_field( $data['text'] ?? '' ),
            'review_date'  => sanitize_text_field( $data['review_date'] ?? '' ),
            'source_label' => sanitize_text_field( $data['source_label'] ?? '' ),
            'source_type'  => sanitize_text_field( $data['source_type'] ?? 'manual' ),
            'external_id'  => sanitize_text_field( $data['external_id'] ?? '' ) ?: null,
            'is_visible'   => intval( $data['is_visible'] ?? 1 ),
            'sort_order'   => intval( $data['sort_order'] ?? 0 ),
            'status'       => sanitize_text_field( $data['status'] ?? 'published' ),
            'source_url'   => isset( $data['source_url'] ) ? esc_url_raw( $data['source_url'] ) : null,
            'sns_tags'     => sanitize_text_field( $data['sns_tags'] ?? '' ) ?: null,
        ), array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s' ) );

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

    /**
     * Update testimonial
     */
    public static function update( $id, $data ) {
        global $wpdb;
        $table = self::table_name();

        $update = array();
        $format = array();

        $fields = array(
            'author_name'  => '%s',
            'rating'       => '%d',
            'text'         => '%s',
            'review_date'  => '%s',
            'source_label' => '%s',
            'source_type'  => '%s',
            'external_id'  => '%s',
            'is_visible'   => '%d',
            'sort_order'   => '%d',
            'status'       => '%s',
            'source_url'   => '%s',
            'sns_tags'     => '%s',
        );

        foreach ( $fields as $field => $fmt ) {
            if ( isset( $data[ $field ] ) ) {
                if ( $fmt === '%s' ) {
                    $update[ $field ] = sanitize_text_field( $data[ $field ] );
                } else {
                    $update[ $field ] = intval( $data[ $field ] );
                }
                $format[] = $fmt;
            }
        }

        if ( empty( $update ) ) return false;

        return $wpdb->update( $table, $update, array( 'id' => $id ), $format, array( '%d' ) );
    }

    /**
     * Delete testimonial
     */
    public static function delete( $id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
    }

    /**
     * Get pending (queued) testimonials
     */
    public static function get_pending() {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->get_results(
            "SELECT * FROM {$table} WHERE status = 'pending' ORDER BY created_at DESC"
        );
    }

    /**
     * Publish a pending testimonial (status → published, is_visible → 1)
     */
    public static function publish_testimonial( $id ) {
        global $wpdb;
        $table = self::table_name();
        return $wpdb->update(
            $table,
            array( 'status' => 'published', 'is_visible' => 1 ),
            array( 'id' => $id ),
            array( '%s', '%d' ),
            array( '%d' )
        );
    }

    /**
     * Toggle visibility
     */
    public static function toggle_visibility( $id ) {
        global $wpdb;
        $table = self::table_name();
        $current = self::get( $id );
        if ( ! $current ) return false;
        $new_val = $current->is_visible ? 0 : 1;
        return $wpdb->update( $table, array( 'is_visible' => $new_val ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
    }

    // =============================================
    // X Embeds CRUD
    // =============================================

    public static function get_x_embeds( $args = array() ) {
        global $wpdb;
        $table = self::x_embeds_table();

        $defaults = array( 'visible_only' => false );
        $args = wp_parse_args( $args, $defaults );

        $where = '1=1';
        if ( $args['visible_only'] ) {
            $where .= ' AND is_visible = 1';
        }

        return $wpdb->get_results( "SELECT * FROM {$table} WHERE {$where} ORDER BY sort_order ASC, created_at DESC" );
    }

    public static function get_x_embed( $id ) {
        global $wpdb;
        $table = self::x_embeds_table();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) );
    }

    public static function insert_x_embed( $data ) {
        global $wpdb;
        $table = self::x_embeds_table();
        $result = $wpdb->insert( $table, array(
            'tweet_url'  => esc_url_raw( $data['tweet_url'] ?? '' ),
            'author'     => sanitize_text_field( $data['author'] ?? '' ),
            'is_visible' => intval( $data['is_visible'] ?? 1 ),
            'sort_order' => intval( $data['sort_order'] ?? 0 ),
        ), array( '%s', '%s', '%d', '%d' ) );
        return $result ? $wpdb->insert_id : false;
    }

    public static function delete_x_embed( $id ) {
        global $wpdb;
        $table = self::x_embeds_table();
        return $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
    }

    public static function toggle_x_embed_visibility( $id ) {
        global $wpdb;
        $table = self::x_embeds_table();
        $current = self::get_x_embed( $id );
        if ( ! $current ) return false;
        $new_val = $current->is_visible ? 0 : 1;
        return $wpdb->update( $table, array( 'is_visible' => $new_val ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
    }
}
