-- ======================================
-- HR-КАБИНЕТ LEVELUP - ТАБЛИЦЫ
-- Дата создания: 2025-12-15
-- ======================================

-- Таблица компаний
CREATE TABLE IF NOT EXISTS companies (
    company_id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(255) NOT NULL,
    bin VARCHAR(12) NOT NULL UNIQUE COMMENT 'БИН компании',
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    tariff_id INT NOT NULL,
    licenses_total INT DEFAULT 20 COMMENT 'Всего лицензий',
    licenses_used INT DEFAULT 0 COMMENT 'Использовано лицензий',
    status ENUM('active', 'suspended', 'trial') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    next_payment_date DATE,
    INDEX idx_bin (bin),
    INDEX idx_status (status),
    INDEX idx_tariff (tariff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Компании-клиенты';

-- Таблица HR-пользователей
CREATE TABLE IF NOT EXISTS hr_users (
    hr_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    position VARCHAR(100) COMMENT 'Должность',
    role ENUM('owner', 'hr_manager', 'viewer') DEFAULT 'hr_manager',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    INDEX idx_email (email),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='HR-специалисты';

-- Таблица тарифов
CREATE TABLE IF NOT EXISTS tariffs (
    tariff_id INT PRIMARY KEY AUTO_INCREMENT,
    tariff_name VARCHAR(50) NOT NULL,
    price_monthly DECIMAL(10, 2) NOT NULL,
    max_employees INT NOT NULL,
    features JSON COMMENT 'Возможности тарифа',
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Тарифные планы';

-- Заполнение тарифов
INSERT INTO tariffs (tariff_name, price_monthly, max_employees, features, description) VALUES
('HR-Start', 49000.00, 20, 
 '{"basic_analytics": true, "monthly_report": true, "voice_analysis": false, "ai_recommendations": false}',
 'Для стартапов до 20 человек'),
('HR-Pro', 99000.00, 100,
 '{"basic_analytics": true, "monthly_report": true, "voice_analysis": true, "ai_recommendations": true, "priority_support": true}',
 'Для роста бизнеса до 100 человек'),
('HR-Corporate', 249000.00, 999999,
 '{"basic_analytics": true, "monthly_report": true, "voice_analysis": true, "ai_recommendations": true, "priority_support": true, "integrations": true, "custom_reports": true, "dedicated_manager": true}',
 'Для крупных компаний');

-- Таблица платежей компаний
CREATE TABLE IF NOT EXISTS company_payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    tariff_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(50) COMMENT 'kaspi, freedom',
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    transaction_id VARCHAR(255),
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    period_start DATE COMMENT 'Начало периода оплаты',
    period_end DATE COMMENT 'Конец периода оплаты',
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id),
    INDEX idx_company (company_id),
    INDEX idx_status (status),
    INDEX idx_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Платежи компаний';

-- Обновление таблицы корпоративных сотрудников
-- Проверяем и добавляем колонки по одной
ALTER TABLE corporate_employees 
ADD COLUMN company_id INT AFTER session_id;

ALTER TABLE corporate_employees 
ADD COLUMN potential_score INT DEFAULT 0 COMMENT 'Потенциал 0-100';

ALTER TABLE corporate_employees 
ADD COLUMN risk_score INT DEFAULT 0 COMMENT 'Риск увольнения 0-100';

ALTER TABLE corporate_employees 
ADD COLUMN last_diagnostic_date TIMESTAMP NULL;

-- Таблица прогресса сотрудников
CREATE TABLE IF NOT EXISTS employee_progress (
    progress_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    skill_name VARCHAR(100) NOT NULL COMMENT 'Название навыка',
    score_before INT COMMENT 'Было',
    score_after INT COMMENT 'Стало',
    change_percent DECIMAL(5, 2) COMMENT 'Изменение в %',
    measured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_skill (skill_name),
    INDEX idx_date (measured_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Прогресс навыков сотрудников';

-- Таблица отчетов HR
CREATE TABLE IF NOT EXISTS hr_reports (
    report_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    report_type ENUM('company', 'department', 'employee') NOT NULL,
    period_start DATE,
    period_end DATE,
    file_path VARCHAR(255) COMMENT 'Путь к PDF/Excel файлу',
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    generated_by INT COMMENT 'ID HR-пользователя',
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES hr_users(hr_id) ON DELETE SET NULL,
    INDEX idx_company (company_id),
    INDEX idx_type (report_type),
    INDEX idx_date (generated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Отчеты для HR';

-- Таблица инсайтов и рекомендаций
CREATE TABLE IF NOT EXISTS hr_insights (
    insight_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    employee_session_id VARCHAR(255),
    insight_type ENUM('promotion', 'risk', 'development', 'general') NOT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    title VARCHAR(255) NOT NULL,
    description TEXT,
    action_recommended TEXT COMMENT 'Рекомендуемое действие',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    FOREIGN KEY (employee_session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_type (insight_type),
    INDEX idx_priority (priority),
    INDEX idx_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Инсайты и рекомендации для HR';

-- Таблица приглашений сотрудников
CREATE TABLE IF NOT EXISTS employee_invitations (
    invitation_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    invitation_token VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    position VARCHAR(100),
    department VARCHAR(100),
    status ENUM('pending', 'accepted', 'expired') DEFAULT 'pending',
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    accepted_at TIMESTAMP NULL,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    INDEX idx_token (invitation_token),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Приглашения сотрудников';

-- ======================================
-- ИНДЕКСЫ ДЛЯ ОПТИМИЗАЦИИ
-- ======================================

-- Индексы для быстрого поиска сотрудников компании
CREATE INDEX idx_corp_employees_company 
ON corporate_employees(company_id, last_diagnostic_date);

-- Индексы для аналитики
CREATE INDEX idx_results_potential 
ON results(session_id, potential_score, risk_score);

-- ======================================
-- VIEWS ДЛЯ УДОБСТВА
-- ======================================

-- View: Статистика по компании
CREATE OR REPLACE VIEW v_company_stats AS
SELECT 
    c.company_id,
    c.company_name,
    c.bin,
    COUNT(DISTINCT ce.session_id) as total_employees,
    c.licenses_used,
    c.licenses_total,
    ROUND(c.licenses_used * 100.0 / c.licenses_total, 2) as license_usage_percent,
    t.tariff_name,
    t.price_monthly,
    c.next_payment_date
FROM companies c
LEFT JOIN corporate_employees ce ON c.company_id = ce.company_id
LEFT JOIN tariffs t ON c.tariff_id = t.tariff_id
WHERE c.status = 'active'
GROUP BY c.company_id;

-- View: Сотрудники с высоким риском
CREATE OR REPLACE VIEW v_high_risk_employees AS
SELECT 
    ce.company_id,
    ce.session_id,
    CONCAT(ce.first_name, ' ', ce.last_name) as full_name,
    ce.position,
    ce.department,
    ce.potential_score,
    ce.risk_score,
    ce.last_diagnostic_date
FROM corporate_employees ce
WHERE ce.risk_score >= 60
ORDER BY ce.risk_score DESC;

-- View: Кандидаты на повышение
CREATE OR REPLACE VIEW v_promotion_candidates AS
SELECT 
    ce.company_id,
    ce.session_id,
    CONCAT(ce.first_name, ' ', ce.last_name) as full_name,
    ce.position,
    ce.department,
    ce.potential_score,
    ce.risk_score,
    ce.last_diagnostic_date
FROM corporate_employees ce
WHERE ce.potential_score >= 80 AND ce.risk_score < 30
ORDER BY ce.potential_score DESC;

-- ======================================
-- КОММЕНТАРИИ К ТАБЛИЦАМ
-- ======================================

-- companies: Основная таблица компаний-клиентов
-- hr_users: HR-специалисты с доступом к кабинету
-- tariffs: Тарифные планы (Start, Pro, Corporate)
-- company_payments: История платежей
-- employee_progress: Динамика навыков сотрудников
-- hr_reports: Сгенерированные отчеты (PDF/Excel)
-- hr_insights: AI-рекомендации для HR
-- employee_invitations: Приглашения для новых сотрудников

-- ======================================
-- ГОТОВО!
-- ======================================
-- Применить миграцию:
-- mysql -u root -p mvp_career < hr_tables.sql

