Birleştirilmiş Veritabanı Şeması
Mimari Karar: Çekirdek tablolar (users, cities) tüm katmanlar arasında paylaşılır. Her katmanın kendi modül tabloları ayrıdır. IoT zaman serisi verileri TimescaleDB'de, diğer tüm veriler MySQL 8'de tutulur.
-- ═══════════════════════════════════════
-- ÇEKIRDEK (Paylaşılan Tablolar)
-- ═══════════════════════════════════════
TABLE users (id, name, email, phone, role, city_id, farm_type, created_at);
TABLE cities (id, name, region, latitude, longitude);
TABLE notifications (id, user_id, type, title, body, is_read, created_at);
-- ═══════════════════════════════════════
-- KATMAN 1: İÇERİK
-- ═══════════════════════════════════════
TABLE articles (id, title, slug, source, category, body, published_at);
TABLE article_tags (article_id, tag_id);
TABLE crops (id, name, slug, scientific_name, description);
TABLE varieties (id, crop_id, name, characteristic, ttsm_code);
TABLE diseases (id, name, symptoms, causes, period);
TABLE crop_diseases (crop_id, disease_id);
TABLE disease_treatments (disease_id, bku_drug_id, method);
TABLE planting_calendar (crop_id, city_id, month, action_type);
TABLE market_prices (product_name, city_id, price, unit, source, date);
TABLE subscribers (id, user_id, email, preferences_json);
-- ═══════════════════════════════════════
-- KATMAN 2: TİCARET
-- ═══════════════════════════════════════
TABLE sellers (id, user_id, company_name, rating, verification_status);
TABLE products (id, seller_id, category, name, price, stock, specs_json);
TABLE orders (id, buyer_id, seller_id, total_amount, status, iyzico_ref);
TABLE order_items (id, order_id, product_id, quantity, unit_price);
TABLE group_buys (id, product_id, min_quantity, current_qty, end_date, discount_pct);
TABLE group_buy_members (group_buy_id, user_id, quantity);
TABLE price_comparisons (product_id, city_id, price, source, timestamp);
-- ═══════════════════════════════════════
-- KATMAN 3: ÜRETİM YÖNETİMİ
-- ═══════════════════════════════════════
TABLE farms (id, user_id, name, type, city_id, total_area);
TABLE parcels (id, farm_id, name, area, crop_id, soil_type, gps_polygon);
TABLE production_logs (id, parcel_id, action_type, input_product, quantity, date, notes);
TABLE harvests (id, parcel_id, crop_id, yield_kg, quality_grade, harvest_date);
TABLE inventory (id, farm_id, product_name, category, quantity, unit, cost_per_unit);
-- ═══════════════════════════════════════
-- KATMAN 4: VERİ & AI (MySQL)
-- ═══════════════════════════════════════
TABLE iot_devices (id, farm_id, device_type, protocol, status, last_seen);
TABLE satellite_images (id, parcel_id, source, capture_date, cloud_cover, file_url);
TABLE vegetation_indices (image_id, parcel_id, ndvi, evi, ndre, calculated_at);
TABLE disease_detections (id, user_id, crop_id, image_url, model_version, result_json, confidence);
TABLE ai_models (id, name, version, accuracy, size_mb, target_platform);
TABLE yield_predictions (parcel_id, crop_id, season, predicted_yield, actual_yield, model_id);
TABLE price_forecasts (product_id, city_id, predicted_price, actual_price, forecast_date);
TABLE data_exports (id, buyer_type, region_id, data_scope, anonymization_level, export_date);
-- KATMAN 4: VERİ & AI (TimescaleDB — ayrı DB)
TABLE sensor_readings (device_id, metric, value, unit, timestamp); -- hypertable
TABLE weather_data (city_id, source, temp, humidity, wind, rain, forecast_date); -- hypertable
-- ═══════════════════════════════════════
-- KATMAN 5: EĞİTİM & TOPLULUK
-- ═══════════════════════════════════════
TABLE courses (id, title, slug, category, instructor_id, duration_min, level, is_free);
TABLE lessons (id, course_id, title, video_url, content_md, sort_order);
TABLE enrollments (user_id, course_id, progress_pct, completed_at, certificate_url);
TABLE quizzes (id, lesson_id, question, options_json, correct_answer);
TABLE forum_categories (id, name, slug, description);
TABLE forum_topics (id, user_id, category_id, title, body, upvotes, is_answered);
TABLE forum_replies (id, topic_id, user_id, body, upvotes, is_expert_verified);
TABLE consultants (id, user_id, specialty, tyd_certificate, rating, hourly_rate);
TABLE appointments (id, consultant_id, farmer_id, datetime, type, status, payment_status);
TABLE reviews (id, appointment_id, rating, comment);
TABLE grants (id, name, institution, deadline, min_amount, max_amount, eligibility_json);
TABLE grant_applications (user_id, grant_id, status, applied_at);
TABLE badges (id, name, icon_url, criteria_json);
TABLE user_badges (user_id, badge_id, earned_at);
TABLE leaderboard (user_id, region_id, points, rank, period);
Toplam: 45 tablo (43 MySQL + 2 TimescaleDB hypertable). Katmanlar arası bağlantılar: users.id, crops.id, cities.id, products.id foreign key'leri ile sağlanır.