---

IF DB_ID('pawnshop_db') IS NOT NULL
BEGIN
    ALTER DATABASE pawnshop_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE pawnshop_db;
END
GO

CREATE DATABASE pawnshop_db;
GO

USE pawnshop_db;
GO

CREATE TABLE Client (
    id INT IDENTITY(1,1) PRIMARY KEY,
    full_name NVARCHAR(150) NOT NULL,
    passport_series NVARCHAR(10) NOT NULL,
    passport_number NVARCHAR(20) NOT NULL,
    address NVARCHAR(200) NOT NULL,
    phone NVARCHAR(20) NULL,
    CONSTRAINT uq_client_passport UNIQUE (passport_series, passport_number)
);

CREATE TABLE Item_Category (
    id INT IDENTITY(1,1) PRIMARY KEY,
    category_name NVARCHAR(100) NOT NULL UNIQUE,
    description NVARCHAR(255) NULL
);

CREATE TABLE Material (
    id INT IDENTITY(1,1) PRIMARY KEY,
    material_name NVARCHAR(100) NOT NULL UNIQUE,
    price_per_gram DECIMAL(10,2) NULL CHECK (price_per_gram > 0)
);

CREATE TABLE Item (
    id INT IDENTITY(1,1) PRIMARY KEY,
    category_id INT NOT NULL,
    title NVARCHAR(150) NOT NULL,
    estimated_value DECIMAL(12,2) NOT NULL CHECK (estimated_value > 0),
    status NVARCHAR(30) NOT NULL CHECK (status IN (N'В залоге', N'Выкуплен', N'В продаже', N'Продан')),
    selling_price DECIMAL(12,2) NULL CHECK (selling_price >= 0),
    CONSTRAINT fk_item_category FOREIGN KEY (category_id) REFERENCES Item_Category(id)
        ON UPDATE CASCADE ON DELETE NO ACTION
);

CREATE TABLE Item_Material (
    item_id INT NOT NULL,
    material_id INT NOT NULL,
    weight_grams DECIMAL(8,3) NOT NULL CHECK (weight_grams > 0),
    PRIMARY KEY (item_id, material_id),
    CONSTRAINT fk_im_item FOREIGN KEY (item_id) REFERENCES Item(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_im_material FOREIGN KEY (material_id) REFERENCES Material(id)
        ON UPDATE CASCADE ON DELETE NO ACTION
);

CREATE TABLE Pawn_Contract (
    id INT IDENTITY(1,1) PRIMARY KEY,
    contract_number NVARCHAR(30) NOT NULL UNIQUE,
    client_id INT NOT NULL,
    item_id INT NOT NULL UNIQUE,
    contract_date DATE NOT NULL,
    due_date DATE NOT NULL,
    loan_amount DECIMAL(12,2) NOT NULL CHECK (loan_amount > 0),
    commission_rate DECIMAL(5,2) NOT NULL CHECK (commission_rate >= 0),
    commission_amount DECIMAL(12,2) NOT NULL CHECK (commission_amount >= 0),
    redemption_date DATE NULL,
    status NVARCHAR(30) NOT NULL CHECK (status IN (N'Активен', N'Выкуплен', N'Просрочен')),
    CONSTRAINT chk_dates CHECK (due_date >= contract_date),
    CONSTRAINT fk_contract_client FOREIGN KEY (client_id) REFERENCES Client(id)
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT fk_contract_item FOREIGN KEY (item_id) REFERENCES Item(id)
        ON UPDATE NO ACTION ON DELETE NO ACTION
);
GO

INSERT INTO Client (full_name, passport_series, passport_number, address, phone) VALUES
(N'Иванов Алексей Сергеевич', N'4510', N'123456', N'г. Москва, ул. Ленина, д. 12, кв. 4', N'+79161112233'),
(N'Петров Дмитрий Николаевич', N'4511', N'234567', N'г. Москва, пр-кт Мира, д. 5, кв. 78', N'+79162223344'),
(N'Смирнова Елена Павловна', N'4512', N'345678', N'г. Москва, ул. Тверская, д. 18, кв. 11', N'+79163334455'),
(N'Кузнецов Максим Игоревич', N'4513', N'456789', N'г. Москва, ул. Арбат, д. 22, кв. 3', N'+79164445566'),
(N'Васильева Ольга Владимировна', N'4514', N'567890', N'г. Москва, ул. Гагарина, д. 7, кв. 89', N'+79165556677'),
(N'Попов Артем Викторович', N'4515', N'678901', N'г. Москва, б-р Яна Райниса, д. 3, кв. 15', N'+79166667788'),
(N'Соколова Анна Андреевна', N'4516', N'789012', N'г. Москва, ш. Энтузиастов, д. 45, кв. 60', N'+79167778899'),
(N'Михайлов Денис Олегович', N'4517', N'890123', N'г. Москва, ул. Профсоюзная, д. 98, кв. 102', N'+79168889900'),
(N'Федорова Татьяна Сергеевна', N'4518', N'901234', N'г. Москва, ул. Новослободская, д. 14, кв. 27', N'+79169990011'),
(N'Морозов Илья Кириллович', N'4519', N'012345', N'г. Москва, ул. Люблинская, д. 112, кв. 5', N'+79160001122');

INSERT INTO Item_Category (category_name, description) VALUES
(N'Кольца', N'Ювелирные кольца, перстни, печатки'),
(N'Цепочки', N'Шейные цепочки различных плетений'),
(N'Браслеты', N'Браслеты жесткие и цепочные'),
(N'Серьги', N'Серьги, пусеты, каффы'),
(N'Подвески', N'Кулоны, медальоны, нательные кресты'),
(N'Часы наручные', N'Механические и кварцевые часы'),
(N'Смартфоны', N'Мобильные устройства и телефоны'),
(N'Ноутбуки', N'Портативные персональные компьютеры'),
(N'Планшеты', N'Планшетные компьютеры и электронные книги'),
(N'Фототехника', N'Зеркальные и системные фотоаппараты, объективы');

INSERT INTO Material (material_name, price_per_gram) VALUES
(N'Золото 585', 3800.00),
(N'Золото 750', 4900.00),
(N'Золото 999', 6500.00),
(N'Серебро 925', 55.00),
(N'Серебро 999', 75.00),
(N'Платина 950', 3200.00),
(N'Палладий 850', 2900.00),
(N'Бриллиант (каратный эквивалент)', 150000.00),
(N'Фианит', 20.00),
(N'Корпусная сталь / сплав', 5.00);

INSERT INTO Item (category_id, title, estimated_value, status, selling_price) VALUES
(1, N'Золотое кольцо с фианитом', 18000.00, N'Выкуплен', NULL),
(2, N'Цепь плетения Бисмарк', 45000.00, N'В залоге', NULL),
(4, N'Серьги с английским замком', 28000.00, N'В продаже', 31000.00),
(1, N'Печатка мужская с гравировкой', 35000.00, N'В залоге', NULL),
(5, N'Крест нательный золотой', 14000.00, N'В залоге', NULL),
(3, N'Браслет серебряный Якорь', 6500.00, N'Выкуплен', NULL),
(7, N'Смартфон Apple iPhone 13 128GB', 35000.00, N'В продаже', 39000.00),
(8, N'Ноутбук Asus ROG Strix G15', 60000.00, N'В залоге', NULL),
(6, N'Часы Tissot Le Locle Automatic', 30000.00, N'В продаже', 34000.00),
(10, N'Фотоаппарат Canon EOS 6D Body', 40000.00, N'В залоге', NULL);

INSERT INTO Item_Material (item_id, material_id, weight_grams) VALUES
(1, 1, 3.200),
(1, 9, 0.400),
(2, 1, 11.500),
(3, 1, 5.800),
(3, 8, 0.150),
(4, 2, 7.100),
(5, 1, 3.500),
(6, 4, 25.000),
(7, 10, 174.000),
(8, 10, 850.000),
(9, 6, 85.000),
(10, 10, 755.000);

INSERT INTO Pawn_Contract (contract_number, client_id, item_id, contract_date, due_date, loan_amount, commission_rate, commission_amount, redemption_date, status) VALUES
(N'ДОГ-2026-0001', 1, 1, '2026-08-01', '2026-09-01', 14000.00, 10.00, 1400.00, '2026-08-25', N'Выкуплен'),
(N'ДОГ-2026-0002', 1, 2, '2026-09-05', '2026-10-05', 36000.00, 8.00, 2880.00, NULL, N'Активен'),
(N'ДОГ-2026-0003', 2, 3, '2026-07-10', '2026-08-10', 22000.00, 10.00, 2200.00, NULL, N'Просрочен'),
(N'ДОГ-2026-0004', 2, 4, '2026-09-12', '2026-10-12', 28000.00, 9.00, 2520.00, NULL, N'Активен'),
(N'ДОГ-2026-0005', 3, 5, '2026-09-20', '2026-10-20', 11000.00, 10.00, 1100.00, NULL, N'Активен'),
(N'ДОГ-2026-0006', 4, 6, '2026-08-15', '2026-09-15', 5000.00, 12.00, 600.00, '2026-09-10', N'Выкуплен'),
(N'ДОГ-2026-0007', 5, 7, '2026-06-01', '2026-07-01', 28000.00, 10.00, 2800.00, NULL, N'Просрочен'),
(N'ДОГ-2026-0008', 6, 8, '2026-09-25', '2026-10-25', 48000.00, 8.00, 3840.00, NULL, N'Активен'),
(N'ДОГ-2026-0009', 7, 9, '2026-05-10', '2026-06-10', 24000.00, 10.00, 2400.00, NULL, N'Просрочен'),
(N'ДОГ-2026-0010', 8, 10, '2026-10-01', '2026-11-01', 32000.00, 9.00, 2880.00, NULL, N'Активен');
GO