scripts/create_tables.sql - main - killogram/E-Analyzer

scripts/create_tables.sql
81 строка3 KB
Korabliov Kirill
Refactor add purchase feachure
01 июн 2025, 23:54
01 июн 2025, 23:549d93c35
-- Создание таблицы закупок
CREATE TABLE purchases (
    id SERIAL PRIMARY KEY,
    registry_number VARCHAR(255) NOT NULL,
    stage VARCHAR(255) NOT NULL,
    method TEXT NOT NULL,
    name TEXT NOT NULL,
    law VARCHAR(100) NOT NULL,
    okpd2 TEXT,
    extra_data JSONB DEFAULT '{}',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    CONSTRAINT unique_registry_number UNIQUE (registry_number)
);

-- Индексы для быстрого поиска
CREATE INDEX idx_purchase_registry_number ON purchases (registry_number);
CREATE INDEX idx_purchase_name ON purchases (name);
CREATE INDEX idx_purchase_data_gin ON purchases USING GIN (extra_data);

-- Полнотекстовый поиск
ALTER TABLE purchases ADD COLUMN search_vector TSVECTOR;
UPDATE purchases SET search_vector = to_tsvector('russian', name);  
CREATE INDEX idx_purchase_search_vector ON purchases USING GIN (search_vector);

-- Создание таблицы цен
CREATE TABLE prices (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER NOT NULL,
    initial_price DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(50) NOT NULL,
    contract_price DECIMAL(15, 2),
    contract_currency VARCHAR(50),
    CONSTRAINT fk_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
);

CREATE INDEX idx_prices_purchase_id ON prices (purchase_id);

-- Создание таблицы дат
CREATE TABLE dates (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER NOT NULL,
    placement_date TIMESTAMP NOT NULL,
    update_date TIMESTAMP NOT NULL,
    application_start_date TIMESTAMP,
    application_end_date TIMESTAMP,
    CONSTRAINT fk_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
);

CREATE INDEX idx_dates_purchase_id ON dates (purchase_id);

-- Создание таблицы заказчиков
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    extra_data JSONB DEFAULT '{}'
);

-- Создание таблицы отслеживаемых закупок
CREATE TABLE tracked_purchases (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    CONSTRAINT unique_purchase_status UNIQUE (purchase_id)
);

CREATE INDEX idx_tracked_purchase_id ON tracked_purchases (purchase_id);

CREATE TABLE purchase_customer (
    id SERIAL PRIMARY KEY,
    purchase_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    CONSTRAINT fk_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT unique_purchase_customer UNIQUE (purchase_id, customer_id)
);

CREATE INDEX idx_purchase_customer_purchase_id ON purchase_customer (purchase_id);
CREATE INDEX idx_purchase_customer_customer_id ON purchase_customer (customer_id);