32. Database Schema

CREATE TABLE wp_fieldforge_field_groups (
    id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    title          varchar(255)        NOT NULL DEFAULT '',
    fields         longtext            NOT NULL,
    location_rules longtext            NOT NULL,
    menu_order     int                 NOT NULL DEFAULT 0,
    status         varchar(20)         NOT NULL DEFAULT 'publish',
    created_at     datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wp_fieldforge_values (
    id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    post_id        bigint(20) unsigned NOT NULL,
    field_group_id bigint(20) unsigned NOT NULL DEFAULT 0,
    field_name     varchar(255)        NOT NULL DEFAULT '',
    field_key      varchar(255)        NOT NULL DEFAULT '',
    field_value    longtext,
    field_type     varchar(50)         NOT NULL DEFAULT '',
    parent_id      bigint(20) unsigned NOT NULL DEFAULT 0,
    row_index      int unsigned        NOT NULL DEFAULT 0,
    layout         varchar(255)        NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    KEY post_field  (post_id, field_name),
    KEY post_group  (post_id, field_group_id),
    KEY field_key   (field_key),
    KEY parent_row  (parent_id, row_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id=1  post_id=42  field_name="team_members"  parent_id=0  row_index=0  field_type="repeater"
id=2  post_id=42  field_name="name"          parent_id=1  row_index=0  field_value="Alice"
id=3  post_id=42  field_name="role"          parent_id=1  row_index=0  field_value="Developer"
id=4  post_id=42  field_name="name"          parent_id=1  row_index=1  field_value="Bob"
id=5  post_id=42  field_name="role"          parent_id=1  row_index=1  field_value="Designer"

CREATE TABLE wp_fieldforge_options (
    id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    option_page    varchar(255)        NOT NULL DEFAULT '',
    field_name     varchar(255)        NOT NULL DEFAULT '',
    field_key      varchar(255)        NOT NULL DEFAULT '',
    field_value    longtext,
    field_type     varchar(50)         NOT NULL DEFAULT '',
    parent_id      bigint(20) unsigned NOT NULL DEFAULT 0,
    row_index      int unsigned        NOT NULL DEFAULT 0,
    layout         varchar(255)        NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    KEY page_field  (option_page, field_name),
    KEY parent_row  (parent_id, row_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wp_fieldforge_revisions (
    id             bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    group_id       bigint(20) unsigned NOT NULL,
    version        int unsigned        NOT NULL DEFAULT 1,
    title          varchar(255)        NOT NULL DEFAULT '',
    fields         longtext            NOT NULL,
    location_rules longtext            NOT NULL,
    menu_order     int                 NOT NULL DEFAULT 0,
    hash           varchar(32)         NOT NULL DEFAULT '',
    user_id        bigint(20) unsigned NOT NULL DEFAULT 0,
    message        varchar(255)        NOT NULL DEFAULT '',
    created_at     datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY group_version (group_id, version),
    KEY group_created (group_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

global $wpdb;

// Get all field values for a specific post
$values = $wpdb->get_results( $wpdb->prepare(
    "SELECT field_name, field_value, field_type
     FROM {$wpdb->prefix}fieldforge_values
     WHERE post_id = %d AND parent_id = 0",
    42
) );

// Count total values per post type
$counts = $wpdb->get_results(
    "SELECT p.post_type, COUNT(v.id) AS total_values
     FROM {$wpdb->prefix}fieldforge_values v
     JOIN {$wpdb->posts} p ON p.ID = v.post_id
     GROUP BY p.post_type
     ORDER BY total_values DESC"
);

// Find all posts where a specific field has a value
$posts_with_price = $wpdb->get_col( $wpdb->prepare(
    "SELECT DISTINCT post_id
     FROM {$wpdb->prefix}fieldforge_values
     WHERE field_name = %s AND field_value != ''",
    'price'
) );