<?php
/**
 * Plugin Name: TE Directory Sync
 * Plugin URI: https://techearl.com/wordpress-business-directory-update-google-sheet
 * Description: Bounded listing reconciliation with strict input validation and a database-backup gate.
 * Version: 1.1.0
 * Author: Ishan Karunaratne
 * Author URI: https://techearl.com
 * License: GPL-2.0-or-later
 * Text Domain: te-directory-sync
 */
defined( 'ABSPATH' ) || exit;

function te_directory_register() {
    register_post_type( 'listing', array(
        'label' => 'Listings', 'public' => true, 'show_in_rest' => true,
        'has_archive' => true, 'rewrite' => array( 'slug' => 'listings' ),
        'supports' => array( 'title', 'editor', 'thumbnail', 'custom-fields' ),
    ) );
    register_taxonomy( 'listing_status', 'listing', array(
        'label' => 'Listing status', 'public' => true, 'show_in_rest' => true,
    ) );
}
add_action( 'init', 'te_directory_register' );

/** Blank means preserve the current schedule; "closed" explicitly clears it. */
function te_directory_parse_hours( $raw ) {
    $raw = trim( $raw );
    if ( '' === $raw ) { return null; }
    $days = array( 'mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun' );
    $hours = array_fill_keys( $days, null );
    if ( 'closed' === strtolower( $raw ) ) { return $hours; }
    foreach ( explode( ';', $raw ) as $clause ) {
        if ( ! preg_match( '/^(Mon|Tue|Wed|Thu|Fri|Sat|Sun)(?:-(Mon|Tue|Wed|Thu|Fri|Sat|Sun))?\s+(\d{1,2}):(\d{2})-(\d{1,2}):(\d{2})$/i', trim( $clause ), $m ) ) {
            throw new RuntimeException( 'Invalid hours clause: ' . trim( $clause ) );
        }
        $from = array_search( strtolower( $m[1] ), $days, true );
        $to = $m[2] !== '' ? array_search( strtolower( $m[2] ), $days, true ) : $from;
        $open = (int) $m[3] * 60 + (int) $m[4];
        $close = (int) $m[5] * 60 + (int) $m[6];
        if ( $from > $to || (int) $m[3] > 23 || (int) $m[5] > 23
            || (int) $m[4] > 59 || (int) $m[6] > 59 || $close <= $open ) {
            throw new RuntimeException( 'Use forward day ranges and same-day times from 00:00 through 23:59.' );
        }
        for ( $i = $from; $i <= $to; $i++ ) {
            if ( null !== $hours[$days[$i]] ) { throw new RuntimeException( 'Overlapping hours clauses.' ); }
            $hours[$days[$i]] = array(
                'open' => sprintf( '%02d:%02d', $m[3], $m[4] ),
                'close' => sprintf( '%02d:%02d', $m[5], $m[6] ),
            );
        }
    }
    return $hours;
}

if ( ! defined( 'WP_CLI' ) || ! WP_CLI ) { return; }

class TE_Directory_Sync_Command {
    /**
     * Preview or apply one bounded sheet range to existing listings.
     *
     * ## OPTIONS
     *
     * --sheet=<id>
     * : Spreadsheet ID.
     * --key=<path>
     * : Private service-account JSON file outside the served site.
     * [--range=<a1>]
     * : Header plus at most 1,000 rows. Default: Listings!A1:F1001.
     * [--backup-dir=<path>]
     * : Existing private mode-0700 directory outside ABSPATH; required with --live.
     * [--live]
     * : Apply only after the whole range validates and database export succeeds.
     */
    public function pull( $args, $assoc ) {
        $lock = null;
        $journal = null;
        try {
            $live = isset( $assoc['live'] );
            $dir = null;
            if ( $live ) {
                $dir = $this->private_dir( $assoc['backup-dir'] ?? '' );
                $mask = umask( 0077 );
                try { $lock = fopen( $dir . '/te-directory.lock', 'c+b' ); }
                finally { umask( $mask ); }
                if ( ! $lock || ! flock( $lock, LOCK_EX | LOCK_NB ) ) { throw new RuntimeException( 'Another directory sync holds the lock.' ); }
            }
            $rows = $this->read_sheet( $assoc );
            if ( ! $rows || array_shift( $rows ) !== array( 'listing_id', 'name', 'phone', 'address', 'hours', 'permanently_closed' ) ) {
                throw new RuntimeException( 'Expected the six documented column names in the documented order.' );
            }
            if ( count( $rows ) > 1000 ) { throw new RuntimeException( 'Partition the input into ranges of at most 1,000 listings.' ); }
            $plan = array();
            $seen = array();
            foreach ( $rows as $offset => $row ) {
                if ( ! array_filter( $row, static fn( $v ) => '' !== trim( (string) $v ) ) ) { continue; }
                $row = array_pad( $row, 6, '' );
                $id = trim( (string) $row[0] );
                if ( count( $row ) !== 6 || '' === $id || isset( $seen[$id] ) ) {
                    throw new RuntimeException( 'Missing/duplicate ID or wrong column count at input row ' . ( $offset + 2 ) );
                }
                $seen[$id] = true;
                $closed = strtolower( trim( (string) $row[5] ) );
                if ( ! in_array( $closed, array( '', 'no', 'yes' ), true ) ) { throw new RuntimeException( "Invalid closed-state for {$id}." ); }
                $title = sanitize_text_field( (string) $row[1] );
                if ( '' === $title ) { throw new RuntimeException( "Empty listing name for {$id}." ); }
                $posts = get_posts( array(
                    'post_type' => 'listing', 'post_status' => 'any', 'posts_per_page' => 2, 'fields' => 'ids',
                    'meta_key' => 'listing_id', 'meta_value' => $id, 'no_found_rows' => true,
                ) );
                if ( count( $posts ) !== 1 || get_post_meta( $posts[0], 'listing_id', false ) !== array( $id ) ) {
                    throw new RuntimeException( "Expected exactly one existing listing with the unique ID {$id}." );
                }
                $post_id = (int) $posts[0];
                $before = $this->state( $post_id );
                $hours = te_directory_parse_hours( (string) $row[4] );
                $after = array(
                    'title' => $title,
                    'meta' => array(
                        'phone' => array( sanitize_text_field( (string) $row[2] ) ),
                        'address' => array( sanitize_text_field( (string) $row[3] ) ),
                        'hours' => null === $hours ? $before['meta']['hours'] : array( json_encode( $hours, JSON_THROW_ON_ERROR ) ),
                        'permanently_closed' => array( 'yes' === $closed ? '1' : '' ),
                    ),
                    'terms' => array( 'yes' === $closed ? 'closed' : 'open' ),
                );
                if ( $before !== $after ) { $plan[] = array( 'listing_id' => $id, 'post_id' => $post_id, 'before' => $before, 'after' => $after ); }
            }
            if ( ! $plan ) { WP_CLI::success( 'Directory already matches this range.' ); return; }
            if ( ! $live ) {
                foreach ( $plan as $entry ) { WP_CLI::line( json_encode( $entry, JSON_THROW_ON_ERROR ) ); }
                WP_CLI::success( count( $plan ) . ' listings would change. Dry run; no writes.' );
                return;
            }
            // Pre-create privately; the child dump process writes inside a
            // private directory while this parent holds the sync lock.
            $backup = $dir . '/te-directory-' . bin2hex( random_bytes( 16 ) ) . '.sql';
            $file = $this->new_file( $backup );
            fclose( $file );
            $result = WP_CLI::runcommand( 'db export ' . escapeshellarg( $backup ) . ' --single-transaction --skip-lock-tables', array(
                'return' => 'all', 'exit_error' => false,
            ) );
            clearstatcache( true, $backup );
            if ( (int) $result->return_code !== 0 || ! is_file( $backup ) || filesize( $backup ) === 0 || ! chmod( $backup, 0600 ) ) {
                throw new RuntimeException( 'Database export failed; no listing writes attempted. Check the local WP-CLI database setup.' );
            }
            $dump = fopen( $backup, 'rb' );
            if ( ! $dump || ! fsync( $dump ) ) {
                if ( is_resource( $dump ) ) { fclose( $dump ); }
                throw new RuntimeException( 'Database backup could not be synchronized; no listing writes attempted.' );
            }
            fclose( $dump );
            $digest = hash_file( 'sha256', $backup );
            if ( false === $digest ) { throw new RuntimeException( 'Database backup readback failed; no listing writes attempted.' ); }
            $journal = $this->new_file( $backup . '.jsonl' );
            $this->record( $journal, array( 'event' => 'plan', 'backup' => basename( $backup ), 'sha256' => $digest, 'entries' => $plan ) );
            WP_CLI::log( 'Database backup: ' . $backup );
            WP_CLI::log( 'Journal: ' . $backup . '.jsonl' );
            $applied = 0;
            foreach ( $plan as $entry ) {
                $id = $entry['post_id'];
                if ( $this->state( $id ) !== $entry['before'] ) { throw new RuntimeException( "Listing {$id} changed after validation; stopping." ); }
                $this->record( $journal, array( 'event' => 'attempt', 'post_id' => $id ) );
                $this->apply( $id, $entry['before'], $entry['after'] );
                if ( $this->state( $id ) !== $entry['after'] ) { throw new RuntimeException( "Listing {$id} did not match the requested state." ); }
                $this->record( $journal, array( 'event' => 'applied', 'post_id' => $id ) );
                $applied++;
            }
            WP_CLI::success( "{$applied} listings changed and verified." );
        } catch ( Throwable $error ) {
            if ( is_resource( $journal ) ) {
                try { $this->record( $journal, array( 'event' => 'error', 'message' => $error->getMessage() ) ); }
                catch ( Throwable $ignored ) { /* The preceding attempt and original backup remain the recovery inputs. */ }
            }
            WP_CLI::error( $error->getMessage() . ' A live run may be partial; inspect the private backup and journal.' );
        } finally {
            if ( is_resource( $journal ) ) { fclose( $journal ); }
            if ( is_resource( $lock ) ) { flock( $lock, LOCK_UN ); fclose( $lock ); }
        }
    }

    private function read_sheet( $assoc ) {
        $path = realpath( $assoc['key'] ?? '' );
        $web = realpath( ABSPATH );
        if ( ! $path || ! is_file( $path ) || ! $web || str_starts_with( $path, $web . DIRECTORY_SEPARATOR )
            || ( fileperms( $path ) & 0077 ) !== 0 || filesize( $path ) > 65536 ) {
            throw new RuntimeException( 'Use a private service-account key outside the web root.' );
        }
        $uploads = wp_get_upload_dir();
        foreach ( array( WP_CONTENT_DIR, $uploads['basedir'] ) as $served ) {
            $base = realpath( $served );
            if ( $base && str_starts_with( $path, $base . DIRECTORY_SEPARATOR ) ) {
                throw new RuntimeException( 'Service-account key must be outside content and uploads.' );
            }
        }
        $key = json_decode( file_get_contents( $path ), true, 512, JSON_THROW_ON_ERROR );
        if ( ! is_string( $key['client_email'] ?? null ) || ! is_string( $key['private_key'] ?? null )
            || ! preg_match( '/^[A-Za-z0-9_-]+$/', $assoc['sheet'] ?? '' ) ) { throw new RuntimeException( 'Invalid key or spreadsheet ID.' ); }
        $encode = static fn( $s ) => rtrim( strtr( base64_encode( $s ), '+/', '-_' ), '=' );
        $now = time();
        $token = $encode( json_encode( array( 'alg' => 'RS256', 'typ' => 'JWT' ), JSON_THROW_ON_ERROR ) ) . '.' . $encode( json_encode( array(
            'iss' => $key['client_email'], 'scope' => 'https://www.googleapis.com/auth/spreadsheets.readonly',
            'aud' => 'https://oauth2.googleapis.com/token', 'iat' => $now, 'exp' => $now + 3600,
        ), JSON_THROW_ON_ERROR ) );
        if ( ! openssl_sign( $token, $signature, $key['private_key'], OPENSSL_ALGO_SHA256 ) ) { throw new RuntimeException( 'Could not sign service-account request.' ); }
        $response = wp_remote_post( 'https://oauth2.googleapis.com/token', array(
            'timeout' => 20, 'body' => array( 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer', 'assertion' => $token . '.' . $encode( $signature ) ),
        ) );
        $auth = $this->response( $response );
        if ( ! is_string( $auth['access_token'] ?? null ) || '' === $auth['access_token'] ) { throw new RuntimeException( 'Token response omitted access_token.' ); }
        $url = 'https://sheets.googleapis.com/v4/spreadsheets/' . rawurlencode( $assoc['sheet'] ) . '/values/' . rawurlencode( $assoc['range'] ?? 'Listings!A1:F1001' );
        $data = $this->response( wp_remote_get( $url, array( 'timeout' => 20, 'headers' => array( 'Authorization' => 'Bearer ' . $auth['access_token'] ) ) ) );
        if ( ! is_array( $data['values'] ?? null ) ) { throw new RuntimeException( 'Sheet response omitted rows.' ); }
        return $data['values'];
    }

    private function response( $response ) {
        if ( is_wp_error( $response ) || wp_remote_retrieve_response_code( $response ) !== 200 ) { throw new RuntimeException( 'Google API request failed; no input accepted.' ); }
        return json_decode( wp_remote_retrieve_body( $response ), true, 512, JSON_THROW_ON_ERROR );
    }

    private function state( $id ) {
        global $wpdb;
        $meta = array();
        foreach ( array( 'phone', 'address', 'hours', 'permanently_closed' ) as $key ) {
            // SQL writes follow the table collation; cached reads use exact
            // keys. Reject aliases before any field in the listing changes.
            $keys = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = %s", $id, $key ) );
            if ( $wpdb->last_error || count( $keys ) > 1 || array_filter( $keys, static fn( $stored ) => $stored !== $key ) ) {
                throw new RuntimeException( "Listing {$id} has an ambiguous meta key or could not be checked: {$key}." );
            }
            $values = get_post_meta( $id, $key, false );
            if ( count( $values ) > 1 || array_filter( $values, static fn( $v ) => ! is_string( $v ) ) ) {
                throw new RuntimeException( "Listing {$id} has unsupported metadata for {$key}." );
            }
            $meta[$key] = $values;
        }
        $terms = wp_get_object_terms( $id, 'listing_status', array( 'fields' => 'slugs' ) );
        if ( is_wp_error( $terms ) || array_diff( $terms, array( 'open', 'closed' ) ) ) { throw new RuntimeException( "Listing {$id} has unsupported status terms." ); }
        sort( $terms );
        return array( 'title' => get_post_field( 'post_title', $id, 'raw' ), 'meta' => $meta, 'terms' => $terms );
    }

    private function apply( $id, $before, $after ) {
        if ( $before['title'] !== $after['title'] ) {
            $result = wp_update_post( wp_slash( array( 'ID' => $id, 'post_title' => $after['title'] ) ), true );
            if ( is_wp_error( $result ) || ! $result ) { throw new RuntimeException( "Title update failed for {$id}." ); }
        }
        foreach ( $after['meta'] as $key => $values ) {
            if ( $values === $before['meta'][$key] ) { continue; }
            if ( false === update_post_meta( $id, $key, wp_slash( $values[0] ) ) ) { throw new RuntimeException( "Meta update failed for {$id}: {$key}." ); }
        }
        if ( $after['terms'] !== $before['terms'] ) {
            $result = wp_set_object_terms( $id, $after['terms'], 'listing_status', false );
            if ( is_wp_error( $result ) ) { throw new RuntimeException( "Status update failed for {$id}." ); }
        }
    }

    private function private_dir( $path ) {
        $dir = realpath( $path );
        $web = realpath( ABSPATH );
        if ( ! $dir || ! $web || ! is_dir( $dir ) || ! is_writable( $dir ) || $dir === $web
            || str_starts_with( $dir, $web . DIRECTORY_SEPARATOR ) || ( fileperms( $dir ) & 0077 ) !== 0 ) {
            throw new RuntimeException( 'Use a writable mode-0700 backup directory outside every served path.' );
        }
        $uploads = wp_get_upload_dir();
        foreach ( array( WP_CONTENT_DIR, $uploads['basedir'] ) as $served ) {
            $base = realpath( $served );
            if ( $base && ( $dir === $base || str_starts_with( $dir, $base . DIRECTORY_SEPARATOR ) ) ) {
                throw new RuntimeException( 'Backup directory must be outside content and uploads, including external paths.' );
            }
        }
        return $dir;
    }

    private function new_file( $path ) {
        $mask = umask( 0077 );
        try { $file = fopen( $path, 'x+b' ); }
        finally { umask( $mask ); }
        if ( ! $file || ! chmod( $path, 0600 ) ) { throw new RuntimeException( 'Private file creation failed.' ); }
        return $file;
    }

    private function record( $file, $event ) {
        $text = json_encode( $event, JSON_THROW_ON_ERROR ) . "\n";
        $offset = 0;
        while ( $offset < strlen( $text ) ) {
            $written = fwrite( $file, substr( $text, $offset ) );
            if ( ! $written ) { throw new RuntimeException( 'Journal write failed.' ); }
            $offset += $written;
        }
        if ( ! fflush( $file ) || ! fsync( $file ) ) { throw new RuntimeException( 'Journal synchronization failed.' ); }
    }
}
WP_CLI::add_command( 'te-directory', 'TE_Directory_Sync_Command' );