Notes

-- Drop existing DCP_ tables to avoid conflicts
DROP TABLE IF EXISTS DCP_Refund;
DROP TABLE IF EXISTS DCP_Order;
DROP TABLE IF EXISTS DCP_Session;
DROP TABLE IF EXISTS DCP_Product;
DROP TABLE IF EXISTS DCP_Source;
DROP TABLE IF EXISTS DCP_Country;

-- Create DCP_Country table with metadata columns
CREATE TABLE DCP_Country (
CountryID INT PRIMARY KEY,
CountryName NVARCHAR(100),
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Create DCP_Source table with metadata columns
CREATE TABLE DCP_Source (
SourceID INT PRIMARY KEY,
SourceType NVARCHAR(100),
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Create DCP_Product table with metadata columns
CREATE TABLE DCP_Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(100),
CostPrice DECIMAL(18, 2),
SellingPrice DECIMAL(18, 2),
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Create DCP_Order table with metadata columns and foreign keys
CREATE TABLE DCP_Order (
OrderID INT PRIMARY KEY,
OrderDate DATETIME,
ProductID INT FOREIGN KEY REFERENCES DCP_Product(ProductID),
Quantity INT,
SourceID INT FOREIGN KEY REFERENCES DCP_Source(SourceID),
CountryID INT FOREIGN KEY REFERENCES DCP_Country(CountryID),
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Create DCP_Refund table with metadata columns and foreign keys
CREATE TABLE DCP_Refund (
RefundID INT PRIMARY KEY,
OrderID INT FOREIGN KEY REFERENCES DCP_Order(OrderID),
RefundDate DATETIME,
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Create DCP_Session table with metadata columns and foreign keys
CREATE TABLE DCP_Session (
SessionID INT PRIMARY KEY,
CountryID INT FOREIGN KEY REFERENCES DCP_Country(CountryID),
StartDate DATETIME,
EndDate DATETIME,
SessionCreateDate DATETIME,
CreateDate DATETIME DEFAULT GETDATE(),
IsDeleted BIT DEFAULT 0
);

-- Insert Data into DCP_Country
INSERT INTO DCP_Country (CountryID, CountryName) VALUES
(1, 'United States'),
(2, 'Canada'),
(3, 'United Kingdom'),
(4, 'Germany'),
(5, 'France'),
(6, 'Australia'),
(7, 'Italy'),
(8, 'Spain'),
(9, 'Netherlands'),
(10, 'Sweden');

-- Insert Data into DCP_Source
INSERT INTO DCP_Source (SourceID, SourceType) VALUES
(1, 'Direct'),
(2, 'Social'),
(3, 'Email'),
(4, 'Referral'),
(5, 'Other');

-- Insert Data into DCP_Product
INSERT INTO DCP_Product (ProductID, ProductName, CostPrice, SellingPrice) VALUES
(1, 'Smartphone', 200.00, 299.99),
(2, 'Laptop', 800.00, 1199.99),
(3, 'Headphones', 50.00, 79.99),
(4, 'Smartwatch', 100.00, 149.99),
(5, 'Tablet', 300.00, 449.99),
(6, 'Camera', 400.00, 599.99),
(7, 'Monitor', 150.00, 229.99),
(8, 'Keyboard', 30.00, 49.99),
(9, 'Mouse', 20.00, 29.99),
(10, 'Printer', 120.00, 179.99);

-- Insert Data into DCP_Order
DECLARE @i INT = 1;

WHILE @i <= 500
BEGIN
INSERT INTO DCP_Order (OrderID, OrderDate, ProductID, Quantity, SourceID, CountryID, CreateDate, IsDeleted)
VALUES (
@i,
DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 365), GETDATE()), -- Random past date within the last year
(SELECT TOP 1 ProductID FROM DCP_Product ORDER BY NEWID()), -- Random ProductID
ABS(CHECKSUM(NEWID()) % 10) + 1, -- Random Quantity between 1 and 10
(SELECT TOP 1 SourceID FROM DCP_Source ORDER BY NEWID()), -- Random SourceID
(SELECT TOP 1 CountryID FROM DCP_Country ORDER BY NEWID()), -- Random CountryID
GETDATE(), -- Creation date
0 -- IsDeleted
);
SET @i = @i + 1;
END;

-- Insert Data into DCP_Session
DECLARE @j INT = 1;

WHILE @j <= 300
BEGIN
INSERT INTO DCP_Session (SessionID, CountryID, StartDate, EndDate, SessionCreateDate, CreateDate, IsDeleted)
VALUES (
@j,
(SELECT TOP 1 CountryID FROM DCP_Country ORDER BY NEWID()), -- Random CountryID
DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 365), GETDATE()), -- Random past start date within the last year
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 30), DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 365), GETDATE())), -- Random past end date within 30 days of the start date
GETDATE(), -- Session creation date
GETDATE(), -- Creation date
0 -- IsDeleted
);
SET @j = @j + 1;
END;

-- Insert Data into DCP_Refund
DECLARE @k INT = 1;

WHILE @k <= 30
BEGIN
INSERT INTO DCP_Refund (RefundID, OrderID, RefundDate, CreateDate, IsDeleted)
VALUES (
@k,
(SELECT TOP 1 OrderID FROM DCP_Order ORDER BY NEWID()), -- Random OrderID
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 14) + 1, -- Random future date within 1 to 14 days after OrderDate
(SELECT OrderDate FROM DCP_Order WHERE OrderID = (SELECT TOP 1 OrderID FROM DCP_Order ORDER BY NEWID()))),
GETDATE(), -- Creation date
0 -- IsDeleted
);
SET @k = @k + 1;
END;


-- Select all records from DCP_Country
SELECT * FROM DCP_Country;

-- Select all records from DCP_Source
SELECT * FROM DCP_Source;

-- Select all records from DCP_Product
SELECT * FROM DCP_Product;

-- Select all records from DCP_Order
SELECT * FROM DCP_Order;

-- Select all records from DCP_Refund
SELECT * FROM DCP_Refund;

-- Select all records from DCP_Session
SELECT * FROM DCP_Session;