-- Public ID obfuscation table
CREATE TABLE IF NOT EXISTS public_ids (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(20) NOT NULL,
    internal_id BIGINT NOT NULL,
    public_id VARCHAR(10) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_entity (entity_type, internal_id),
    UNIQUE KEY uq_public (entity_type, public_id),
    INDEX idx_lookup (entity_type, public_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FAQ system
CREATE TABLE IF NOT EXISTS faqs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    public_id VARCHAR(10) DEFAULT NULL,
    store_id INT DEFAULT NULL COMMENT 'NULL = universal, store_id = per-store custom',
    tab_group VARCHAR(255) DEFAULT NULL COMMENT 'Encrypted - grouping label for tab system',
    question TEXT NOT NULL COMMENT 'Encrypted',
    answer TEXT NOT NULL COMMENT 'Encrypted',
    display_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_store (store_id),
    INDEX idx_active (is_active, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
