USE [master]
GO

/****** Veritabanını Oluşturma ******/
/****** Object:  Database [db_OguzhanCetinkaya]    Script Date: 16.06.2023 02:12:19 ******/
CREATE DATABASE [db_OguzhanCetinkaya]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'db_OguzhanCetinkaya', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.EXPRESSON\MSSQL\DATA\db_OguzhanCetinkaya.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'db_OguzhanCetinkaya_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.EXPRESSON\MSSQL\DATA\db_OguzhanCetinkaya_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO


/****** Tabloları ve Anahtar Alanları Oluşturma ******/

CREATE TABLE tbl_Employees (
    Empl_ID INT PRIMARY KEY,
    Empl_FName VARCHAR(255),
    Empl_LName VARCHAR(255),
    Empl_BDate DATE,
    Empl_Name AS CONCAT(Empl_FName, ' ', Empl_LName),
    Emp_Start_Date DATE,
    Empl_Left_Date DATE,
    Empl_Address VARCHAR(255),
    Empl_City VARCHAR(255),
    Empl_Province VARCHAR(255),
    Empl_Phone VARCHAR(255),
    Empl_Cell VARCHAR(255),
    Empl_Email VARCHAR(255),
    Dept_ID INT,
    Gender_ID INT,
    Title_ID INT,
    Empl_Photo VARCHAR(255),
    Empl_CV VARCHAR(255),
    Empl_CV_File VARCHAR(255),
    Empl_CV_Web VARCHAR(255),
    Entered_By VARCHAR(255),
    Entered_Date DATE,
    Is_Empl_Active BIT
);

CREATE TABLE tbl_EmployeeWages (
    Wage_ID INT PRIMARY KEY,
    
	Empl_ID INT,
    FOREIGN KEY (Empl_ID) REFERENCES tbl_Employees(Empl_ID),

    Wage_Date DATE,
    Wage_Amount DECIMAL(10, 2),
    Wage_Commission DECIMAL(10, 2),
    Wage_Total AS (Wage_Amount + Wage_Commission),
	Month_ID INT,
    Wage_Year AS ((Wage_Amount + Wage_Commission) * 12),
);

CREATE TABLE tbl_Departments (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(255),
    Dept_Phone VARCHAR(255),

    Manager_ID INT,
    FOREIGN KEY (Manager_ID) REFERENCES tbl_Employees(Empl_ID)
);

CREATE TABLE tbl_Lookups (
    LK_ID INT PRIMARY KEY,
    Gender VARCHAR(255),
	Title VARCHAR(255),
	City VARCHAR(255),
    Province VARCHAR(255),
    Country VARCHAR(255),
    Month VARCHAR(255),
    Role VARCHAR(255)
);

CREATE TABLE tbl_Users (
    User_ID INT PRIMARY KEY,
    User_Name VARCHAR(255),
    User_Psw VARCHAR(255),

    Role_ID INT,
    FOREIGN KEY (Role_ID) REFERENCES tbl_Lookups(LK_ID),
);


/****** Verileri Girme ******/

-- tbl_Employees tablosuna veri ekleme
INSERT INTO tbl_Employees (Empl_ID, Empl_FName, Empl_LName, Empl_BDate, Emp_Start_Date, Empl_Address, Empl_City, Empl_Province, Empl_Phone, Empl_Cell, Empl_Email, Dept_ID, Gender_ID, Title_ID, Empl_Photo, Empl_CV, Empl_CV_File, Empl_CV_Web, Entered_By, Entered_Date, Is_Empl_Active)
VALUES
    (1, 'Ahmet', 'Yılmaz', '1985-05-10', '2021-01-01', '123 Park Caddesi', 'İstanbul', 'İstanbul', '555-111-2222', '555-333-4444', 'ahmet.yilmaz@example.com', 1, 1, 1, 'resim1.jpg', 'cv1.pdf', 'cv1.pdf', 'http://ornek.com/cv1.pdf', 'Admin', '2021-01-01', 1),
    (2, 'Ayşe', 'Demir', '1990-12-15', '2021-03-15', '456 Sokak', 'Ankara', 'Ankara', '555-222-3333', '555-444-5555', 'ayse.demir@example.com', 2, 2, 2, 'resim2.jpg', 'cv2.pdf', 'cv2.pdf', 'http://ornek.com/cv2.pdf', 'Admin', '2021-03-15', 1),
    (3, 'Mehmet', 'Kaya', '1988-07-20', '2021-05-01', '789 Bulvar', 'İzmir', 'İzmir', '555-333-4444', '555-555-6666', 'mehmet.kaya@example.com', 3, 1, 1, 'resim3.jpg', 'cv3.pdf', 'cv3.pdf', 'http://ornek.com/cv3.pdf', 'Admin', '2021-05-01', 1),
    (4, 'Zeynep', 'Yıldırım', '1995-03-08', '2021-07-01', '321 Cadde', 'Bursa', 'Bursa', '555-444-5555', '555-777-8888', 'zeynep.yildirim@example.com', 4, 2, 2, 'resim4.jpg', 'cv4.pdf', 'cv4.pdf', 'http://ornek.com/cv4.pdf', 'Admin', '2021-07-01', 1),
    (5, 'Mustafa', 'Arslan', '1992-09-25', '2021-09-15', '654 Sokak', 'Antalya', 'Antalya', '555-555-6666', '555-999-0000', 'mustafa.arslan@example.com', 5, 1, 1, 'resim5.jpg', 'cv5.pdf', 'cv5.pdf', 'http://ornek.com/cv5.pdf', 'Admin', '2021-09-15', 1),
    (6, 'Selin', 'Öztürk', '1998-06-12', '2021-11-01', '987 Bulvar', 'Adana', 'Adana', '555-666-7777', '555-111-2222', 'selin.ozturk@example.com', 6, 2, 2, 'resim6.jpg', 'cv6.pdf', 'cv6.pdf', 'http://ornek.com/cv6.pdf', 'Admin', '2021-11-01', 1),
    (7, 'Kadir', 'Sarı', '1993-02-18', '2022-01-15', '1234 Cadde', 'Eskişehir', 'Eskişehir', '555-777-8888', '555-333-4444', 'kadir.sari@example.com', 7, 1, 1, 'resim7.jpg', 'cv7.pdf', 'cv7.pdf', 'http://ornek.com/cv7.pdf', 'Admin', '2022-01-15', 1),
    (8, 'Elif', 'Güneş', '1996-08-03', '2022-03-01', '5678 Sokak', 'Gaziantep', 'Gaziantep', '555-888-9999', '555-444-5555', 'elif.gunes@example.com', 8, 2, 2, 'resim8.jpg', 'cv8.pdf', 'cv8.pdf', 'http://ornek.com/cv8.pdf', 'Admin', '2022-03-01', 1),
    (9, 'Hakan', 'Aydın', '1991-04-30', '2022-05-01', '9012 Bulvar', 'Konya', 'Konya', '555-999-0000', '555-555-6666', 'hakan.aydin@example.com', 9, 1, 1, 'resim9.jpg', 'cv9.pdf', 'cv9.pdf', 'http://ornek.com/cv9.pdf', 'Admin', '2022-05-01', 1),
    (10, 'Sevgi', 'Türk', '1997-11-11', '2022-07-01', '3456 Cadde', 'Trabzon', 'Trabzon', '555-000-1111', '555-666-7777', 'sevgi.turk@example.com', 10, 2, 2, 'resim10.jpg', 'cv10.pdf', 'cv10.pdf', 'http://ornek.com/cv10.pdf', 'Admin', '2022-07-01', 1),
	(11, 'Ahmet', 'Yılmaz', '1995-05-20', '2023-07-01', '7890 Sokak', 'İzmir', 'İzmir', '555-111-2222', '555-777-8888', 'ahmet.yilmaz@example.com', 3, 1, 1, 'resim11.jpg', 'cv11.pdf', 'cv11.pdf', 'http://ornek.com/cv11.pdf', 'Admin', '2023-07-01', 1),
    (12, 'Ayşe', 'Demir', '1992-08-12', '2023-09-01', '2345 Cadde', 'Ankara', 'Ankara', '555-222-3333', '555-888-9999', 'ayse.demir@example.com', 4, 2, 2, 'resim12.jpg', 'cv12.pdf', 'cv12.pdf', 'http://ornek.com/cv12.pdf', 'Admin', '2023-09-01', 1),
    (13, 'Mehmet', 'Kaya', '1990-03-25', '2023-11-01', '6789 Bulvar', 'Antalya', 'Antalya', '555-333-4444', '555-999-0000', 'mehmet.kaya@example.com', 5, 1, 1, 'resim13.jpg', 'cv13.pdf', 'cv13.pdf', 'http://ornek.com/cv13.pdf', 'Admin', '2023-11-01', 1),
    (14, 'Fatma', 'Şahin', '1994-11-02', '2024-01-01', '1234 Sokak', 'Bursa', 'Bursa', '555-444-5555', '555-000-1111', 'fatma.sahin@example.com', 6, 2, 2, 'resim14.jpg', 'cv14.pdf', 'cv14.pdf', 'http://ornek.com/cv14.pdf', 'Admin', '2024-01-01', 1),
    (15, 'Ali', 'Koç', '1991-06-15', '2024-03-01', '5678 Cadde', 'Eskişehir', 'Eskişehir', '555-555-6666', '555-111-2222', 'ali.koc@example.com', 7, 1, 1, 'resim15.jpg', 'cv15.pdf', 'cv15.pdf', 'http://ornek.com/cv15.pdf', 'Admin', '2024-03-01', 1),
    (16, 'Zeynep', 'Yıldız', '1993-09-08', '2024-05-01', '9012 Bulvar', 'Gaziantep', 'Gaziantep', '555-666-7777', '555-222-3333', 'zeynep.yildiz@example.com', 8, 2, 2, 'resim16.jpg', 'cv16.pdf', 'cv16.pdf', 'http://ornek.com/cv16.pdf', 'Admin', '2024-05-01', 1),
    (17, 'Mustafa', 'Can', '1996-02-18', '2024-07-01', '3456 Sokak', 'İstanbul', 'İstanbul', '555-777-8888', '555-333-4444', 'mustafa.can@example.com', 9, 1, 1, 'resim17.jpg', 'cv17.pdf', 'cv17.pdf', 'http://ornek.com/cv17.pdf', 'Admin', '2024-07-01', 1),
    (18, 'Aylin', 'Öztürk', '1998-07-29', '2024-09-01', '7890 Cadde', 'İzmir', 'İzmir', '555-888-9999', '555-444-5555', 'aylin.ozturk@example.com', 10, 2, 2, 'resim18.jpg', 'cv18.pdf', 'cv18.pdf', 'http://ornek.com/cv18.pdf', 'Admin', '2024-09-01', 1),
    (19, 'Ahmet', 'Şahin', '1997-04-13', '2025-01-01', '2345 Sokak', 'Ankara', 'Ankara', '555-999-0000', '555-555-6666', 'ahmet.sahin@example.com', 1, 1, 1, 'resim19.jpg', 'cv19.pdf', 'cv19.pdf', 'http://ornek.com/cv19.pdf', 'Admin', '2025-01-01', 1),
    (20, 'Elif', 'Kara', '1995-10-27', '2025-03-01', '6789 Cadde', 'Antalya', 'Antalya', '555-000-1111', '555-666-7777', 'elif.kara@example.com', 2, 2, 2, 'resim20.jpg', 'cv20.pdf', 'cv20.pdf', 'http://ornek.com/cv20.pdf', 'Admin', '2025-03-01', 1);

-- tbl_EmployeeWages tablosuna veri ekleme
INSERT INTO tbl_EmployeeWages (Wage_ID, Empl_ID, Wage_Date, Wage_Amount, Wage_Commission, Month_ID)
VALUES
    (1, 1, '2023-01-01', 2500.00, 300.00, 1),
    (2, 2, '2023-01-01', 2800.00, 400.00, 1),
    (3, 3, '2023-01-01', 2700.00, 350.00, 1),
    (4, 4, '2023-01-01', 3000.00, 500.00, 1),
    (5, 5, '2023-01-01', 2600.00, 320.00, 1),
    (6, 6, '2023-01-01', 2900.00, 420.00, 1),
    (7, 7, '2023-01-01', 2750.00, 360.00, 1),
    (8, 8, '2023-01-01', 2650.00, 330.00, 1),
    (9, 9, '2023-01-01', 3100.00, 550.00, 1),
    (10, 10, '2023-01-01', 2800.00, 400.00, 1),
    (11, 11, '2023-07-01', 3500.00, 600.00, 1),
    (12, 12, '2023-07-01', 3000.00, 500.00, 1),
    (13, 13, '2023-07-01', 3200.00, 400.00, 1),
    (14, 14, '2023-07-01', 3800.00, 800.00, 1),
    (15, 15, '2023-07-01', 3600.00, 700.00, 1),
    (16, 16, '2023-07-01', 3400.00, 300.00, 1),
    (17, 17, '2023-07-01', 3700.00, 500.00, 1),
    (18, 18, '2023-07-01', 3300.00, 600.00, 1),
    (19, 19, '2023-07-01', 3900.00, 900.00, 1),
    (20, 20, '2023-07-01', 3200.00, 400.00, 1);
-- tbl_Departments tablosuna veri ekleme
INSERT INTO tbl_Departments (Dept_ID, Dept_Name, Dept_Phone, Manager_ID)
VALUES
    (1, 'İnsan Kaynakları', '555-111-2222', 1),
    (2, 'Muhasebe', '555-222-3333', 2),
    (3, 'Pazarlama', '555-333-4444', 3),
    (4, 'Satış', '555-444-5555', 4),
    (5, 'Müşteri Hizmetleri', '555-555-6666', 5),
    (6, 'Ar-Ge', '555-666-7777', 6),
    (7, 'Üretim', '555-777-8888', 7),
    (8, 'Kalite Kontrol', '555-888-9999', 8),
    (9, 'Lojistik', '555-999-0000', 9),
    (10, 'IT', '555-000-1111', 10),
    (11, 'Bilgi İşlem', '555-666-7777', 16),
    (12, 'Finans', '555-000-1111', 20);
-- tbl_Lookups tablosuna veri ekleme
INSERT INTO tbl_Lookups (LK_ID, Gender, Title, City, Province, Country, Month, Role)
VALUES
    (1, 'Erkek', 'Yönetici', 'İstanbul', 'İstanbul', 'Türkiye', 'Ocak', 'Admin'),
    (2, 'Kadın', 'Personel', 'Ankara', 'Ankara', 'Türkiye', 'Şubat', 'Kullanıcı'),
    (3, 'Erkek', 'Yönetici', 'İzmir', 'İzmir', 'Türkiye', 'Mart', 'Admin'),
    (4, 'Kadın', 'Personel', 'Bursa', 'Bursa', 'Türkiye', 'Nisan', 'Kullanıcı'),
    (5, 'Erkek', 'Yönetici', 'Antalya', 'Antalya', 'Türkiye', 'Mayıs', 'Admin'),
    (6, 'Kadın', 'Personel', 'Adana', 'Adana', 'Türkiye', 'Haziran', 'Kullanıcı'),
    (7, 'Erkek', 'Yönetici', 'Eskişehir', 'Eskişehir', 'Türkiye', 'Temmuz', 'Admin'),
    (8, 'Kadın', 'Personel', 'Gaziantep', 'Gaziantep', 'Türkiye', 'Ağustos', 'Kullanıcı'),
    (9, 'Erkek', 'Yönetici', 'Konya', 'Konya', 'Türkiye', 'Eylül', 'Admin'),
    (10, 'Kadın', 'Personel', 'Trabzon', 'Trabzon', 'Türkiye', 'Ekim', 'Kullanıcı'),
	(11, 'Erkek', 'Müdür', 'İzmir', 'İzmir', 'Türkiye', 'Ocak', 'Admin'),
    (12, 'Kadın', 'Personel', 'Ankara', 'Ankara', 'Türkiye', 'Şubat', 'Kullanıcı'),
    (13, 'Erkek', 'Yönetici', 'Antalya', 'Antalya', 'Türkiye', 'Mart', 'Kullanıcı'),
    (14, 'Kadın', 'Müdür', 'Bursa', 'Bursa', 'Türkiye', 'Nisan', 'Kullanıcı'),
    (15, 'Erkek', 'Personel', 'Eskişehir', 'Eskişehir', 'Türkiye', 'Mayıs', 'Kullanıcı'),
    (16, 'Kadın', 'Yönetici', 'Gaziantep', 'Gaziantep', 'Türkiye', 'Haziran', 'Kullanıcı'),
    (17, 'Erkek', 'Müdür', 'İstanbul', 'İstanbul', 'Türkiye', 'Temmuz', 'Kullanıcı'),
    (18, 'Kadın', 'Personel', 'İzmir', 'İzmir', 'Türkiye', 'Ağustos', 'Kullanıcı'),
    (19, 'Erkek', 'Yönetici', 'Ankara', 'Ankara', 'Türkiye', 'Eylül', 'Kullanıcı'),
    (20, 'Kadın', 'Müdür', 'Antalya', 'Antalya', 'Türkiye', 'Ekim', 'Kullanıcı');

-- tbl_Users tablosuna veri ekleme
INSERT INTO tbl_Users (User_ID, User_Name, User_Psw, Role_ID)
VALUES
    (1, 'admin', '123456', 1),
    (2, 'kullanici1', 'abcdef', 2),
    (3, 'kullanici2', 'qwerty', 2),
    (4, 'kullanici3', '123abc', 2),
    (5, 'kullanici4', 'xyz789', 2),
    (6, 'kullanici5', 'pass123', 2),
    (7, 'kullanici6', 'test456', 2),
    (8, 'kullanici7', '987qwe', 2),
    (9, 'kullanici8', 'pass789', 2),
    (10, 'kullanici9', 'test123', 2),
	(11, 'ahmet.yilmaz', '123456', 11),
    (12, 'ayse.demir', 'abcdef', 12),
    (13, 'mehmet.kaya', 'qwerty', 13),
    (14, 'fatma.sahin', '111222', 14),
    (15, 'ali.koc', '333444', 15),
    (16, 'zeynep.yildiz', '555666', 16),
    (17, 'mustafa.can', '777888', 17),
    (18, 'aylin.ozturk', '999000', 18),
    (19, 'ahmet.sahin', 'abcd1234', 19),
    (20, 'elif.kara', 'efgh5678', 20);

/****** Tablo Referans Alan (Foreign Key) ve Default’ları Oluşturma ******/
-- tbl_Employees tablosuna referans alanları ekleniyor
ALTER TABLE tbl_Employees
ADD CONSTRAINT FK_Employees_Dept FOREIGN KEY (Dept_ID) REFERENCES tbl_Departments(Dept_ID);

ALTER TABLE tbl_Employees
ADD CONSTRAINT FK_Employees_Gender FOREIGN KEY (Gender_ID) REFERENCES tbl_Lookups(LK_ID);

ALTER TABLE tbl_Employees
ADD CONSTRAINT FK_Employees_Title FOREIGN KEY (Title_ID) REFERENCES tbl_Lookups(LK_ID);

-- tbl_EmployeeWages tablosuna referans alanları ekleniyor
ALTER TABLE tbl_EmployeeWages
ADD CONSTRAINT FK_EmployeeWages_Empl FOREIGN KEY (Empl_ID) REFERENCES tbl_Employees(Empl_ID);

ALTER TABLE tbl_EmployeeWages
ADD CONSTRAINT FK_EmployeeWages_Month FOREIGN KEY (Month_ID) REFERENCES tbl_Lookups(LK_ID);

-- tbl_Departments tablosuna referans alanı ekleniyor
ALTER TABLE tbl_Departments
ADD CONSTRAINT FK_Departments_Manager FOREIGN KEY (Manager_ID) REFERENCES tbl_Employees(Empl_ID);

-- tbl_Users tablosuna referans alanı ekleniyor
ALTER TABLE tbl_Users
ADD CONSTRAINT FK_Users_Role FOREIGN KEY (Role_ID) REFERENCES tbl_Lookups(LK_ID);

-- tbl_Employees tablosunda varsayılan değerler atanıyor
ALTER TABLE tbl_Employees
ADD CONSTRAINT DF_Employees_EnteredDate DEFAULT GETDATE() FOR Entered_Date;

ALTER TABLE tbl_Employees
ADD CONSTRAINT DF_Employees_IsEmplActive DEFAULT 1 FOR Is_Empl_Active;

-- tbl_EmployeeWages tablosunda varsayılan değerler atanıyor
ALTER TABLE tbl_EmployeeWages
ADD CONSTRAINT DF_EmployeeWages_WageDate DEFAULT GETDATE() FOR Wage_Date;

-- tbl_Departments tablosunda varsayılan değer atanıyor
ALTER TABLE tbl_Departments
ADD CONSTRAINT DF_Departments_DeptPhone DEFAULT 'N/A' FOR Dept_Phone;

-- tbl_Lookups tablosunda varsayılan değerler atanıyor
ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Gender DEFAULT 'Unknown' FOR Gender;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Title DEFAULT 'Unknown' FOR Title;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_City DEFAULT 'Unknown' FOR City;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Province DEFAULT 'Unknown' FOR Province;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Country DEFAULT 'Unknown' FOR Country;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Month DEFAULT 'Unknown' FOR Month;

ALTER TABLE tbl_Lookups
ADD CONSTRAINT DF_Lookups_Role DEFAULT 'Unknown' FOR Role;



/****** Tabloları Sorgulama ******/
SELECT * FROM tbl_Employees
SELECT * FROM tbl_EmployeeWages
SELECT * FROM tbl_Departments
SELECT * FROM tbl_Users
SELECT * FROM tbl_Lookups