-- Migration 028: Add MySQL versions of tables that previously only existed in SQLite.
-- The web API (web/api/) used SQLite via includes/db.php for notes and page content.
-- web2/api/operations.php reads from MySQL ftt_notes/notes and ftt_barcode_history/barcode_history.
-- This migration creates proper MySQL equivalents so both API layers share the same DB.

-- Notes (MySQL equivalent of SQLite notes table from web/api/init_database.php)
CREATE TABLE IF NOT EXISTS notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL DEFAULT 'Note',
    content LONGTEXT NOT NULL,
    attachment_url VARCHAR(512) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_notes_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL DEFAULT 'Note',
    content LONGTEXT NOT NULL,
    attachment_url VARCHAR(512) NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ftt_notes_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Barcode scan history (used by web2/api/operations.php get_barcode_history action)
CREATE TABLE IF NOT EXISTS barcode_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL DEFAULT '',
    barcode_value VARCHAR(512) NOT NULL,
    barcode_type VARCHAR(64) NOT NULL DEFAULT 'unknown',
    raw_data TEXT NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_barcode_history_device (device_id),
    INDEX idx_barcode_history_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_barcode_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL DEFAULT '',
    barcode_value VARCHAR(512) NOT NULL,
    barcode_type VARCHAR(64) NOT NULL DEFAULT 'unknown',
    raw_data TEXT NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ftt_barcode_history_device (device_id),
    INDEX idx_ftt_barcode_history_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Page content (MySQL equivalent of SQLite app_page_content from web/api/init_database.php)
-- Used by web2/api/get_page_content.php for tabs with content_type = page.
CREATE TABLE IF NOT EXISTS app_page_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(128) NOT NULL,
    format VARCHAR(32) NOT NULL DEFAULT 'html',
    body LONGTEXT NOT NULL,
    version INT NOT NULL DEFAULT 1,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_app_page_content_slug (slug),
    INDEX idx_app_page_content_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_app_page_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(128) NOT NULL,
    format VARCHAR(32) NOT NULL DEFAULT 'html',
    body LONGTEXT NOT NULL,
    version INT NOT NULL DEFAULT 1,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_ftt_app_page_content_slug (slug),
    INDEX idx_ftt_app_page_content_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;