-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.14.0.7165
-- --------------------------------------------------------



-- Dumping database structure for anime_app
CREATE DATABASE IF NOT EXISTS `anime_app` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `anime_app`;

-- Dumping structure for table anime_app.anime
CREATE TABLE IF NOT EXISTS `anime` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Anime identifikators',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Anime nosaukums',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Anime apraksts',
  `release_year` year DEFAULT NULL COMMENT 'Anime izlaiduma gads',
  `episodes` int DEFAULT NULL COMMENT 'Anime epizožu skaits',
  `image_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Anime attēla saite',
  `created_at` timestamp NULL DEFAULT (now()) COMMENT 'Anime iz',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tiek saglabāti vispārējie dati par katru anime seriālu.';

-- Dumping data for table anime_app.anime: 3 rows
DELETE FROM `anime`;
INSERT INTO `anime` (`id`, `title`, `description`, `release_year`, `episodes`, `image_url`, `created_at`) VALUES
	(1, 'Attack on Titan', 'Cilvēce cīnās pret milzīgiem titāniem.', '2013', 75, 'aot.jpg', '2025-10-29 08:15:07'),
	(2, 'Demon Slayer', 'Zēns kļūst par dēmonu slepkavu, lai atriebtos par ģimeni.', '2019', 55, 'ds.jpg', '2025-10-29 08:15:07'),
	(3, 'Naruto', 'Jauns nindzja, kurš sapņo kļūt par Hokage.', '2002', 220, 'naruto.jpg', '2025-10-29 08:15:07');

-- Dumping structure for table anime_app.anime_genres
CREATE TABLE IF NOT EXISTS `anime_genres` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Anime un žanru sasaistes identifikators',
  `anime_id` int NOT NULL COMMENT 'Anime identifikators (foreign key)',
  `genre_id` int NOT NULL COMMENT 'Žanra id (foreign key)',
  PRIMARY KEY (`id`),
  KEY `anime_id` (`anime_id`),
  KEY `genre_id` (`genre_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Sasaista noteiktos anime ar tiem attiecīgo žanru.';

-- Dumping data for table anime_app.anime_genres: 6 rows
DELETE FROM `anime_genres`;
INSERT INTO `anime_genres` (`id`, `anime_id`, `genre_id`) VALUES
	(1, 1, 1),
	(2, 1, 4),
	(3, 2, 1),
	(4, 2, 3),
	(5, 3, 1),
	(6, 3, 2);

-- Dumping structure for table anime_app.anime_list
CREATE TABLE IF NOT EXISTS `anime_list` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Anime saraksta identifikators',
  `user_id` int NOT NULL COMMENT 'Lietotāja identifikators',
  `anime_id` int NOT NULL COMMENT 'Anime identifikators',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Pievienotā anime nosaukums',
  `image_url` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Pievienotā attēla attēla saite',
  `status` enum('watching','completed','on_hold','dropped','plan_to_watch') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'plan_to_watch' COMMENT 'Lietotāja atzīmetais anime statuss anime sarakstā',
  `score` tinyint unsigned DEFAULT '0' COMMENT 'Reitings, ko lietotājs piešķir anime seriālam',
  `episodes_watched` int unsigned DEFAULT '0' COMMENT 'Lietotāja atzīmētais noskatīto epizožu skaits.',
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Lietotāja privāto piezīmju saturs',
  `updated_at` timestamp NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP COMMENT 'Anime ieraksta atjaunošanas datums',
  `created_at` timestamp NULL DEFAULT (now()) COMMENT 'Anime ieraksta pievienošanas datums',
  PRIMARY KEY (`id`),
  KEY `FK_anime_list_users` (`user_id`),
  KEY `FK2` (`anime_id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tiek saglabāti vispārējie vajadzīgie dati priekš anime saraksta izveides.';

-- Dumping data for table anime_app.anime_list: 5 rows
DELETE FROM `anime_list`;
INSERT INTO `anime_list` (`id`, `user_id`, `anime_id`, `title`, `image_url`, `status`, `score`, `episodes_watched`, `notes`, `updated_at`, `created_at`) VALUES
	(9, 1, 269, 'Bleach', 'https://cdn.myanimelist.net/images/anime/1541/147774.jpg', 'completed', 0, 1, NULL, '2025-09-22 06:45:53', '2025-09-22 05:43:14'),
	(11, 2, 59978, 'Sousou no Frieren 2nd Season', 'https://cdn.myanimelist.net/images/anime/1064/152251.jpg', 'plan_to_watch', 0, 0, NULL, '2025-11-11 00:18:39', '2025-11-11 00:18:39'),
	(13, 2, 269, 'Bleach', 'https://cdn.myanimelist.net/images/anime/1541/147774.jpg', 'dropped', 3, 5, NULL, '2025-11-11 00:32:14', '2025-11-11 00:27:00'),
	(14, 2, 11061, 'Hunter x Hunter (2011)', 'https://cdn.myanimelist.net/images/anime/1337/99013.jpg', 'on_hold', 4, 8, NULL, '2025-11-11 00:32:05', '2025-11-11 00:32:05'),
	(15, 2, 40839, 'Kanojo, Okarishimasu', 'https://cdn.myanimelist.net/images/anime/1902/128382.jpg', 'watching', 3, 0, NULL, '2025-12-18 07:16:10', '2025-11-11 00:32:44');

-- Dumping structure for table anime_app.comments
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Komentāra identifikators',
  `user_id` int NOT NULL COMMENT 'Lietotāja identifikators, kas rakstīja komentāru',
  `anime_id` int NOT NULL COMMENT 'anime identifikators pie kura pievienots komentārs',
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Komentāra saturs',
  `created_at` timestamp NULL DEFAULT (now()) COMMENT 'Komentāra izveides datums',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `anime_id` (`anime_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Topošo komentāru funkcijas datu tabula. Saglabās lietotāju rakstītos komentārus.';

-- Dumping data for table anime_app.comments: 2 rows
DELETE FROM `comments`;
INSERT INTO `comments` (`id`, `user_id`, `anime_id`, `comment_text`, `created_at`) VALUES
	(1, 1, 1, 'Ļoti iespaidīgs stāsts un animācija!', '2025-10-29 08:15:07'),
	(2, 2, 3, 'Plānoju skatīties, izskatās interesanti.', '2025-10-29 08:15:07');

-- Dumping structure for table anime_app.genres
CREATE TABLE IF NOT EXISTS `genres` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Žanru identifikators',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Žanru nosaukums',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Saglabā anime seriālu žanru id un nosaukumus.';

-- Dumping data for table anime_app.genres: 4 rows
DELETE FROM `genres`;
INSERT INTO `genres` (`id`, `name`) VALUES
	(1, 'Action'),
	(2, 'Adventure'),
	(3, 'Fantasy'),
	(4, 'Drama');

-- Dumping structure for table anime_app.user_anime_list
CREATE TABLE IF NOT EXISTS `user_anime_list` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Lietotāja izveidotā saraksta identifikators',
  `user_id` int NOT NULL COMMENT 'Lietotāja identifikators (foreign key)',
  `anime_id` int NOT NULL COMMENT 'Anime identifikators (foreign key)',
  `status` enum('Skatos','Noskatīts','Plānoju skatīties') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Anime statuss',
  `rating` tinyint unsigned DEFAULT NULL COMMENT 'Anime reitings',
  `added_at` timestamp NULL DEFAULT (now()) COMMENT 'Datums, kad tika pievienots anime anime sarakstam',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `anime_id` (`anime_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Šajā tabulā tiek saglabāti katra lietotāja anime saraksta dati.';

-- Dumping data for table anime_app.user_anime_list: 3 rows
DELETE FROM `user_anime_list`;
INSERT INTO `user_anime_list` (`id`, `user_id`, `anime_id`, `status`, `rating`, `added_at`) VALUES
	(1, 1, 1, 'Noskatīts', 9, '2025-10-29 08:15:07'),
	(2, 1, 2, 'Skatos', 8, '2025-10-29 08:15:07'),
	(3, 2, 3, 'Plānoju skatīties', NULL, '2025-10-29 08:15:07');

