docker/postgres/initss.sql - master - en_vui/ge

docker/postgres/initss.sql
151 строка14 KB
Famel1x
init
11 фев 2026, 10:08
11 фев 2026, 10:081504a02
-- 1. Создание структуры таблиц
CREATE TABLE IF NOT EXISTS clients (client_id SERIAL PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), middle_name VARCHAR(100), birth_date DATE, gender CHAR(1), inn VARCHAR(12), snils VARCHAR(14), passport_serial VARCHAR(4), passport_number VARCHAR(6), passport_issuer_code VARCHAR(7), passport_issue_date DATE, address_reg_country VARCHAR(50), address_reg_city VARCHAR(50), address_reg_street VARCHAR(100), address_reg_house VARCHAR(10), phone_primary VARCHAR(20), email_primary VARCHAR(100), employer_name VARCHAR(150), job_title VARCHAR(100), monthly_income DECIMAL(15,2), credit_score INT, risk_level VARCHAR(10), is_vip BOOLEAN, manager_id INT, registration_date TIMESTAMP, last_login_date TIMESTAMP, marketing_consent BOOLEAN, data_processing_consent BOOLEAN);
CREATE TABLE IF NOT EXISTS accounts (account_id SERIAL PRIMARY KEY, client_id INT, account_number VARCHAR(20), currency_code CHAR(3), balance DECIMAL(15,2), blocked_amount DECIMAL(15,2), credit_limit DECIMAL(15,2), open_date DATE, close_date DATE, status VARCHAR(20), tariff_plan_id INT, branch_id INT, interest_rate DECIMAL(5,2), last_activity_date TIMESTAMP, overdraft_allowed BOOLEAN, is_joint_account BOOLEAN, tax_residency_country VARCHAR(50), statement_delivery_method VARCHAR(20));
CREATE TABLE IF NOT EXISTS cards (card_id SERIAL PRIMARY KEY, account_id INT, card_number_masked VARCHAR(19), card_holder_name VARCHAR(100), expiration_date DATE, cvv_hash VARCHAR(64), payment_system VARCHAR(20), card_type VARCHAR(20), design_code VARCHAR(10), issue_date DATE, activation_date DATE, status VARCHAR(20), pin_attempts_left INT, daily_limit DECIMAL(15,2), monthly_limit DECIMAL(15,2), is_contactless BOOLEAN, is_virtual BOOLEAN, delivery_branch_id INT, reissue_reason VARCHAR(50));
CREATE TABLE IF NOT EXISTS transactions (trans_id BIGSERIAL PRIMARY KEY, account_id INT, card_id INT, amount DECIMAL(15,2), currency CHAR(3), amount_base_currency DECIMAL(15,2), trans_date TIMESTAMP, value_date DATE, direction VARCHAR(10), operation_type VARCHAR(50), mcc_code VARCHAR(4), merchant_name VARCHAR(100), merchant_city VARCHAR(50), merchant_country CHAR(3), terminal_id VARCHAR(20), status VARCHAR(20), auth_code VARCHAR(10), rrn VARCHAR(20), fee_amount DECIMAL(10,2), cashback_amount DECIMAL(10,2), ip_address VARCHAR(45), device_id VARCHAR(100));
CREATE TABLE IF NOT EXISTS loans (loan_id SERIAL PRIMARY KEY, client_id INT, contract_number VARCHAR(50), product_type VARCHAR(50), amount_issued DECIMAL(15,2), amount_remaining DECIMAL(15,2), interest_rate DECIMAL(5,2), psk_rate DECIMAL(5,2), start_date DATE, end_date_planned DATE, end_date_actual DATE, monthly_payment DECIMAL(15,2), payment_day INT, overdue_days INT, overdue_amount DECIMAL(15,2), status VARCHAR(20), collateral_type VARCHAR(50), insurance_contract_id INT);
CREATE TABLE IF NOT EXISTS deposits (deposit_id SERIAL PRIMARY KEY, client_id INT, account_id INT, contract_number VARCHAR(50), amount_initial DECIMAL(15,2), currency CHAR(3), rate DECIMAL(5,2), open_date DATE, close_date_planned DATE, is_revocable BOOLEAN, is_replenishable BOOLEAN, capitalization_freq VARCHAR(20), interest_payout_account_id INT, auto_renewal BOOLEAN, early_close_penalty_rate DECIMAL(5,2), tax_amount_held DECIMAL(15,2));
CREATE TABLE IF NOT EXISTS branches (branch_id SERIAL PRIMARY KEY, name VARCHAR(100), region_code VARCHAR(10), city VARCHAR(50), address VARCHAR(150), postal_code VARCHAR(10), latitude DECIMAL(9,6), longitude DECIMAL(9,6), phone VARCHAR(20), manager_full_name VARCHAR(100), open_time TIME, close_time TIME, is_weekend_open BOOLEAN, has_atm BOOLEAN, has_currency_exchange BOOLEAN, has_vip_zone BOOLEAN, has_safe_deposit BOOLEAN, staff_count INT, atm_count INT);
CREATE TABLE IF NOT EXISTS employees (emp_id SERIAL PRIMARY KEY, tab_number VARCHAR(20), first_name VARCHAR(100), last_name VARCHAR(100), department VARCHAR(100), position VARCHAR(100), grade VARCHAR(10), manager_id INT, hire_date DATE, salary DECIMAL(15,2), kpi_score DECIMAL(5,2), email_corporate VARCHAR(100), phone_internal VARCHAR(20), access_level INT, is_active BOOLEAN, office_location_id INT, last_security_training DATE, vacation_days_balance INT);
CREATE TABLE IF NOT EXISTS products (product_id SERIAL PRIMARY KEY, product_code VARCHAR(20), name VARCHAR(100), type VARCHAR(50), currency_allowed VARCHAR(10), min_amount DECIMAL(15,2), max_amount DECIMAL(15,2), base_rate DECIMAL(5,2), min_term_days INT, max_term_days INT, is_active BOOLEAN, launch_date DATE, archive_date DATE, target_audience_segment VARCHAR(50), description TEXT, conditions_url VARCHAR(200));
CREATE TABLE IF NOT EXISTS audit_logs (log_id BIGSERIAL PRIMARY KEY, event_timestamp TIMESTAMP, user_id INT, user_type VARCHAR(20), event_type VARCHAR(50), object_type VARCHAR(50), object_id VARCHAR(50), ip_address VARCHAR(45), user_agent TEXT, session_id VARCHAR(64), status VARCHAR(20), error_message TEXT, old_value TEXT, new_value TEXT, server_node_id VARCHAR(20), execution_time_ms INT, risk_score DECIMAL(5,2));

-- 2. Добавление колонок Attribute_XX (Massive columns)
DO $$
DECLARE
    t_name TEXT;
    i INT;
    start_cols INT;
BEGIN
    FOR t_name IN 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
          AND table_name IN ('clients', 'accounts', 'cards', 'transactions', 'loans', 'deposits', 'branches', 'employees', 'products', 'audit_logs')
    LOOP
        SELECT count(*) INTO start_cols 
        FROM information_schema.columns 
        WHERE table_name = t_name AND table_schema = 'public';

        FOR i IN (start_cols + 1)..50 LOOP
            EXECUTE format('ALTER TABLE %I ADD COLUMN attribute_%s TEXT', t_name, i);
        END LOOP;
    END LOOP;
END $$;

DO $$
DECLARE
    arr_first_names TEXT[] := ARRAY['Ivan', 'Petr', 'Sergey', 'Alexey', 'Dmitry', 'Maria', 'Elena', 'Olga', 'Svetlana', 'Anna', 'John', 'Michael', 'David'];
    arr_last_names TEXT[] := ARRAY['Ivanov', 'Petrov', 'Sidorov', 'Kuznetsov', 'Popov', 'Smirnov', 'Vasilyev', 'Smith', 'Johnson', 'Williams'];
    arr_cities TEXT[] := ARRAY['Moscow', 'Saint-Petersburg', 'Novosibirsk', 'Yekaterinburg', 'Kazan', 'Nizhny Novgorod', 'Chelyabinsk', 'Samara', 'Omsk'];
    arr_streets TEXT[] := ARRAY['Lenina st.', 'Mira ave.', 'Sovetskaya st.', 'Pushkina st.', 'Gagarina st.', 'Kirova st.'];
    arr_currencies TEXT[] := ARRAY['RUB', 'USD', 'EUR', 'CNY'];
    arr_statuses TEXT[] := ARRAY['Active', 'Blocked', 'Closed', 'Pending'];
    arr_trans_types TEXT[] := ARRAY['Purchase', 'Transfer', 'Withdrawal', 'Payment'];
    arr_merchants TEXT[] := ARRAY['Auchan', 'Pyaterochka', 'Uber', 'Netflix', 'Lukoil', 'Gazpromneft', 'Apple Store', 'Steam', 'Yandex Eda'];
BEGIN
    -- Clients
    INSERT INTO clients (first_name, last_name, middle_name, birth_date, gender, passport_number, address_reg_city, monthly_income, credit_score, risk_level, is_vip, registration_date)
    SELECT 
        arr_first_names[floor(random() * array_length(arr_first_names, 1) + 1)],
        arr_last_names[floor(random() * array_length(arr_last_names, 1) + 1)],
        'Ivanovich',
        NOW() - (random() * 365 * 60 + 365 * 18 || ' days')::interval,
        CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END,
        lpad((floor(random() * 999999)::text), 6, '0'),
        arr_cities[floor(random() * array_length(arr_cities, 1) + 1)],
        (random() * 150000 + 20000)::numeric(15,2),
        floor(random() * 850 + 300),
        CASE WHEN random() > 0.9 THEN 'High' WHEN random() > 0.7 THEN 'Medium' ELSE 'Low' END,
        (random() > 0.95),
        NOW() - (random() * 1000 || ' days')::interval
    FROM generate_series(1, 400);

    -- Branches
    INSERT INTO branches (name, city, address, staff_count, has_atm, open_time, close_time)
    SELECT 'Branch #' || i, arr_cities[floor(random() * array_length(arr_cities, 1) + 1)], arr_streets[floor(random() * array_length(arr_streets, 1) + 1)] || ', ' || floor(random()*100+1), floor(random() * 50 + 5), (random() > 0.2), '09:00', '20:00'
    FROM generate_series(1, 400) AS i;

    -- Products
    INSERT INTO products (name, type, currency_allowed, base_rate, is_active)
    SELECT 'Product ' || i, CASE WHEN i % 3 = 0 THEN 'Loan' WHEN i % 3 = 1 THEN 'Deposit' ELSE 'Card' END, 'RUB', (random() * 20 + 5)::numeric(5,2), true
    FROM generate_series(1, 400) AS i;

    -- Accounts
    INSERT INTO accounts (client_id, account_number, currency_code, balance, status, open_date, branch_id)
    SELECT floor(random() * 400 + 1), '40817810' || lpad(floor(random() * 999999999999)::text, 12, '0'), arr_currencies[floor(random() * array_length(arr_currencies, 1) + 1)], (random() * 500000 - 1000)::numeric(15,2), arr_statuses[floor(random() * array_length(arr_statuses, 1) + 1)], NOW() - (random() * 1000 || ' days')::interval, floor(random() * 400 + 1)
    FROM generate_series(1, 400);

    -- Cards
    INSERT INTO cards (account_id, card_number_masked, payment_system, status, expiration_date)
    SELECT floor(random() * 400 + 1), '4276********' || lpad(floor(random() * 9999)::text, 4, '0'), CASE WHEN random() > 0.5 THEN 'Visa' ELSE 'MasterCard' END, 'Active', NOW() + (random() * 1000 || ' days')::interval
    FROM generate_series(1, 400);

    -- Transactions
    INSERT INTO transactions (account_id, amount, currency, trans_date, operation_type, merchant_name, status)
    SELECT floor(random() * 400 + 1), (random() * 10000 + 100)::numeric(15,2), 'RUB', NOW() - (random() * 365 || ' days')::interval, arr_trans_types[floor(random() * array_length(arr_trans_types, 1) + 1)], arr_merchants[floor(random() * array_length(arr_merchants, 1) + 1)], 'Success'
    FROM generate_series(1, 400);

    -- Loans
    INSERT INTO loans (client_id, amount_issued, interest_rate, start_date, status)
    SELECT floor(random() * 400 + 1), (random() * 5000000 + 100000)::numeric(15,2), (random() * 15 + 10)::numeric(5,2), NOW() - (random() * 500 || ' days')::interval, 'Active'
    FROM generate_series(1, 400);

    -- Deposits
    INSERT INTO deposits (client_id, amount_initial, rate, open_date, currency)
    SELECT floor(random() * 400 + 1), (random() * 1000000 + 50000)::numeric(15,2), (random() * 10 + 4)::numeric(5,2), NOW() - (random() * 300 || ' days')::interval, 'RUB'
    FROM generate_series(1, 400);

    -- Employees
    INSERT INTO employees (first_name, last_name, department, salary, hire_date)
    SELECT arr_first_names[floor(random() * array_length(arr_first_names, 1) + 1)], arr_last_names[floor(random() * array_length(arr_last_names, 1) + 1)], CASE WHEN random() > 0.5 THEN 'Sales' ELSE 'IT' END, (random() * 200000 + 40000)::numeric(15,2), NOW() - (random() * 2000 || ' days')::interval
    FROM generate_series(1, 400);

    -- Audit Logs
    INSERT INTO audit_logs (event_timestamp, user_id, event_type, ip_address, status)
    SELECT NOW() - (random() * 30 || ' days')::interval, floor(random() * 400 + 1), CASE WHEN random() > 0.5 THEN 'Login' ELSE 'View_Report' END, '192.168.1.' || floor(random() * 255), 'Success'
    FROM generate_series(1, 400);
END $$;

-- 4. Заполнение атрибутов случайным мусором
DO $$
DECLARE
    t_name TEXT; c_name TEXT;
BEGIN
    FOR t_name IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name IN ('clients', 'accounts', 'cards', 'transactions', 'loans', 'deposits', 'branches', 'employees', 'products', 'audit_logs') LOOP
        FOR c_name IN SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = t_name AND column_name LIKE 'attribute_%' LOOP
            EXECUTE format('UPDATE %I SET %I = md5(random()::text)', t_name, c_name);
        END LOOP;
    END LOOP;
END $$;

-- 5. Обогащение данных Клиентов (ИНН, СНИЛС, Контакты)
DO $$
DECLARE
    arr_domains TEXT[] := ARRAY['gmail.com', 'mail.ru', 'yandex.ru', 'outlook.com', 'bk.ru'];
    arr_companies TEXT[] := ARRAY['Gazprom', 'Sberbank', 'Yandex', 'Lukoil', 'X5 Retail', 'Magnit', 'Rostelecom', 'MTS', 'Individual Entrepreneur'];
    arr_titles TEXT[] := ARRAY['Manager', 'Engineer', 'Developer', 'Accountant', 'Driver', 'Salesperson', 'Director', 'Analyst'];
    arr_streets TEXT[] := ARRAY['Lenina', 'Mira', 'Pobedy', 'Sovetskaya', 'Kirova', 'Gagarina', 'Lesnaya'];
BEGIN
    UPDATE clients SET 
        inn = (100000000000 + floor(random() * 899999999999))::text,
        snils = lpad(floor(random()*999)::text, 3, '0') || '-' || lpad(floor(random()*999)::text, 3, '0') || '-' || lpad(floor(random()*999)::text, 3, '0') || ' ' || lpad(floor(random()*99)::text, 2, '0'),
        passport_serial = lpad(floor(random() * 9999)::text, 4, '0'),
        passport_issuer_code = lpad(floor(random() * 999)::text, 3, '0') || '-' || lpad(floor(random() * 999)::text, 3, '0'),
        passport_issue_date = birth_date + (14 + floor(random() * 20) || ' years')::interval,
        email_primary = lower(first_name || '.' || last_name || floor(random()*100)::text || '@' || arr_domains[floor(random() * array_length(arr_domains, 1) + 1)]),
        phone_primary = '+79' || lpad(floor(random() * 999999999)::text, 9, '0'),
        address_reg_country = 'Russia',
        address_reg_street = arr_streets[floor(random() * array_length(arr_streets, 1) + 1)] || ' st.',
        address_reg_house = floor(random() * 150 + 1)::text,
        employer_name = arr_companies[floor(random() * array_length(arr_companies, 1) + 1)],
        job_title = arr_titles[floor(random() * array_length(arr_titles, 1) + 1)]
    WHERE inn IS NULL;
END $$;

-- 6. Фикс городов в транзакциях
UPDATE transactions 
SET 
    merchant_city = (ARRAY['Moscow', 'Saint-Petersburg', 'Novosibirsk', 'Kazan', 'Sochi'])[floor(random() * 5 + 1)],
    merchant_country = 'RUS'
WHERE merchant_city IS NULL;