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

/**
 * v3.0.0 relationship index. Existing comma-separated post meta remains the
 * canonical compatibility layer, while this indexed table accelerates reverse
 * relationship lookups for larger firms.
 */
function lt_core_relationship_table() {
    global $wpdb;
    return $wpdb->prefix . 'lt_relationships';
}

function lt_core_relationship_fields() {
    $map = array();
    foreach ( lt_core_meta_schema() as $source_type => $fields ) {
        foreach ( $fields as $key => $field ) {
            if ( in_array( $field['type'], array( 'relation', 'single_relation' ), true ) && ! empty( $field['post_type'] ) ) {
                $map[ $source_type ][ $key ] = $field['post_type'];
            }
        }
    }
    return $map;
}

function lt_core_install_relationship_table() {
    global $wpdb;
    $table = lt_core_relationship_table();
    $charset = $wpdb->get_charset_collate();
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        source_id bigint(20) unsigned NOT NULL,
        source_type varchar(32) NOT NULL,
        relationship varchar(64) NOT NULL,
        target_id bigint(20) unsigned NOT NULL,
        target_type varchar(32) NOT NULL,
        sort_order int(11) unsigned NOT NULL DEFAULT 0,
        PRIMARY KEY  (id),
        UNIQUE KEY source_relationship_target (source_id,relationship,target_id),
        KEY target_lookup (target_id,target_type,relationship),
        KEY source_lookup (source_id,source_type,relationship)
    ) {$charset};";
    dbDelta( $sql );
    wp_cache_set( 'relationship_table_exists', true, 'law-temple-core' );
    update_option( 'lt_core_relationship_db_version', '1.0', false );
}

function lt_core_relationship_table_exists() {
    $cached = wp_cache_get( 'relationship_table_exists', 'law-temple-core' );
    if ( false !== $cached ) { return (bool) $cached; }
    global $wpdb;
    $table = lt_core_relationship_table();
    $exists = ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) === $table );
    wp_cache_set( 'relationship_table_exists', $exists, 'law-temple-core' );
    return $exists;
}

function lt_core_sync_relationships_for_post( $post_id, $post = null ) {
    if ( wp_is_post_revision( $post_id ) || wp_is_post_autosave( $post_id ) || ! lt_core_relationship_table_exists() ) { return; }
    $post = $post ?: get_post( $post_id );
    if ( ! $post ) { return; }
    $all = lt_core_relationship_fields();
    if ( empty( $all[ $post->post_type ] ) ) { return; }
    global $wpdb;
    $table = lt_core_relationship_table();
    foreach ( $all[ $post->post_type ] as $key => $target_type ) {
        $wpdb->delete( $table, array( 'source_id'=>(int)$post_id, 'relationship'=>$key ), array( '%d','%s' ) );
        $ids = lt_core_csv_ids( get_post_meta( $post_id, $key, true ) );
        foreach ( $ids as $order => $target_id ) {
            if ( $target_id <= 0 ) { continue; }
            $wpdb->insert( $table, array(
                'source_id'=>(int)$post_id,
                'source_type'=>$post->post_type,
                'relationship'=>$key,
                'target_id'=>(int)$target_id,
                'target_type'=>$target_type,
                'sort_order'=>(int)$order,
            ), array( '%d','%s','%s','%d','%s','%d' ) );
        }
    }
}
add_action( 'save_post', 'lt_core_sync_relationships_for_post', 30, 2 );

function lt_core_relationship_meta_changed( $meta_id, $post_id, $meta_key, $meta_value = null ) {
    $post_type = get_post_type( $post_id );
    $map = lt_core_relationship_fields();
    if ( $post_type && ! empty( $map[ $post_type ][ $meta_key ] ) ) {
        lt_core_sync_relationships_for_post( $post_id, get_post( $post_id ) );
    }
}
add_action( 'added_post_meta', 'lt_core_relationship_meta_changed', 20, 4 );
add_action( 'updated_post_meta', 'lt_core_relationship_meta_changed', 20, 4 );
add_action( 'deleted_post_meta', 'lt_core_relationship_meta_changed', 20, 4 );

function lt_core_relationship_index_migrate_all( $limit = 5000 ) {
    if ( ! lt_core_relationship_table_exists() ) { return 0; }
    $types = array_keys( lt_core_relationship_fields() );
    $ids = get_posts( array(
        'post_type'=>$types,
        'post_status'=>array( 'publish','draft','private','pending' ),
        'posts_per_page'=>max( 1, min( 5000, absint( $limit ) ) ),
        'fields'=>'ids',
        'orderby'=>'ID',
        'order'=>'ASC',
        'no_found_rows'=>true,
    ) );
    foreach ( $ids as $id ) { lt_core_sync_relationships_for_post( $id, get_post( $id ) ); }
    update_option( 'lt_core_relationship_index_last_migration', current_time( 'mysql' ), false );
    return count( $ids );
}

function lt_core_relationship_source_ids( $source_type, $relationship, $target_id, $limit = 0 ) {
    if ( ! lt_core_relationship_table_exists() ) { return array(); }
    global $wpdb;
    $table = lt_core_relationship_table();
    $sql = $wpdb->prepare(
        "SELECT source_id FROM {$table} WHERE source_type=%s AND relationship=%s AND target_id=%d ORDER BY sort_order ASC, source_id ASC",
        $source_type, $relationship, absint( $target_id )
    );
    if ( $limit > 0 ) { $sql .= $wpdb->prepare( ' LIMIT %d', absint( $limit ) ); }
    return array_map( 'absint', (array) $wpdb->get_col( $sql ) );
}

function lt_core_relationship_index_upgrade() {
    $version = get_option( 'lt_core_relationship_db_version' );
    if ( '1.0' !== $version || ! lt_core_relationship_table_exists() ) {
        lt_core_install_relationship_table();
        lt_core_relationship_index_migrate_all();
    }
}
add_action( 'plugins_loaded', 'lt_core_relationship_index_upgrade', 20 );