Peter
101 lines
3.6 KiB
SQL
101 lines
3.6 KiB
SQL
-- Erstelle alle Tabellen zuerst
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
capacity INTEGER NOT NULL DEFAULT 0,
|
|
color TEXT DEFAULT '#3b82f6',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tables (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id INTEGER NOT NULL,
|
|
x INTEGER DEFAULT 0,
|
|
y INTEGER DEFAULT 0,
|
|
width INTEGER DEFAULT 100,
|
|
height INTEGER DEFAULT 100,
|
|
max_guests INTEGER NOT NULL,
|
|
shape TEXT DEFAULT 'rect',
|
|
status TEXT DEFAULT 'active',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS room_bookings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id INTEGER NOT NULL,
|
|
date TEXT NOT NULL,
|
|
time_from TEXT NOT NULL,
|
|
time_to TEXT NOT NULL,
|
|
guests INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
phone TEXT,
|
|
email TEXT,
|
|
event_type TEXT,
|
|
notes TEXT,
|
|
status TEXT DEFAULT 'confirmed',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reservations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
table_id INTEGER NOT NULL,
|
|
date TEXT NOT NULL,
|
|
time_from TEXT NOT NULL,
|
|
time_to TEXT NOT NULL,
|
|
guests INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
phone TEXT,
|
|
email TEXT,
|
|
source TEXT DEFAULT 'manual',
|
|
notes TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_date ON reservations(date);
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_table ON reservations(table_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tables_room ON tables(room_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reservations_time ON reservations(time_from, time_to);
|
|
CREATE INDEX IF NOT EXISTS idx_room_bookings_date ON room_bookings(date);
|
|
CREATE INDEX IF NOT EXISTS idx_room_bookings_room ON room_bookings(room_id);
|
|
CREATE INDEX IF NOT EXISTS idx_room_bookings_time ON room_bookings(time_from, time_to);
|
|
|
|
-- Raeume einfuegen
|
|
INSERT INTO rooms (id, name, capacity, color) VALUES
|
|
(1, 'Hauptraum', 80, '#3b82f6'),
|
|
(2, 'Saal A', 40, '#10b981'),
|
|
(3, 'Saal B', 30, '#f59e0b');
|
|
|
|
-- Tische fuer Hauptraum
|
|
INSERT INTO tables (room_id, x, y, width, height, max_guests, shape, status) VALUES
|
|
(1, 50, 50, 120, 80, 4, 'rect', 'active'),
|
|
(1, 200, 50, 120, 80, 4, 'rect', 'active'),
|
|
(1, 350, 50, 120, 80, 6, 'rect', 'active'),
|
|
(1, 50, 150, 100, 100, 6, 'round', 'active'),
|
|
(1, 200, 150, 120, 80, 4, 'rect', 'active'),
|
|
(1, 350, 150, 120, 80, 4, 'rect', 'active'),
|
|
(1, 50, 300, 150, 150, 8, 'round', 'active'),
|
|
(1, 250, 300, 150, 150, 10, 'round', 'active');
|
|
|
|
-- Tische fuer Saal A
|
|
INSERT INTO tables (room_id, x, y, width, height, max_guests, shape, status) VALUES
|
|
(2, 50, 50, 100, 80, 4, 'rect', 'active'),
|
|
(2, 180, 50, 100, 80, 4, 'rect', 'active'),
|
|
(2, 310, 50, 100, 80, 4, 'rect', 'active'),
|
|
(2, 50, 150, 100, 80, 6, 'rect', 'active'),
|
|
(2, 180, 150, 100, 80, 6, 'rect', 'active');
|
|
|
|
-- Tische fuer Saal B
|
|
INSERT INTO tables (room_id, x, y, width, height, max_guests, shape, status) VALUES
|
|
(3, 50, 50, 100, 80, 4, 'rect', 'active'),
|
|
(3, 180, 50, 100, 80, 4, 'rect', 'active'),
|
|
(3, 50, 150, 120, 80, 6, 'rect', 'active'),
|
|
(3, 200, 150, 80, 80, 4, 'round', 'active'),
|
|
(3, 320, 150, 80, 80, 4, 'round', 'active');
|
|
|
|
-- Test Daten
|
|
INSERT INTO users (username, password, is_admin) VALUES
|
|
('admin', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);
|