-- WC360 Veri Aktarim Sistemi Database Tablolari
-- Basit versiyon

-- Mevcut toilets tablosuna yeni alanlar ekle
ALTER TABLE toilets 
ADD COLUMN external_id VARCHAR(100) NULL,
ADD COLUMN external_source VARCHAR(50) DEFAULT 'manual',
ADD COLUMN osm_tags JSON NULL,
ADD COLUMN country_code VARCHAR(2) NULL;

-- Import Batches Tablosu
CREATE TABLE IF NOT EXISTS import_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_codes JSON NOT NULL,
    total_countries INT NOT NULL DEFAULT 0,
    processed_countries INT NOT NULL DEFAULT 0,
    status ENUM('started', 'running', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'started',
    progress DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    total_imported INT NOT NULL DEFAULT 0,
    total_errors INT NOT NULL DEFAULT 0,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_by INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Import Logs Tablosu
CREATE TABLE IF NOT EXISTS import_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NOT NULL,
    country_code VARCHAR(2) NOT NULL,
    external_id VARCHAR(100) NOT NULL,
    toilet_id INT NULL,
    status ENUM('imported', 'error', 'skipped', 'duplicate') NOT NULL,
    error_message TEXT NULL,
    osm_data JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Import Activities Tablosu
CREATE TABLE IF NOT EXISTS import_activities (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NOT NULL,
    level ENUM('info', 'success', 'warning', 'error') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Import Countries Tablosu
CREATE TABLE IF NOT EXISTS import_countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_code VARCHAR(2) NOT NULL UNIQUE,
    country_name VARCHAR(100) NOT NULL,
    language_code VARCHAR(5) NOT NULL DEFAULT 'en',
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    last_import_at DATETIME NULL,
    total_imported INT NOT NULL DEFAULT 0,
    success_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Import Settings Tablosu
CREATE TABLE IF NOT EXISTS import_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    setting_type ENUM('string', 'number', 'boolean', 'json') NOT NULL DEFAULT 'string',
    description TEXT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'general',
    is_system BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Varsayilan ayarlari ekle
INSERT IGNORE INTO import_settings (setting_key, setting_value, setting_type, description, category) VALUES
('import_batch_size', '1000', 'number', 'Tek seferde islenecek maksimum kayit sayisi', 'performance'),
('import_timeout', '900', 'number', 'Aktarim timeout suresi (saniye)', 'performance'),
('overpass_api_url', 'https://overpass-api.de/api/interpreter', 'string', 'Overpass API URL', 'external_api');

-- Bazi ulkeleri ekle
INSERT IGNORE INTO import_countries (country_code, country_name, language_code, currency, is_enabled) VALUES
('TR', 'Turkiye', 'tr', 'TRY', TRUE),
('US', 'United States', 'en', 'USD', TRUE),
('GB', 'United Kingdom', 'en', 'GBP', TRUE),
('DE', 'Germany', 'de', 'EUR', TRUE),
('FR', 'France', 'fr', 'EUR', TRUE),
('IT', 'Italy', 'it', 'EUR', TRUE),
('ES', 'Spain', 'es', 'EUR', TRUE); 