fda

CREATE TABLE Client (
ID INT PRIMARY KEY IDENTITY(1,1),
FullName NVARCHAR(50) NOT NULL,
Passport NVARCHAR(16) UNIQUE NOT NULL,
Address NVARCHAR(100)
);

CREATE TABLE Product (
ID INT PRIMARY KEY IDENTITY(1,1),
Type NVARCHAR(100),
AppraisalValue DECIMAL(10, 2),
WearPercentage DECIMAL(5, 2),
Status NVARCHAR(50),
CHECK (Status IN ('в_залоге', 'на_продаже', 'выкуплен'))
);

CREATE TABLE Material (
ID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50),
UnitCost DECIMAL(10, 2)
);

CREATE TABLE ProductComposition (
ProductID INT FOREIGN KEY REFERENCES Product(ID),
MaterialID INT FOREIGN KEY REFERENCES Material(ID),
Weight DECIMAL(10, 2)
);

CREATE TABLE PledgeContract (
ID INT PRIMARY KEY IDENTITY(1,1),
ProductID INT FOREIGN KEY REFERENCES Product(ID),
ClientID INT FOREIGN KEY REFERENCES Client(ID),
Date DATE,
AmountGiven DECIMAL(10, 2),
RedemptionPeriod INT,
Commission DECIMAL(10, 2),
Status NVARCHAR(50),
CHECK (Status IN ('активный', 'просрочен', 'завершен'))
);

CREATE TABLE Sales (
ID INT PRIMARY KEY IDENTITY(1,1),
ProductID INT FOREIGN KEY REFERENCES Product(ID),
SalePrice DECIMAL(10, 2),
SaleDate DATE,
Buyer NVARCHAR(100),
Details NVARCHAR(200)
);