backend/backup/init.sql - master - netsvvir/treeRepo

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

-- Таблица пользователей (аккаунтов)
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
);

-- Таблица табличек
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
);

-- Таблица деревьев
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 VARCHAR(100) 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_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 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 'Срок аренды участка под деревом';

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

-- Создание представления для удобного просмотра статистики по площадкам
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;