-- Migration 011: Dynamic Profile Fields

-- 1. Profile fields definition table
CREATE TABLE IF NOT EXISTS `profile_fields` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `label` VARCHAR(255) NOT NULL,
    `field_name` VARCHAR(255) NOT NULL UNIQUE,
    `field_type` ENUM('text','textarea','email','number','select','checkbox','radio','date','url') NOT NULL DEFAULT 'text',
    `options` TEXT DEFAULT NULL,
    `placeholder` VARCHAR(255) DEFAULT NULL,
    `help_text` VARCHAR(255) DEFAULT NULL,
    `is_required` TINYINT(1) DEFAULT 0,
    `sort_order` INT DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `show_in_directory` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Profile field values table
CREATE TABLE IF NOT EXISTS `profile_field_values` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `field_id` INT NOT NULL,
    `value` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `user_field` (`user_id`, `field_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`field_id`) REFERENCES `profile_fields`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;