CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    login VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    role_id INTEGER REFERENCES roles(role_id)
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE manufacturers (
    manufacturer_id SERIAL PRIMARY KEY,
    manufacturer_name VARCHAR(100) NOT NULL
);

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL
);

CREATE TABLE age_groups (
    age_group_id SERIAL PRIMARY KEY,
    age_label VARCHAR(20) NOT NULL
);

CREATE TABLE toys (
    toy_id SERIAL PRIMARY KEY,
    toy_name VARCHAR(150) NOT NULL,
    category_id INTEGER REFERENCES categories(category_id),
    manufacturer_id INTEGER REFERENCES manufacturers(manufacturer_id),
    price NUMERIC(10,2) NOT NULL,
    discount INTEGER DEFAULT 0,
    image VARCHAR(255)
);

CREATE TABLE toy_age_groups (
    toy_id INTEGER REFERENCES toys(toy_id),
    age_group_id INTEGER REFERENCES age_groups(age_group_id),
    PRIMARY KEY (toy_id, age_group_id)
);

CREATE TABLE toy_suppliers (
    toy_id INTEGER REFERENCES toys(toy_id),
    supplier_id INTEGER REFERENCES suppliers(supplier_id),
    PRIMARY KEY (toy_id, supplier_id)
);

CREATE TABLE stock (
    toy_id INTEGER PRIMARY KEY REFERENCES toys(toy_id),
    quantity INTEGER NOT NULL
);

INSERT INTO roles (role_name) VALUES
('Гость'),
('Покупатель'),
('Сотрудник'),
('Администратор');

INSERT INTO users (login, password, full_name, role_id) VALUES
('guest','guest','Гость',1),
('anna','111','Иванова Анна Сергеевна',2),
('oleg','222','Кузнецов Олег Петрович',2),
('manager','333','Смирнова Мария Андреевна',3),
('admin','admin','Сидоров Максим Игоревич',4);

INSERT INTO categories (category_name) VALUES
('Мягкие игрушки'),
('Конструкторы'),
('Развивающие игрушки'),
('Настольные игры'),
('Роботы');

INSERT INTO manufacturers (manufacturer_name) VALUES
('Lego'),
('Hasbro'),
('Mattel'),
('PlaySmart'),
('Fisher Price');

INSERT INTO suppliers (supplier_name) VALUES
('ООО Радуга'),
('ИП Смайл'),('ToyImport'),
('KidsWorld');

INSERT INTO age_groups (age_label) VALUES
('0-1'),
('1-3'),
('3-5'),
('5-7'),
('7+');

INSERT INTO toys (toy_name, category_id, manufacturer_id, price, discount, image) VALUES
('Плюшевый мишка',1,4,1500,10,'bear.png'),
('Конструктор City',2,1,4200,25,'city.png'),
('Развивающий куб',3,5,2300,0,NULL),
('Робот трансформер',5,2,5200,30,'robot.png'),
('Настольная игра Лото',4,3,1800,5,NULL),
('Кукла классическая',1,3,2700,15,'doll.png'),
('Конструктор Junior',2,1,3100,0,NULL),
('Музыкальный телефон',3,5,2100,20,NULL),
('Робот на пульте',5,2,6400,35,'rc.png'),
('Игра Мемори',4,3,1600,0,NULL);

INSERT INTO toy_age_groups (toy_id, age_group_id) VALUES
(1,2),(1,3),
(2,4),(2,5),
(3,2),
(4,4),(4,5),
(5,3),(5,4),
(6,3),(6,4),
(7,2),(7,3),
(8,2),
(9,5),
(10,3),(10,4);

INSERT INTO toy_suppliers (toy_id, supplier_id) VALUES
(1,1),(1,2),
(2,3),
(3,1),(3,4),
(4,2),(4,3),
(5,1),
(6,4),
(7,3),
(8,2),(8,4),
(9,3),
(10,1),(10,2);

INSERT INTO stock (toy_id, quantity) VALUES
(1,12),(2,5),(3,0),(4,7),(5,10),
(6,4),(7,8),(8,0),(9,3),(10,15);