91 lines
5.3 KiB
MySQL
91 lines
5.3 KiB
MySQL
|
-- ========= ЮРИДИЧЕСКИЙ ОТДЕЛ: ДОСУДЕБНАЯ РАБОТА =========
|
|||
|
|
-- Миграция для создания таблиц досудебной работы с должниками
|
|||
|
|
|
|||
|
|
-- Должники в юридическом отделе
|
|||
|
|
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 'Обещанные должниками оплаты с отслеживанием выполнения';
|