Manually Rebuilding Custom Tables after Migration

post migration rebuild.sql

-- The Events Calendar - Custom Tables Rebuild Script
-- Run this if events are not displaying on your website
-- This script will rebuild the wp_tec_events and wp_tec_occurrences tables

-- 1. DROP old/corrupted tables
DROP TABLE IF EXISTS wp_tec_occurrences;
DROP TABLE IF EXISTS wp_tec_events;

-- 2. CREATE wp_tec_events table
CREATE TABLE wp_tec_events (
    event_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    post_id bigint(20) unsigned NOT NULL UNIQUE,
    start_date varchar(19) NOT NULL,
    end_date varchar(19) DEFAULT NULL,
    timezone varchar(30) NOT NULL DEFAULT 'UTC',
    start_date_utc varchar(19) NOT NULL,
    end_date_utc varchar(19) DEFAULT NULL,
    duration mediumint(30) DEFAULT 7200,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    hash varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. CREATE wp_tec_occurrences table
CREATE TABLE wp_tec_occurrences (
    occurrence_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    event_id bigint(20) unsigned NOT NULL,
    post_id bigint(20) unsigned NOT NULL,
    start_date datetime NOT NULL,
    start_date_utc datetime NOT NULL,
    end_date datetime NOT NULL,
    end_date_utc datetime NOT NULL,
    duration mediumint(30) DEFAULT 7200,
    hash varchar(40) NOT NULL UNIQUE,
    updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX event_id (event_id),
    INDEX idx_wp_tec_occurrences_post_id_dates (post_id, end_date, start_date),
    INDEX idx_wp_tec_occurrences_post_id_dates_utc (post_id, end_date_utc, start_date_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. POPULATE wp_tec_events from existing event posts
INSERT INTO wp_tec_events (post_id, start_date, end_date, timezone, start_date_utc, end_date_utc, duration, hash)
SELECT 
    p.ID as post_id,
    COALESCE((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventStartDate' LIMIT 1), DATE_FORMAT(p.post_date, '%Y-%m-%d %H:%i:%s')) as start_date,
    COALESCE((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventEndDate' LIMIT 1), DATE_FORMAT(DATE_ADD(p.post_date, INTERVAL 2 HOUR), '%Y-%m-%d %H:%i:%s')) as end_date,
    COALESCE((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventTimezone' LIMIT 1), 'UTC') as timezone,
    COALESCE((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventStartDateUTC' LIMIT 1), DATE_FORMAT(p.post_date, '%Y-%m-%d %H:%i:%s')) as start_date_utc,
    COALESCE((SELECT meta_value FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventEndDateUTC' LIMIT 1), DATE_FORMAT(DATE_ADD(p.post_date, INTERVAL 2 HOUR), '%Y-%m-%d %H:%i:%s')) as end_date_utc,
    COALESCE((SELECT CAST(meta_value AS UNSIGNED) FROM wp_postmeta WHERE post_id = p.ID AND meta_key = '_EventDuration' LIMIT 1), 7200) as duration,
    MD5(CONCAT(p.ID, '_', p.post_modified)) as hash
FROM wp_posts p
WHERE p.post_type = 'tribe_events' AND p.post_status IN ('publish', 'draft', 'pending');

-- 5. POPULATE wp_tec_occurrences from wp_tec_events
INSERT INTO wp_tec_occurrences (event_id, post_id, start_date, start_date_utc, end_date, end_date_utc, duration, hash)
SELECT 
    te.event_id,
    te.post_id,
    STR_TO_DATE(te.start_date, '%Y-%m-%d %H:%i:%s') as start_date,
    STR_TO_DATE(te.start_date_utc, '%Y-%m-%d %H:%i:%s') as start_date_utc,
    STR_TO_DATE(te.end_date, '%Y-%m-%d %H:%i:%s') as end_date,
    STR_TO_DATE(te.end_date_utc, '%Y-%m-%d %H:%i:%s') as end_date_utc,
    te.duration,
    MD5(CONCAT(te.event_id, '_', te.hash)) as hash
FROM wp_tec_events te;

-- 6. VERIFY - Check the results
SELECT 
    'wp_tec_events' as table_name,
    COUNT(*) as record_count
FROM wp_tec_events
UNION ALL
SELECT 
    'wp_tec_occurrences' as table_name,
    COUNT(*) as record_count
FROM wp_tec_occurrences;