-- 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;

-- API anahtarları tablosu (güvenlik için)
CREATE TABLE 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;

-- Ö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');

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

-- Admin kullanıcısı tablosu (basit authentication için)
CREATE TABLE 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 admin kullanıcısı (şifre: admin123)
INSERT INTO admin_users (username, password, email) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@company.com');