Shift Old Posts Archive

### **1. Backup Your Database**
Before making any modifications, **always take a backup** of your database to prevent accidental data loss.

### **2. Identify Old Posts**
Run the following SQL query to find posts older than 2 years:

SELECT ID, post_date, post_title FROM wp_posts 
WHERE post_type = 'post' 
AND post_date < NOW() - INTERVAL 2 YEAR;

### **3. Move Data to an Archive Table**
If you want to move (not delete) old posts and metadata, create an archive table and insert data into it.

#### **3.1. Create Archive Tables**

CREATE TABLE wp_posts_archive LIKE wp_posts;
CREATE TABLE wp_postmeta_archive LIKE wp_postmeta;

#### **3.2. Move Old Posts to Archive**

INSERT INTO wp_posts_archive SELECT * FROM wp_posts 
WHERE post_type = 'post' 
AND post_date < NOW() - INTERVAL 2 YEAR;

#### **3.3. Move Corresponding Post Meta**

INSERT INTO wp_postmeta_archive 
SELECT wp_postmeta.* FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'post' 
AND wp_posts.post_date < NOW() - INTERVAL 2 YEAR;

### **4. Delete the Old Data**
Once you've moved the data, delete the old records from the main tables.

#### **4.1. Delete Post Metadata First**

DELETE wp_postmeta FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'post' 
AND wp_posts.post_date < NOW() - INTERVAL 2 YEAR;

#### **4.2. Delete Old Posts**

DELETE FROM wp_posts 
WHERE post_type = 'post' 
AND post_date < NOW() - INTERVAL 2 YEAR;

### **5. Optimize Tables (Optional)**
To reclaim space and optimize performance, run:

OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;

—

### **Alternative: Change Post Status Instead of Moving**
If you just want to mark old posts as "archived" instead of moving them:

UPDATE wp_posts 
SET post_status = 'archived'
WHERE post_type = 'post' 
AND post_date < NOW() - INTERVAL 2 YEAR;

This keeps the posts in the system but excludes them from regular listings.

—

### **Additional Considerations**
– If you want to **automate** this, create a scheduled task (Cron Job) in WordPress or the server.
– If using **custom post types**, replace `'post'` with the relevant `post_type`.
– If you’re using a **large database**, consider using batched deletion to avoid timeouts.

Would you like help setting up a WordPress cron job for this? 🚀

### **Option 1: Keep Data in `wp_posts` but Change `post_status`**
Instead of moving or deleting orders, update their status to something like `"archived"`, and modify your queries to show archived orders when necessary.

#### **Mark Orders as Archived**

UPDATE wp_posts 
SET post_status = 'archived'
WHERE post_type = 'shop_order' 
AND post_date < NOW() - INTERVAL 2 YEAR;

#### **Modify WooCommerce Queries**
If using WooCommerce, you need to modify how orders are queried. Add `'archived'` to the `post_status` in your order query:

$args = [
    'post_type'   => 'shop_order',
    'post_status' => ['wc-completed', 'wc-processing', 'archived'],
    'author'      => get_current_user_id(),
    'date_query'  => [
        'before' => date('Y-m-d', strtotime('-2 years'))
    ],
];

$orders = get_posts($args);

—

### **Option 2: Move Orders to an Archive Table but Provide a Frontend Query**
If you still want to move old orders to `wp_posts_archive`, allow users to query archived orders from a **custom database query** in WordPress.

#### **Move Orders to Archive**

CREATE TABLE wp_posts_archive LIKE wp_posts;
CREATE TABLE wp_postmeta_archive LIKE wp_postmeta;

INSERT INTO wp_posts_archive 
SELECT * FROM wp_posts 
WHERE post_type = 'shop_order' 
AND post_date < NOW() - INTERVAL 2 YEAR;

INSERT INTO wp_postmeta_archive 
SELECT wp_postmeta.* FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'shop_order' 
AND wp_posts.post_date < NOW() - INTERVAL 2 YEAR;

DELETE wp_postmeta FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'shop_order' 
AND wp_posts.post_date < NOW() - INTERVAL 2 YEAR;

DELETE FROM wp_posts 
WHERE post_type = 'shop_order' 
AND post_date < NOW() - INTERVAL 2 YEAR;

#### **Query Archived Orders in WordPress**
Modify your WooCommerce account page (or add a new endpoint) to fetch orders from the archive:

global $wpdb;
$user_id = get_current_user_id();

$archived_orders = $wpdb->get_results("
    SELECT * FROM wp_posts_archive
    WHERE post_type = 'shop_order' 
    AND post_author = $user_id
    ORDER BY post_date DESC
");

—

### **Option 3: Use a Separate Archive Page for Old Orders**
If you move orders, create a **separate "Order Archive" page** where users can access their old orders via a different query.

1. Add a custom endpoint in WooCommerce:

add_action('init', function() {
       add_rewrite_endpoint('archived-orders', EP_ROOT | EP_PAGES);
   });

2. Query archived orders on the new page.

—

### **Option 4: Store Older Orders in a Different Table and Use a Custom API**
Instead of using the default WooCommerce tables, store older orders in a **custom table** and build an API to retrieve them.

#### **Create a Custom Table for Archived Orders**

CREATE TABLE wp_archived_orders LIKE wp_posts;
CREATE TABLE wp_archived_order_meta LIKE wp_postmeta;

Move orders there and create a custom endpoint in WordPress that fetches orders from `wp_archived_orders`.

—

### **Best Approach?**
– If users frequently need access to old orders → **Option 1 (Change `post_status`)**
– If old orders are rarely accessed but should still be available → **Option 2 (Move & Query)**
– If you want a clean WooCommerce interface without cluttering active orders → **Option 3 (Separate Page)**
– If you are handling massive data and performance is a concern → **Option 4 (Custom Table & API)**

Would you like help implementing one of these in WooCommerce? 🚀