backend/init.sql - master - netsvvir/treeRepo

backend/init.sql
190 строк9 KB
netsvvir
Upload project
23 ноя 2025, 15:06
23 ноя 2025, 15:06ce0a46d
-- Создание таблиц в правильном порядке с учетом зависимостей

-- Таблица пользователей (аккаунтов)
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица площадок
CREATE TABLE areas (
    area_id SERIAL PRIMARY KEY,
    address TEXT NOT NULL,
    description TEXT,
    total_plots INTEGER NOT NULL DEFAULT 0,
    available_plots INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица типов табличек
CREATE TABLE plaque_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL,
    base_price DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255) -- Добавлено поле для URL изображения
);

-- Таблица типов деревьев (добавлена новая таблица)
CREATE TABLE tree_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL,
    description TEXT, -- ДОБАВЛЕНО: описание типа дерева
    base_price DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255) -- Поле для URL изображения дерева
);

-- Таблица табличек
CREATE TABLE plaques (
    plaque_id SERIAL PRIMARY KEY,
    type_id INTEGER REFERENCES plaque_types(type_id),
    price DECIMAL(10,2) NOT NULL,
    plaque_text TEXT,
    is_paid BOOLEAN DEFAULT FALSE,
    is_installed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица участков
CREATE TABLE plots (
    plot_id SERIAL PRIMARY KEY,
    area_id INTEGER REFERENCES areas(area_id),
    address TEXT NOT NULL,
    description TEXT,
    plot_number VARCHAR(20) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    owner_id INTEGER REFERENCES accounts(account_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица деревьев (ОБНОВЛЕНО: заменено tree_type на tree_type_id)
CREATE TABLE trees (
    tree_id SERIAL PRIMARY KEY,
    owner_id INTEGER REFERENCES accounts(account_id),
    plot_id INTEGER REFERENCES plots(plot_id),
    area_id INTEGER REFERENCES areas(area_id),
    tree_type_id INTEGER REFERENCES tree_types(type_id) NOT NULL, -- ЗАМЕНЕНО: ссылка на типы деревьев
    lease_term INTERVAL NOT NULL,
    plaque_id INTEGER REFERENCES plaques(plaque_id),
    planted_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для улучшения производительности
CREATE INDEX idx_trees_owner_id ON trees(owner_id);
CREATE INDEX idx_trees_plot_id ON trees(plot_id);
CREATE INDEX idx_trees_area_id ON trees(area_id);
CREATE INDEX idx_trees_tree_type_id ON trees(tree_type_id); -- ДОБАВЛЕН: индекс для типа дерева
CREATE INDEX idx_plots_area_id ON plots(area_id);
CREATE INDEX idx_plots_owner_id ON plots(owner_id);
CREATE INDEX idx_plots_plot_number ON plots(plot_number);
CREATE INDEX idx_plaques_type_id ON plaques(type_id);

-- Функция для автоматического обновления счетчиков участков на площадке
CREATE OR REPLACE FUNCTION update_area_plots_count()
RETURNS TRIGGER AS $$
BEGIN
    -- Обновляем счетчики для всех площадок, если это необходимо
    IF TG_OP = 'DELETE' THEN
        UPDATE areas 
        SET total_plots = (
            SELECT COUNT(*) 
            FROM plots 
            WHERE area_id = OLD.area_id
        ),
        available_plots = (
            SELECT COUNT(*) 
            FROM plots 
            WHERE area_id = OLD.area_id AND owner_id IS NULL
        )
        WHERE area_id = OLD.area_id;
    ELSE
        UPDATE areas 
        SET total_plots = (
            SELECT COUNT(*) 
            FROM plots 
            WHERE area_id = NEW.area_id
        ),
        available_plots = (
            SELECT COUNT(*) 
            FROM plots 
            WHERE area_id = NEW.area_id AND owner_id IS NULL
        )
        WHERE area_id = NEW.area_id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Триггеры для автоматического обновления счетчиков участков
CREATE TRIGGER trigger_update_plots_count_insert
    AFTER INSERT ON plots
    FOR EACH ROW
    EXECUTE FUNCTION update_area_plots_count();

CREATE TRIGGER trigger_update_plots_count_update
    AFTER UPDATE OF area_id, owner_id ON plots
    FOR EACH ROW
    EXECUTE FUNCTION update_area_plots_count();

CREATE TRIGGER trigger_update_plots_count_delete
    AFTER DELETE ON plots
    FOR EACH ROW
    EXECUTE FUNCTION update_area_plots_count();

-- Ограничение для проверки целостности данных
ALTER TABLE areas ADD CONSTRAINT check_plots_count 
CHECK (available_plots <= total_plots AND available_plots >= 0);

-- Комментарии к таблицам и колонкам
COMMENT ON TABLE accounts IS 'Таблица пользователей (аккаунтов) системы';
COMMENT ON TABLE areas IS 'Таблица площадок для посадки деревьев';
COMMENT ON TABLE plaque_types IS 'Таблица типов табличек для деревьев';
COMMENT ON TABLE tree_types IS 'Таблица типов деревьев';
COMMENT ON TABLE plaques IS 'Таблица табличек, устанавливаемых около деревьев';
COMMENT ON TABLE plots IS 'Таблица участков на площадках';
COMMENT ON TABLE trees IS 'Таблица посаженных деревьев';

COMMENT ON COLUMN areas.total_plots IS 'Общее количество участков на площадке';
COMMENT ON COLUMN areas.available_plots IS 'Количество доступных для аренды участков';
COMMENT ON COLUMN plaques.is_paid IS 'Статус оплаты таблички';
COMMENT ON COLUMN plaques.is_installed IS 'Статус установки таблички';
COMMENT ON COLUMN trees.lease_term IS 'Срок аренды участка под деревом';
COMMENT ON COLUMN plaque_types.image_url IS 'URL изображения таблички';
COMMENT ON COLUMN tree_types.image_url IS 'URL изображения дерева';
COMMENT ON COLUMN tree_types.description IS 'Описание типа дерева'; -- ДОБАВЛЕН комментарий
COMMENT ON COLUMN trees.tree_type_id IS 'Ссылка на тип дерева из таблицы tree_types';

-- Вставка начальных данных для типов табличек (обновлено с image_url)
INSERT INTO plaque_types (type_name, base_price, image_url) VALUES 
('Стандартная', 500.00, '/images/plaques/standard.jpg'),
('Премиум', 1000.00, '/images/plaques/premium.jpg'),
('Детская', 300.00, '/images/plaques/child.jpg'),
('Памятная', 800.00, '/images/plaques/memorial.jpg');

-- Вставка начальных данных для типов деревьев (добавлена новая вставка с описаниями)
INSERT INTO tree_types (type_name, description, base_price, image_url) VALUES 
('Дуб', 'Мощное лиственное дерево с прочной древесиной, символ долголетия и силы. Растет медленно, но живет сотни лет.', 1000.00, '/images/trees/oak.jpg'),
('Береза', 'Изящное дерево с белой корой, символ русской природы. Быстро растет, неприхотлива к почвам.', 800.00, '/images/trees/birch.jpg'),
('Сосна', 'Вечнозеленое хвойное дерево с ароматной хвоей. Отлично очищает воздух и устойчиво к засухе.', 600.00, '/images/trees/pine.jpg'),
('Ель', 'Пушистое хвойное дерево с густой кроной. Сохраняет зеленый цвет круглый год, популярна в ландшафтном дизайне.', 500.00, '/images/trees/spruce.jpg'),
('Клен', 'Декоративное дерево с красивой формой листьев, особенно эффектное осенью. Растет умеренно быстро.', 700.00, '/images/trees/maple.jpg');

-- Создание представления для удобного просмотра статистики по площадкам
CREATE VIEW area_stats AS
SELECT 
    a.area_id,
    a.address,
    a.description,
    a.total_plots,
    a.available_plots,
    COUNT(t.tree_id) as planted_trees,
    ROUND((a.available_plots::DECIMAL / NULLIF(a.total_plots, 0)) * 100, 2) as available_percent
FROM areas a
LEFT JOIN plots p ON a.area_id = p.area_id
LEFT JOIN trees t ON p.plot_id = t.plot_id
GROUP BY a.area_id, a.address, a.description, a.total_plots, a.available_plots;