-- ══════════════════════════════════════════════════════════════
-- 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;
