-- ============================================================
-- profile_section_items
-- Stores individual media items (photo / video / document)
-- that belong to a custom profile section.
-- Text sections keep their content in profile_sections.content.
-- ============================================================

CREATE TABLE IF NOT EXISTS `profile_section_items` (
    `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `section_id`    INT UNSIGNED  NOT NULL,
    `file_path`     VARCHAR(500)  NULL DEFAULT NULL,
    `url`           VARCHAR(1000) NULL DEFAULT NULL,
    `original_name` VARCHAR(255)  NULL DEFAULT NULL,
    `file_size`     INT UNSIGNED  NULL DEFAULT NULL,
    `mime_type`     VARCHAR(100)  NULL DEFAULT NULL,
    `sort_order`    SMALLINT      NOT NULL DEFAULT 0,
    `created_at`    DATETIME      NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_psi_section` (`section_id`, `sort_order`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Migrate existing single-item sections ──────────────────────────────────
-- Rows in profile_sections that already have a file_path or url are moved
-- into the new items table so no data is lost.

INSERT INTO `profile_section_items`
    (`section_id`, `file_path`, `url`, `original_name`, `file_size`, `mime_type`, `sort_order`, `created_at`)
SELECT
    `id`, `file_path`, `url`, `original_name`, `file_size`, `mime_type`, 0, `created_at`
FROM `profile_sections`
WHERE `section_type` IN ('photo', 'video', 'document')
  AND (`file_path` IS NOT NULL OR `url` IS NOT NULL);