CacheCoc.sql

-- ==========================================
-- CREATE DATABASE
-- ==========================================

IF DB_ID('CacheCollector') IS NOT NULL
DROP DATABASE CacheCollector;
GO

CREATE DATABASE CacheCollector;
GO

USE CacheCollector;
GO

-- ==========================================
-- Role
-- ==========================================

CREATE TABLE Role (
roleId INT IDENTITY(1,1) PRIMARY KEY,
roleName VARCHAR(50) NOT NULL
);

INSERT INTO Role(roleName) VALUES
('admin'),
('customer'),
('seller');

-- ==========================================
-- User
-- ==========================================

CREATE TABLE [User] (
userId INT IDENTITY(1,1) PRIMARY KEY,
fullName VARCHAR(100),
username VARCHAR(50) UNIQUE,
passwordHash VARCHAR(255),
roleId INT,
address VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(100),
activate BIT DEFAULT 1,
createdAt DATETIME DEFAULT GETDATE(),

FOREIGN KEY(roleId) REFERENCES Role(roleId)
);

INSERT INTO [User](fullName,username,passwordHash,roleId,address,phone,email,activate)
VALUES
('Admin User','admin','123456',1,'Admin Address','111111111','[admin@cachecollector.com](mailto:admin@cachecollector.com)',1),
('John Doe','john','123456',2,'USA','222222222','[john@email.com](mailto:john@email.com)',1),
('Retro Seller','seller','123456',3,'Japan','333333333','[seller@email.com](mailto:seller@email.com)',1);

-- ==========================================
-- Category
-- ==========================================

CREATE TABLE Category (
categoryId INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(255)
);

INSERT INTO Category(name,description) VALUES
('Retro Computers','Old desktop computers'),
('Classic Phones','Vintage mobile phones'),
('Vintage Gaming','Retro gaming consoles'),
('Accessories','Retro tech accessories');

-- ==========================================
-- Product
-- ==========================================

CREATE TABLE Product (
productId INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(200),
description VARCHAR(MAX),
image VARCHAR(255),
price DECIMAL(10,2),
stock INT,
categoryId INT,
yearManufactured INT,
qualityScore INT,
importDate DATE,
usingDate DATE,
status BIT DEFAULT 1,
createdAt DATETIME DEFAULT GETDATE(),

FOREIGN KEY(categoryId) REFERENCES Category(categoryId)
);

INSERT INTO Product
(name,description,image,price,stock,categoryId,yearManufactured,qualityScore,importDate,usingDate,status)
VALUES
('IBM Model M Keyboard','Legendary mechanical keyboard','ibm_keyboard.jpg',150.00,5,4,1985,8,'2024-01-10','1985-01-01',1),
('Nokia 3310','Classic indestructible phone','nokia3310.jpg',60.00,10,2,2000,9,'2024-02-01','2000-01-01',1),
('Nintendo GameBoy','Original handheld gaming console','gameboy.jpg',120.00,4,3,1989,7,'2024-03-01','1989-01-01',1),
('Apple iMac G3','Colorful vintage Apple computer','imacg3.jpg',300.00,2,1,1998,6,'2024-02-15','1998-01-01',1),
('Sony Walkman','Portable cassette music player','walkman.jpg',80.00,6,4,1995,8,'2024-04-01','1995-01-01',1);

-- ==========================================
-- CartItem
-- ==========================================

CREATE TABLE CartItem (
cartItemId INT IDENTITY(1,1) PRIMARY KEY,
userId INT,
productId INT,
quantity INT,
addedAt DATETIME DEFAULT GETDATE(),

FOREIGN KEY(userId) REFERENCES [User](userId),
FOREIGN KEY(productId) REFERENCES Product(productId)
);

INSERT INTO CartItem(userId,productId,quantity)
VALUES
(2,1,1),
(2,3,1);

-- ==========================================
-- Order
-- ==========================================

CREATE TABLE [Order] (
orderId INT IDENTITY(1,1) PRIMARY KEY,
userId INT,
orderDate DATETIME DEFAULT GETDATE(),
total DECIMAL(10,2),
status BIT DEFAULT 1,

FOREIGN KEY(userId) REFERENCES [User](userId)
);

INSERT INTO [Order](userId,total,status)
VALUES
(2,270.00,1);

-- ==========================================
-- OrderItem
-- ==========================================

CREATE TABLE OrderItem (
orderItemId INT IDENTITY(1,1) PRIMARY KEY,
orderId INT,
productId INT,
quantity INT,
price DECIMAL(10,2),

FOREIGN KEY(orderId) REFERENCES [Order](orderId),
FOREIGN KEY(productId) REFERENCES Product(productId)
);

INSERT INTO OrderItem(orderId,productId,quantity,price)
VALUES
(1,1,1,150.00),
(1,3,1,120.00);