-- Pages/Blog System Migration

-- Page Categories Table
CREATE TABLE IF NOT EXISTS `page_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `display_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Pages Table
CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `type` enum('page','blog') DEFAULT 'page',
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext DEFAULT NULL,
  `excerpt` text DEFAULT NULL,
  `featured_image` varchar(500) DEFAULT NULL,
  `gallery` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`gallery`)),
  `author` varchar(100) DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `meta_keywords` text DEFAULT NULL,
  `status` enum('draft','published','archived') DEFAULT 'draft',
  `featured` tinyint(1) DEFAULT 0,
  `views` int(11) DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `category_id` (`category_id`),
  KEY `type` (`type`),
  KEY `status` (`status`),
  CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `page_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Menu Items Table
CREATE TABLE IF NOT EXISTS `menu_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `title` varchar(100) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `page_id` int(11) DEFAULT NULL,
  `target` enum('_self','_blank') DEFAULT '_self',
  `location` enum('header','footer','both') DEFAULT 'header',
  `icon` varchar(100) DEFAULT NULL,
  `css_class` varchar(100) DEFAULT NULL,
  `display_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `page_id` (`page_id`),
  KEY `location` (`location`),
  KEY `status` (`status`),
  CONSTRAINT `menu_items_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `menu_items_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Sample Data

-- Insert sample page categories
INSERT INTO `page_categories` (`name`, `slug`, `description`, `status`, `display_order`) VALUES
('Kurumsal', 'kurumsal', 'Kurumsal sayfalar', 'active', 1),
('Blog', 'blog', 'Blog yazıları', 'active', 2),
('Haberler', 'haberler', 'Güncel haberler', 'active', 3);

-- Insert sample pages
INSERT INTO `pages` (`category_id`, `type`, `title`, `slug`, `content`, `excerpt`, `status`, `published_at`) VALUES
(1, 'page', 'Hakkımızda', 'hakkimizda', '<h2>Hakkımızda</h2><p>Ninova Turizm olarak...</p>', 'Ninova Turizm hakkında bilgi', 'published', NOW()),
(1, 'page', 'İletişim', 'iletisim', '<h2>İletişim</h2><p>Bize ulaşın...</p>', 'İletişim bilgileri', 'published', NOW()),
(2, 'blog', 'Umre Hazırlık Rehberi', 'umre-hazirlik-rehberi', '<h2>Umre Hazırlık Rehberi</h2><p>Umreye hazırlanırken...</p>', 'Umre öncesi yapılması gerekenler', 'published', NOW());

-- Insert sample menu items
INSERT INTO `menu_items` (`parent_id`, `title`, `url`, `page_id`, `location`, `display_order`, `status`) VALUES
(NULL, 'Hakkımızda', NULL, 1, 'header', 10, 'active'),
(NULL, 'İletişim', NULL, 2, 'footer', 10, 'active'),
(NULL, 'Blog', '/blog', NULL, 'header', 20, 'active');