21a844cb8a
A previous `git pull --rebase origin main` dropped 177 local commits,
losing 3400+ files across admin-v2, backend, studio-v2, website,
klausur-service, and many other services. The partial restore attempt
(660295e2) only recovered some files.
This commit restores all missing files from pre-rebase ref 98933f5e
while preserving post-rebase additions (night-scheduler, night-mode UI,
NightModeWidget dashboard integration).
Restored features include:
- AI Module Sidebar (FAB), OCR Labeling, OCR Compare
- GPU Dashboard, RAG Pipeline, Magic Help
- Klausur-Korrektur (8 files), Abitur-Archiv (5+ files)
- Companion, Zeugnisse-Crawler, Screen Flow
- Full backend, studio-v2, website, klausur-service
- All compliance SDKs, agent-core, voice-service
- CI/CD configs, documentation, scripts
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
226 lines
6.5 KiB
Go
226 lines
6.5 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
"log"
|
|
"time"
|
|
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
)
|
|
|
|
// DB wraps the database connection pool
|
|
type DB struct {
|
|
Pool *pgxpool.Pool
|
|
}
|
|
|
|
// Connect creates a new database connection pool
|
|
func Connect(databaseURL string) (*DB, error) {
|
|
config, err := pgxpool.ParseConfig(databaseURL)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Configure connection pool
|
|
config.MaxConns = 25
|
|
config.MinConns = 5
|
|
config.MaxConnLifetime = time.Hour
|
|
config.MaxConnIdleTime = 30 * time.Minute
|
|
config.HealthCheckPeriod = time.Minute
|
|
|
|
pool, err := pgxpool.NewWithConfig(context.Background(), config)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Test connection
|
|
if err := pool.Ping(context.Background()); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
log.Println("Database connection established")
|
|
return &DB{Pool: pool}, nil
|
|
}
|
|
|
|
// Close closes the database connection pool
|
|
func (db *DB) Close() {
|
|
db.Pool.Close()
|
|
}
|
|
|
|
// Migrate runs database migrations
|
|
func Migrate(db *DB) error {
|
|
ctx := context.Background()
|
|
|
|
migrations := []string{
|
|
// School Years
|
|
`CREATE TABLE IF NOT EXISTS school_years (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(20) NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
is_current BOOLEAN DEFAULT false,
|
|
teacher_id UUID NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
)`,
|
|
|
|
// Classes
|
|
`CREATE TABLE IF NOT EXISTS classes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
teacher_id UUID NOT NULL,
|
|
school_year_id UUID REFERENCES school_years(id),
|
|
name VARCHAR(20) NOT NULL,
|
|
grade_level INT NOT NULL,
|
|
school_type VARCHAR(30),
|
|
federal_state VARCHAR(50),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(teacher_id, school_year_id, name)
|
|
)`,
|
|
|
|
// Students
|
|
`CREATE TABLE IF NOT EXISTS students (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
class_id UUID REFERENCES classes(id) ON DELETE CASCADE,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
birth_date DATE,
|
|
student_number VARCHAR(50),
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
)`,
|
|
|
|
// Subjects
|
|
`CREATE TABLE IF NOT EXISTS subjects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
teacher_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
short_name VARCHAR(10),
|
|
is_main_subject BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(teacher_id, name)
|
|
)`,
|
|
|
|
// Exams
|
|
`CREATE TABLE IF NOT EXISTS exams (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
teacher_id UUID NOT NULL,
|
|
class_id UUID REFERENCES classes(id),
|
|
subject_id UUID REFERENCES subjects(id),
|
|
title VARCHAR(255) NOT NULL,
|
|
exam_type VARCHAR(30) NOT NULL,
|
|
topic VARCHAR(255),
|
|
content TEXT,
|
|
source_file_path VARCHAR(500),
|
|
difficulty_level INT DEFAULT 3,
|
|
duration_minutes INT,
|
|
max_points DECIMAL(5,2),
|
|
is_template BOOLEAN DEFAULT false,
|
|
parent_exam_id UUID REFERENCES exams(id),
|
|
status VARCHAR(20) DEFAULT 'draft',
|
|
exam_date DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
)`,
|
|
|
|
// Exam Results
|
|
`CREATE TABLE IF NOT EXISTS exam_results (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
exam_id UUID REFERENCES exams(id) ON DELETE CASCADE,
|
|
student_id UUID REFERENCES students(id) ON DELETE CASCADE,
|
|
points_achieved DECIMAL(5,2),
|
|
grade DECIMAL(2,1),
|
|
percentage DECIMAL(5,2),
|
|
notes TEXT,
|
|
is_absent BOOLEAN DEFAULT false,
|
|
needs_rewrite BOOLEAN DEFAULT false,
|
|
approved_by_teacher BOOLEAN DEFAULT false,
|
|
approved_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(exam_id, student_id)
|
|
)`,
|
|
|
|
// Grade Overview
|
|
`CREATE TABLE IF NOT EXISTS grade_overview (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
student_id UUID REFERENCES students(id) ON DELETE CASCADE,
|
|
subject_id UUID REFERENCES subjects(id),
|
|
school_year_id UUID REFERENCES school_years(id),
|
|
semester INT NOT NULL,
|
|
written_grade_avg DECIMAL(3,2),
|
|
written_grade_count INT DEFAULT 0,
|
|
oral_grade DECIMAL(3,2),
|
|
oral_notes TEXT,
|
|
final_grade DECIMAL(3,2),
|
|
final_grade_locked BOOLEAN DEFAULT false,
|
|
written_weight INT DEFAULT 60,
|
|
oral_weight INT DEFAULT 40,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(student_id, subject_id, school_year_id, semester)
|
|
)`,
|
|
|
|
// Attendance
|
|
`CREATE TABLE IF NOT EXISTS attendance (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
student_id UUID REFERENCES students(id) ON DELETE CASCADE,
|
|
date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL,
|
|
periods INT DEFAULT 1,
|
|
reason TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(student_id, date)
|
|
)`,
|
|
|
|
// Gradebook Entries
|
|
`CREATE TABLE IF NOT EXISTS gradebook_entries (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
class_id UUID REFERENCES classes(id) ON DELETE CASCADE,
|
|
student_id UUID REFERENCES students(id),
|
|
date DATE NOT NULL,
|
|
entry_type VARCHAR(30) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
is_visible_to_parents BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
)`,
|
|
|
|
// Certificates
|
|
`CREATE TABLE IF NOT EXISTS certificates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
student_id UUID REFERENCES students(id) ON DELETE CASCADE,
|
|
school_year_id UUID REFERENCES school_years(id),
|
|
semester INT NOT NULL,
|
|
certificate_type VARCHAR(30),
|
|
template_name VARCHAR(100),
|
|
grades_json JSONB,
|
|
remarks TEXT,
|
|
absence_days INT DEFAULT 0,
|
|
absence_days_unexcused INT DEFAULT 0,
|
|
generated_pdf_path VARCHAR(500),
|
|
status VARCHAR(20) DEFAULT 'draft',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
)`,
|
|
|
|
// Indexes
|
|
`CREATE INDEX IF NOT EXISTS idx_students_class ON students(class_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_exams_teacher ON exams(teacher_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_exams_class ON exams(class_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_exam_results_exam ON exam_results(exam_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_exam_results_student ON exam_results(student_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_grade_overview_student ON grade_overview(student_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_attendance_student ON attendance(student_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance(date)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_gradebook_class ON gradebook_entries(class_id)`,
|
|
}
|
|
|
|
for _, migration := range migrations {
|
|
_, err := db.Pool.Exec(ctx, migration)
|
|
if err != nil {
|
|
log.Printf("Migration error: %v\nSQL: %s", err, migration)
|
|
return err
|
|
}
|
|
}
|
|
|
|
log.Println("Database migrations completed")
|
|
return nil
|
|
}
|