CREATE DATABASE OnlineFilmSatis

GO

USE OnlineFilmSatis

GO
CREATE TABLE kategori (
kategori_id int IDENTITY(1,1) PRIMARY KEY ,
kategori_adi varchar(255) NOT NULL
)
CREATE TABLE dil (
dil_id int IDENTITY(1,1) PRIMARY KEY ,
dil nvarchar(255) NOT NULL
)
CREATE TABLE film (
film_id int IDENTITY(1,1) PRIMARY KEY ,
film_adi nvarchar(255) NOT NULL,
yapim_yili datetime,
dil_id int,
sure int,
rating int,
film_fiyat int,
film_stok int
)
CREATE TABLE film_kategori (
film_id int IDENTITY(1,1) PRIMARY KEY ,
kategori_id int NOT NULL
)


CREATE TABLE film_oyuncu (
oyuncu_id int IDENTITY(1,1) PRIMARY KEY ,
film_id int NOT NULL
)
CREATE TABLE oyuncu (
oyuncu_id int IDENTITY(1,1) PRIMARY KEY ,
oyuncu_adi nvarchar(255) NOT NULL,
oyuncu_soyadi nvarchar(255) NOT NULL
)
CREATE TABLE siparis (
siparis_id int IDENTITY(1,1) PRIMARY KEY ,
siparis_tarihi datetime NOT NULL,
musteri_id int,
film_id int,
)
CREATE TABLE musteri (
musteri_id int IDENTITY(1,1) PRIMARY KEY ,
musteri_adi nvarchar(255) NOT NULL,
musteri_soyadi nvarchar(255) NOT NULL,
musteri_email nvarchar(255) NOT NULL,
musteri_kayit_tarihi datetime NOT NULL,
musteri_adres nvarchar(255) NOT NULL
)
CREATE TABLE odeme (
odeme_id int IDENTITY(1,1) PRIMARY KEY ,
musteri_id int NOT NULL,
siparis_id int not null,
tutar decimal not null
)



--Keyler

ALTER TABLE odeme
ADD CONSTRAINT FK_odeme_musteri
FOREIGN KEY (musteri_id) REFERENCES musteri(musteri_id);

ALTER TABLE odeme
ADD CONSTRAINT FK_odeme_siparis
FOREIGN KEY (siparis_id) REFERENCES siparis(siparis_id);

ALTER TABLE siparis
ADD CONSTRAINT FK_siparis_musteri
FOREIGN KEY (musteri_id) REFERENCES musteri(musteri_id);

ALTER TABLE siparis
ADD CONSTRAINT FK_siparis_film
FOREIGN KEY (film_id) REFERENCES film(film_id);

ALTER TABLE film_oyuncu
ADD CONSTRAINT FK_film_oyuncu_film
FOREIGN KEY (film_id) REFERENCES film(film_id);

ALTER TABLE film
ADD CONSTRAINT FK_film_dil
FOREIGN KEY (dil_id) REFERENCES dil(dil_id);

ALTER TABLE film_kategori
ADD CONSTRAINT FK_film_kategori_kategori
FOREIGN KEY (kategori_id) REFERENCES kategori(kategori_id);

IF OBJECT_ID('dbo.sp_FilmSiparis') IS NOT NULL
BEGIN
DROP PROCEDURE sp_FilmSiparis
END
GO
CREATE PROCEDURE dbo.sp_FilmSiparis
@film_id INT,
@musteri_id INT,
@adet INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @film_fiyat INT;
DECLARE @film_stok INT;
DECLARE @siparis_id INT;
DECLARE @tutar DECIMAL;

IF @adet <= 0
BEGIN
RAISERROR('Adet 0 veya daha küçük olamaz', 16, 1);
RETURN;
END

BEGIN TRY
BEGIN TRANSACTION

SELECT @film_fiyat = film_fiyat, @film_stok = film_stok
FROM film
WHERE film_id = @film_id;


UPDATE film
SET film_stok = film_stok - @adet
WHERE film_id = @film_id;

INSERT INTO siparis (siparis_tarihi, musteri_id)
VALUES (GETDATE(), @musteri_id);

SELECT @siparis_id = SCOPE_IDENTITY();

SET @tutar = @film_fiyat * @adet;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END



EXEC dbo.sp_FilmSiparis @film_id=1,
@musteri_id=1,
@adet=5

IF OBJECT_ID('dbo.tr_siparis_ekleme') IS NOT NULL
BEGIN
DROP TRIGGER tr_siparis_ekleme
END
GO
CREATE TRIGGER tr_siparis_ekleme ON [dbo].[siparis]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @film_id int, @musteri_id int;
SELECT @film_id = film_id, @musteri_id = musteri_id FROM inserted;

IF NOT EXISTS (SELECT * FROM film WHERE film_id = @film_id)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR ('Film bulunamadı', 16, 1);
RETURN;
END

IF NOT EXISTS (SELECT * FROM musteri WHERE musteri_id = @musteri_id)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR ('Müşteri bulunamadı', 16, 1);
RETURN;
END

IF (SELECT film_stok FROM film WHERE film_id = @film_id) <= 0
BEGIN
ROLLBACK TRANSACTION;
RAISERROR ('Stokta film kalmadı', 16, 1);
RETURN;
END

INSERT INTO odeme (musteri_id, siparis_id, tutar)
SELECT musteri_id, siparis_id, film_fiyat
FROM inserted i
JOIN film f ON i.film_id = f.film_id;
END