-- Merkezi Zorunlu Duyuru Sistemi Veritabanı Yapısı
-- MySQL 5.7+ - Proje ön ekli tablo isimleri

-- Veritabanı kullan (mevcut veritabanı)
USE `omerbagu_omerbaguc`;

-- Gruplar tablosu
CREATE TABLE `announcement_groups` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `group_name` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Client'lar tablosu
CREATE TABLE `announcement_clients` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `client_uuid` VARCHAR(36) NOT NULL UNIQUE,
    `pc_name` VARCHAR(255) NOT NULL,
    `user_name` VARCHAR(255) NOT NULL,
    `group_id` INT NOT NULL,
    `last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`group_id`) REFERENCES `announcement_groups`(`id`) ON DELETE RESTRICT,
    INDEX `idx_client_uuid` (`client_uuid`),
    INDEX `idx_group_id` (`group_id`)
) ENGINE=InnoDB;

-- Duyurular tablosu
CREATE TABLE `announcement_announcements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `is_mandatory` BOOLEAN DEFAULT TRUE,
    `publish_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expire_time` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_publish_time` (`publish_time`),
    INDEX `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB;

-- Duyuru-Grup ilişkileri tablosu
CREATE TABLE `announcement_announcement_groups` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `announcement_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`announcement_id`) REFERENCES `announcement_announcements`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `announcement_groups`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_announcement_group` (`announcement_id`, `group_id`)
) ENGINE=InnoDB;

-- Okuma kayıtları tablosu
CREATE TABLE `announcement_reads` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `announcement_id` INT NOT NULL,
    `client_uuid` VARCHAR(36) NOT NULL,
    `read_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`announcement_id`) REFERENCES `announcement_announcements`(`id`) ON DELETE CASCADE,
    INDEX `idx_client_uuid` (`client_uuid`),
    INDEX `idx_announcement_id` (`announcement_id`),
    UNIQUE KEY `unique_announcement_client` (`announcement_id`, `client_uuid`)
) ENGINE=InnoDB;

-- API anahtarları tablosu (güvenlik için)
CREATE TABLE `announcement_api_keys` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `key_name` VARCHAR(100) NOT NULL,
    `api_key` VARCHAR(255) NOT NULL UNIQUE,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NULL
) ENGINE=InnoDB;

-- Admin kullanıcısı tablosu (basit authentication için)
CREATE TABLE `announcement_admin_users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255),
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Örnek veriler
INSERT INTO `announcement_groups` (`group_name`, `description`) VALUES 
('A Şubesi', 'Ana şube merkezi'),
('B Şubesi', 'İkinci şube'),
('C Şubesi', 'Üçüncü şube'),
('Yönetim', 'Yönetim kadrosu');

-- Örnek API anahtarı
INSERT INTO `announcement_api_keys` (`key_name`, `api_key`) VALUES 
('default_client_key', 'ANNOUNCEMENT_API_KEY_2024_SECURE_TOKEN');

-- Örnek admin kullanıcısı (şifre: admin123)
INSERT INTO `announcement_admin_users` (`username`, `password`, `email`) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@company.com');

-- Test duyurusu (isteğe bağlı)
INSERT INTO `announcement_announcements` (`title`, `content`, `is_mandatory`) VALUES 
('Hoş Geldin Duyurusu', 'Sisteme hoş geldiniz! Bu bir test duyurusudur.', TRUE);

-- Test duyurusunu tüm gruplara ata
INSERT INTO `announcement_announcement_groups` (`announcement_id`, `group_id`) 
SELECT 1, `id` FROM `announcement_groups`;

-- Başarılı mesajı
SELECT 'Veritabanı başarıyla oluşturuldu!' AS mesaj;