141 lines
7.6 KiB
MySQL
141 lines
7.6 KiB
MySQL
|
-- ========= ЮРИДИЧЕСКИЙ ОТДЕЛ: ПОЛНАЯ МИГРАЦИЯ =========
|
|||
|
|
-- Миграция для создания всех таблиц юридического отдела
|
|||
|
|
|
|||
|
|
-- Договоры
|
|||
|
|
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 'Комментарии к судебным делам';
|