-- WC360 Veritabanı Tabloları

-- Kullanıcılar tablosu
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

-- Tuvalet kategorileri tablosu
CREATE TABLE toilet_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7), -- hex color
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tuvaletler tablosu
CREATE TABLE toilets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    address TEXT,
    category_id INT,
    
    -- Özellikler
    is_free BOOLEAN DEFAULT TRUE,
    price DECIMAL(5,2) DEFAULT 0.00,
    is_accessible BOOLEAN DEFAULT FALSE, -- engelli erişimi
    has_baby_change BOOLEAN DEFAULT FALSE,
    has_wifi BOOLEAN DEFAULT FALSE,
    is_24_hours BOOLEAN DEFAULT FALSE,
    
    -- Çalışma saatleri
    opening_hours JSON, -- {"monday": "09:00-18:00", "tuesday": "09:00-18:00", ...}
    
    -- Durum
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    
    -- Meta
    added_by INT, -- user_id
    verified_by INT, -- admin user_id
    view_count INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (category_id) REFERENCES toilet_categories(id),
    FOREIGN KEY (added_by) REFERENCES users(id),
    FOREIGN KEY (verified_by) REFERENCES users(id),
    
    INDEX idx_location (latitude, longitude),
    INDEX idx_category (category_id),
    INDEX idx_active (is_active, deleted_at)
);

-- Tuvalet fotoğrafları tablosu
CREATE TABLE toilet_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toilet_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    image_type ENUM('main', 'interior', 'exterior', 'other') DEFAULT 'other',
    uploaded_by INT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (toilet_id) REFERENCES toilets(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- Yorumlar ve puanlar tablosu
CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toilet_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    
    -- Detaylı puanlama
    cleanliness_rating INT CHECK (cleanliness_rating >= 1 AND cleanliness_rating <= 5),
    accessibility_rating INT CHECK (accessibility_rating >= 1 AND accessibility_rating <= 5),
    
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (toilet_id) REFERENCES toilets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    
    UNIQUE KEY unique_user_toilet (user_id, toilet_id, deleted_at)
);

-- Favoriler tablosu
CREATE TABLE user_favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    toilet_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (toilet_id) REFERENCES toilets(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_favorite (user_id, toilet_id)
);

-- API anahtarları tablosu (gelecekte mobil app için)
CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(100) NOT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    user_id INT,
    permissions JSON, -- ["read", "write", "admin"]
    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id)
); 