141 lines
7.6 KiB
SQL
Executable File
141 lines
7.6 KiB
SQL
Executable File
-- ========= ЮРИДИЧЕСКИЙ ОТДЕЛ: ПОЛНАЯ МИГРАЦИЯ =========
|
||
-- Миграция для создания всех таблиц юридического отдела
|
||
|
||
-- Договоры
|
||
CREATE TABLE IF NOT EXISTS legal_contracts (
|
||
id VARCHAR(50) PRIMARY KEY,
|
||
number TEXT NOT NULL UNIQUE,
|
||
type TEXT NOT NULL, -- Тип договора (Поставка, Услуги и т.д.)
|
||
counterparty TEXT NOT NULL, -- Название контрагента
|
||
counterparty_inn TEXT, -- ИНН контрагента (для связи с проверками)
|
||
amount NUMERIC(15, 2) NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'finance_approval', 'counterparty_approval', 'signing', 'active', 'archived')),
|
||
start_date DATE NOT NULL,
|
||
end_date DATE NOT NULL,
|
||
auto_prolongation BOOLEAN DEFAULT FALSE,
|
||
manager TEXT NOT NULL, -- Кто ведет договор
|
||
has_disagreements BOOLEAN DEFAULT FALSE,
|
||
contract_file_url TEXT, -- Ссылка на файл договора
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_legal_contracts_status ON legal_contracts(status);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_contracts_counterparty ON legal_contracts(counterparty);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_contracts_number ON legal_contracts(number);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_contracts_dates ON legal_contracts(start_date, end_date);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_contracts_created ON legal_contracts(created_at DESC);
|
||
|
||
-- Судебные дела
|
||
CREATE TABLE IF NOT EXISTS legal_court_cases (
|
||
id VARCHAR(50) PRIMARY KEY,
|
||
case_number TEXT NOT NULL UNIQUE,
|
||
type VARCHAR(20) NOT NULL CHECK (type IN ('arbitration', 'civil', 'debt_recovery')),
|
||
role VARCHAR(20) NOT NULL CHECK (role IN ('plaintiff', 'defendant')),
|
||
subject TEXT NOT NULL,
|
||
debtor_name TEXT,
|
||
address TEXT,
|
||
amount NUMERIC(15, 2) NOT NULL,
|
||
recovered_amount NUMERIC(15, 2) DEFAULT 0,
|
||
amount_at_bailiffs NUMERIC(15, 2) DEFAULT 0,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'pre_trial' CHECK (status IN ('pre_trial', 'litigation', 'decision_received', 'enforcement', 'closed')),
|
||
fssp_status TEXT,
|
||
bailiff_name TEXT,
|
||
fssp_last_action_date DATE,
|
||
next_hearing_date DATE,
|
||
judge TEXT,
|
||
court_name TEXT,
|
||
case_file_url TEXT, -- Ссылка на материалы дела
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_status ON legal_court_cases(status);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_type ON legal_court_cases(type);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_role ON legal_court_cases(role);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_number ON legal_court_cases(case_number);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_hearing ON legal_court_cases(next_hearing_date);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_court_cases_created ON legal_court_cases(created_at DESC);
|
||
|
||
-- Доверенности (оставляем для истории, но можно использовать для других целей)
|
||
CREATE TABLE IF NOT EXISTS legal_powers_of_attorney (
|
||
id VARCHAR(50) PRIMARY KEY,
|
||
number TEXT NOT NULL UNIQUE,
|
||
issued_to TEXT NOT NULL, -- Кому выдана
|
||
issue_date DATE NOT NULL,
|
||
expiry_date DATE NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'expired', 'revoked')),
|
||
authority TEXT NOT NULL, -- Полномочия
|
||
document_file_url TEXT, -- Ссылка на файл доверенности
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_legal_poa_status ON legal_powers_of_attorney(status);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_poa_expiry ON legal_powers_of_attorney(expiry_date);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_poa_number ON legal_powers_of_attorney(number);
|
||
|
||
-- История изменений договоров
|
||
CREATE TABLE IF NOT EXISTS legal_contract_history (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
contract_id VARCHAR(50) NOT NULL REFERENCES legal_contracts(id) ON DELETE CASCADE,
|
||
from_status VARCHAR(20),
|
||
to_status VARCHAR(20) NOT NULL,
|
||
changed_by TEXT NOT NULL,
|
||
change_reason TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_contract_history_contract ON legal_contract_history(contract_id);
|
||
CREATE INDEX IF NOT EXISTS idx_contract_history_date ON legal_contract_history(created_at DESC);
|
||
|
||
-- История изменений судебных дел
|
||
CREATE TABLE IF NOT EXISTS legal_court_case_history (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
case_id VARCHAR(50) NOT NULL REFERENCES legal_court_cases(id) ON DELETE CASCADE,
|
||
from_status VARCHAR(20),
|
||
to_status VARCHAR(20) NOT NULL,
|
||
changed_by TEXT NOT NULL,
|
||
change_reason TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_court_case_history_case ON legal_court_case_history(case_id);
|
||
CREATE INDEX IF NOT EXISTS idx_court_case_history_date ON legal_court_case_history(created_at DESC);
|
||
|
||
-- Комментарии к договорам
|
||
CREATE TABLE IF NOT EXISTS legal_contract_comments (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
contract_id VARCHAR(50) NOT NULL REFERENCES legal_contracts(id) ON DELETE CASCADE,
|
||
author TEXT NOT NULL,
|
||
comment TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_contract_comments_contract ON legal_contract_comments(contract_id);
|
||
CREATE INDEX IF NOT EXISTS idx_contract_comments_date ON legal_contract_comments(created_at DESC);
|
||
|
||
-- Комментарии к судебным делам
|
||
CREATE TABLE IF NOT EXISTS legal_court_case_comments (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
case_id VARCHAR(50) NOT NULL REFERENCES legal_court_cases(id) ON DELETE CASCADE,
|
||
author TEXT NOT NULL,
|
||
comment TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_court_case_comments_case ON legal_court_case_comments(case_id);
|
||
CREATE INDEX IF NOT EXISTS idx_court_case_comments_date ON legal_court_case_comments(created_at DESC);
|
||
|
||
-- Комментарии для документации
|
||
COMMENT ON TABLE legal_contracts IS 'Договоры юридического отдела';
|
||
COMMENT ON TABLE legal_court_cases IS 'Судебные дела';
|
||
COMMENT ON TABLE legal_powers_of_attorney IS 'Доверенности';
|
||
COMMENT ON TABLE legal_contract_history IS 'История изменений статусов договоров';
|
||
COMMENT ON TABLE legal_court_case_history IS 'История изменений статусов судебных дел';
|
||
COMMENT ON TABLE legal_contract_comments IS 'Комментарии к договорам';
|
||
COMMENT ON TABLE legal_court_case_comments IS 'Комментарии к судебным делам';
|