579 lines
16 KiB
PHP
579 lines
16 KiB
PHP
<?php
|
|
/**
|
|
* Database Handler Class
|
|
*
|
|
* Handles all database operations including table creation,
|
|
* CRUD operations for Instagram posts and logging.
|
|
*
|
|
* @package Instagram_Gallery_Sync_Pro
|
|
*/
|
|
|
|
// Prevent direct access
|
|
if (!defined('ABSPATH')) {
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* Class IGSP_Database
|
|
*/
|
|
class IGSP_Database
|
|
{
|
|
|
|
/**
|
|
* Database version
|
|
*
|
|
* @var string
|
|
*/
|
|
private $db_version = '1.1.0';
|
|
|
|
/**
|
|
* Constructor
|
|
*/
|
|
public function __construct()
|
|
{
|
|
// Check for database updates on admin init
|
|
add_action('admin_init', array($this, 'maybe_upgrade'));
|
|
}
|
|
|
|
/**
|
|
* Create database tables
|
|
*
|
|
* @return void
|
|
*/
|
|
public function create_tables()
|
|
{
|
|
global $wpdb;
|
|
|
|
$charset_collate = $wpdb->get_charset_collate();
|
|
|
|
// Posts table
|
|
$posts_table = IGSP_TABLE_POSTS;
|
|
$sql_posts = "CREATE TABLE {$posts_table} (
|
|
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
instagram_id VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) NOT NULL,
|
|
image_local_path VARCHAR(500) DEFAULT NULL,
|
|
image_thumbnail_path VARCHAR(500) DEFAULT NULL,
|
|
post_url VARCHAR(500) DEFAULT NULL,
|
|
caption TEXT DEFAULT NULL,
|
|
likes_count INT(11) DEFAULT NULL,
|
|
comments_count INT(11) DEFAULT NULL,
|
|
posted_at DATETIME DEFAULT NULL,
|
|
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
file_size INT(11) DEFAULT 0,
|
|
image_width INT(11) DEFAULT 0,
|
|
image_height INT(11) DEFAULT 0,
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
is_manual TINYINT(1) DEFAULT 0,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY instagram_id (instagram_id),
|
|
KEY username (username),
|
|
KEY posted_at (posted_at),
|
|
KEY is_active (is_active)
|
|
) {$charset_collate};";
|
|
|
|
// Log table
|
|
$log_table = IGSP_TABLE_LOG;
|
|
$sql_log = "CREATE TABLE {$log_table} (
|
|
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
log_type VARCHAR(50) NOT NULL DEFAULT 'info',
|
|
message TEXT NOT NULL,
|
|
details LONGTEXT DEFAULT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
KEY log_type (log_type),
|
|
KEY created_at (created_at)
|
|
) {$charset_collate};";
|
|
|
|
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
|
|
|
|
dbDelta($sql_posts);
|
|
dbDelta($sql_log);
|
|
|
|
// Store database version
|
|
update_option('igsp_db_version', $this->db_version);
|
|
}
|
|
|
|
/**
|
|
* Check and run database upgrades if needed
|
|
*
|
|
* @return void
|
|
*/
|
|
public function maybe_upgrade()
|
|
{
|
|
$installed_version = get_option('igsp_db_version', '0');
|
|
|
|
if (version_compare($installed_version, $this->db_version, '<')) {
|
|
$this->create_tables();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Insert or update a post
|
|
*
|
|
* @param array $data Post data
|
|
* @return int|false Insert ID or false on failure
|
|
*/
|
|
public function insert_post($data)
|
|
{
|
|
global $wpdb;
|
|
|
|
// Check if post already exists
|
|
$existing = $this->get_post_by_instagram_id($data['instagram_id']);
|
|
|
|
if ($existing) {
|
|
// Update existing post
|
|
return $this->update_post($existing->id, $data);
|
|
}
|
|
|
|
// Sanitize data
|
|
$insert_data = array(
|
|
'instagram_id' => sanitize_text_field($data['instagram_id']),
|
|
'username' => sanitize_text_field($data['username']),
|
|
'image_local_path' => isset($data['image_local_path']) ? sanitize_text_field($data['image_local_path']) : '',
|
|
'image_thumbnail_path' => isset($data['image_thumbnail_path']) ? sanitize_text_field($data['image_thumbnail_path']) : '',
|
|
'post_url' => isset($data['post_url']) ? esc_url_raw($data['post_url']) : '',
|
|
'caption' => isset($data['caption']) ? wp_kses_post($data['caption']) : '',
|
|
'likes_count' => isset($data['likes_count']) ? absint($data['likes_count']) : null,
|
|
'comments_count' => isset($data['comments_count']) ? absint($data['comments_count']) : null,
|
|
'posted_at' => isset($data['posted_at']) ? sanitize_text_field($data['posted_at']) : current_time('mysql'),
|
|
'synced_at' => current_time('mysql'),
|
|
'file_size' => isset($data['file_size']) ? absint($data['file_size']) : 0,
|
|
'image_width' => isset($data['image_width']) ? absint($data['image_width']) : 0,
|
|
'image_height' => isset($data['image_height']) ? absint($data['image_height']) : 0,
|
|
'is_active' => isset($data['is_active']) ? absint($data['is_active']) : 1,
|
|
);
|
|
|
|
$result = $wpdb->insert(
|
|
IGSP_TABLE_POSTS,
|
|
$insert_data,
|
|
array(
|
|
'%s',
|
|
'%s',
|
|
'%s',
|
|
'%s',
|
|
'%s',
|
|
'%s',
|
|
'%d',
|
|
'%d',
|
|
'%s',
|
|
'%s',
|
|
'%d',
|
|
'%d',
|
|
'%d',
|
|
'%d'
|
|
)
|
|
);
|
|
|
|
return $result ? $wpdb->insert_id : false;
|
|
}
|
|
|
|
/**
|
|
* Update a post
|
|
*
|
|
* @param int $id Post ID
|
|
* @param array $data Post data
|
|
* @return bool
|
|
*/
|
|
public function update_post($id, $data)
|
|
{
|
|
global $wpdb;
|
|
|
|
$update_data = array();
|
|
$formats = array();
|
|
|
|
// Map of fields and their formats
|
|
$field_map = array(
|
|
'image_local_path' => '%s',
|
|
'image_thumbnail_path' => '%s',
|
|
'post_url' => '%s',
|
|
'caption' => '%s',
|
|
'likes_count' => '%d',
|
|
'comments_count' => '%d',
|
|
'posted_at' => '%s',
|
|
'file_size' => '%d',
|
|
'image_width' => '%d',
|
|
'image_height' => '%d',
|
|
'is_active' => '%d',
|
|
);
|
|
|
|
foreach ($field_map as $field => $format) {
|
|
if (isset($data[$field])) {
|
|
$update_data[$field] = $data[$field];
|
|
$formats[] = $format;
|
|
}
|
|
}
|
|
|
|
// Always update synced_at
|
|
$update_data['synced_at'] = current_time('mysql');
|
|
$formats[] = '%s';
|
|
|
|
return $wpdb->update(
|
|
IGSP_TABLE_POSTS,
|
|
$update_data,
|
|
array('id' => $id),
|
|
$formats,
|
|
array('%d')
|
|
) !== false;
|
|
}
|
|
|
|
/**
|
|
* Get post by Instagram ID
|
|
*
|
|
* @param string $instagram_id Instagram post ID
|
|
* @return object|null
|
|
*/
|
|
public function get_post_by_instagram_id($instagram_id)
|
|
{
|
|
global $wpdb;
|
|
|
|
return $wpdb->get_row(
|
|
$wpdb->prepare(
|
|
"SELECT * FROM " . IGSP_TABLE_POSTS . " WHERE instagram_id = %s",
|
|
$instagram_id
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Get all posts
|
|
*
|
|
* @param array $args Query arguments
|
|
* @return array
|
|
*/
|
|
public function get_posts($args = array())
|
|
{
|
|
global $wpdb;
|
|
|
|
$defaults = array(
|
|
'limit' => 12,
|
|
'offset' => 0,
|
|
'order' => 'newest',
|
|
'active' => true,
|
|
'username' => '',
|
|
'search' => '',
|
|
);
|
|
|
|
$args = wp_parse_args($args, $defaults);
|
|
|
|
// Build query
|
|
$where = array('1=1');
|
|
$values = array();
|
|
|
|
if ($args['active']) {
|
|
$where[] = 'is_active = 1';
|
|
}
|
|
|
|
if (!empty($args['username'])) {
|
|
$where[] = 'username = %s';
|
|
$values[] = $args['username'];
|
|
}
|
|
|
|
if (!empty($args['search'])) {
|
|
$where[] = '(caption LIKE %s OR username LIKE %s)';
|
|
$search_term = '%' . $wpdb->esc_like($args['search']) . '%';
|
|
$values[] = $search_term;
|
|
$values[] = $search_term;
|
|
}
|
|
|
|
// Order
|
|
switch ($args['order']) {
|
|
case 'oldest':
|
|
$order = 'posted_at ASC';
|
|
break;
|
|
case 'random':
|
|
$order = 'RAND()';
|
|
break;
|
|
case 'newest':
|
|
default:
|
|
$order = 'posted_at DESC';
|
|
break;
|
|
}
|
|
|
|
$where_clause = implode(' AND ', $where);
|
|
|
|
// Handle limit
|
|
$limit_clause = '';
|
|
if ($args['limit'] > 0) {
|
|
$limit_clause = $wpdb->prepare('LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
|
|
}
|
|
|
|
$query = "SELECT * FROM " . IGSP_TABLE_POSTS . " WHERE {$where_clause} ORDER BY {$order} {$limit_clause}";
|
|
|
|
if (!empty($values)) {
|
|
$query = $wpdb->prepare($query, $values);
|
|
}
|
|
|
|
return $wpdb->get_results($query);
|
|
}
|
|
|
|
/**
|
|
* Count posts
|
|
*
|
|
* @param bool $active_only Count only active posts
|
|
* @return int
|
|
*/
|
|
public function count_posts($active_only = true)
|
|
{
|
|
global $wpdb;
|
|
|
|
$where = $active_only ? 'WHERE is_active = 1' : '';
|
|
|
|
return (int) $wpdb->get_var(
|
|
"SELECT COUNT(*) FROM " . IGSP_TABLE_POSTS . " {$where}"
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Count posts with optional filters
|
|
*
|
|
* @param array $args Filter arguments
|
|
* @return int
|
|
*/
|
|
public function count_posts_filtered($args = array())
|
|
{
|
|
global $wpdb;
|
|
|
|
$defaults = array(
|
|
'active' => null,
|
|
'search' => '',
|
|
);
|
|
|
|
$args = wp_parse_args($args, $defaults);
|
|
|
|
$where = array('1=1');
|
|
$values = array();
|
|
|
|
if ($args['active'] !== null) {
|
|
$where[] = 'is_active = %d';
|
|
$values[] = $args['active'] ? 1 : 0;
|
|
}
|
|
|
|
if (!empty($args['search'])) {
|
|
$where[] = '(caption LIKE %s OR username LIKE %s)';
|
|
$search_term = '%' . $wpdb->esc_like($args['search']) . '%';
|
|
$values[] = $search_term;
|
|
$values[] = $search_term;
|
|
}
|
|
|
|
$where_clause = implode(' AND ', $where);
|
|
$query = "SELECT COUNT(*) FROM " . IGSP_TABLE_POSTS . " WHERE {$where_clause}";
|
|
|
|
if (!empty($values)) {
|
|
$query = $wpdb->prepare($query, $values);
|
|
}
|
|
|
|
return (int) $wpdb->get_var($query);
|
|
}
|
|
|
|
/**
|
|
* Toggle post active status
|
|
*
|
|
* @param int $id Post ID
|
|
* @return bool
|
|
*/
|
|
public function toggle_post_active($id)
|
|
{
|
|
global $wpdb;
|
|
|
|
$post = $this->get_post($id);
|
|
if (!$post) {
|
|
return false;
|
|
}
|
|
|
|
$new_status = $post->is_active ? 0 : 1;
|
|
|
|
return $wpdb->update(
|
|
IGSP_TABLE_POSTS,
|
|
array('is_active' => $new_status),
|
|
array('id' => $id),
|
|
array('%d'),
|
|
array('%d')
|
|
) !== false;
|
|
}
|
|
|
|
/**
|
|
* Insert a manually added post
|
|
*
|
|
* @param array $data Post data
|
|
* @return int|false Insert ID or false on failure
|
|
*/
|
|
public function insert_manual_post($data)
|
|
{
|
|
global $wpdb;
|
|
|
|
$instagram_id = 'manual_' . time() . '_' . wp_rand(1000, 9999);
|
|
|
|
$insert_data = array(
|
|
'instagram_id' => $instagram_id,
|
|
'username' => sanitize_text_field($data['username'] ?? get_option('igsp_username', 'manual')),
|
|
'image_local_path' => isset($data['image_local_path']) ? sanitize_text_field($data['image_local_path']) : '',
|
|
'image_thumbnail_path' => isset($data['image_thumbnail_path']) ? sanitize_text_field($data['image_thumbnail_path']) : '',
|
|
'post_url' => isset($data['post_url']) ? esc_url_raw($data['post_url']) : '',
|
|
'caption' => isset($data['caption']) ? wp_kses_post($data['caption']) : '',
|
|
'likes_count' => 0,
|
|
'comments_count' => 0,
|
|
'posted_at' => isset($data['posted_at']) && !empty($data['posted_at']) ? sanitize_text_field($data['posted_at']) : current_time('mysql'),
|
|
'synced_at' => current_time('mysql'),
|
|
'file_size' => isset($data['file_size']) ? absint($data['file_size']) : 0,
|
|
'image_width' => isset($data['image_width']) ? absint($data['image_width']) : 0,
|
|
'image_height' => isset($data['image_height']) ? absint($data['image_height']) : 0,
|
|
'is_active' => 1,
|
|
'is_manual' => 1,
|
|
);
|
|
|
|
$result = $wpdb->insert(
|
|
IGSP_TABLE_POSTS,
|
|
$insert_data,
|
|
array(
|
|
'%s', '%s', '%s', '%s', '%s', '%s',
|
|
'%d', '%d', '%s', '%s', '%d', '%d', '%d', '%d', '%d'
|
|
)
|
|
);
|
|
|
|
return $result ? $wpdb->insert_id : false;
|
|
}
|
|
|
|
/**
|
|
* Delete post
|
|
*
|
|
* @param int $id Post ID
|
|
* @return bool
|
|
*/
|
|
public function delete_post($id)
|
|
{
|
|
global $wpdb;
|
|
|
|
// Get post for file cleanup
|
|
$post = $this->get_post($id);
|
|
|
|
if ($post) {
|
|
// Delete associated files
|
|
$this->delete_post_files($post);
|
|
}
|
|
|
|
return $wpdb->delete(
|
|
IGSP_TABLE_POSTS,
|
|
array('id' => $id),
|
|
array('%d')
|
|
) !== false;
|
|
}
|
|
|
|
/**
|
|
* Get single post by ID
|
|
*
|
|
* @param int $id Post ID
|
|
* @return object|null
|
|
*/
|
|
public function get_post($id)
|
|
{
|
|
global $wpdb;
|
|
|
|
return $wpdb->get_row(
|
|
$wpdb->prepare(
|
|
"SELECT * FROM " . IGSP_TABLE_POSTS . " WHERE id = %d",
|
|
$id
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Delete post image files
|
|
*
|
|
* @param object $post Post object
|
|
* @return void
|
|
*/
|
|
private function delete_post_files($post)
|
|
{
|
|
$upload_dir = wp_upload_dir();
|
|
$base_path = $upload_dir['basedir'];
|
|
|
|
// Delete main image
|
|
if (!empty($post->image_local_path)) {
|
|
$file_path = $base_path . '/' . $post->image_local_path;
|
|
if (file_exists($file_path)) {
|
|
unlink($file_path);
|
|
}
|
|
}
|
|
|
|
// Delete thumbnail
|
|
if (!empty($post->image_thumbnail_path)) {
|
|
$thumb_path = $base_path . '/' . $post->image_thumbnail_path;
|
|
if (file_exists($thumb_path)) {
|
|
unlink($thumb_path);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Delete old posts
|
|
*
|
|
* @param int $days Delete posts older than X days
|
|
* @return int Number of deleted posts
|
|
*/
|
|
public function delete_old_posts($days)
|
|
{
|
|
global $wpdb;
|
|
|
|
if ($days <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
$date_threshold = date('Y-m-d H:i:s', strtotime("-{$days} days"));
|
|
|
|
// Get posts to delete for file cleanup
|
|
$posts = $wpdb->get_results(
|
|
$wpdb->prepare(
|
|
"SELECT * FROM " . IGSP_TABLE_POSTS . " WHERE synced_at < %s",
|
|
$date_threshold
|
|
)
|
|
);
|
|
|
|
foreach ($posts as $post) {
|
|
$this->delete_post_files($post);
|
|
}
|
|
|
|
// Delete from database
|
|
return $wpdb->query(
|
|
$wpdb->prepare(
|
|
"DELETE FROM " . IGSP_TABLE_POSTS . " WHERE synced_at < %s",
|
|
$date_threshold
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Delete all posts
|
|
*
|
|
* @return bool
|
|
*/
|
|
public function delete_all_posts()
|
|
{
|
|
global $wpdb;
|
|
|
|
// Get all posts for file cleanup
|
|
$posts = $wpdb->get_results("SELECT * FROM " . IGSP_TABLE_POSTS);
|
|
|
|
foreach ($posts as $post) {
|
|
$this->delete_post_files($post);
|
|
}
|
|
|
|
// Truncate table
|
|
return $wpdb->query("TRUNCATE TABLE " . IGSP_TABLE_POSTS) !== false;
|
|
}
|
|
|
|
/**
|
|
* Get existing Instagram IDs
|
|
*
|
|
* @return array
|
|
*/
|
|
public function get_existing_instagram_ids()
|
|
{
|
|
global $wpdb;
|
|
|
|
return $wpdb->get_col(
|
|
"SELECT instagram_id FROM " . IGSP_TABLE_POSTS
|
|
);
|
|
}
|
|
}
|