91 lines
5.3 KiB
SQL
Executable File
91 lines
5.3 KiB
SQL
Executable File
-- ========= ЮРИДИЧЕСКИЙ ОТДЕЛ: ДОСУДЕБНАЯ РАБОТА =========
|
||
-- Миграция для создания таблиц досудебной работы с должниками
|
||
|
||
-- Должники в юридическом отделе
|
||
CREATE TABLE IF NOT EXISTS legal_debtors (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
building_id VARCHAR(50) REFERENCES buildings(id) ON DELETE SET NULL,
|
||
apartment TEXT NOT NULL,
|
||
debtor_name TEXT,
|
||
phone TEXT,
|
||
email TEXT,
|
||
address TEXT NOT NULL,
|
||
debt_amount NUMERIC(10, 2) NOT NULL,
|
||
debt_months INTEGER NOT NULL,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_progress', 'promised_payment', 'transferred_to_court', 'resolved')),
|
||
transferred_from_finance BOOLEAN DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_legal_debtors_building ON legal_debtors(building_id);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_debtors_status ON legal_debtors(status);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_debtors_apartment ON legal_debtors(apartment);
|
||
CREATE INDEX IF NOT EXISTS idx_legal_debtors_created ON legal_debtors(created_at DESC);
|
||
|
||
-- Досудебная работа
|
||
CREATE TABLE IF NOT EXISTS pre_trial_work (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
debtor_id BIGINT NOT NULL REFERENCES legal_debtors(id) ON DELETE CASCADE,
|
||
assigned_to TEXT, -- Кто ведет дело
|
||
status VARCHAR(20) NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_progress', 'promised_payment', 'transferred_to_court', 'resolved')),
|
||
promised_payment_date DATE,
|
||
promised_payment_amount NUMERIC(10, 2),
|
||
transferred_to_court BOOLEAN DEFAULT FALSE,
|
||
court_case_id VARCHAR(50), -- Связь с судебным делом
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_work_debtor ON pre_trial_work(debtor_id);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_work_status ON pre_trial_work(status);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_work_assigned ON pre_trial_work(assigned_to);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_work_court_case ON pre_trial_work(court_case_id);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_work_promised_date ON pre_trial_work(promised_payment_date);
|
||
|
||
-- Действия (звонки, письма, визиты)
|
||
CREATE TABLE IF NOT EXISTS pre_trial_actions (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
work_id BIGINT NOT NULL REFERENCES pre_trial_work(id) ON DELETE CASCADE,
|
||
action_type VARCHAR(20) NOT NULL CHECK (action_type IN ('call', 'letter', 'visit')),
|
||
action_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
performed_by TEXT NOT NULL,
|
||
result TEXT, -- Результат действия
|
||
notes TEXT,
|
||
attachments TEXT[], -- Файлы (например, фото визита)
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_actions_work ON pre_trial_actions(work_id);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_actions_type ON pre_trial_actions(action_type);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_actions_date ON pre_trial_actions(action_date DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_pre_trial_actions_performed ON pre_trial_actions(performed_by);
|
||
|
||
-- Обещанные оплаты
|
||
CREATE TABLE IF NOT EXISTS promised_payments (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
work_id BIGINT NOT NULL REFERENCES pre_trial_work(id) ON DELETE CASCADE,
|
||
promised_date DATE NOT NULL,
|
||
promised_amount NUMERIC(10, 2) NOT NULL,
|
||
actual_payment_date DATE,
|
||
actual_payment_amount NUMERIC(10, 2),
|
||
is_paid BOOLEAN DEFAULT FALSE,
|
||
reminder_sent BOOLEAN DEFAULT FALSE,
|
||
reminder_date DATE,
|
||
notes TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_promised_payments_work ON promised_payments(work_id);
|
||
CREATE INDEX IF NOT EXISTS idx_promised_payments_date ON promised_payments(promised_date);
|
||
CREATE INDEX IF NOT EXISTS idx_promised_payments_paid ON promised_payments(is_paid);
|
||
CREATE INDEX IF NOT EXISTS idx_promised_payments_reminder ON promised_payments(reminder_sent, reminder_date);
|
||
|
||
-- Комментарий для документации
|
||
COMMENT ON TABLE legal_debtors IS 'Должники, переданные из финансового отдела в юридический отдел для досудебной работы';
|
||
COMMENT ON TABLE pre_trial_work IS 'Досудебная работа по взысканию задолженности с должников';
|
||
COMMENT ON TABLE pre_trial_actions IS 'Действия, выполненные в рамках досудебной работы (звонки, письма, визиты)';
|
||
COMMENT ON TABLE promised_payments IS 'Обещанные должниками оплаты с отслеживанием выполнения';
|