-- Merkezi Zorunlu Duyuru Sistemi Veritabanı Yapısı
-- MySQL 5.7+

CREATE DATABASE IF NOT EXISTS announcement_system CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;
USE announcement_system;

-- Gruplar tablosu
CREATE TABLE `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 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 `groups`(id) ON DELETE RESTRICT,
    INDEX idx_client_uuid (client_uuid),
    INDEX idx_group_id (group_id)
) ENGINE=InnoDB;

-- Duyurular tablosu
CREATE TABLE 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_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 announcements(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    UNIQUE KEY unique_announcement_group (announcement_id, group_id)
) ENGINE=InnoDB;

-- Okuma kayıtları tablosu
CREATE TABLE 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 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;

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