START TRANSACTION;



CREATE TABLE `articles` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `header` varchar(255) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `meta_keywords` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `url_part` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `short_description` text DEFAULT NULL,
  `seo_index` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `articles_categories` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `header` varchar(255) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `meta_keywords` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `url_part` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `short_description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `auth_assignment` (
  `item_name` varchar(64) NOT NULL,
  `user_id` varchar(64) NOT NULL,
  `created_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `auth_assignment` (`item_name`, `user_id`, `created_at`) VALUES
('admin', '1', 1736373329);

CREATE TABLE `auth_item` (
  `name` varchar(64) NOT NULL,
  `type` smallint(6) NOT NULL,
  `description` text DEFAULT NULL,
  `rule_name` varchar(64) DEFAULT NULL,
  `data` blob DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `auth_item` (`name`, `type`, `description`, `rule_name`, `data`, `created_at`, `updated_at`) VALUES
('admin', 1, 'Администратор', NULL, NULL, 1736373329, 1736373329),
('adminAccess', 2, 'Admin Access', NULL, NULL, 1736373328, 1736373328),
('createArticle', 2, 'Create Article', NULL, NULL, 1736373329, 1736373329),
('createArticleCategory', 2, 'Create Article Category', NULL, NULL, 1736373329, 1736373329),
('createBlock', 2, 'Create Block', NULL, NULL, 1736373329, 1736373329),
('createContacts', 2, 'Create Contacts', NULL, NULL, 1736373329, 1736373329),
('createGallery', 2, 'Create Gallery', NULL, NULL, 1736373329, 1736373329),
('createMail', 2, 'Create Mail', NULL, NULL, 1736373329, 1736373329),
('createMaterial', 2, 'Create Material', NULL, NULL, 1736373329, 1736373329),
('createMenu', 2, 'Create Menu', NULL, NULL, 1736373329, 1736373329),
('createPage', 2, 'Create Page', NULL, NULL, 1736373329, 1736373329),
('createProject', 2, 'Create Project', NULL, NULL, 1736373329, 1736373329),
('createUser', 2, 'Create User', NULL, NULL, 1736373329, 1736373329),
('createVacancy', 2, 'Create Vacancy', NULL, NULL, 1736373329, 1736373329),
('deleteArticle', 2, 'Delete Article', NULL, NULL, 1736373329, 1736373329),
('deleteArticleCategory', 2, 'Delete Article Category', NULL, NULL, 1736373329, 1736373329),
('deleteBlock', 2, 'Delete Block', NULL, NULL, 1736373329, 1736373329),
('deleteContacts', 2, 'Delete Contacts', NULL, NULL, 1736373329, 1736373329),
('deleteGallery', 2, 'Delete Gallery', NULL, NULL, 1736373329, 1736373329),
('deleteMail', 2, 'Delete Mail', NULL, NULL, 1736373329, 1736373329),
('deleteMaterial', 2, 'Delete Material', NULL, NULL, 1736373329, 1736373329),
('deleteMenu', 2, 'Delete Menu', NULL, NULL, 1736373329, 1736373329),
('deletePage', 2, 'Delete Page', NULL, NULL, 1736373329, 1736373329),
('deleteProject', 2, 'Delete Project', NULL, NULL, 1736373329, 1736373329),
('deleteUser', 2, 'Delete User', NULL, NULL, 1736373329, 1736373329),
('deleteVacancy', 2, 'Delete Vacancy', NULL, NULL, 1736373329, 1736373329),
('downloadMaterial', 2, 'Download Material', NULL, NULL, 1736373329, 1736373329),
('updateArticle', 2, 'Update Article', NULL, NULL, 1736373329, 1736373329),
('updateArticleCategory', 2, 'Update Article Category', NULL, NULL, 1736373329, 1736373329),
('updateBlock', 2, 'Update Block', NULL, NULL, 1736373329, 1736373329),
('updateContacts', 2, 'Update Contacts', NULL, NULL, 1736373329, 1736373329),
('updateGallery', 2, 'Update Gallery', NULL, NULL, 1736373329, 1736373329),
('updateGeneralSettings', 2, 'Update General Settings', NULL, NULL, 1736373329, 1736373329),
('updateMail', 2, 'Update Mail', NULL, NULL, 1736373329, 1736373329),
('updateMaterial', 2, 'Update Material', NULL, NULL, 1736373329, 1736373329),
('updateMenu', 2, 'Update Menu', NULL, NULL, 1736373329, 1736373329),
('updateModuleSettings', 2, 'Update Module Settings', NULL, NULL, 1736373329, 1736373329),
('updateNotifications', 2, 'Update Notifications', NULL, NULL, 1736373329, 1736373329),
('updatePage', 2, 'Update Page', NULL, NULL, 1736373329, 1736373329),
('updateProject', 2, 'Update Project', NULL, NULL, 1736373329, 1736373329),
('updateRequests', 2, 'Update Requests', NULL, NULL, 1736373329, 1736373329),
('updateUser', 2, 'Update User', NULL, NULL, 1736373329, 1736373329),
('updateVacancy', 2, 'Update Vacancy', NULL, NULL, 1736373329, 1736373329),
('viewArticle', 2, 'View Article', NULL, NULL, 1736373329, 1736373329),
('viewArticleCategory', 2, 'View Article Category', NULL, NULL, 1736373329, 1736373329),
('viewBlock', 2, 'View Block', NULL, NULL, 1736373329, 1736373329),
('viewContacts', 2, 'View Contacts', NULL, NULL, 1736373329, 1736373329),
('viewGallery', 2, 'View Gallery', NULL, NULL, 1736373329, 1736373329),
('viewMail', 2, 'View Mail', NULL, NULL, 1736373329, 1736373329),
('viewMaterial', 2, 'View Material', NULL, NULL, 1736373329, 1736373329),
('viewMenu', 2, 'View Menu', NULL, NULL, 1736373329, 1736373329),
('viewModuleList', 2, 'View Module List', NULL, NULL, 1736373329, 1736373329),
('viewNotifications', 2, 'View Notifications', NULL, NULL, 1736373329, 1736373329),
('viewPage', 2, 'View Page', NULL, NULL, 1736373329, 1736373329),
('viewProject', 2, 'View Project', NULL, NULL, 1736373329, 1736373329),
('viewRequests', 2, 'View Requests', NULL, NULL, 1736373329, 1736373329),
('viewResponses', 2, 'View Responses', NULL, NULL, 1736373329, 1736373329),
('viewUser', 2, 'View User', NULL, NULL, 1736373329, 1736373329),
('viewVacancy', 2, 'View Vacancy', NULL, NULL, 1736373329, 1736373329);

CREATE TABLE `auth_item_child` (
  `parent` varchar(64) NOT NULL,
  `child` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `auth_item_child` (`parent`, `child`) VALUES
('admin', 'adminAccess'),
('admin', 'createArticle'),
('admin', 'createArticleCategory'),
('admin', 'createBlock'),
('admin', 'createContacts'),
('admin', 'createGallery'),
('admin', 'createMail'),
('admin', 'createMaterial'),
('admin', 'createMenu'),
('admin', 'createPage'),
('admin', 'createProject'),
('admin', 'createUser'),
('admin', 'createVacancy'),
('admin', 'deleteArticle'),
('admin', 'deleteArticleCategory'),
('admin', 'deleteBlock'),
('admin', 'deleteContacts'),
('admin', 'deleteGallery'),
('admin', 'deleteMail'),
('admin', 'deleteMaterial'),
('admin', 'deleteMenu'),
('admin', 'deletePage'),
('admin', 'deleteProject'),
('admin', 'deleteUser'),
('admin', 'deleteVacancy'),
('admin', 'downloadMaterial'),
('admin', 'updateArticle'),
('admin', 'updateArticleCategory'),
('admin', 'updateBlock'),
('admin', 'updateContacts'),
('admin', 'updateGallery'),
('admin', 'updateGeneralSettings'),
('admin', 'updateMail'),
('admin', 'updateMaterial'),
('admin', 'updateMenu'),
('admin', 'updateModuleSettings'),
('admin', 'updateNotifications'),
('admin', 'updatePage'),
('admin', 'updateProject'),
('admin', 'updateRequests'),
('admin', 'updateUser'),
('admin', 'updateVacancy'),
('admin', 'viewArticle'),
('admin', 'viewArticleCategory'),
('admin', 'viewBlock'),
('admin', 'viewContacts'),
('admin', 'viewGallery'),
('admin', 'viewMail'),
('admin', 'viewMaterial'),
('admin', 'viewMenu'),
('admin', 'viewModuleList'),
('admin', 'viewNotifications'),
('admin', 'viewPage'),
('admin', 'viewProject'),
('admin', 'viewRequests'),
('admin', 'viewResponses'),
('admin', 'viewUser'),
('admin', 'viewVacancy');

CREATE TABLE `auth_rule` (
  `name` varchar(64) NOT NULL,
  `data` blob DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `blocks` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `classname` varchar(100) DEFAULT NULL,
  `json_data` text DEFAULT NULL,
  `alias` varchar(100) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `blocks_links` (
  `block_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  `entity_class` varchar(100) NOT NULL,
  `sort_order` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `contacts` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `map_code` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `content_pages` (
  `id` int(11) NOT NULL,
  `image` varchar(250) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `header` varchar(255) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `short_description` text DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `meta_keywords` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `url_full` varchar(255) DEFAULT NULL,
  `homepage` tinyint(1) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `seo_index` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `content_pages` (`id`, `image`, `parent_id`, `header`, `content`, `short_description`, `meta_title`, `meta_description`, `meta_keywords`, `url`, `template_id`, `url_full`, `homepage`, `name`, `seo_index`) VALUES
(1, '', NULL, 'Главная страница', '<p>Основной контент</p>\r\n', '<p>Анонс</p>\r\n', '', '', '', 'glavnaya_stranitsa', 1, NULL, 1, 'Главная страница', 0);

CREATE TABLE `gallery` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `gallery_items` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `gallery_id` int(11) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `file` varchar(255) DEFAULT NULL,
  `video_source` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `mails` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `notification_id` int(11) DEFAULT NULL,
  `targets` varchar(255) DEFAULT NULL,
  `params` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `gallery_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `short_description` text DEFAULT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `program_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials_category` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials_files` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `file` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials_file_links` (
  `id` int(11) NOT NULL,
  `material_id` int(11) DEFAULT NULL,
  `file_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials_file_type` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `materials_programs` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `menu` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `alias` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `menu_item` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `type` int(11) NOT NULL,
  `menu_id` int(11) NOT NULL,
  `page_id` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  `direct_url` varchar(250) DEFAULT NULL,
  `image` varchar(250) DEFAULT NULL,
  `icon` varchar(250) DEFAULT NULL,
  `header` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `migration` (
  `version` varchar(180) NOT NULL,
  `apply_time` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `migration` (`version`, `apply_time`) VALUES
('m000000_000000_base', 1736373322),
('m130524_201442_init', 1736373323),
('m140506_102106_rbac_init', 1736373323),
('m170907_052038_rbac_add_index_on_auth_assignment_user_id', 1736373323),
('m180523_151638_rbac_updates_indexes_without_prefix', 1736373323),
('m200409_110543_rbac_update_mssql_trigger', 1736373323),
('m241017_020734_content_pages_table', 1736373323),
('m241018_222529_blocks', 1736373323),
('m241018_222620_blocks_links', 1736373324),
('m241103_171702_menu_tables', 1736373324),
('m241104_012210_gallery_tables', 1736373324),
('m241105_182749_blocks_table_fix', 1736373324),
('m241107_222412_block_links_id', 1736373324),
('m241112_200952_contacts_table', 1736373324),
('m241112_233648_materials_tables', 1736373324),
('m241113_011206_notifications_tables', 1736373324),
('m241113_011550_user_requests', 1736373324),
('m241116_091713_articles', 1736373324),
('m241117_170004_vacancies', 1736373324),
('m241117_170837_projects', 1736373324),
('m241125_131717_column_fixes', 1736373324),
('m241125_201140_column_fuxes_2', 1736373324),
('m241127_151538_fix_pages', 1736373324),
('m241128_043944_templates', 1736373325),
('m241202_171255_fixes', 1736373325),
('m241206_140704_video', 1736373325),
('m241209_005740_mailes', 1736373325),
('m241210_202721_keys', 1736373327),
('m241216_003715_tags', 1736373327),
('m241216_032618_material_programs', 1736373327),
('m241218_063039_contacts_map_fix', 1736373327),
('m250106_200553_sitemap', 1736373327),
('yii\\queue\\db\\migrations\\M161119140200Queue', 1736373323),
('yii\\queue\\db\\migrations\\M170307170300Later', 1736373323),
('yii\\queue\\db\\migrations\\M170509001400Retry', 1736373323),
('yii\\queue\\db\\migrations\\M170601155600Priority', 1736373323),
('yii\\queue\\db\\migrations\\M211218163000JobQueueSize', 1736373323);

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `create_message` tinyint(1) DEFAULT NULL,
  `template` varchar(255) DEFAULT NULL,
  `send_email` tinyint(1) DEFAULT NULL,
  `email_template` varchar(255) DEFAULT NULL,
  `params` text DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `notification_messages` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `viewed` tinyint(1) DEFAULT NULL,
  `notification_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `notification_users` (
  `user_id` int(11) DEFAULT NULL,
  `notification_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `gallery_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `projects_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `projects_materials` (
  `id` int(11) NOT NULL,
  `material_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `queue` (
  `id` int(11) NOT NULL,
  `channel` varchar(255) NOT NULL,
  `job` longblob NOT NULL,
  `pushed_at` int(11) NOT NULL,
  `ttr` int(11) NOT NULL,
  `delay` int(11) NOT NULL DEFAULT 0,
  `priority` int(11) UNSIGNED NOT NULL DEFAULT 1024,
  `reserved_at` int(11) DEFAULT NULL,
  `attempt` int(11) DEFAULT NULL,
  `done_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tags` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tags_links` (
  `tag_id` int(11) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `entity_class` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `templates` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `view` varchar(255) DEFAULT NULL,
  `params` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `templates` (`id`, `name`, `image`, `type`, `view`, `params`) VALUES
(1, 'Стандартная страница', '/web/images/templates/mainpage.jpg', 'page', '@app/views/pages/default', NULL);

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `auth_key` varchar(32) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `image` varchar(250) DEFAULT NULL,
  `password_reset_token` varchar(255) DEFAULT NULL,
  `verification_token` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT 10,
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `user` (`id`, `name`, `auth_key`, `password_hash`, `image`, `password_reset_token`, `verification_token`, `email`, `status`, `created_at`, `updated_at`) VALUES
(1, 'admin', '', '$2y$13$p1xOzYqdnLbasXgZ5hyXpOdDJniCJezxq65FiqaDXgAQ8z5Hk67Nm', NULL, NULL, NULL, 'admin@email.com', 10, 1736373329, 1736373329);

CREATE TABLE `user_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `data` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `type` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `vacancies` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `position` varchar(255) DEFAULT NULL,
  `responsibilities` varchar(255) DEFAULT NULL,
  `requirements` varchar(255) DEFAULT NULL,
  `conditions` varchar(255) DEFAULT NULL,
  `salary` varchar(255) DEFAULT NULL,
  `schedule` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `active` tinyint(1) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `url_part` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `vacancies_requests` (
  `id` int(11) NOT NULL,
  `vacancy_id` int(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  `resume` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `articles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_articles_category_id` (`category_id`),
  ADD KEY `fk_articles_template_id` (`template_id`);

ALTER TABLE `articles_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_articles_categories_parent_id` (`parent_id`),
  ADD KEY `fk_articles_categories_template_id` (`template_id`);

ALTER TABLE `auth_assignment`
  ADD PRIMARY KEY (`item_name`,`user_id`),
  ADD KEY `idx-auth_assignment-user_id` (`user_id`);

ALTER TABLE `auth_item`
  ADD PRIMARY KEY (`name`),
  ADD KEY `rule_name` (`rule_name`),
  ADD KEY `idx-auth_item-type` (`type`);

ALTER TABLE `auth_item_child`
  ADD PRIMARY KEY (`parent`,`child`),
  ADD KEY `child` (`child`);

ALTER TABLE `auth_rule`
  ADD PRIMARY KEY (`name`);

ALTER TABLE `blocks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_blocks_template_id` (`template_id`);

ALTER TABLE `blocks_links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_blocks_links_block_id` (`block_id`);

ALTER TABLE `contacts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `content_pages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_content_pages_template_id` (`template_id`);

ALTER TABLE `gallery`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `gallery_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_gallery_items_gallery_id` (`gallery_id`);

ALTER TABLE `mails`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_mails_template_id` (`template_id`),
  ADD KEY `fk_mails_notification_id` (`notification_id`);

ALTER TABLE `materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_materials_category_id` (`category_id`),
  ADD KEY `fk_materials_gallery_id` (`gallery_id`),
  ADD KEY `fk_materials_program_id_materials_programs_id` (`program_id`);

ALTER TABLE `materials_category`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_materials_category_parent_id` (`parent_id`);

ALTER TABLE `materials_files`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_materials_files_type` (`type`);

ALTER TABLE `materials_file_links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_materials_file_links_material_id` (`material_id`),
  ADD KEY `fk_materials_file_links_file_id` (`file_id`);

ALTER TABLE `materials_file_type`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `materials_programs`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `menu`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `menu_item`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_menu_item_parent_id` (`parent_id`),
  ADD KEY `fk_menu_item_menu_id` (`menu_id`),
  ADD KEY `fk_menu_item_page_id` (`page_id`);

ALTER TABLE `migration`
  ADD PRIMARY KEY (`version`);

ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `notification_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_notification_messages_user_id` (`user_id`),
  ADD KEY `fk_notification_messages_notification_id` (`notification_id`);

ALTER TABLE `notification_users`
  ADD KEY `fk_notification_users_user_id` (`user_id`),
  ADD KEY `fk_notification_users_notification_id` (`notification_id`);

ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_projects_category_id` (`category_id`);

ALTER TABLE `projects_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_projects_categories_parent_id` (`parent_id`);

ALTER TABLE `projects_materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_projects_materials_material_id` (`material_id`),
  ADD KEY `fk_projects_materials_project_id` (`project_id`);

ALTER TABLE `queue`
  ADD PRIMARY KEY (`id`),
  ADD KEY `channel` (`channel`),
  ADD KEY `reserved_at` (`reserved_at`),
  ADD KEY `priority` (`priority`);

ALTER TABLE `tags`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tags_links`
  ADD KEY `fk_tags_links_tag_id_tags_id` (`tag_id`);

ALTER TABLE `templates`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `password_reset_token` (`password_reset_token`);

ALTER TABLE `user_requests`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `vacancies`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_vacancies_template_id` (`template_id`);

ALTER TABLE `vacancies_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_vacancies_requests_vacancy_id` (`vacancy_id`);


ALTER TABLE `articles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `articles_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `blocks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `blocks_links`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `contacts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `content_pages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `gallery`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `gallery_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mails`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials_files`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials_file_links`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials_file_type`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `materials_programs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `menu`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `menu_item`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notification_messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `projects`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `projects_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `projects_materials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `queue`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `tags`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `templates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `user_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `vacancies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `vacancies_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `articles`
  ADD CONSTRAINT `fk_articles_category_id` FOREIGN KEY (`category_id`) REFERENCES `articles_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_articles_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `articles_categories`
  ADD CONSTRAINT `fk_articles_categories_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `articles_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_articles_categories_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `auth_assignment`
  ADD CONSTRAINT `auth_assignment_ibfk_1` FOREIGN KEY (`item_name`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `auth_item`
  ADD CONSTRAINT `auth_item_ibfk_1` FOREIGN KEY (`rule_name`) REFERENCES `auth_rule` (`name`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `auth_item_child`
  ADD CONSTRAINT `auth_item_child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `auth_item_child_ibfk_2` FOREIGN KEY (`child`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `blocks`
  ADD CONSTRAINT `fk_blocks_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `blocks_links`
  ADD CONSTRAINT `fk_blocks_links_block_id` FOREIGN KEY (`block_id`) REFERENCES `blocks` (`id`) ON DELETE CASCADE;

ALTER TABLE `content_pages`
  ADD CONSTRAINT `fk_content_pages_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `gallery_items`
  ADD CONSTRAINT `fk_gallery_items_gallery_id` FOREIGN KEY (`gallery_id`) REFERENCES `gallery` (`id`) ON DELETE CASCADE;

ALTER TABLE `mails`
  ADD CONSTRAINT `fk_mails_notification_id` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_mails_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `materials`
  ADD CONSTRAINT `fk_materials_category_id` FOREIGN KEY (`category_id`) REFERENCES `materials_category` (`id`),
  ADD CONSTRAINT `fk_materials_gallery_id` FOREIGN KEY (`gallery_id`) REFERENCES `gallery` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_materials_program_id_materials_programs_id` FOREIGN KEY (`program_id`) REFERENCES `materials_programs` (`id`) ON DELETE SET NULL;

ALTER TABLE `materials_category`
  ADD CONSTRAINT `fk_materials_category_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `materials_category` (`id`) ON DELETE SET NULL;

ALTER TABLE `materials_files`
  ADD CONSTRAINT `fk_materials_files_type` FOREIGN KEY (`type`) REFERENCES `materials_file_type` (`id`);

ALTER TABLE `materials_file_links`
  ADD CONSTRAINT `fk_materials_file_links_file_id` FOREIGN KEY (`file_id`) REFERENCES `materials_files` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_materials_file_links_material_id` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE CASCADE;

ALTER TABLE `menu_item`
  ADD CONSTRAINT `fk_menu_item_menu_id` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_menu_item_page_id` FOREIGN KEY (`page_id`) REFERENCES `content_pages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_menu_item_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `menu_item` (`id`) ON DELETE SET NULL;

ALTER TABLE `notification_messages`
  ADD CONSTRAINT `fk_notification_messages_notification_id` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_notification_messages_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;

ALTER TABLE `notification_users`
  ADD CONSTRAINT `fk_notification_users_notification_id` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_notification_users_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;

ALTER TABLE `projects`
  ADD CONSTRAINT `fk_projects_category_id` FOREIGN KEY (`category_id`) REFERENCES `projects_categories` (`id`) ON DELETE SET NULL;

ALTER TABLE `projects_categories`
  ADD CONSTRAINT `fk_projects_categories_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `projects_categories` (`id`) ON DELETE SET NULL;

ALTER TABLE `projects_materials`
  ADD CONSTRAINT `fk_projects_materials_material_id` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_projects_materials_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

ALTER TABLE `tags_links`
  ADD CONSTRAINT `fk_tags_links_tag_id_tags_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE;

ALTER TABLE `vacancies`
  ADD CONSTRAINT `fk_vacancies_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`);

ALTER TABLE `vacancies_requests`
  ADD CONSTRAINT `fk_vacancies_requests_vacancy_id` FOREIGN KEY (`vacancy_id`) REFERENCES `vacancies` (`id`) ON DELETE CASCADE;
COMMIT;

