database.sql - master - delphin/CatRW

database.sql
295 строк12 KB
delphin
start project
02 мар 2026, 09:14
02 мар 2026, 09:144b0ee03
-- database.sql
-- 🚂 ЖД КАТАЛОГ - ОСНОВНЫЕ ТАБЛИЦЫ

-- 1. Пользователи - ОБНОВЛЁННАЯ ВЕРСИЯ
-- Упрощенная таблица пользователей
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('root', 'admin', 'editor', 'viewer') DEFAULT 'editor',
    full_name VARCHAR(100),
    language VARCHAR(10) DEFAULT 'ru',
    is_active BOOLEAN DEFAULT TRUE,
    ban_reason TEXT,
    banned_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME,
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Первый пользователь (root)
-- Пароль: password (хэш уже вставлен)
INSERT INTO users (username, email, password_hash, role, full_name, is_active) 
VALUES (
    'admin',
    'admin@railway.local',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password
    'root',
    'Администратор',
    TRUE    
) ON DUPLICATE KEY UPDATE role='root';

-- Тестовый admin
INSERT INTO users (username, email, password_hash, role, full_name) 
VALUES (
    'user1',
    'user1@railway.local',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password
    'editor',
    'Тестовый пользователь'
) ON DUPLICATE KEY UPDATE role='editor';

-- Заблокированный пользователь для примера
INSERT INTO users (username, email, password_hash, role, full_name, ban_reason, banned_at) 
VALUES (
    'spammer',
    'spam@test.local',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password
    'viewer',
    'Спамер',
    'Рассылка спама в комментариях',
    '2025-11-10 14:30:00'
) ON DUPLICATE KEY UPDATE ban_reason='Рассылка спама в комментариях';


-- 2. Производители (справочник) - СОЗДАЁМ ПЕРВОЙ, т.к. на неё ссылаются
CREATE TABLE IF NOT EXISTS manufacturers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,      -- "Piko", "Märklin", "РЖД" (оригинал!)
    country_code VARCHAR(2),                -- DE, AT, JP, CN, RU
    website VARCHAR(255),                   -- опционально
    founded_year SMALLINT UNSIGNED,         -- NULL если неизвестно
    closed_year SMALLINT UNSIGNED,          -- NULL если ещё жив
    notes TEXT,                             -- для редких случаев
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_country (country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Тестовые производители
INSERT INTO manufacturers (name, country_code, website, founded_year) VALUES
('Piko', 'DE', 'https://www.piko.de/', 1949),
('Roco', 'AT', 'https://www.roco.cc/', 1960),
('Fleischmann', 'DE', 'https://www.fleischmann.de/', 1887),
('Märklin', 'DE', 'https://www.maerklin.de/', 1859),
('Brawa', 'DE', 'https://www.brawa.de/', 1948),
('Hornby', 'GB', 'https://www.hornby.com/', 1901);

-- 3. Общественный каталог моделей
CREATE TABLE IF NOT EXISTS catalog_models (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Классификация
    type_code VARCHAR(20) NOT NULL,           -- locomotive, wagon, passenger, freight
    subtype_code VARCHAR(20),                 -- steam, diesel, electric, tank_loco
    scale_code VARCHAR(5) NOT NULL,           -- H0, TT, N, Z, G
    era_code VARCHAR(10),                     -- I, II, III, IV, V, VI
    manufacturer_id INT NOT NULL,             -- ССЫЛКА НА manufacturers.id
    
    -- Основная информация
    model_number VARCHAR(100),                -- артикул производителя
    name VARCHAR(200) NOT NULL,               -- BR 86, SBB Re 460
    road_number VARCHAR(50),                  -- номер на модели
    railway_company INT,             -- ССЫЛКА НА railway_companies.id (DR,  DB, ÖBB, SBB)
    country_code VARCHAR(2),                  -- DE, AT, CH
    
    -- Технические характеристики
    length_mm DECIMAL(6,2),
    weight_gr DECIMAL(6,2),
    coupling_code VARCHAR(20),                -- nem_362, rapido, screw
    
    -- Цифровые системы
    has_dcc_decoder BOOLEAN DEFAULT FALSE,
    has_sound BOOLEAN DEFAULT FALSE,
    
    -- Освещение
    has_lights BOOLEAN DEFAULT FALSE,
    
    -- Ручные доработки
    manual_electric BOOLEAN DEFAULT FALSE,
    manual_painting BOOLEAN DEFAULT FALSE,
    
    -- Цена
    rec_price DECIMAL(10,2),
    currency_code VARCHAR(3) DEFAULT 'EUR',
    
    -- Описания
    description TEXT,
    prototype_info TEXT,
    
    -- Детальные признаки в JSON
    features_json JSON DEFAULT NULL,
    
    -- Системные поля
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    
    -- Внешние ключи
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Индексы
    INDEX idx_type_scale (type_code, scale_code),
    INDEX idx_manufacturer (manufacturer_id),
    INDEX idx_scale (scale_code),
    INDEX idx_status (status),
    INDEX idx_has_lights (has_lights),
    INDEX idx_has_dcc_decoder (has_dcc_decoder),
    INDEX idx_has_sound (has_sound)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Тестовые модели
INSERT INTO catalog_models (
    manufacturer_id, type_code, subtype_code, scale_code, era_code, 
    name, model_number, status
) VALUES
(1, 'locomotive', 'steam', 'H0', 'III', 'BR 86', 'Piko-59800', 'published'),
(2, 'locomotive', 'electric', 'TT', 'V', 'ÖBB 1044', 'Roco-63330', 'published'),
(3, 'wagon', 'passenger', 'N', 'IV', 'DB Silberlinge', 'Fleischmann-7231', 'published'),
(4, 'locomotive', 'steam', 'H0', 'II', 'BR 01', 'Märklin-37001', 'published'),
(5, 'locomotive', 'diesel', 'H0', 'IV', 'DB BR 218', 'Brawa-40218', 'published'),
(6, 'locomotive', 'steam', 'H0', 'I', 'Flying Scotsman', 'Hornby-R1234', 'published');

-- 4. Личные коллекции пользователей
CREATE TABLE IF NOT EXISTS personal_collections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    catalog_model_id INT NOT NULL,
    
    -- Личные данные
    inventory_number VARCHAR(50),
    purchase_date DATE,
    purchase_price DECIMAL(10,2),
    condition_code VARCHAR(20) DEFAULT 'new',
    storage_location VARCHAR(100),
    notes TEXT,
    
    -- Видимость
    is_public BOOLEAN DEFAULT FALSE,
    show_price BOOLEAN DEFAULT FALSE,
    
    -- Системное
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    
    -- Ограничения
    UNIQUE KEY unique_user_model (user_id, catalog_model_id),
    
    -- Внешние ключи
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (catalog_model_id) REFERENCES catalog_models(id) ON DELETE CASCADE,
    
    -- Индексы
    INDEX idx_user_model (user_id, catalog_model_id),
    INDEX idx_public (is_public),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Тестовая коллекция
INSERT INTO personal_collections (user_id, catalog_model_id, condition_code, is_public) VALUES
(2, 1, 'new', TRUE),
(2, 3, 'used', TRUE),
(2, 5, 'mint', FALSE);


-- 1. Типоразмеры (scales) - у нас уже есть scale_code в catalog_models
-- Просто делаем таблицу с доп. информацией
CREATE TABLE IF NOT EXISTS scales (
    code VARCHAR(5) PRIMARY KEY,           -- H0, TT, N, Z
    name_en VARCHAR(50),                   -- HO Scale (для системы/админки)
    scale_ratio VARCHAR(20),               -- 1:87 (техническая информация)
    track_gauge_mm DECIMAL(5,2),           -- 16.5 (техническая информация)
    description TEXT                       -- Любые заметки для админа
);

-- 2. Типы моделей (model_types) - у нас уже есть type_code в catalog_models
CREATE TABLE IF NOT EXISTS model_types (
    code VARCHAR(20) PRIMARY KEY          -- locomotive, wagon, passenger, freight
);

-- 3. Подтипы (model_subtypes) - у нас уже есть subtype_code в catalog_models
CREATE TABLE IF NOT EXISTS model_subtypes (
    code VARCHAR(20) PRIMARY KEY,          -- steam, diesel, electric
    type_code VARCHAR(20) NOT NULL,        -- locomotive
    FOREIGN KEY (type_code) REFERENCES model_types(code)
);	
	
	-- Типоразмеры (стандартные)
INSERT INTO scales (code, name_en, scale_ratio, track_gauge_mm, description) VALUES
('H0', 'HO Scale', '1:87', 16.5, 'Стандартный европейский типоразмер'),
('TT', 'TT Scale', '1:120', 12, 'Типоразмер TT (TableTop)'),
('N', 'N Scale', '1:160', 9, 'Типоразмер N (Nine millimeter)'),
('Z', 'Z Scale', '1:220', 6.5, 'Самый маленький массовый типоразмер'),

('H0m', 'HO Scale', '1:87', 12, 'Узкая колея HO'),
('H0e', 'HO Scale', '1:87', 9, 'Узкая колея HO'),
('TTm', 'TT Scale', '1:120', 9, 'Узкая колея TT'),
('TTe', 'TT Scale', '1:120', 6.5, 'Узкая колея TT'),

('00', '00 Scale', '1:76', 16.5, 'Стандартный British типоразмер'),

('G', 'G Scale', '1:24', 45, 'Садовые модельные железные дороги'),


('S', 'S Scale', '1:64', 22.5, 'Устаревный игрушечный типоразмер'),
('0', '0 Scale', '1:45', 32, 'Большие модельные железные дороги'),
('I', 'I Scale', '1:32', 45, 'Большие модельные железные дороги');

-- Типы моделей
INSERT INTO model_types (code) VALUES
('locomotive'),
('wagon'),
('passenger'),
('freight');

-- Подтипы для локомотивов
INSERT INTO model_subtypes (code, type_code) VALUES
('steam', 'locomotive'),
('diesel', 'locomotive'),
('electric', 'locomotive'),
('tank_loco', 'locomotive');

-- Подтипы для вагонов
INSERT INTO model_subtypes (code, type_code) VALUES
('box_wagon', 'wagon'),
('flat_wagon', 'wagon'),
('tank_wagon', 'wagon'),
('hoppers', 'wagon');

CREATE TABLE IF NOT EXISTS railway_companies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(10) NOT NULL UNIQUE,       -- db, oebb, sbb, etc
    name VARCHAR(100) NOT NULL,             -- Deutsche Bahn, ÖBB, SBB
    country_code VARCHAR(2),                -- DE, AT, CH
    founded_year SMALLINT UNSIGNED,
    closed_year SMALLINT UNSIGNED,
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_country (country_code)
);

-- Базовые данные
INSERT INTO railway_companies (code, name, country_code) VALUES
('db', 'Deutsche Bahn', 'DE'),
('oebb', 'Österreichische Bundesbahnen (ÖBB)', 'AT'),
('sbb', 'Schweizerische Bundesbahnen (SBB)', 'CH'),
('sncf', 'Société Nationale des Chemins de fer Français', 'FR'),
('fs', 'Ferrovie dello Stato Italiane', 'IT'),
('renfe', 'Red Nacional de los Ferrocarriles Españoles', 'ES'),
('br', 'British Rail', 'GB'),
('amtrak', 'Amtrak', 'US'),
('jr', 'Japan Railways', 'JP'),
('rzd', 'Российские железные дороги (РЖД)', 'RU'),
('pkp', 'Polskie Koleje Państwowe', 'PL'),
('cd', 'České dráhy', 'CZ'),
('mav', 'Magyar Államvasutak', 'HU');