-- Mevcut veritabanını güncellemek için SQL komutları
-- Eğer veritabanını sıfırdan kuruyorsanız install.sql kullanın.

-- 1. Subcategories Tablosu
CREATE TABLE IF NOT EXISTS `subcategories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_turkish_ci NOT NULL,
  `rank_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `subcategories_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 2. Firms Tablosuna Yeni Alanlar
ALTER TABLE `firms` 
ADD COLUMN `subcategory_id` int(11) DEFAULT NULL AFTER `category_id`,
ADD COLUMN `content` text COLLATE utf8mb4_turkish_ci DEFAULT NULL AFTER `image_url`,
ADD COLUMN `working_hours` varchar(255) COLLATE utf8mb4_turkish_ci DEFAULT NULL AFTER `content`,
ADD COLUMN `is_featured` tinyint(1) DEFAULT 0 AFTER `working_hours`;

-- 3. News Tablosu
CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_turkish_ci NOT NULL,
  `content` text COLLATE utf8mb4_turkish_ci NOT NULL,
  `image_url` varchar(255) COLLATE utf8mb4_turkish_ci DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 4. Events Tablosu
CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_turkish_ci NOT NULL,
  `description` text COLLATE utf8mb4_turkish_ci,
  `event_date` datetime NOT NULL,
  `location` varchar(255) COLLATE utf8mb4_turkish_ci DEFAULT NULL,
  `image_url` varchar(255) COLLATE utf8mb4_turkish_ci DEFAULT NULL,
  `organizer` varchar(255) COLLATE utf8mb4_turkish_ci DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 5. Important Numbers Tablosu
CREATE TABLE IF NOT EXISTS `important_numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_turkish_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_turkish_ci NOT NULL,
  `rank_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 6. Admins Tablosuna Rol
ALTER TABLE `admins` 
ADD COLUMN `role` varchar(20) COLLATE utf8mb4_turkish_ci DEFAULT 'admin' AFTER `password`;
