-- Initial migration for Zeiterfassung UniCon

CREATE TYPE role AS ENUM ('mitarbeiter', 'admin');
CREATE TYPE absence_type AS ENUM ('urlaub', 'krank', 'sonderurlaub');

CREATE TABLE users (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	first_name VARCHAR(100) NOT NULL,
	last_name VARCHAR(100) NOT NULL,
	email VARCHAR(255) NOT NULL UNIQUE,
	password_hash TEXT,
	role role NOT NULL DEFAULT 'mitarbeiter',
	bundesland VARCHAR(2) NOT NULL DEFAULT 'MV',
	standard_start TIME NOT NULL DEFAULT '08:00',
	standard_end TIME NOT NULL DEFAULT '17:00',
	break_minutes_default INTEGER NOT NULL DEFAULT 30,
	weekly_hours DECIMAL(4,1) NOT NULL DEFAULT 40.0,
	vacation_days_per_year INTEGER NOT NULL DEFAULT 30,
	active BOOLEAN NOT NULL DEFAULT true,
	invite_token TEXT,
	created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE sessions (
	id TEXT PRIMARY KEY,
	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	expires_at TIMESTAMP NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE time_entries (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	date DATE NOT NULL,
	start_time TIME,
	end_time TIME,
	break_minutes INTEGER NOT NULL DEFAULT 30,
	is_auto_filled BOOLEAN NOT NULL DEFAULT false,
	is_corrected BOOLEAN NOT NULL DEFAULT false,
	confirmed_at TIMESTAMP,
	created_at TIMESTAMP NOT NULL DEFAULT now(),
	updated_at TIMESTAMP NOT NULL DEFAULT now(),
	UNIQUE(user_id, date)
);

CREATE TABLE absences (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	date_from DATE NOT NULL,
	date_to DATE NOT NULL,
	type absence_type NOT NULL,
	notes TEXT,
	created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE holidays (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	bundesland VARCHAR(2) NOT NULL,
	date DATE NOT NULL,
	name VARCHAR(200) NOT NULL,
	year INTEGER NOT NULL,
	UNIQUE(bundesland, date)
);

CREATE TABLE vacation_balances (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	year INTEGER NOT NULL,
	total_days INTEGER NOT NULL,
	carried_over_days INTEGER NOT NULL DEFAULT 0,
	UNIQUE(user_id, year)
);

-- Feiertage Mecklenburg-Vorpommern 2025 & 2026
INSERT INTO holidays (bundesland, date, name, year) VALUES
-- 2025
('MV', '2025-01-01', 'Neujahr', 2025),
('MV', '2025-04-18', 'Karfreitag', 2025),
('MV', '2025-04-21', 'Ostermontag', 2025),
('MV', '2025-05-01', 'Tag der Arbeit', 2025),
('MV', '2025-05-29', 'Christi Himmelfahrt', 2025),
('MV', '2025-06-09', 'Pfingstmontag', 2025),
('MV', '2025-10-03', 'Tag der Deutschen Einheit', 2025),
('MV', '2025-10-31', 'Reformationstag', 2025),
('MV', '2025-12-24', 'Heiligabend (halber Tag)', 2025),
('MV', '2025-12-25', '1. Weihnachtstag', 2025),
('MV', '2025-12-26', '2. Weihnachtstag', 2025),
('MV', '2025-12-31', 'Silvester (halber Tag)', 2025),
-- 2026
('MV', '2026-01-01', 'Neujahr', 2026),
('MV', '2026-04-03', 'Karfreitag', 2026),
('MV', '2026-04-06', 'Ostermontag', 2026),
('MV', '2026-05-01', 'Tag der Arbeit', 2026),
('MV', '2026-05-14', 'Christi Himmelfahrt', 2026),
('MV', '2026-05-25', 'Pfingstmontag', 2026),
('MV', '2026-10-03', 'Tag der Deutschen Einheit', 2026),
('MV', '2026-10-31', 'Reformationstag', 2026),
('MV', '2026-12-25', '1. Weihnachtstag', 2026),
('MV', '2026-12-26', '2. Weihnachtstag', 2026);