blp104-160525

docker-compose.yml

services:
  postgres_real_estate:
    image: postgres:17
    container_name: real_estate_db
    environment:
      POSTGRES_USER: blp104
      POSTGRES_PASSWORD: strong_password
      POSTGRES_DB: real_estate_db
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "pg_isready", "-q"]

  seeder:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: real_estate_seeder
    depends_on:
      postgres_real_estate:
        condition: service_healthy
    environment:
      DB_HOST: postgres_real_estate
      DB_PORT: 5432
      DB_NAME: real_estate_db
      DB_USER: blp104
      DB_PASS: strong_password
      # Diğer seed parametreleri
      NUM_BRANCHES: 5
      NUM_EMPLOYEES_PER_BRANCH_AVG: 5
      NUM_CLIENTS: 100
      NUM_PROPERTIES: 150
      NUM_LISTINGS_PERCENTAGE: 0.8
      NUM_APPOINTMENTS_PER_ACTIVE_LISTING_AVG: 2

volumes:
  postgres_data:

Dockerfile

# Python için resmi bir base image
FROM python:3.9-slim

# Çalışma dizinini ayarla
WORKDIR /app

RUN apt-get update && apt-get install -y postgresql-client nano && rm -rf /var/lib/apt/lists/*

# Bağımlılıklar dosyasını kopyala
COPY requirements.txt requirements.txt

# Bağımlılıkları yükle
RUN pip install --no-cache-dir -r requirements.txt

# Uygulama kodunu kopyala
COPY seed_database.py .

CMD ["python", "seed_database.py"]

init.sql

-- Veritabanı oluşturma (opsiyonel, Docker compose içinde de yapılabilir)
-- CREATE DATABASE real_estate_db;
-- \c real_estate_db; -- Veritabanına bağlan

-- 1. Branches (Şubeler)
CREATE TABLE Branches (
    branch_id SERIAL PRIMARY KEY,
    branch_name VARCHAR(100) NOT NULL UNIQUE,
    address TEXT NOT NULL,
    phone_number VARCHAR(20),
    email VARCHAR(100) UNIQUE,
    manager_id INTEGER
);

-- 2. Employees (Çalışanlar)
CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    branch_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    position VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    CONSTRAINT fk_employee_branch FOREIGN KEY (branch_id) REFERENCES Branches(branch_id) ON DELETE RESTRICT
);

-- Branches tablosundaki manager_id için FOREIGN KEY (Çalışanlar tablosu oluşturulduktan sonra)
ALTER TABLE Branches
ADD CONSTRAINT fk_branch_manager FOREIGN KEY (manager_id) REFERENCES Employees(employee_id) ON DELETE SET NULL;

-- 3. Clients (Müşteriler)
CREATE TABLE Clients (
    client_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    address TEXT,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Property_Types (Emlak Tipleri)
CREATE TABLE Property_Types (
    property_type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE -- e.g., 'Apartment', 'House', 'Land', 'Commercial'
);

-- 5. Properties (Emlaklar)
CREATE TABLE Properties (
    property_id SERIAL PRIMARY KEY,
    property_type_id INTEGER NOT NULL,
    owner_client_id INTEGER, -- Mülkün sahibi olan müşteri (satıcı)
    address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    postal_code VARCHAR(10),
    country VARCHAR(50) DEFAULT 'Türkiye',
    description TEXT,
    number_of_bedrooms INTEGER CHECK (number_of_bedrooms >= 0),
    number_of_bathrooms INTEGER CHECK (number_of_bathrooms >= 0),
    square_meters DECIMAL(10, 2) CHECK (square_meters > 0),
    year_built INTEGER,
    date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_property_type FOREIGN KEY (property_type_id) REFERENCES Property_Types(property_type_id) ON DELETE RESTRICT,
    CONSTRAINT fk_property_owner FOREIGN KEY (owner_client_id) REFERENCES Clients(client_id) ON DELETE SET NULL -- Sahip silinirse mülk kalır ama sahibi null olur
);

-- 6. Features (Özellikler) - Lookup Table
CREATE TABLE Features (
    feature_id SERIAL PRIMARY KEY,
    feature_name VARCHAR(100) NOT NULL UNIQUE -- e.g., 'Swimming Pool', 'Garage', 'Balcony', 'Sea View'
);

-- 7. Property_Features (Emlak Özellikleri) - Junction Table for Many-to-Many
CREATE TABLE Property_Features (
    property_id INTEGER NOT NULL,
    feature_id INTEGER NOT NULL,
    PRIMARY KEY (property_id, feature_id),
    CONSTRAINT fk_pf_property FOREIGN KEY (property_id) REFERENCES Properties(property_id) ON DELETE CASCADE,
    CONSTRAINT fk_pf_feature FOREIGN KEY (feature_id) REFERENCES Features(feature_id) ON DELETE CASCADE
);

-- 8. Listings (İlanlar)
CREATE TYPE listing_status_enum AS ENUM ('Active', 'Pending', 'Sold', 'Rented', 'Expired', 'Cancelled');
CREATE TYPE listing_type_enum AS ENUM ('Sale', 'Rent');

CREATE TABLE Listings (
    listing_id SERIAL PRIMARY KEY,
    property_id INTEGER NOT NULL UNIQUE, -- Bir emlak aynı anda sadece bir aktif ilanda olabilir
    agent_id INTEGER NOT NULL, -- İlanı yöneten çalışan
    branch_id INTEGER NOT NULL, -- İlanın verildiği şube
    listing_date DATE NOT NULL DEFAULT CURRENT_DATE,
    expiration_date DATE,
    price DECIMAL(12, 2) NOT NULL CHECK (price > 0),
    listing_type listing_type_enum NOT NULL,
    status listing_status_enum NOT NULL DEFAULT 'Active',
    description_notes TEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_listing_property FOREIGN KEY (property_id) REFERENCES Properties(property_id) ON DELETE CASCADE,
    CONSTRAINT fk_listing_agent FOREIGN KEY (agent_id) REFERENCES Employees(employee_id) ON DELETE RESTRICT,
    CONSTRAINT fk_listing_branch FOREIGN KEY (branch_id) REFERENCES Branches(branch_id) ON DELETE RESTRICT
);

-- 9. Appointments (Randevular)
CREATE TYPE appointment_status_enum AS ENUM ('Scheduled', 'Completed', 'Cancelled', 'Rescheduled');

CREATE TABLE Appointments (
    appointment_id SERIAL PRIMARY KEY,
    listing_id INTEGER NOT NULL,
    client_id INTEGER NOT NULL, -- Randevuyu alan müşteri
    agent_id INTEGER NOT NULL, -- Randevuyu ayarlayan/eşlik eden çalışan
    appointment_datetime TIMESTAMP NOT NULL,
    status appointment_status_enum NOT NULL DEFAULT 'Scheduled',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_appointment_listing FOREIGN KEY (listing_id) REFERENCES Listings(listing_id) ON DELETE CASCADE,
    CONSTRAINT fk_appointment_client FOREIGN KEY (client_id) REFERENCES Clients(client_id) ON DELETE CASCADE,
    CONSTRAINT fk_appointment_agent FOREIGN KEY (agent_id) REFERENCES Employees(employee_id) ON DELETE RESTRICT
);

-- 10. Transactions (İşlemler)
CREATE TABLE Transactions (
    transaction_id SERIAL PRIMARY KEY,
    listing_id INTEGER NOT NULL,
    buyer_client_id INTEGER NOT NULL, -- Alıcı/Kiracı müşteri
    seller_client_id INTEGER, -- Satıcı müşteri (Properties.owner_client_id'den de alınabilir ama burada da tutmak raporlama için iyi olabilir)
    agent_id INTEGER NOT NULL, -- İşlemi gerçekleştiren çalışan
    branch_id INTEGER NOT NULL, -- İşlemin yapıldığı şube
    transaction_date DATE NOT NULL DEFAULT CURRENT_DATE,
    transaction_type listing_type_enum NOT NULL, -- 'Sale' or 'Rent'
    final_price DECIMAL(12, 2) NOT NULL CHECK (final_price > 0),
    commission_rate DECIMAL(4,2) CHECK (commission_rate >= 0 AND commission_rate <= 100), -- Yüzde olarak
    commission_amount DECIMAL(10,2) GENERATED ALWAYS AS (final_price * commission_rate / 100) STORED, -- Otomatik hesaplanan komisyon
    notes TEXT,
    CONSTRAINT fk_transaction_listing FOREIGN KEY (listing_id) REFERENCES Listings(listing_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_buyer_client FOREIGN KEY (buyer_client_id) REFERENCES Clients(client_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_seller_client FOREIGN KEY (seller_client_id) REFERENCES Clients(client_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_agent FOREIGN KEY (agent_id) REFERENCES Employees(employee_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_branch FOREIGN KEY (branch_id) REFERENCES Branches(branch_id) ON DELETE RESTRICT
);

-- Indexler (Performans için önemli)
CREATE INDEX idx_employees_branch_id ON Employees(branch_id);
CREATE INDEX idx_properties_property_type_id ON Properties(property_type_id);
CREATE INDEX idx_properties_owner_client_id ON Properties(owner_client_id);
CREATE INDEX idx_properties_city ON Properties(city);
CREATE INDEX idx_listings_property_id ON Listings(property_id);
CREATE INDEX idx_listings_agent_id ON Listings(agent_id);
CREATE INDEX idx_listings_branch_id ON Listings(branch_id);
CREATE INDEX idx_listings_status ON Listings(status);
CREATE INDEX idx_listings_type ON Listings(listing_type);
CREATE INDEX idx_appointments_listing_id ON Appointments(listing_id);
CREATE INDEX idx_appointments_client_id ON Appointments(client_id);
CREATE INDEX idx_appointments_agent_id ON Appointments(agent_id);
CREATE INDEX idx_transactions_listing_id ON Transactions(listing_id);
CREATE INDEX idx_transactions_buyer_client_id ON Transactions(buyer_client_id);
CREATE INDEX idx_transactions_agent_id ON Transactions(agent_id);

requirements.txt

Faker
psycopg2-binary

seed_database.py

import psycopg2
from psycopg2 import extras
from faker import Faker
import random
from datetime import datetime, timedelta
import os
import time

# Veritabanı bağlantı bilgileri (environment variable'lardan alınacak)
DB_HOST = os.environ.get("DB_HOST", "localhost")
DB_PORT = os.environ.get("DB_PORT", "5432")
DB_NAME = os.environ.get("DB_NAME", "real_estate_db")
DB_USER = os.environ.get("DB_USER", "emlak_user")
DB_PASS = os.environ.get("DB_PASS")

# Kaç adet kayıt oluşturulacağı
NUM_BRANCHES = int(os.environ.get("NUM_BRANCHES", 5))
NUM_EMPLOYEES_PER_BRANCH_AVG = int(os.environ.get("NUM_EMPLOYEES_PER_BRANCH_AVG", 3))
NUM_CLIENTS = int(os.environ.get("NUM_CLIENTS", 50))
NUM_PROPERTIES = int(os.environ.get("NUM_PROPERTIES", 70))
NUM_LISTINGS_PERCENTAGE = float(os.environ.get("NUM_LISTINGS_PERCENTAGE", 0.8))
NUM_APPOINTMENTS_PER_ACTIVE_LISTING_AVG = int(os.environ.get("NUM_APPOINTMENTS_PER_ACTIVE_LISTING_AVG", 2))
NUM_TRANSACTIONS_PERCENTAGE = float(os.environ.get("NUM_TRANSACTIONS_PERCENTAGE", 0.2))


fake = Faker('tr_TR')

# Yardımcı Fonksiyonlar
def get_random_item(item_list):
    return random.choice(item_list) if item_list else None

def generate_phone_number():
    return f"05{random.randint(30, 59):02d}{random.randint(100, 999):03d}{random.randint(10, 99):02d}{random.randint(10, 99):02d}"

# Ana Seed Fonksiyonu
def seed_data():
    # Hata ayıklama için ortam değişkenlerini yazdır
    print(f"--- Seeder Bağlantı Bilgileri ---")
    print(f"DB_HOST: {DB_HOST}")
    print(f"DB_PORT: {DB_PORT}")
    print(f"DB_NAME: {DB_NAME}")
    print(f"DB_USER: {DB_USER}")
    print(f"DB_PASS (set mi?): {'Evet' if DB_PASS and len(DB_PASS) > 0 else 'Hayır veya Boş'}")
    print(f"----------------------------------")

    conn = None
    retries = 10
    wait_time = 5

    if not DB_PASS:
        print("HATA: DB_PASS ortam değişkeni ayarlanmamış veya boş. Script sonlandırılıyor.")
        return

    for i in range(retries):
        try:
            print(f"Veritabanına bağlanmaya çalışılıyor ({DB_HOST}:{DB_PORT})... Deneme {i+1}/{retries}")
            conn = psycopg2.connect(host=DB_HOST, port=DB_PORT, dbname=DB_NAME, user=DB_USER, password=DB_PASS)
            cur = conn.cursor()
            print("Veritabanı bağlantısı başarılı!")
            break
        except psycopg2.OperationalError as e:
            print(f"Bağlantı hatası: {e}")
            if i < retries - 1:
                print(f"{wait_time} saniye sonra tekrar denenecek...")
                time.sleep(wait_time)
            else:
                print("Maksimum deneme sayısına ulaşıldı. Script sonlandırılıyor.")
                return

    if not conn:
        return

    try:
        print("Mevcut veriler temizleniyor (CASCADE)...")
        tables_to_truncate = [
            "Transactions", "Appointments", "Listings", "Property_Features",
            "Properties", "Clients", "Employees", "Branches", "Features", "Property_Types"
        ] # Sıralama önemli FK'lar yüzünden (en bağımlıdan en aza doğru)
        for table in tables_to_truncate:
            try:
                cur.execute(f"TRUNCATE TABLE {table} RESTART IDENTITY CASCADE;")
                print(f"- {table} tablosu temizlendi.")
            except psycopg2.Error as e:
                print(f"Hata: {table} temizlenirken: {e}")
                conn.rollback()
        conn.commit()
        print("Temizleme tamamlandı.\n")


        # 1. Property_Types (Emlak Tipleri)
        print("Property_Types oluşturuluyor...")
        property_types_data_tuples = [('Apartment',), ('House',), ('Land',), ('Commercial Building',), ('Office',)]
        property_type_ids = []
        for pt_data in property_types_data_tuples:
            cur.execute("INSERT INTO Property_Types (type_name) VALUES (%s) RETURNING property_type_id;", pt_data)
            property_type_id = cur.fetchone()[0]
            property_type_ids.append(property_type_id)
        conn.commit()
        print(f"{len(property_type_ids)} emlak tipi oluşturuldu.\n")

        # 2. Features (Özellikler)
        print("Features oluşturuluyor...")
        features_data_tuples = [
            ('Swimming Pool',), ('Garage',), ('Balcony',), ('Sea View',), ('Garden',),
            ('Security System',), ('Furnished',), ('Fireplace',), ('Central Heating',), ('Air Conditioning',)
        ]
        feature_ids = []
        for f_data in features_data_tuples:
            cur.execute("INSERT INTO Features (feature_name) VALUES (%s) RETURNING feature_id;", f_data)
            feature_id = cur.fetchone()[0]
            feature_ids.append(feature_id)
        conn.commit()
        print(f"{len(feature_ids)} özellik oluşturuldu.\n")

        # 3. Branches (Şubeler)
        print("Branches oluşturuluyor...")
        branch_ids = []
        for _ in range(NUM_BRANCHES):
            city = fake.city()
            branch_tuple = (
                f"{city} {fake.company_suffix()}",
                fake.address(),
                generate_phone_number(),
                fake.unique.company_email()
            )
            cur.execute(
                "INSERT INTO Branches (branch_name, address, phone_number, email) VALUES (%s, %s, %s, %s) RETURNING branch_id;",
                branch_tuple
            )
            branch_ids.append(cur.fetchone()[0])
        conn.commit()
        print(f"{len(branch_ids)} şube oluşturuldu.\n")

        # 4. Employees (Çalışanlar) & Branch Manager'ları Güncelle
        print("Employees oluşturuluyor ve Branch Manager'lar atanıyor...")
        employee_details = [] # (employee_id, branch_id, position) saklamak için
        possible_positions = ['Agent', 'Senior Agent', 'Consultant', 'Assistant']

        if not branch_ids:
            print("UYARI: Hiç şube oluşturulmadı, çalışan eklenemiyor.")
        else:
            for branch_id_val in branch_ids:
                # Her şubeye bir yönetici
                emp_data_manager = (
                    branch_id_val, fake.first_name(), fake.last_name(), 'Manager',
                    generate_phone_number(), fake.unique.email(),
                    fake.date_between(start_date='-10y', end_date='-1y'),
                    random.randint(15000, 30000)
                )
                cur.execute(
                    "INSERT INTO Employees (branch_id, first_name, last_name, position, phone_number, email, hire_date, salary) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING employee_id;",
                    emp_data_manager
                )
                manager_id = cur.fetchone()[0]
                employee_details.append({'id': manager_id, 'branch_id': branch_id_val, 'position': 'Manager'})
                cur.execute("UPDATE Branches SET manager_id = %s WHERE branch_id = %s;", (manager_id, branch_id_val))

                for _ in range(random.randint(1, NUM_EMPLOYEES_PER_BRANCH_AVG * 2 -1)):
                    position = random.choice(possible_positions)
                    emp_data = (
                        branch_id_val, fake.first_name(), fake.last_name(), position,
                        generate_phone_number(), fake.unique.email(),
                        fake.date_between(start_date='-5y', end_date='today'),
                        random.randint(8000, 18000) if position != 'Senior Agent' else random.randint(12000, 22000)
                    )
                    cur.execute(
                        "INSERT INTO Employees (branch_id, first_name, last_name, position, phone_number, email, hire_date, salary) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING employee_id;",
                        emp_data
                    )
                    employee_id = cur.fetchone()[0]
                    employee_details.append({'id': employee_id, 'branch_id': branch_id_val, 'position': position})
            conn.commit()
            print(f"{len(employee_details)} çalışan oluşturuldu ve şube yöneticileri atandı.\n")

        # 5. Clients (Müşteriler)
        print("Clients oluşturuluyor...")
        client_ids = []
        for _ in range(NUM_CLIENTS):
            client_tuple = (
                fake.first_name(), fake.last_name(), generate_phone_number(),
                fake.unique.email(), fake.address(),
                fake.date_time_this_decade(before_now=True, after_now=False)
            )
            cur.execute(
                "INSERT INTO Clients (first_name, last_name, phone_number, email, address, registration_date) VALUES (%s, %s, %s, %s, %s, %s) RETURNING client_id;",
                client_tuple
            )
            client_ids.append(cur.fetchone()[0])
        conn.commit()
        print(f"{len(client_ids)} müşteri oluşturuldu.\n")

        # 6. Properties (Emlaklar)
        print("Properties oluşturuluyor...")
        property_data_with_owners = []
        if not property_type_ids or not client_ids:
            print("UYARI: Emlak tipi veya müşteri yok, emlak eklenemiyor.")
        else:
            for _ in range(NUM_PROPERTIES):
                prop_type_id = get_random_item(property_type_ids)
                owner_id = get_random_item(client_ids)
                city = fake.city()
                year_built = int(fake.year()) if random.random() > 0.1 else None
                bedrooms = random.randint(0, 6)
                bathrooms = random.randint(0, max(1, bedrooms // 2 + 1))

                prop_tuple = (
                    prop_type_id, owner_id, fake.street_address(), city, fake.postcode(), 'Türkiye',
                    fake.paragraph(nb_sentences=3), bedrooms, bathrooms, round(random.uniform(30.0, 500.0), 2),
                    year_built, fake.date_time_this_year(before_now=True, after_now=False)
                )
                cur.execute(
                    """INSERT INTO Properties (property_type_id, owner_client_id, address, city, postal_code, country,
                                            description, number_of_bedrooms, number_of_bathrooms, square_meters,
                                            year_built, date_added)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING property_id, owner_client_id;""",
                    prop_tuple
                )
                row = cur.fetchone()
                property_data_with_owners.append({'id': row[0], 'owner_id': row[1]})
            conn.commit()
        property_ids = [p['id'] for p in property_data_with_owners]
        print(f"{len(property_ids)} emlak oluşturuldu.\n")

        # 7. Property_Features (Emlak Özellikleri)
        print("Property_Features oluşturuluyor...")
        property_features_data_to_insert = []
        if property_ids and feature_ids:
            for prop_id in property_ids:
                num_features_for_prop = random.randint(0, 5)
                if num_features_for_prop > 0:
                    selected_feature_ids = random.sample(feature_ids, min(num_features_for_prop, len(feature_ids)))
                    for feat_id in selected_feature_ids:
                        property_features_data_to_insert.append((prop_id, feat_id))
            if property_features_data_to_insert:
                try:
                    extras.execute_values(
                        cur,
                        "INSERT INTO Property_Features (property_id, feature_id) VALUES %s ON CONFLICT DO NOTHING",
                        property_features_data_to_insert,
                        page_size=100 # Büyük veri setleri için
                    )
                    conn.commit()
                    print(f"{len(property_features_data_to_insert)} emlak-özellik ilişkisi oluşturuldu.\n")
                except psycopg2.Error as e:
                    print(f"Property_Features eklenirken hata: {e}")
                    conn.rollback()
        else:
            print("UYARI: Emlak veya özellik ID'leri bulunamadı, Property_Features eklenemiyor.\n")


        # 8. Listings (İlanlar)
        print("Listings oluşturuluyor...")
        listing_details = []

        if property_ids and employee_details:
            num_props_to_list = int(len(property_ids) * NUM_LISTINGS_PERCENTAGE)
            # Benzersiz property_id'ler için sample kullanıyoruz, çünkü bir emlak birden fazla aktif ilanda olmamalı (UNIQUE kısıtı)
            properties_to_list_ids = random.sample(property_ids, min(num_props_to_list, len(property_ids)))

            for prop_id in properties_to_list_ids:
                agent_detail = get_random_item([emp for emp in employee_details if emp['position'] != 'Manager']) # Yöneticiler ilan açmasın
                if not agent_detail: # Eğer sadece managerlar varsa veya hiç çalışan yoksa rastgele birini seç
                    agent_detail = get_random_item(employee_details)

                if not agent_detail: continue # Hala çalışan yoksa atla

                agent_id = agent_detail['id']
                branch_id_of_agent = agent_detail['branch_id']

                listing_date = fake.date_object(end_datetime=datetime.now() - timedelta(days=1))
                expiration_date = listing_date + timedelta(days=random.randint(60, 180))
                listing_type = random.choice(['Sale', 'Rent'])
                price = random.randint(500000, 10000000) if listing_type == 'Sale' else random.randint(1000, 30000)
                status = random.choice(['Active', 'Pending', 'Expired'])

                listing_tuple = (
                    prop_id, agent_id, branch_id_of_agent, listing_date, expiration_date, price,
                    listing_type, status, fake.sentence(nb_words=10)
                )
                try:
                    cur.execute(
                        """INSERT INTO Listings (property_id, agent_id, branch_id, listing_date, expiration_date, price,
                                                listing_type, status, description_notes)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                        RETURNING listing_id, property_id, agent_id, branch_id, listing_type, status, listing_date, expiration_date;""",
                        listing_tuple
                    )
                    row = cur.fetchone()
                    listing_details.append({
                        'id': row[0], 'property_id': row[1], 'agent_id': row[2], 'branch_id': row[3],
                        'type': row[4], 'status': row[5], 'listing_date': row[6], 'expiration_date': row[7]
                    })
                except psycopg2.IntegrityError as e: # Unique constraint (property_id) ihlali olabilir
                    print(f"İlan eklenirken IntegrityError (muhtemelen property_id zaten listede): {e} - Atlanıyor.")
                    conn.rollback() # Hatalı işlemi geri al
                    continue # Sonraki ilana geç
            conn.commit()
            print(f"{len(listing_details)} ilan oluşturuldu.\n")
        else:
            print("UYARI: Emlak veya çalışan bulunamadı, ilan eklenemiyor.\n")


        # 9. Appointments (Randevular)
        print("Appointments oluşturuluyor...")
        appointments_to_insert = []
        active_or_pending_listings = [l for l in listing_details if l['status'] in ['Active', 'Pending']]

        if active_or_pending_listings and client_ids:
            for lst_detail in active_or_pending_listings:
                for _ in range(random.randint(0, NUM_APPOINTMENTS_PER_ACTIVE_LISTING_AVG * 2)):
                    client_id = get_random_item(client_ids)
                    if not client_id: continue

                    start_appointment_dt = lst_detail['listing_date'] + timedelta(days=1)
                    end_appointment_dt = lst_detail['expiration_date'] if lst_detail['expiration_date'] and lst_detail['expiration_date'] > start_appointment_dt else start_appointment_dt + timedelta(days=30)

                    if start_appointment_dt >= end_appointment_dt: # Geçerli aralık yoksa
                         appointment_dt = fake.date_time_between_dates(datetime_start=start_appointment_dt, datetime_end=start_appointment_dt + timedelta(days=7))
                    else:
                         appointment_dt = fake.date_time_between_dates(datetime_start=start_appointment_dt, datetime_end=end_appointment_dt)

                    appointments_to_insert.append((
                        lst_detail['id'], client_id, lst_detail['agent_id'],
                        appointment_dt,
                        random.choice(['Scheduled', 'Completed', 'Cancelled', 'Rescheduled']),
                        fake.sentence(nb_words=5)
                    ))
            if appointments_to_insert:
                try:
                    extras.execute_values(
                        cur,
                        """INSERT INTO Appointments (listing_id, client_id, agent_id, appointment_datetime, status, notes)
                        VALUES %s""",
                        appointments_to_insert,
                        page_size=100
                    )
                    conn.commit()
                    print(f"{len(appointments_to_insert)} randevu oluşturuldu.\n")
                except psycopg2.Error as e:
                    print(f"Randevu eklenirken hata: {e}")
                    conn.rollback()
        else:
             print("UYARI: Aktif ilan veya müşteri bulunamadı, randevu eklenemiyor.\n")


        # 10. Transactions (İşlemler)
        print("Transactions oluşturuluyor...")
        transactions_to_insert = []
        updated_listing_statuses = []

        eligible_listings_for_transaction = [
            l for l in listing_details if l['status'] == 'Active' and (l['expiration_date'] is None or l['expiration_date'] >= datetime.now().date())
        ]
        num_transactions_to_create = int(len(eligible_listings_for_transaction) * NUM_TRANSACTIONS_PERCENTAGE)

        if eligible_listings_for_transaction and client_ids:
            listings_for_transaction = random.sample(
                eligible_listings_for_transaction,
                min(num_transactions_to_create, len(eligible_listings_for_transaction))
            )

            for lst_detail in listings_for_transaction:
                buyer_client_id = get_random_item(client_ids)
                # Emlak sahibini bul
                prop_owner_info = next((p for p in property_data_with_owners if p['id'] == lst_detail['property_id']), None)
                if not prop_owner_info: continue
                seller_client_id = prop_owner_info['owner_id']

                if not buyer_client_id or not seller_client_id: continue
                if buyer_client_id == seller_client_id : # Alıcı satıcı ile aynı olamaz
                    temp_clients = [c_id for c_id in client_ids if c_id != seller_client_id]
                    if not temp_clients: continue
                    buyer_client_id = get_random_item(temp_clients)
                    if not buyer_client_id : continue


                transaction_date = fake.date_between_dates(date_start=lst_detail['listing_date'], date_end=datetime.now().date())

                cur.execute("SELECT price FROM Listings WHERE listing_id = %s", (lst_detail['id'],))
                original_price_row = cur.fetchone()
                if not original_price_row: continue
                original_price = original_price_row[0]

                final_price = round(float(original_price) * random.uniform(0.90, 1.02), 2)
                commission_rate = round(random.uniform(1.5, 4.0), 2)

                transactions_to_insert.append((
                    lst_detail['id'], buyer_client_id, seller_client_id, lst_detail['agent_id'],
                    lst_detail['branch_id'], transaction_date, lst_detail['type'],
                    final_price, commission_rate, fake.sentence(nb_words=8)
                ))

                new_status = 'Sold' if lst_detail['type'] == 'Sale' else 'Rented'
                updated_listing_statuses.append((new_status, lst_detail['id']))

            if transactions_to_insert:
                try:
                    extras.execute_values(
                        cur,
                        """INSERT INTO Transactions (listing_id, buyer_client_id, seller_client_id, agent_id, branch_id,
                                                transaction_date, transaction_type, final_price, commission_rate, notes)
                        VALUES %s""",
                        transactions_to_insert,
                        page_size=100
                    )
                    if updated_listing_statuses:
                        extras.execute_values(
                            cur,
                            "UPDATE Listings SET status = data.status FROM (VALUES %s) AS data (status, listing_id) WHERE Listings.listing_id = data.listing_id;",
                            updated_listing_statuses,
                            page_size=100
                        )
                    conn.commit()
                    print(f"{len(transactions_to_insert)} işlem oluşturuldu ve ilgili ilan durumları güncellendi.\n")
                except psycopg2.Error as e:
                    print(f"İşlem eklenirken hata: {e}")
                    conn.rollback()
        else:
            print("UYARI: İşlem için uygun ilan veya müşteri bulunamadı.\n")


        print("Seed işlemi başarıyla tamamlandı!")

    except psycopg2.Error as e:
        print(f"Veritabanı hatası: {e}")
        if conn:
            conn.rollback()
    except Exception as e:
        print(f"Beklenmedik bir hata oluştu: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            cur.close()
            conn.close()
            print("Veritabanı bağlantısı kapatıldı.")

if __name__ == "__main__":
    seed_data()