-- ============================================
-- ПОЛНАЯ СХЕМА БД MVP CAREER
-- 33 таблицы + 6 представлений
-- Создано из локальной БД (mysqldump + views)
-- ============================================

-- Удаляем временные представления если есть

-- Удаляем представления перед созданием таблиц (если есть)
DROP VIEW IF EXISTS `v_company_stats`;
DROP VIEW IF EXISTS `v_diagnostics_stats`;
DROP VIEW IF EXISTS `v_employees_with_development`;
DROP VIEW IF EXISTS `v_high_risk_employees`;
DROP VIEW IF EXISTS `v_promotion_candidates`;
DROP VIEW IF EXISTS `v_recent_interactions`;

--
-- ------------------------------------------------------


--
-- Table structure for table `analytics_snapshots`
--

CREATE TABLE `analytics_snapshots` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `snapshot_date` date NOT NULL,
  `overall_progress` int DEFAULT '0' COMMENT 'Total progress percentage',
  `confidence_level` int DEFAULT '0',
  `presentation_skill` int DEFAULT '0',
  `creativity_level` int DEFAULT '0',
  `voice_confidence` int DEFAULT '0',
  `voice_clarity` int DEFAULT '0',
  `voice_tempo` int DEFAULT '0',
  `stress_index` int DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_snapshot` (`session_id`,`snapshot_date`),
  KEY `idx_session` (`session_id`),
  KEY `idx_date` (`snapshot_date`),
  CONSTRAINT `analytics_snapshots_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `answers`
--

CREATE TABLE `answers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `question_id` int NOT NULL,
  `answer_value` int NOT NULL,
  `time_ms` int DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_answer` (`session_id`,`question_id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1689 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `company_id` int NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `bin` varchar(12) NOT NULL COMMENT 'БИН компании',
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `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 NULL DEFAULT CURRENT_TIMESTAMP,
  `next_payment_date` date DEFAULT NULL,
  PRIMARY KEY (`company_id`),
  UNIQUE KEY `bin` (`bin`),
  KEY `idx_bin` (`bin`),
  KEY `idx_status` (`status`),
  KEY `idx_tariff` (`tariff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Компании-клиенты';

--
-- Table structure for table `company_payments`
--

CREATE TABLE `company_payments` (
  `payment_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `tariff_id` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL COMMENT 'kaspi, freedom',
  `status` enum('pending','completed','failed','refunded') DEFAULT 'pending',
  `transaction_id` varchar(255) DEFAULT NULL,
  `payment_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `period_start` date DEFAULT NULL COMMENT 'Начало периода оплаты',
  `period_end` date DEFAULT NULL COMMENT 'Конец периода оплаты',
  PRIMARY KEY (`payment_id`),
  KEY `tariff_id` (`tariff_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_status` (`status`),
  KEY `idx_date` (`payment_date`),
  CONSTRAINT `company_payments_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `company_payments_ibfk_2` FOREIGN KEY (`tariff_id`) REFERENCES `tariffs` (`tariff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Платежи компаний';

--
-- Table structure for table `corporate_employees`
--

CREATE TABLE `corporate_employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_id` int NOT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `middle_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `department` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `potential_score` int DEFAULT '0' COMMENT 'Потенциал 0-100',
  `risk_score` int DEFAULT '0' COMMENT 'Риск увольнения 0-100',
  `last_diagnostic_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_corp_employees_company` (`company_id`,`last_diagnostic_date`),
  CONSTRAINT `corporate_employees_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `diagnostic_results`
--

CREATE TABLE `diagnostic_results` (
  `result_id` int NOT NULL AUTO_INCREMENT,
  `employee_session_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `diagnostic_id` int DEFAULT NULL,
  `diagnostic_type` enum('riasec','voice','games','stress','full') COLLATE utf8mb4_unicode_ci NOT NULL,
  `completed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `scores_json` json DEFAULT NULL,
  `insights_json` json DEFAULT NULL,
  `recommendations_json` json DEFAULT NULL,
  PRIMARY KEY (`result_id`),
  KEY `idx_employee` (`employee_session_id`),
  CONSTRAINT `fk_diagnostic_results_session` FOREIGN KEY (`employee_session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `diagnostic_templates`
--

CREATE TABLE `diagnostic_templates` (
  `template_id` int NOT NULL AUTO_INCREMENT,
  `template_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `target_role` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `includes_riasec` tinyint(1) DEFAULT '1',
  `includes_voice` tinyint(1) DEFAULT '1',
  `includes_games` tinyint(1) DEFAULT '1',
  `includes_stress` tinyint(1) DEFAULT '0',
  `duration_minutes` int DEFAULT '15',
  `competencies_weights` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`template_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `diagnostics`
--

CREATE TABLE `diagnostics` (
  `diagnostic_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `template_id` int DEFAULT NULL,
  `diagnostic_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_employees` json DEFAULT NULL,
  `target_departments` json DEFAULT NULL,
  `deadline_date` date DEFAULT NULL,
  `status` enum('draft','active','completed','cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'draft',
  `total_invited` int DEFAULT '0',
  `total_completed` int DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`diagnostic_id`),
  KEY `idx_company` (`company_id`),
  KEY `fk_diagnostics_template` (`template_id`),
  KEY `fk_diagnostics_hr` (`created_by`),
  CONSTRAINT `fk_diagnostics_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_diagnostics_hr` FOREIGN KEY (`created_by`) REFERENCES `hr_users` (`hr_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_diagnostics_template` FOREIGN KEY (`template_id`) REFERENCES `diagnostic_templates` (`template_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `employee_development_plans`
--

CREATE TABLE `employee_development_plans` (
  `plan_id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `goal_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `goal_description` text COLLATE utf8mb4_unicode_ci,
  `week_number` int NOT NULL,
  `checklist` json DEFAULT NULL,
  `status` enum('pending','in_progress','completed','cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `completion_percentage` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`plan_id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `fk_dev_plans_session` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `employee_interactions`
--

CREATE TABLE `employee_interactions` (
  `interaction_id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `interaction_type` enum('meeting','feedback','diagnostic','coaching','review','training') COLLATE utf8mb4_unicode_ci NOT NULL,
  `interaction_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `outcome` enum('positive','neutral','negative') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hr_user_id` int DEFAULT NULL,
  `attachments` json DEFAULT NULL,
  PRIMARY KEY (`interaction_id`),
  KEY `idx_session` (`session_id`),
  KEY `fk_interactions_hr` (`hr_user_id`),
  CONSTRAINT `fk_interactions_hr` FOREIGN KEY (`hr_user_id`) REFERENCES `hr_users` (`hr_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_interactions_session` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `employee_invitations`
--

CREATE TABLE `employee_invitations` (
  `invitation_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `email` varchar(255) NOT NULL,
  `invitation_token` varchar(255) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `status` enum('pending','accepted','expired') DEFAULT 'pending',
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  `accepted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`invitation_id`),
  UNIQUE KEY `invitation_token` (`invitation_token`),
  KEY `company_id` (`company_id`),
  KEY `idx_token` (`invitation_token`),
  KEY `idx_email` (`email`),
  KEY `idx_status` (`status`),
  CONSTRAINT `employee_invitations_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Приглашения сотрудников';

--
-- Table structure for table `employee_progress`
--

CREATE TABLE `employee_progress` (
  `progress_id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) NOT NULL,
  `skill_name` varchar(100) NOT NULL,
  `score_before` int DEFAULT NULL,
  `score_after` int DEFAULT NULL,
  `change_percent` decimal(5,2) DEFAULT NULL,
  `measured_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`progress_id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_skill` (`skill_name`),
  KEY `idx_date` (`measured_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `guests`
--

CREATE TABLE `guests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`),
  CONSTRAINT `guests_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Анонимные пользователи (гости)';

--
-- Table structure for table `hr_insights`
--

CREATE TABLE `hr_insights` (
  `insight_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `employee_session_id` varchar(255) DEFAULT NULL,
  `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,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`insight_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_type` (`insight_type`),
  KEY `idx_priority` (`priority`),
  KEY `idx_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `hr_reports`
--

CREATE TABLE `hr_reports` (
  `report_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `report_type` enum('company','department','employee') NOT NULL,
  `period_start` date DEFAULT NULL,
  `period_end` date DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL COMMENT 'Путь к PDF/Excel файлу',
  `generated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `generated_by` int DEFAULT NULL COMMENT 'ID HR-пользователя',
  PRIMARY KEY (`report_id`),
  KEY `generated_by` (`generated_by`),
  KEY `idx_company` (`company_id`),
  KEY `idx_type` (`report_type`),
  KEY `idx_date` (`generated_at`),
  CONSTRAINT `hr_reports_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `hr_reports_ibfk_2` FOREIGN KEY (`generated_by`) REFERENCES `hr_users` (`hr_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Отчеты для HR';

--
-- Table structure for table `hr_users`
--

CREATE TABLE `hr_users` (
  `hr_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL COMMENT 'Должность',
  `role` enum('owner','hr_manager','viewer') DEFAULT 'hr_manager',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`hr_id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email` (`email`),
  KEY `idx_company` (`company_id`),
  CONSTRAINT `hr_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HR-специалисты';

--
-- Table structure for table `kpi_analytics`
--

CREATE TABLE `kpi_analytics` (
  `analytics_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `department` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `avg_skills` decimal(5,2) DEFAULT NULL,
  `avg_potential` decimal(5,2) DEFAULT NULL,
  `risk_percentage` decimal(5,2) DEFAULT NULL,
  `retention_rate` decimal(5,2) DEFAULT NULL,
  `growth_indicators` json DEFAULT NULL,
  `total_employees` int DEFAULT '0',
  `high_potential_count` int DEFAULT '0',
  `high_risk_count` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`analytics_id`),
  KEY `idx_company_period` (`company_id`,`period_start`,`period_end`),
  CONSTRAINT `fk_kpi_analytics_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `micropractice_progress`
--

CREATE TABLE `micropractice_progress` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `practice_type` enum('voice','presentation','challenge') COLLATE utf8mb4_unicode_ci NOT NULL,
  `practice_date` date NOT NULL,
  `completed` tinyint(1) DEFAULT '0',
  `score` int DEFAULT NULL COMMENT 'Progress points earned',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_practice` (`session_id`,`practice_type`,`practice_date`),
  KEY `idx_session` (`session_id`),
  KEY `idx_date` (`practice_date`),
  CONSTRAINT `micropractice_progress_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `palm_analysis`
--

CREATE TABLE `palm_analysis` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `analysis_data` json DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  CONSTRAINT `palm_analysis_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'KZT',
  `payment_method` enum('kaspi','freedom','card','stripe') COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','completed','failed','refunded') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `transaction_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `reaction_answers`
--

CREATE TABLE `reaction_answers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `round` int NOT NULL,
  `selected_index` int NOT NULL,
  `time_ms` int NOT NULL,
  `correct` tinyint(1) DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `reaction_answers_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=912 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `recommended_microcourses`
--

CREATE TABLE `recommended_microcourses` (
  `recommendation_id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `course_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `course_url` text COLLATE utf8mb4_unicode_ci,
  `skill_to_improve` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` enum('high','medium','low') COLLATE utf8mb4_unicode_ci DEFAULT 'medium',
  `status` enum('recommended','started','completed','skipped') COLLATE utf8mb4_unicode_ci DEFAULT 'recommended',
  `recommended_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`recommendation_id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `fk_microcourses_session` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `results`
--

CREATE TABLE `results` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `computed_result_json` json NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  CONSTRAINT `results_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_type` enum('anonymous','guest','user','employee','hr') COLLATE utf8mb4_unicode_ci DEFAULT 'anonymous',
  `is_pro` tinyint(1) DEFAULT '0',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_user_type` (`user_type`),
  KEY `idx_is_pro` (`is_pro`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `shared_results`
--

CREATE TABLE `shared_results` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `share_platform` enum('telegram','whatsapp','vk','instagram','link') COLLATE utf8mb4_unicode_ci NOT NULL,
  `share_token` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique token for shared link',
  `views_count` int DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `share_token` (`share_token`),
  KEY `idx_session` (`session_id`),
  KEY `idx_token` (`share_token`),
  CONSTRAINT `shared_results_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `skill_recommendations`
--

CREATE TABLE `skill_recommendations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `profession_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `skill_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `skill_category` enum('technical','soft','creative') COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` int DEFAULT '1' COMMENT '1=high, 2=medium, 3=low',
  `is_clicked` tinyint(1) DEFAULT '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `skill_recommendations_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `subscription_id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `tariff_id` int NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `auto_renew` tinyint(1) DEFAULT '1',
  `payment_status` enum('active','pending','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `last_payment_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`subscription_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_status` (`payment_status`),
  KEY `fk_subscriptions_tariff` (`tariff_id`),
  CONSTRAINT `fk_subscriptions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_subscriptions_tariff` FOREIGN KEY (`tariff_id`) REFERENCES `tariffs` (`tariff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `tariffs`
--

CREATE TABLE `tariffs` (
  `tariff_id` int NOT NULL AUTO_INCREMENT,
  `tariff_name` varchar(50) NOT NULL,
  `price_monthly` decimal(10,2) NOT NULL,
  `price_yearly` decimal(10,2) DEFAULT NULL,
  `max_employees` int NOT NULL,
  `features` json DEFAULT NULL COMMENT 'Возможности тарифа',
  `description` text,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tariff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Тарифные планы';

--
-- Table structure for table `user_data`
--

CREATE TABLE `user_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  CONSTRAINT `user_data_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `user_goals`
--

CREATE TABLE `user_goals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `session_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `goal_text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_completed` tinyint(1) DEFAULT '0',
  `created_at` datetime NOT NULL,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `user_goals_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

-- ============================================
-- ПРЕДСТАВЛЕНИЯ (VIEWS)
-- ============================================

DROP VIEW IF EXISTS `v_company_stats`;
CREATE ALGORITHM=UNDEFINED VIEW `v_company_stats` AS 
select `c`.`company_id` AS `company_id`,`c`.`company_name` AS `company_name`,`t`.`tariff_name` AS `tariff_name`,count(distinct `ce`.`id`) AS `total_employees`,`c`.`licenses_total` AS `licenses_total`,`c`.`licenses_used` AS `licenses_used`,round(((`c`.`licenses_used` / nullif(`c`.`licenses_total`,0)) * 100),2) AS `license_usage_percentage`,count(distinct `dr`.`result_id`) AS `total_diagnostics`,`c`.`status` AS `company_status`,`c`.`next_payment_date` AS `next_payment_date` from (((`companies` `c` left join `tariffs` `t` on((`c`.`tariff_id` = `t`.`tariff_id`))) left join `corporate_employees` `ce` on((`c`.`company_id` = `ce`.`company_id`))) left join `diagnostic_results` `dr` on((`ce`.`session_id` = `dr`.`employee_session_id`))) group by `c`.`company_id`;

DROP VIEW IF EXISTS `v_diagnostics_stats`;
CREATE ALGORITHM=UNDEFINED VIEW `v_diagnostics_stats` AS 
select `d`.`company_id` AS `company_id`,`c`.`company_name` AS `company_name`,count(`d`.`diagnostic_id`) AS `total_diagnostics`,sum((case when (`d`.`status` = 'active') then 1 else 0 end)) AS `active_diagnostics`,sum((case when (`d`.`status` = 'completed') then 1 else 0 end)) AS `completed_diagnostics`,sum(`d`.`total_invited`) AS `total_participants`,sum(`d`.`total_completed`) AS `total_completed`,round(((sum(`d`.`total_completed`) * 100.0) / nullif(sum(`d`.`total_invited`),0)),2) AS `completion_rate` from (`diagnostics` `d` join `companies` `c` on((`d`.`company_id` = `c`.`company_id`))) group by `d`.`company_id`,`c`.`company_name`;

DROP VIEW IF EXISTS `v_employees_with_development`;
CREATE ALGORITHM=UNDEFINED VIEW `v_employees_with_development` AS 
select `ce`.`id` AS `employee_id`,`ce`.`first_name` AS `first_name`,`ce`.`last_name` AS `last_name`,`ce`.`department` AS `department`,`ce`.`company_id` AS `company_id`,count(distinct `edp`.`plan_id`) AS `total_plans`,sum((case when (`edp`.`status` = 'completed') then 1 else 0 end)) AS `completed_plans`,avg(`edp`.`completion_percentage`) AS `avg_completion`,max(`edp`.`updated_at`) AS `last_update` from (`corporate_employees` `ce` left join `employee_development_plans` `edp` on((`ce`.`session_id` = `edp`.`session_id`))) group by `ce`.`id`;

DROP VIEW IF EXISTS `v_high_risk_employees`;
CREATE ALGORITHM=UNDEFINED VIEW `v_high_risk_employees` AS 
select `ce`.`id` AS `employee_id`,`ce`.`first_name` AS `first_name`,`ce`.`last_name` AS `last_name`,`ce`.`department` AS `department`,`ce`.`position` AS `position`,`ce`.`potential_score` AS `potential_score`,`ce`.`risk_score` AS `risk_score`,`ce`.`last_diagnostic_date` AS `last_diagnostic_date`,`c`.`company_id` AS `company_id`,`c`.`company_name` AS `company_name` from (`corporate_employees` `ce` join `companies` `c` on((`ce`.`company_id` = `c`.`company_id`))) where (`ce`.`risk_score` >= 60) order by `ce`.`risk_score` desc;

DROP VIEW IF EXISTS `v_promotion_candidates`;
CREATE ALGORITHM=UNDEFINED VIEW `v_promotion_candidates` AS 
select `ce`.`id` AS `employee_id`,`ce`.`first_name` AS `first_name`,`ce`.`last_name` AS `last_name`,`ce`.`department` AS `department`,`ce`.`position` AS `position`,`ce`.`potential_score` AS `potential_score`,`ce`.`risk_score` AS `risk_score`,`ce`.`last_diagnostic_date` AS `last_diagnostic_date`,`c`.`company_id` AS `company_id`,`c`.`company_name` AS `company_name` from (`corporate_employees` `ce` join `companies` `c` on((`ce`.`company_id` = `c`.`company_id`))) where ((`ce`.`potential_score` >= 80) and (`ce`.`risk_score` < 30)) order by `ce`.`potential_score` desc;

DROP VIEW IF EXISTS `v_recent_interactions`;
CREATE ALGORITHM=UNDEFINED VIEW `v_recent_interactions` AS 
select `ei`.`interaction_id` AS `interaction_id`,`ce`.`first_name` AS `first_name`,`ce`.`last_name` AS `last_name`,`ce`.`department` AS `department`,`ei`.`interaction_type` AS `interaction_type`,`ei`.`interaction_date` AS `interaction_date`,`ei`.`title` AS `title`,`ei`.`outcome` AS `outcome`,`hr`.`email` AS `hr_email`,`c`.`company_name` AS `company_name`,`c`.`company_id` AS `company_id` from (((`employee_interactions` `ei` join `corporate_employees` `ce` on((`ei`.`session_id` = `ce`.`session_id`))) left join `hr_users` `hr` on((`ei`.`hr_user_id` = `hr`.`hr_id`))) join `companies` `c` on((`ce`.`company_id` = `c`.`company_id`))) order by `ei`.`interaction_date` desc limit 100;

-- Восстанавливаем настройки
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;;
SET SQL_MODE=@OLD_SQL_MODE;;
SET AUTOCOMMIT=@OLD_AUTOCOMMIT;;
;
-- Проверка: список всех таблиц и представлений;
-- SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_TYPE, TABLE_NAME;;
