-- Создание таблиц для разных типов пользователей
USE mvp_career;

-- ============================================
-- 1. ТАБЛИЦА ДЛЯ ГОСТЕЙ (АНОНИМНЫХ ПОЛЬЗОВАТЕЛЕЙ)
-- ============================================
CREATE TABLE IF NOT EXISTS guests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NULL,
    age INT NULL,
    city VARCHAR(100) NULL,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id)
) COMMENT='Анонимные пользователи (гости)';

-- ============================================
-- 2. ТАБЛИЦА ДЛЯ HR-СПЕЦИАЛИСТОВ
-- ============================================
CREATE TABLE IF NOT EXISTS hr_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    company_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    position VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_company_id (company_id),
    INDEX idx_email (email)
) COMMENT='HR-специалисты';

SELECT 'Таблицы guests и hr_users созданы!' as status;

