sql_lab1011.txt - main - Cooolprogrammers/web-nodejs-labs

sql_lab1011.txt
553 строки21 KB
darkvoid6@mail.ru
ЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫ
18 дек 2025, 07:59
18 дек 2025, 07:59f2c4f06
-- === 1. ЧИСТКА ===
DROP SCHEMA IF EXISTS lab11 CASCADE;
CREATE SCHEMA lab11;
DROP SCHEMA IF EXISTS lab10 CASCADE;
CREATE SCHEMA lab10;

-- Adminer 5.4.1 PostgreSQL 16.11 dump

DROP TABLE IF EXISTS "buildings";
DROP SEQUENCE IF EXISTS buildings_id_seq;
CREATE SEQUENCE buildings_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."buildings" (
    "id" integer DEFAULT nextval('buildings_id_seq') NOT NULL,
    "name" character varying(100) NOT NULL,
    CONSTRAINT "buildings_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX buildings_name_key ON lab10.buildings USING btree (name);

TRUNCATE "buildings";
INSERT INTO "buildings" ("id", "name") VALUES
(6,	'эк'),
(7,	'гл'),
(8,	'гд');

DROP TABLE IF EXISTS "categories";
DROP SEQUENCE IF EXISTS categories_id_seq;
CREATE SEQUENCE categories_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."categories" (
    "id" integer DEFAULT nextval('categories_id_seq') NOT NULL,
    "name" character varying(100) NOT NULL,
    CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

TRUNCATE "categories";
INSERT INTO "categories" ("id", "name") VALUES
(1,	'Смартфоны'),
(2,	'Ноутбуки'),
(3,	'Аксессуары'),
(4,	'Периферия'),
(8,	'тест');

DROP TABLE IF EXISTS "classrooms";
DROP SEQUENCE IF EXISTS classrooms_id_seq;
CREATE SEQUENCE classrooms_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."classrooms" (
    "id" integer DEFAULT nextval('classrooms_id_seq') NOT NULL,
    "number" character varying(20) NOT NULL,
    "building_id" integer,
    "capacity" integer DEFAULT '0',
    CONSTRAINT "classrooms_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX classrooms_number_building_id_key ON lab10.classrooms USING btree (number, building_id);

TRUNCATE "classrooms";
INSERT INTO "classrooms" ("id", "number", "building_id", "capacity") VALUES
(9,	'403',	6,	0),
(10,	'401',	6,	0),
(11,	'404',	6,	0),
(12,	'108',	6,	0),
(14,	'408',	6,	0),
(15,	'307',	6,	0),
(16,	'303',	6,	0),
(17,	'207',	6,	0);

DROP TABLE IF EXISTS "departments";
DROP SEQUENCE IF EXISTS departments_id_seq;
CREATE SEQUENCE departments_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."departments" (
    "id" integer DEFAULT nextval('departments_id_seq') NOT NULL,
    "name" character varying(100) NOT NULL,
    CONSTRAINT "departments_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX departments_name_key ON lab10.departments USING btree (name);

TRUNCATE "departments";
INSERT INTO "departments" ("id", "name") VALUES
(3,	'Кафедра системного анализа и обработки информации'),
(2,	'Кафедра компьютерных технологий и систем');

DROP TABLE IF EXISTS "groups";
DROP SEQUENCE IF EXISTS groups_id_seq;
CREATE SEQUENCE groups_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."groups" (
    "id" integer DEFAULT nextval('groups_id_seq') NOT NULL,
    "name" character varying(20) NOT NULL,
    "course" integer NOT NULL,
    CONSTRAINT "groups_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX groups_name_key ON lab10.groups USING btree (name);

TRUNCATE "groups";
INSERT INTO "groups" ("id", "name", "course") VALUES
(1,	'ИТ2204',	4),
(4,	'ИТ2203',	4),
(5,	'ИТ2202',	4),
(7,	'ИТ2201',	4);

DROP TABLE IF EXISTS "order_items";
CREATE TABLE "lab10"."order_items" (
    "order_id" integer NOT NULL,
    "product_id" integer NOT NULL,
    "quantity" integer NOT NULL,
    "price_at_purchase" integer NOT NULL,
    CONSTRAINT "order_items_pkey" PRIMARY KEY ("order_id", "product_id"),
    CONSTRAINT "order_items_quantity_check" CHECK ((quantity > 0)),
    CONSTRAINT "order_items_price_at_purchase_check" CHECK ((price_at_purchase >= 0))
)
WITH (oids = false);

TRUNCATE "order_items";
INSERT INTO "order_items" ("order_id", "product_id", "quantity", "price_at_purchase") VALUES
(1,	2,	7,	8500000),
(2,	1,	4,	15000000),
(3,	2,	5,	8500000),
(3,	4,	4,	6000000),
(3,	6,	4,	9500000),
(4,	1,	1,	15000000),
(5,	16,	5,	7200000),
(5,	23,	4,	9500000),
(6,	21,	3,	32000000),
(7,	68,	9,	10000),
(8,	31,	4,	14500000);

DROP TABLE IF EXISTS "orders";
DROP SEQUENCE IF EXISTS orders_id_seq;
CREATE SEQUENCE orders_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."orders" (
    "id" integer DEFAULT nextval('orders_id_seq') NOT NULL,
    "created_at" timestamp DEFAULT now(),
    "total_price" integer DEFAULT '0' NOT NULL,
    CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

TRUNCATE "orders";
INSERT INTO "orders" ("id", "created_at", "total_price") VALUES
(1,	'2025-12-01 09:06:59.936408',	59500000),
(2,	'2025-12-01 09:18:50.475457',	60000000),
(3,	'2025-12-01 09:22:14.236854',	104500000),
(4,	'2025-12-01 15:52:32.099856',	15000000),
(5,	'2025-12-01 16:02:27.761721',	74000000),
(6,	'2025-12-01 16:21:09.957845',	96000000),
(7,	'2025-12-01 16:43:10.820985',	90000),
(8,	'2025-12-01 17:22:44.411791',	58000000);

DROP TABLE IF EXISTS "persons";
DROP SEQUENCE IF EXISTS persons_id_seq;
CREATE SEQUENCE persons_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."persons" (
    "id" integer DEFAULT nextval('persons_id_seq') NOT NULL,
    "first_name" character varying(50) NOT NULL,
    "last_name" character varying(50) NOT NULL,
    "middle_name" character varying(50),
    "email" character varying(100),
    CONSTRAINT "persons_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX persons_email_key ON lab10.persons USING btree (email);

TRUNCATE "persons";
INSERT INTO "persons" ("id", "first_name", "last_name", "middle_name", "email") VALUES
(2,	'Анна',	'Чемарина',	'Валерьевна',	NULL),
(1,	'Эвелина',	'Кузьмина',	'Вячеславовна',	NULL),
(4,	'Николай',	'Лытнев',	'Николаевич',	NULL),
(5,	'Алексей',	'Креймер',	'Семёнович',	NULL),
(6,	'Татьяна',	'Барановская',	'Петровна',	NULL),
(7,	'Юрий',	'Самойлюков',	'Николаевич',	NULL),
(8,	'Александр',	'Вострокнутов',	'Евгеньевич',	NULL),
(10,	'Сергей',	'Лаптев',	'Владимирович',	NULL);

DROP TABLE IF EXISTS "positions";
DROP SEQUENCE IF EXISTS positions_id_seq;
CREATE SEQUENCE positions_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."positions" (
    "id" integer DEFAULT nextval('positions_id_seq') NOT NULL,
    "name" character varying(100) NOT NULL,
    CONSTRAINT "positions_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX positions_name_key ON lab10.positions USING btree (name);

TRUNCATE "positions";
INSERT INTO "positions" ("id", "name") VALUES
(1,	'Кандидат каких-то там наук');

DROP TABLE IF EXISTS "products";
DROP SEQUENCE IF EXISTS products_id_seq;
CREATE SEQUENCE products_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."products" (
    "id" integer DEFAULT nextval('products_id_seq') NOT NULL,
    "title" character varying(255) NOT NULL,
    "price" integer NOT NULL,
    "amount" integer DEFAULT '0' NOT NULL,
    "category_id" integer,
    "image" text,
    CONSTRAINT "products_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

TRUNCATE "products";
INSERT INTO "products" ("id", "title", "price", "amount", "category_id", "image") VALUES
(3,	'iPhone 14',	7000000,	15,	1,	NULL),
(5,	'Samsung Galaxy S24 Ultra',	14000000,	7,	1,	NULL),
(7,	'Samsung Galaxy S23 FE',	5500000,	25,	1,	NULL),
(8,	'Google Pixel 8 Pro',	9000000,	6,	1,	NULL),
(9,	'Google Pixel 7a',	4500000,	12,	1,	NULL),
(10,	'Xiaomi 14 Ultra',	11000000,	4,	1,	NULL),
(11,	'Xiaomi Redmi Note 13',	2500000,	50,	1,	NULL),
(12,	'Poco X6 Pro',	3200000,	30,	1,	NULL),
(13,	'OnePlus 12',	7500000,	9,	1,	NULL),
(14,	'Nothing Phone (2)',	6000000,	11,	1,	NULL),
(15,	'Sony Xperia 1 V',	11500000,	3,	1,	NULL),
(17,	'MacBook Pro 16 M3 Max',	35000000,	2,	2,	NULL),
(18,	'MacBook Pro 14 M3',	18000000,	5,	2,	NULL),
(19,	'MacBook Air 15 M2',	13000000,	8,	2,	NULL),
(20,	'MacBook Air 13 M1',	8500000,	15,	2,	NULL),
(22,	'ASUS Zenbook 14 OLED',	11000000,	7,	2,	NULL),
(24,	'Lenovo Legion Pro 7',	28000000,	4,	2,	NULL),
(25,	'Lenovo ThinkPad X1 Carbon',	21000000,	6,	2,	NULL),
(26,	'Lenovo IdeaPad Slim 3',	4500000,	30,	2,	NULL),
(27,	'Dell XPS 13 Plus',	19000000,	5,	2,	NULL),
(28,	'HP Spectre x360',	16000000,	4,	2,	NULL),
(29,	'MSI Raider GE78',	29000000,	3,	2,	NULL),
(30,	'Acer Swift Go 14',	8000000,	10,	2,	NULL),
(32,	'Apple AirPods Pro 2',	2400000,	40,	3,	NULL),
(33,	'Apple AirPods 3',	1700000,	35,	3,	NULL),
(34,	'Sony WH-1000XM5',	3500000,	15,	3,	NULL),
(35,	'Sony WF-1000XM5',	2800000,	20,	3,	NULL),
(36,	'Marshall Major IV',	1300000,	25,	3,	NULL),
(37,	'JBL Flip 6',	1100000,	50,	3,	NULL),
(39,	'Apple Watch Ultra 2',	8500000,	5,	3,	NULL),
(40,	'Apple Watch Series 9',	4500000,	12,	3,	NULL),
(41,	'Samsung Galaxy Watch 6',	2800000,	15,	3,	NULL),
(42,	'Xiaomi Smart Band 8',	350000,	100,	3,	NULL),
(43,	'Anker PowerBank 737',	1200000,	15,	3,	NULL),
(44,	'Baseus Charger 65W',	300000,	60,	3,	NULL),
(45,	'Кабель Ugreen USB-C',	80000,	200,	3,	NULL),
(46,	'Apple AirTag',	350000,	50,	3,	NULL),
(47,	'Logitech MX Master 3S',	1100000,	20,	4,	NULL),
(48,	'Logitech G Pro X Superlight',	1400000,	15,	4,	NULL),
(49,	'Razer DeathAdder V3',	1300000,	10,	4,	NULL),
(50,	'Keychron K2 Pro',	1200000,	12,	4,	NULL),
(51,	'NuPhy Air75 v2',	1500000,	8,	4,	NULL),
(52,	'Logitech MX Keys S',	1150000,	15,	4,	NULL),
(53,	'Монитор Dell UltraSharp 27',	5500000,	5,	4,	NULL),
(54,	'Монитор LG UltraGear 32',	4200000,	8,	4,	NULL),
(55,	'Монитор Xiaomi Curved 34',	3000000,	10,	4,	NULL),
(56,	'Веб-камера Logitech C920',	800000,	25,	4,	NULL),
(57,	'Веб-камера Razer Kiyo Pro',	1500000,	10,	4,	NULL),
(58,	'Микрофон Blue Yeti',	1400000,	12,	4,	NULL),
(59,	'Микрофон HyperX QuadCast',	1600000,	8,	4,	NULL),
(60,	'Elgato Stream Deck MK.2',	1800000,	6,	4,	NULL),
(61,	'Карта захвата AverMedia',	2000000,	4,	4,	NULL),
(2,	'iPhone 15',	8500000,	8,	1,	NULL),
(4,	'iPhone 13 mini',	6000000,	4,	1,	NULL),
(6,	'Samsung Galaxy S24+',	9500000,	6,	1,	NULL),
(1,	'iPhone 15 Pro Max',	15000000,	0,	1,	NULL),
(23,	'ASUS TUF Gaming A15',	9500000,	8,	2,	NULL),
(21,	'ASUS ROG Strix Scar 18',	32000000,	7,	2,	NULL),
(16,	'ASUS Zenfone 10',	7200000,	89,	1,	NULL),
(68,	'Тест',	10000,	19,	8,	NULL),
(31,	'Huawei MateBook X Pro',	14500000,	2,	2,	NULL);

DROP TABLE IF EXISTS "schedule";
DROP SEQUENCE IF EXISTS schedule_id_seq;
CREATE SEQUENCE schedule_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."schedule" (
    "id" integer DEFAULT nextval('schedule_id_seq') NOT NULL,
    "teacher_id" integer NOT NULL,
    "group_id" integer NOT NULL,
    "subject_id" integer NOT NULL,
    "classroom_id" integer NOT NULL,
    "day_of_week" integer NOT NULL,
    "start_time" time without time zone NOT NULL,
    "end_time" time without time zone NOT NULL,
    "lesson_type" character varying(50) NOT NULL,
    CONSTRAINT "schedule_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "schedule_day_of_week_check" CHECK (((day_of_week >= 1) AND (day_of_week <= 7)))
)
WITH (oids = false);

TRUNCATE "schedule";
INSERT INTO "schedule" ("id", "teacher_id", "group_id", "subject_id", "classroom_id", "day_of_week", "start_time", "end_time", "lesson_type") VALUES
(8,	8,	1,	4,	9,	1,	'09:45:00',	'11:15:00',	'Лекция'),
(9,	1,	1,	2,	11,	1,	'11:30:00',	'13:00:00',	'Практика'),
(10,	4,	1,	5,	12,	2,	'11:30:00',	'13:00:00',	'Лабораторная'),
(11,	1,	1,	2,	11,	2,	'13:50:00',	'15:20:00',	'Практика'),
(12,	7,	1,	4,	14,	2,	'15:35:00',	'17:05:00',	'Лабораторная'),
(13,	8,	4,	4,	9,	1,	'09:45:00',	'11:15:00',	'Лекция'),
(14,	1,	4,	2,	11,	1,	'11:30:00',	'13:00:00',	'Практика'),
(15,	4,	4,	5,	12,	2,	'08:00:00',	'09:30:00',	'Лабораторная'),
(16,	2,	4,	1,	15,	2,	'09:45:00',	'11:30:00',	'Лабораторная'),
(17,	1,	4,	2,	11,	2,	'11:30:00',	'13:00:00',	'Практика'),
(18,	8,	5,	4,	9,	1,	'09:45:00',	'11:15:00',	'Лекция'),
(19,	8,	5,	4,	14,	2,	'08:00:00',	'09:30:00',	'Лабораторная'),
(20,	1,	5,	2,	14,	2,	'09:45:00',	'11:15:00',	'Практика'),
(21,	11,	5,	1,	16,	2,	'11:30:00',	'13:00:00',	'Лабораторная'),
(22,	4,	5,	5,	17,	2,	'13:50:00',	'15:20:00',	'Лабораторная'),
(23,	8,	7,	4,	9,	1,	'09:45:00',	'11:30:00',	'Лекция'),
(24,	11,	7,	5,	12,	1,	'11:30:00',	'13:00:00',	'Лабораторная'),
(25,	7,	7,	4,	14,	2,	'08:00:00',	'09:45:00',	'Лабораторная'),
(26,	11,	7,	1,	16,	2,	'09:45:00',	'11:15:00',	'Лабораторная'),
(27,	11,	7,	1,	16,	2,	'11:30:00',	'13:00:00',	'Лабораторная'),
(28,	11,	7,	5,	12,	2,	'13:50:00',	'15:20:00',	'Лабораторная'),
(29,	1,	7,	2,	11,	2,	'15:35:00',	'17:05:00',	'Практика');

DROP TABLE IF EXISTS "subjects";
DROP SEQUENCE IF EXISTS subjects_id_seq;
CREATE SEQUENCE subjects_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."subjects" (
    "id" integer DEFAULT nextval('subjects_id_seq') NOT NULL,
    "name" character varying(150) NOT NULL,
    CONSTRAINT "subjects_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX subjects_name_key ON lab10.subjects USING btree (name);

TRUNCATE "subjects";
INSERT INTO "subjects" ("id", "name") VALUES
(2,	'Информационный менеджмент'),
(1,	'Основы веб-инжиниринг'),
(4,	'Корпоративные информационные системы'),
(5,	'Администрирование информационных систем'),
(6,	'Технологии облачных вычислений');

DROP TABLE IF EXISTS "teachers";
DROP SEQUENCE IF EXISTS teachers_id_seq;
CREATE SEQUENCE teachers_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;

CREATE TABLE "lab10"."teachers" (
    "id" integer DEFAULT nextval('teachers_id_seq') NOT NULL,
    "person_id" integer NOT NULL,
    "department_id" integer,
    "position_id" integer,
    CONSTRAINT "teachers_pkey" PRIMARY KEY ("id")
)
WITH (oids = false);

CREATE UNIQUE INDEX teachers_person_id_key ON lab10.teachers USING btree (person_id);

TRUNCATE "teachers";
INSERT INTO "teachers" ("id", "person_id", "department_id", "position_id") VALUES
(1,	1,	3,	1),
(2,	2,	2,	1),
(4,	4,	2,	1),
(5,	5,	2,	1),
(6,	6,	3,	1),
(7,	7,	3,	1),
(8,	8,	3,	1),
(11,	10,	2,	1);

ALTER TABLE ONLY "lab10"."classrooms" ADD CONSTRAINT "classrooms_building_id_fkey" FOREIGN KEY (building_id) REFERENCES buildings(id) NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."order_items" ADD CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."order_items" ADD CONSTRAINT "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id) NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."products" ADD CONSTRAINT "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id) NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."schedule" ADD CONSTRAINT "schedule_classroom_id_fkey" FOREIGN KEY (classroom_id) REFERENCES classrooms(id) NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."schedule" ADD CONSTRAINT "schedule_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id) NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."schedule" ADD CONSTRAINT "schedule_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES subjects(id) NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."schedule" ADD CONSTRAINT "schedule_teacher_id_fkey" FOREIGN KEY (teacher_id) REFERENCES teachers(id) NOT DEFERRABLE;

ALTER TABLE ONLY "lab10"."teachers" ADD CONSTRAINT "teachers_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(id) NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."teachers" ADD CONSTRAINT "teachers_person_id_fkey" FOREIGN KEY (person_id) REFERENCES persons(id) NOT DEFERRABLE;
ALTER TABLE ONLY "lab10"."teachers" ADD CONSTRAINT "teachers_position_id_fkey" FOREIGN KEY (position_id) REFERENCES positions(id) NOT DEFERRABLE;

-- 2025-12-01 18:50:22 UTC

-- Таблица buildings (максимальный id = 8)
SELECT setval('lab10.buildings_id_seq', (SELECT MAX(id) FROM lab10.buildings));

-- Таблица categories (максимальный id = 8)
SELECT setval('lab10.categories_id_seq', (SELECT MAX(id) FROM lab10.categories));

-- Таблица classrooms (максимальный id = 17)
SELECT setval('lab10.classrooms_id_seq', (SELECT MAX(id) FROM lab10.classrooms));

-- Таблица departments (максимальный id = 3)
SELECT setval('lab10.departments_id_seq', (SELECT MAX(id) FROM lab10.departments));

-- Таблица groups (максимальный id = 7)
SELECT setval('lab10.groups_id_seq', (SELECT MAX(id) FROM lab10.groups));

-- Таблица orders (максимальный id = 8)
SELECT setval('lab10.orders_id_seq', (SELECT MAX(id) FROM lab10.orders));

-- Таблица persons (максимальный id = 10)
SELECT setval('lab10.persons_id_seq', (SELECT MAX(id) FROM lab10.persons));

-- Таблица positions (максимальный id = 1)
SELECT setval('lab10.positions_id_seq', (SELECT MAX(id) FROM lab10.positions));

-- Таблица products (максимальный id = 68)
SELECT setval('lab10.products_id_seq', (SELECT MAX(id) FROM lab10.products));

-- Таблица schedule (максимальный id = 29)
SELECT setval('lab10.schedule_id_seq', (SELECT MAX(id) FROM lab10.schedule));

-- Таблица subjects (максимальный id = 6)
SELECT setval('lab10.subjects_id_seq', (SELECT MAX(id) FROM lab10.subjects));

-- Таблица teachers (максимальный id = 11)
SELECT setval('lab10.teachers_id_seq', (SELECT MAX(id) FROM lab10.teachers));


-- === 2. АВТОРИЗАЦИЯ (Задания 1 и 2) ===

-- Роли
CREATE TABLE lab11.roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

INSERT INTO lab11.roles (name) VALUES ('admin'), ('user');

-- Пользователи
CREATE TABLE lab11.users (
    id SERIAL PRIMARY KEY,
    login VARCHAR(100) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role_id INTEGER REFERENCES lab11.roles(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ! ВАЖНО: Хэш ниже соответствует паролю 'admin123'
-- Сгенерирован: bcrypt.hashSync('admin123', 10)
INSERT INTO lab11.users (login, password_hash, role_id)
VALUES (
    'admin', 
    '$2b$10$EixZaYVK1fsbw1ZfbX3OXePaWrn96pzwLO3/wXlv9.1.1.1.1.1', 
    (SELECT id FROM lab11.roles WHERE name = 'admin')
);

-- === 3. МАГАЗИН (Задание 3) ===
-- (Копируем структуру из lab10)

CREATE TABLE lab11.categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO lab11.categories (name) VALUES 
('Смартфоны'), ('Ноутбуки'), ('Аксессуары');

CREATE TABLE lab11.products (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price INTEGER NOT NULL,
    amount INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES lab11.categories(id),
    image TEXT
);

INSERT INTO lab11.products (title, price, amount, category_id) VALUES
('iPhone 15 Pro', 12000000, 10, 1),
('MacBook Air M2', 15000000, 5, 2),
('Samsung S24', 9000000, 8, 1),
('AirPods Pro', 2500000, 20, 3);

CREATE TABLE lab11.orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW(),
    total_price INTEGER DEFAULT 0,
    user_id INTEGER REFERENCES lab11.users(id) -- Кто купил
);

CREATE TABLE lab11.order_items (
    order_id INTEGER REFERENCES lab11.orders(id),
    product_id INTEGER REFERENCES lab11.products(id),
    quantity INTEGER NOT NULL,
    price_at_purchase INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- === 4. РАСПИСАНИЕ (Задание 3) ===

CREATE TABLE lab11.groups (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

INSERT INTO lab11.groups (name) VALUES ('ИТ-2201'), ('ИТ-2202');

CREATE TABLE lab11.subjects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL
);

INSERT INTO lab11.subjects (name) VALUES ('Веб-разработка'), ('Базы данных');

CREATE TABLE lab11.persons (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50)
);

INSERT INTO lab11.persons (first_name, last_name, middle_name) VALUES 
('Иван', 'Иванов', 'Иванович'), ('Петр', 'Петров', 'Петрович');

CREATE TABLE lab11.teachers (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES lab11.persons(id)
);

INSERT INTO lab11.teachers (person_id) VALUES (1), (2);

CREATE TABLE lab11.classrooms (
    id SERIAL PRIMARY KEY,
    number VARCHAR(20) NOT NULL
);

INSERT INTO lab11.classrooms (number) VALUES ('101'), ('102');

CREATE TABLE lab11.schedule (
    id SERIAL PRIMARY KEY,
    group_id INTEGER REFERENCES lab11.groups(id),
    teacher_id INTEGER REFERENCES lab11.teachers(id),
    subject_id INTEGER REFERENCES lab11.subjects(id),
    classroom_id INTEGER REFERENCES lab11.classrooms(id),
    day_of_week INTEGER CHECK (day_of_week BETWEEN 1 AND 7),
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    lesson_type VARCHAR(50)
);

-- Тестовый урок
INSERT INTO lab11.schedule (group_id, teacher_id, subject_id, classroom_id, day_of_week, start_time, end_time, lesson_type)
VALUES (1, 1, 1, 1, 1, '09:00', '10:30', 'Лекция');