-- Siteler tablosu
CREATE TABLE IF NOT EXISTS sites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    domain VARCHAR(190) NOT NULL UNIQUE,
    category VARCHAR(120) NOT NULL,
    default_locale VARCHAR(12) NULL,
    settings JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kullanıcılar
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(60) NOT NULL,
    preferences JSON NULL,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY users_site_email_unique (site_id, email),
    CONSTRAINT fk_users_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

-- Oturumlar
CREATE TABLE IF NOT EXISTS sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    session_key CHAR(36) NOT NULL UNIQUE,
    locale VARCHAR(12) NOT NULL,
    role VARCHAR(60) NOT NULL DEFAULT 'guest',
    csrf_token CHAR(64) NULL,
    nonce CHAR(64) NULL,
    metadata JSON NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_sessions_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_sessions_site (site_id),
    INDEX idx_sessions_token (csrf_token),
    INDEX idx_sessions_nonce (nonce)
);

-- Mesajlar
CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    role ENUM('user','assistant','system') NOT NULL,
    content LONGTEXT NOT NULL,
    sources JSON NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_messages_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE,
    INDEX idx_messages_session_created (session_id, created_at)
);

-- Döküman parçaları (RAG)
CREATE TABLE IF NOT EXISTS docs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    source_type VARCHAR(60) NOT NULL,
    source_id VARCHAR(190) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    embedding JSON NOT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY docs_site_source_unique (site_id, source_type, source_id),
    FULLTEXT KEY ft_docs_content (content),
    CONSTRAINT fk_docs_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

-- Ürünler
CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    sku VARCHAR(120) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    image_url VARCHAR(255) NULL,
    stock INT NOT NULL DEFAULT 0,
    tags JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY products_site_sku_unique (site_id, sku),
    CONSTRAINT fk_products_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

-- Olaylar
CREATE TABLE IF NOT EXISTS events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    type VARCHAR(120) NOT NULL,
    payload JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_events_site_created (site_id, created_at),
    CONSTRAINT fk_events_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

-- Geri bildirim
CREATE TABLE IF NOT EXISTS feedback (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    site_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    type VARCHAR(60) NOT NULL,
    comment TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_feedback_message FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE,
    CONSTRAINT fk_feedback_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE,
    CONSTRAINT fk_feedback_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
);

-- Audit logları
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    session_id INT UNSIGNED NULL,
    action VARCHAR(150) NOT NULL,
    metadata JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_site_created (site_id, created_at),
    CONSTRAINT fk_audit_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE SET NULL
);

-- Desteklenen diller
CREATE TABLE IF NOT EXISTS locales (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NULL,
    code VARCHAR(12) NOT NULL,
    label VARCHAR(120) NOT NULL,
    direction ENUM('ltr','rtl') NOT NULL DEFAULT 'ltr',
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY locales_site_code_unique (site_id, code),
    CONSTRAINT fk_locales_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);

-- Çok dilli içerik çevirileri
CREATE TABLE IF NOT EXISTS translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    source_type VARCHAR(60) NOT NULL,
    source_id VARCHAR(190) NOT NULL,
    locale VARCHAR(12) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY translations_unique (site_id, source_type, source_id, locale),
    CONSTRAINT fk_translations_site FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE
);
