SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Zbirka podatkov: `vaja1`
CREATE DATABASE IF NOT EXISTS `baza` DEFAULT CHARACTER SET utf32 COLLATE utf32_slovenian_ci;
USE `baza`;

-- Struktura tabele `users`
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` text COLLATE utf32_slovenian_ci NOT NULL,
  `password` text COLLATE utf32_slovenian_ci NOT NULL,
  `email` text COLLATE utf32_slovenian_ci,
  `ime` text COLLATE utf32_slovenian_ci,
  `priimek` text COLLATE utf32_slovenian_ci,
  `naslov` text COLLATE utf32_slovenian_ci,
  `posta` text COLLATE utf32_slovenian_ci,
  `telefon` text COLLATE utf32_slovenian_ci,
  `adm` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=utf32_slovenian_ci;


-- Struktura tabele `ads`
DROP TABLE IF EXISTS `ads`;
CREATE TABLE IF NOT EXISTS `ads` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf32_slovenian_ci NOT NULL,
  `description` text COLLATE utf32_slovenian_ci NOT NULL,
  `user_id` int NOT NULL,
  `date` DATETIME COLLATE utf32_slovenian_ci NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ads_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=utf32_slovenian_ci;

-- Struktura tabele `categorys`
DROP TABLE IF EXISTS `categorys`;
CREATE TABLE IF NOT EXISTS `categorys` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf32_slovenian_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=utf32_slovenian_ci;

INSERT INTO categorys (title)
VALUES ("Telefon"),("Avto"),("Narava"),("Hisa"),("Motor");

DROP TABLE IF EXISTS `ads_categorys`;
CREATE TABLE IF NOT EXISTS `ads_categorys` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ads_id` int NOT NULL,
  `categorys_id` int NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_cat_ad` FOREIGN KEY (`ads_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ad_cat` FOREIGN KEY (`categorys_id`) REFERENCES `categorys`(`id`) ON DELETE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=utf32_slovenian_ci;

DROP TABLE IF EXISTS `comments`;
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ads_id` int NOT NULL,
  `users_id` int NOT NULL,
  `content` text COLLATE utf32_slovenian_ci,
  `date` DATETIME COLLATE utf32_slovenian_ci NOT NULL,
  `country` text COLLATE utf32_slovenian_ci,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ad_comm` FOREIGN KEY (`ads_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_comm_us` FOREIGN KEY (`users_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=utf32_slovenian_ci;

DROP TABLE IF EXISTS `ads_image`;
CREATE TABLE IF NOT EXISTS `ads_image` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ads_id` int NOT NULL,
  `image` text COLLATE utf32_slovenian_ci NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ad_img` FOREIGN KEY (`ads_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf32 COLLATE=UTF32_SLOVENIAN_CI;