migrations/001_init.up.sql - develop - novd7/max_bot_tickets

migrations/001_init.up.sql
66 строк2 KB
HTechn
Возможности администратора + багфиксы
27 май 2026, 22:10
27 май 2026, 22:10db9aee2
CREATE TABLE IF NOT EXISTS users (
    chat_id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL,
    verified_email VARCHAR(255),
    full_name VARCHAR(255),
    consent_given_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	is_banned BOOLEAN DEFAULT false
);

CREATE TABLE IF NOT EXISTS teachers (
    id SERIAL PRIMARY KEY,
    chat_id BIGINT UNIQUE NOT NULL REFERENCES users(chat_id) ON DELETE CASCADE,
    full_name TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_teachers_chat_id ON teachers(chat_id);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE TABLE IF NOT EXISTS tickets (
    id SERIAL PRIMARY KEY,
    number VARCHAR(255) UNIQUE NOT NULL,
    student_chat_id BIGINT REFERENCES users(chat_id) ON DELETE CASCADE,
    teacher_chat_id BIGINT REFERENCES users(chat_id) ON DELETE SET NULL,
    category VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(50) NOT NULL,
    outcome VARCHAR(255),
    rating VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tickets_number ON tickets(number);
CREATE INDEX IF NOT EXISTS idx_tickets_student ON tickets(student_chat_id);
CREATE INDEX IF NOT EXISTS idx_tickets_teacher ON tickets(teacher_chat_id);
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);
CREATE INDEX IF NOT EXISTS idx_tickets_created ON tickets(created_at);

CREATE TABLE IF NOT EXISTS audit_log (
    id SERIAL PRIMARY KEY,
    ticket_id INT REFERENCES tickets(id) ON DELETE CASCADE,
    action VARCHAR(255) NOT NULL,
    from_role VARCHAR(50) NOT NULL,
    from_chat_id BIGINT REFERENCES users(chat_id) ON DELETE CASCADE,
    comment TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_audit_ticket ON audit_log(ticket_id);
CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_log(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action);

CREATE TABLE IF NOT EXISTS attachments (
    id SERIAL PRIMARY KEY,
    ticket_id INT REFERENCES tickets(id) ON DELETE CASCADE,
    file_url TEXT NOT NULL,
    file_type VARCHAR(100),
    uploaded_by BIGINT REFERENCES users(chat_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_attachments_ticket ON attachments(ticket_id);