-- ══════════════════════════════════════════════════════════════ -- Acrylic Frame Nigeria — plain PHP/MySQL schema -- Replaces: WordPress core tables, WooCommerce, and the plugin's -- own wp_afn_order_sessions — while keeping every column the -- plugin's logic actually depends on. -- ══════════════════════════════════════════════════════════════ SET NAMES utf8mb4; -- ── 1. ORDER SESSIONS (was wp_afn_order_sessions) ── -- Same shape as the plugin's table, 1:1 — see afn_session_install() -- in the original plugin for why each column exists (deposit vs. -- meta-lead tracked separately, soft-delete via deleted_at, etc.) CREATE TABLE IF NOT EXISTS afn_order_sessions ( session_id VARCHAR(64) NOT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, data LONGTEXT NULL, deposit_paid TINYINT(1) NOT NULL DEFAULT 0, deposit_reference VARCHAR(100) NULL, processed_at DATETIME NULL, meta_lead_sent TINYINT(1) NOT NULL DEFAULT 0, meta_lead_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, meta_lead_last_attempt DATETIME NULL, meta_lead_http_code SMALLINT NULL, meta_lead_error VARCHAR(255) NULL, deleted_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (session_id), KEY updated_at (updated_at), KEY deposit_reference (deposit_reference), KEY meta_lead_retry (deposit_paid, meta_lead_sent, meta_lead_attempts), KEY deleted_at (deleted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 2. PRODUCTS (replaces WooCommerce entirely) ── CREATE TABLE IF NOT EXISTS afn_products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, size_label VARCHAR(60) NULL, -- e.g. "24 x 36" price DECIMAL(12,2) NOT NULL, image_url TEXT NULL, active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 3. SETTINGS (replaces get_option/update_option for the -- Control Panel — one JSON blob keyed like the plugin's own -- afn_control_panel_get_settings(), so the deep-merge-with- -- defaults logic ports directly) ── CREATE TABLE IF NOT EXISTS afn_settings ( setting_key VARCHAR(190) NOT NULL PRIMARY KEY, setting_value LONGTEXT NULL, updated_at DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 4. ADMIN USERS (replaces WordPress's user/role system) ── CREATE TABLE IF NOT EXISTS afn_admin_users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(150) NULL, created_at DATETIME NOT NULL, last_login_at DATETIME NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 5. WEBHOOK LOG (was logged into an option/transient in the -- plugin's admin panel display — given a real table here) ── CREATE TABLE IF NOT EXISTS afn_webhook_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, gateway VARCHAR(30) NOT NULL, status VARCHAR(30) NOT NULL, -- 'received' | 'verified' | 'ignored' | 'error' detail TEXT NULL, created_at DATETIME NOT NULL, KEY gateway (gateway), KEY created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 6. LEAD EMAIL LOG (HOT Lead / Abandoned Lead / Pending Deposit / -- Payment Confirmation notifications) ── CREATE TABLE IF NOT EXISTS afn_lead_email_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, lead_type VARCHAR(40) NOT NULL, status VARCHAR(20) NOT NULL, -- 'sent' | 'failed' detail TEXT NULL, notification_id VARCHAR(100) NULL, created_at DATETIME NOT NULL, KEY lead_type (lead_type), KEY created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 7. FEED POSTS (new — the Instagram-style feed, per the -- architecture doc §6. Not a duplicate of products: a post may -- reference a product_id, price/name are read from afn_products -- at render time) ── CREATE TABLE IF NOT EXISTS afn_posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, type VARCHAR(30) NOT NULL DEFAULT 'product', -- product|process|lifestyle|transformation|objection_handling|social_proof product_id BIGINT UNSIGNED NULL, media_url TEXT NULL, media_type VARCHAR(10) NOT NULL DEFAULT 'video', -- video|image thumbnail_url TEXT NULL, caption TEXT NULL, detail_chips JSON NULL, -- ["5mm acrylic","Ready to hang"] published TINYINT(1) NOT NULL DEFAULT 0, featured TINYINT(1) NOT NULL DEFAULT 0, order_index INT NOT NULL DEFAULT 0, category VARCHAR(60) NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, KEY published (published), KEY order_index (order_index), FOREIGN KEY (product_id) REFERENCES afn_products(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 8. FEED ANALYTICS EVENTS (new — architecture doc §8) ── CREATE TABLE IF NOT EXISTS afn_feed_analytics_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id VARCHAR(64) NULL, event_type VARCHAR(40) NOT NULL, post_id BIGINT UNSIGNED NULL, product_id BIGINT UNSIGNED NULL, metadata JSON NULL, created_at DATETIME NOT NULL, KEY event_type (event_type), KEY post_id (post_id), KEY session_id (session_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 9. BUSINESS PROFILE (identity-layer header content, admin-editable) ── CREATE TABLE IF NOT EXISTS afn_business_profile ( id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1, name VARCHAR(190) NOT NULL DEFAULT 'Acrylic Frame Nigeria', category_label VARCHAR(120) NOT NULL DEFAULT 'Acrylic Frame Shop', logo_url TEXT NULL, cover_media_url TEXT NULL, rating DECIMAL(2,1) NOT NULL DEFAULT 4.9, review_count INT NOT NULL DEFAULT 0, trust_strip VARCHAR(255) NULL, -- "500+ frames delivered · Lagos, PH, Abuja, Enugu" reply_time_label VARCHAR(120) NULL, -- "Replies fast" (see §10.5 — avoid a bare "Open 24 hours" claim) whatsapp_number VARCHAR(20) NULL, phone_number VARCHAR(20) NULL, address VARCHAR(255) NULL, updated_at DATETIME NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;