-- ============================================================
-- SatisDB (Gelistirici Surumu) — SQL alistirma veritabani
-- Icerik: Musteriler(1.200) Siparisler(4.800) SiparisDetay(12.000)
--         Urunler(24) DimTarih(730) + BuyukSiparisler(100.000)
--         100K'lik tablo INDEKSSIZ birakildi: index ve query tuning
--         makalelerindeki olcumleri kendiniz tekrarlayabilirsiniz.
--         Veri kalitesi alistirmalari icin kasitli kirli kayitlar da var.
-- Kullanim: SSMS'te yeni sorgu penceresinde acin, F5 ile calistirin.
--           Mevcut SatisDB varsa silinip yeniden kurulur. (~30 sn)
-- Kaynak: veriakademi.com
-- ============================================================
-- SatisDB: ofisdata SQL makaleleri icin ornek veritabani
-- 1200 musteri / 4800 siparis / detay satirlari / takvim
-- + veri kalitesi makalesi icin kasitli kirli kayitlar
SET NOCOUNT ON;

IF DB_ID('SatisDB') IS NOT NULL
BEGIN
    ALTER DATABASE SatisDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SatisDB;
END;
CREATE DATABASE SatisDB;

GO
USE SatisDB;
GO
-- SatisDB tablo + veri uretimi (SatisDB baglaminda calistirilir)
SET NOCOUNT ON;
SET DATEFIRST 1;  -- 1 = Pazartesi (gun adi CASE'leri buna gore)

-- ============ TABLOLAR ============
CREATE TABLE dbo.Musteriler (
    MusteriID     INT IDENTITY(1,1) PRIMARY KEY,
    MusteriAdi    NVARCHAR(80)  NOT NULL,
    Sehir         NVARCHAR(40)  NOT NULL,
    Email         NVARCHAR(120) NULL,
    KayitTarihi   DATE          NOT NULL
);

CREATE TABLE dbo.Urunler (
    UrunID      INT IDENTITY(1,1) PRIMARY KEY,
    UrunAdi     NVARCHAR(80) NOT NULL,
    Kategori    NVARCHAR(40) NOT NULL,
    BirimFiyat  DECIMAL(10,2) NOT NULL
);

-- Siparisler: BILEREK foreign key YOK (veri kalitesi makalesindeki
-- yetim kayit senaryosu gercek sorgularla gosterilebilsin diye)
CREATE TABLE dbo.Siparisler (
    SiparisID     INT IDENTITY(1000,1) PRIMARY KEY,
    MusteriID     INT      NOT NULL,
    SiparisTarihi DATE     NOT NULL,
    ToplamTutar   DECIMAL(12,2) NOT NULL
);

CREATE TABLE dbo.SiparisDetay (
    SatirID    INT IDENTITY(1,1) PRIMARY KEY,
    SiparisID  INT NOT NULL,
    UrunID     INT NOT NULL,
    Adet       INT NOT NULL,
    BirimFiyat DECIMAL(10,2) NOT NULL
);

CREATE TABLE dbo.DimTarih (
    Tarih      DATE PRIMARY KEY,
    Yil        INT NOT NULL,
    Ay         INT NOT NULL,
    AyAdi      NVARCHAR(15) NOT NULL,
    Ceyrek     INT NOT NULL,
    Gun        INT NOT NULL,
    GunAdi     NVARCHAR(15) NOT NULL,
    HaftaSonu  BIT NOT NULL
);

-- ============ URUNLER (24 ofis urunu) ============
INSERT INTO dbo.Urunler (UrunAdi, Kategori, BirimFiyat) VALUES
(N'A4 Fotokopi Kagidi 500lu',   N'Kirtasiye',   185.00),
(N'Tukenmez Kalem Mavi 50li',   N'Kirtasiye',   240.00),
(N'Spiral Defter A5',           N'Kirtasiye',    65.00),
(N'Zimba Makinesi',             N'Kirtasiye',   145.00),
(N'Klasor Genis Sirt',          N'Kirtasiye',    78.00),
(N'Post-it Not 5 Renk',         N'Kirtasiye',    95.00),
(N'Beyaz Tahta Kalemi 4lu',     N'Kirtasiye',   120.00),
(N'Evrak Rafi Metal',           N'Kirtasiye',   310.00),
(N'Kablosuz Mouse',             N'Elektronik',  450.00),
(N'Mekanik Klavye',             N'Elektronik', 1250.00),
(N'USB Bellek 64GB',            N'Elektronik',  380.00),
(N'Webcam Full HD',             N'Elektronik',  890.00),
(N'Kulaklik Mikrofonlu',        N'Elektronik',  675.00),
(N'Monitor Yukseltici Stand',   N'Elektronik',  520.00),
(N'HDMI Kablo 2m',              N'Elektronik',  150.00),
(N'Cok Girisli USB Hub',        N'Elektronik',  425.00),
(N'Toner Kartus Siyah',         N'Sarf',       1480.00),
(N'Murekkep Kartus Renkli',     N'Sarf',        960.00),
(N'Etiket Rulosu Termal',       N'Sarf',        220.00),
(N'Laminasyon Filmi A4 100lu', N'Sarf',        340.00),
(N'Ofis Koltugu Ergonomik',     N'Mobilya',    5400.00),
(N'Calisma Masasi 140cm',       N'Mobilya',    4200.00),
(N'Dosya Dolabi 4 Cekmece',     N'Mobilya',    3800.00),
(N'Ayakli Aydinlatma',          N'Mobilya',    1150.00);

-- ============ MUSTERILER (1200) ============
;WITH N AS (
    SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.Musteriler (MusteriAdi, Sehir, Email, KayitTarihi)
SELECT
    ad.Ad + N' ' + soy.Soyad,
    sh.Sehir,
    LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        ad.Ad + N'.' + soy.Soyad,
        N'ç',N'c'),N'ğ',N'g'),N'ı',N'i'),N'ö',N'o'),N'ş',N's'),N'ü',N'u'),N' ',N''))
        + CAST(rn AS NVARCHAR(10)) + N'@ornekmail.com',
    DATEADD(DAY, -(rn % 900) - 30, '2026-08-01')
FROM N
CROSS APPLY (SELECT CASE rn % 20
    WHEN 0 THEN N'Ahmet' WHEN 1 THEN N'Ayşe' WHEN 2 THEN N'Mehmet' WHEN 3 THEN N'Elif'
    WHEN 4 THEN N'Mustafa' WHEN 5 THEN N'Zeynep' WHEN 6 THEN N'Emre' WHEN 7 THEN N'Selin'
    WHEN 8 THEN N'Burak' WHEN 9 THEN N'Deniz' WHEN 10 THEN N'Cem' WHEN 11 THEN N'Gamze'
    WHEN 12 THEN N'Okan' WHEN 13 THEN N'Merve' WHEN 14 THEN N'Serkan' WHEN 15 THEN N'Pınar'
    WHEN 16 THEN N'Tolga' WHEN 17 THEN N'Esra' WHEN 18 THEN N'Kerem' ELSE N'Büşra' END AS Ad) ad
CROSS APPLY (SELECT CASE rn % 17
    WHEN 0 THEN N'Yılmaz' WHEN 1 THEN N'Kaya' WHEN 2 THEN N'Demir' WHEN 3 THEN N'Çelik'
    WHEN 4 THEN N'Şahin' WHEN 5 THEN N'Öztürk' WHEN 6 THEN N'Aydın' WHEN 7 THEN N'Arslan'
    WHEN 8 THEN N'Doğan' WHEN 9 THEN N'Kılıç' WHEN 10 THEN N'Aslan' WHEN 11 THEN N'Çetin'
    WHEN 12 THEN N'Kara' WHEN 13 THEN N'Koç' WHEN 14 THEN N'Kurt' WHEN 15 THEN N'Özdemir'
    ELSE N'Polat' END AS Soyad) soy
CROSS APPLY (SELECT CASE
    WHEN rn % 100 < 28 THEN N'İstanbul' WHEN rn % 100 < 45 THEN N'Ankara'
    WHEN rn % 100 < 58 THEN N'İzmir'    WHEN rn % 100 < 68 THEN N'Bursa'
    WHEN rn % 100 < 77 THEN N'Antalya'  WHEN rn % 100 < 84 THEN N'Kocaeli'
    WHEN rn % 100 < 90 THEN N'Konya'    WHEN rn % 100 < 95 THEN N'Gaziantep'
    ELSE N'Adana' END AS Sehir) sh;

-- kirli veri: 15 NULL email, 8 bos email, 5 duplike email
UPDATE dbo.Musteriler SET Email = NULL  WHERE MusteriID % 80 = 3;   -- 15 adet
UPDATE dbo.Musteriler SET Email = N''   WHERE MusteriID % 150 = 7;  -- 8 adet
UPDATE dbo.Musteriler SET Email = N'muhasebe@ornekfirma.com' WHERE MusteriID IN (101,205,318,442,559);

-- ============ SIPARISLER (4800; 12 yetim + kirli tutar/tarih) ============
;WITH N AS (
    SELECT TOP (4788) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.Siparisler (MusteriID, SiparisTarihi, ToplamTutar)
SELECT
    ((rn * 37) % 1140) + 1,                                   -- ilk ~1140 musteriye dagit (60 musteri hic siparissiz kalir)
    DATEADD(DAY, -((rn * 13) % 590), '2026-08-15'),           -- son ~19 ay
    0
FROM N;

-- 12 yetim siparis (var olmayan musteri ID'leri)
INSERT INTO dbo.Siparisler (MusteriID, SiparisTarihi, ToplamTutar)
VALUES (9001,'2026-03-14',0),(9002,'2026-03-21',0),(9003,'2026-04-02',0),
       (9010,'2026-04-18',0),(9011,'2026-05-05',0),(9012,'2026-05-19',0),
       (9020,'2026-06-01',0),(9021,'2026-06-12',0),(9022,'2026-06-27',0),
       (9030,'2026-07-08',0),(9031,'2026-07-20',0),(9032,'2026-08-02',0);

-- ============ SIPARIS DETAY (siparis basina 1-4 satir) ============
;WITH S AS (SELECT SiparisID, ROW_NUMBER() OVER (ORDER BY SiparisID) rn FROM dbo.Siparisler),
K AS (SELECT 1 k UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)
INSERT INTO dbo.SiparisDetay (SiparisID, UrunID, Adet, BirimFiyat)
SELECT s.SiparisID,
       ((s.rn * 7 + k.k * 5) % 24) + 1,
       ((s.rn + k.k) % 5) + 1,
       0
FROM S s JOIN K k ON k.k <= (s.rn % 4) + 1;

-- detay birim fiyatlarini urun listesinden cek
UPDATE d SET d.BirimFiyat = u.BirimFiyat
FROM dbo.SiparisDetay d JOIN dbo.Urunler u ON u.UrunID = d.UrunID;

-- 7 satirda kasitli fiyat tutarsizligi (liste fiyatindan sapma)
UPDATE dbo.SiparisDetay SET BirimFiyat = BirimFiyat * 0.5 WHERE SatirID % 1700 = 11;

-- siparis toplamlarini detaydan hesapla
UPDATE s SET s.ToplamTutar = t.Toplam
FROM dbo.Siparisler s
JOIN (SELECT SiparisID, SUM(Adet * BirimFiyat) Toplam
      FROM dbo.SiparisDetay GROUP BY SiparisID) t
  ON t.SiparisID = s.SiparisID;

-- kirli: 6 sifir/negatif tutar, 4 gelecek tarihli siparis
UPDATE dbo.Siparisler SET ToplamTutar = 0     WHERE SiparisID IN (1234,2345,3456);
UPDATE dbo.Siparisler SET ToplamTutar = -1250 WHERE SiparisID IN (1500,2600,3700);
UPDATE dbo.Siparisler SET SiparisTarihi = '2027-01-15' WHERE SiparisID IN (1111,2222);
UPDATE dbo.Siparisler SET SiparisTarihi = '2026-11-30' WHERE SiparisID IN (3333,4444);

-- ============ TAKVIM (2025-01-01 .. 2026-12-31) ============
;WITH T AS (
    SELECT TOP (730) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1,
                             CAST('2025-01-01' AS DATE)) Tarih
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.DimTarih
SELECT Tarih, YEAR(Tarih), MONTH(Tarih),
    CASE MONTH(Tarih) WHEN 1 THEN N'Ocak' WHEN 2 THEN N'Şubat' WHEN 3 THEN N'Mart'
        WHEN 4 THEN N'Nisan' WHEN 5 THEN N'Mayıs' WHEN 6 THEN N'Haziran'
        WHEN 7 THEN N'Temmuz' WHEN 8 THEN N'Ağustos' WHEN 9 THEN N'Eylül'
        WHEN 10 THEN N'Ekim' WHEN 11 THEN N'Kasım' ELSE N'Aralık' END,
    DATEPART(QUARTER, Tarih), DAY(Tarih),
    CASE DATEPART(WEEKDAY, Tarih) WHEN 1 THEN N'Pazartesi' WHEN 2 THEN N'Salı'
        WHEN 3 THEN N'Çarşamba' WHEN 4 THEN N'Perşembe' WHEN 5 THEN N'Cuma'
        WHEN 6 THEN N'Cumartesi' ELSE N'Pazar' END,
    CASE WHEN DATEPART(WEEKDAY, Tarih) IN (6,7) THEN 1 ELSE 0 END
FROM T;

-- ============ INDEKSLER (performans makalesi oncesi/sonrasi icin
--              Siparisler.MusteriID BILEREK indekssiz birakildi) ============
CREATE INDEX IX_SiparisDetay_SiparisID ON dbo.SiparisDetay (SiparisID);

-- ============ OZET ============
SELECT 'Musteriler' Tablo, COUNT(*) Satir FROM dbo.Musteriler
UNION ALL SELECT 'Urunler', COUNT(*) FROM dbo.Urunler
UNION ALL SELECT 'Siparisler', COUNT(*) FROM dbo.Siparisler
UNION ALL SELECT 'SiparisDetay', COUNT(*) FROM dbo.SiparisDetay
UNION ALL SELECT 'DimTarih', COUNT(*) FROM dbo.DimTarih;

GO
-- SatisDB'ye performans alistirma tablosu: BuyukSiparisler (100.000 satir)
-- Index stratejileri ve query tuning makalelerinde gercek olcum icin.
SET NOCOUNT ON;

IF OBJECT_ID('dbo.BuyukSiparisler','U') IS NOT NULL DROP TABLE dbo.BuyukSiparisler;

CREATE TABLE dbo.BuyukSiparisler (
    SiparisID     INT IDENTITY(1,1) PRIMARY KEY,
    MusteriID     INT           NOT NULL,
    UrunID        INT           NOT NULL,
    Sehir         NVARCHAR(40)  NOT NULL,
    SiparisTarihi DATE          NOT NULL,
    Adet          INT           NOT NULL,
    Tutar         DECIMAL(12,2) NOT NULL,
    Durum         NVARCHAR(20)  NOT NULL
);

;WITH N AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.BuyukSiparisler (MusteriID, UrunID, Sehir, SiparisTarihi, Adet, Tutar, Durum)
SELECT
    ((rn * 37) % 1200) + 1,
    ((rn * 7) % 24) + 1,
    CASE
        WHEN rn % 100 < 28 THEN N'İstanbul' WHEN rn % 100 < 45 THEN N'Ankara'
        WHEN rn % 100 < 58 THEN N'İzmir'    WHEN rn % 100 < 68 THEN N'Bursa'
        WHEN rn % 100 < 77 THEN N'Antalya'  WHEN rn % 100 < 84 THEN N'Kocaeli'
        WHEN rn % 100 < 90 THEN N'Konya'    WHEN rn % 100 < 95 THEN N'Gaziantep'
        ELSE N'Adana' END,
    DATEADD(DAY, -((rn * 13) % 730), '2026-08-15'),
    ((rn + 3) % 9) + 1,
    (((rn * 53) % 4800) + 150) * 1.0 + ((rn % 100) / 100.0),
    CASE WHEN rn % 25 = 0 THEN N'Iptal'
         WHEN rn % 11 = 0 THEN N'Beklemede'
         ELSE N'Tamamlandi' END
FROM N;

-- BILEREK: MusteriID / Sehir / SiparisTarihi uzerinde indeks YOK
-- (makalelerde "once/sonra" olcumleri icin okuyucu kendisi ekleyecek)

SELECT 'BuyukSiparisler' AS Tablo, COUNT(*) AS Satir FROM dbo.BuyukSiparisler;
SELECT Durum, COUNT(*) AS Adet FROM dbo.BuyukSiparisler GROUP BY Durum;
