-- SCD Type 0: Fixed Dimension (No Changes Allowed)

CREATE TABLE Customer_SCD0 (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

-- Insert Initial Data
INSERT INTO Customer_SCD0 (CustomerID, Name, Address)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA');

-- Attempting an update should not be allowed (handled at the application level)
UPDATE Customer_SCD0
SET Address = '456 New St, New York, NY'
WHERE CustomerID = 1;

-- SCD Type 1: Overwrite the Old Data (No History Maintained)

CREATE TABLE Customer_SCD1 (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

-- Insert Initial Data
INSERT INTO Customer_SCD1 (CustomerID, Name, Address)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA');

-- Update Data (Overwrites Old Address)
UPDATE Customer_SCD1
SET Address = '456 New St, New York, NY'
WHERE CustomerID = 1;

-- SCD Type 2: Track Full History (New Row per Change)

CREATE TABLE Customer_SCD2 (
    SurrogateKey INT IDENTITY(1,1) PRIMARY KEY, -- Unique Key for Versioning
    CustomerID INT,
    Name VARCHAR(100),
    Address VARCHAR(255),
    StartDate DATETIME DEFAULT GETDATE(),
    EndDate DATETIME NULL,
    IsCurrent BIT DEFAULT 1
);

-- Insert Initial Data
INSERT INTO Customer_SCD2 (CustomerID, Name, Address, StartDate, EndDate, IsCurrent)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA', GETDATE(), NULL, 1);

-- Address Change: Mark Old Record as Inactive and Insert a New Record
UPDATE Customer_SCD2
SET EndDate = GETDATE(), IsCurrent = 0
WHERE CustomerID = 1 AND IsCurrent = 1;

INSERT INTO Customer_SCD2 (CustomerID, Name, Address, StartDate, EndDate, IsCurrent)
VALUES (1, 'John Doe', '456 New St, New York, NY', GETDATE(), NULL, 1);

-- SCD Type 3: Store Previous Value in Separate Column (Limited History)

CREATE TABLE Customer_SCD3 (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    CurrentAddress VARCHAR(255),
    PreviousAddress VARCHAR(255),
    ChangeDate DATETIME
);

-- Insert Initial Data
INSERT INTO Customer_SCD3 (CustomerID, Name, CurrentAddress, PreviousAddress, ChangeDate)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA', NULL, GETDATE());

-- Address Change: Move Current to Previous and Store New Address
UPDATE Customer_SCD3
SET PreviousAddress = CurrentAddress,
    CurrentAddress = '456 New St, New York, NY',
    ChangeDate = GETDATE()
WHERE CustomerID = 1;

-- SCD Type 4: Separate History Table

CREATE TABLE Customer_SCD4 (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

CREATE TABLE Customer_History_SCD4 (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    Name VARCHAR(100),
    Address VARCHAR(255),
    ChangeDate DATETIME DEFAULT GETDATE()
);

-- Insert Initial Data
INSERT INTO Customer_SCD4 (CustomerID, Name, Address)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA');

-- Address Change: Store Old Record in History Table Before Update
INSERT INTO Customer_History_SCD4 (CustomerID, Name, Address)
SELECT CustomerID, Name


-- SCD Type 6: Hybrid (Combining Type 1, 2, and 3)

CREATE TABLE Customer_SCD6 (
    SurrogateKey INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    Name VARCHAR(100),
    CurrentAddress VARCHAR(255),
    PreviousAddress VARCHAR(255),
    StartDate DATETIME DEFAULT GETDATE(),
    EndDate DATETIME NULL,
    IsCurrent BIT DEFAULT 1
);

-- Insert Initial Data
INSERT INTO Customer_SCD6 (CustomerID, Name, CurrentAddress, PreviousAddress, StartDate, EndDate, IsCurrent)
VALUES (1, 'John Doe', '123 Old St, Los Angeles, CA', NULL, GETDATE(), NULL, 1);

-- Address Change: Update Old Record, Store Previous Value, and Insert New Record
UPDATE Customer_SCD6
SET EndDate = GETDATE(), IsCurrent = 0
WHERE CustomerID = 1 AND IsCurrent = 1;

INSERT INTO Customer_SCD6 (CustomerID, Name, CurrentAddress, PreviousAddress, StartDate, EndDate, IsCurrent)
VALUES (1, 'John Doe', '456 New St, New York, NY