MarcWieland
114 lines
5.8 KiB
SQL
114 lines
5.8 KiB
SQL
-- =====================================================================
|
|
-- Handwerksfreund - PostgreSQL / Supabase Schema & Initial Data
|
|
-- =====================================================================
|
|
|
|
-- 1. Kunden-Tabelle (customers)
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
customer_number VARCHAR(50) UNIQUE NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
street VARCHAR(255) NOT NULL,
|
|
zip_code VARCHAR(20) NOT NULL,
|
|
city VARCHAR(255) NOT NULL,
|
|
latitude DOUBLE PRECISION NOT NULL,
|
|
longitude DOUBLE PRECISION NOT NULL,
|
|
distance_km DOUBLE PRECISION DEFAULT 0.0,
|
|
phone VARCHAR(50),
|
|
email VARCHAR(255),
|
|
notes TEXT,
|
|
first_contact_date DATE DEFAULT CURRENT_DATE,
|
|
open_orders_count INT DEFAULT 0,
|
|
last_order_date DATE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 2. Verbaute Teile / Gerätekartei (installed_parts)
|
|
CREATE TABLE IF NOT EXISTS installed_parts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
category VARCHAR(255) NOT NULL,
|
|
serial_number VARCHAR(255),
|
|
quantity INT DEFAULT 1,
|
|
installed_at DATE DEFAULT CURRENT_DATE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 3. Aufträge (work_orders)
|
|
CREATE TABLE IF NOT EXISTS work_orders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
scheduled_date DATE DEFAULT CURRENT_DATE,
|
|
status VARCHAR(50) DEFAULT 'offen', -- offen, inBearbeitung, abgeschlossen, storniert
|
|
completed_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 4. Auftrags-Anhänge & Baupläne (order_attachments)
|
|
CREATE TABLE IF NOT EXISTS order_attachments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID REFERENCES work_orders(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
category VARCHAR(50) DEFAULT 'sonstiges', -- bauplan, schaltplan, foto_vor_ort, abnahmeprotokoll, sonstiges
|
|
file_path TEXT NOT NULL,
|
|
file_size_bytes BIGINT,
|
|
mime_type VARCHAR(100),
|
|
uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 5. Termin-Einträge / Zeitleiste (schedule_items)
|
|
CREATE TABLE IF NOT EXISTS schedule_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID REFERENCES work_orders(id) ON DELETE SET NULL,
|
|
customer_id UUID REFERENCES customers(id) ON DELETE SET NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
customer_name VARCHAR(255),
|
|
address VARCHAR(255),
|
|
task_description TEXT,
|
|
start_time VARCHAR(10) NOT NULL, -- z.B. "10:30"
|
|
end_time VARCHAR(10) NOT NULL, -- z.B. "13:00"
|
|
type VARCHAR(50) DEFAULT 'job', -- job, routine, lunchBreak, feierabend
|
|
is_completed BOOLEAN DEFAULT FALSE,
|
|
is_in_progress BOOLEAN DEFAULT FALSE,
|
|
scheduled_date DATE DEFAULT CURRENT_DATE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- RLS (Row Level Security) aktivieren
|
|
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE installed_parts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE order_attachments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE schedule_items ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Öffentliche Lese- & Schreib-Policies für Prototyping
|
|
CREATE POLICY "Public Customers Access" ON customers FOR ALL USING (true) WITH CHECK (true);
|
|
CREATE POLICY "Public Parts Access" ON installed_parts FOR ALL USING (true) WITH CHECK (true);
|
|
CREATE POLICY "Public Orders Access" ON work_orders FOR ALL USING (true) WITH CHECK (true);
|
|
CREATE POLICY "Public Attachments Access" ON order_attachments FOR ALL USING (true) WITH CHECK (true);
|
|
CREATE POLICY "Public Schedule Access" ON schedule_items FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
-- =====================================================================
|
|
-- Seed Data (Initial-Daten aus dem Mockup)
|
|
-- =====================================================================
|
|
|
|
INSERT INTO customers (id, customer_number, name, street, zip_code, city, latitude, longitude, distance_km, phone, email, notes, first_contact_date, open_orders_count, last_order_date)
|
|
VALUES
|
|
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'K-10025', 'Max Mustermann', 'Musterweg 12', '12345', 'Musterstadt', 50.1135, 8.6790, 2.4, '0171 1234567', 'max.mustermann@email.de', 'Schlüsselbox an der Garage', '2022-03-10', 1, '2024-05-15'),
|
|
('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 'K-10026', 'Sabine Schulze', 'Nordring 45', '12347', 'Musterstadt', 50.1265, 8.6890, 4.1, '0160 9876543', 'sabine.schulze@example.com', 'Hund im Garten, bitte vorher anrufen.', '2021-11-04', 0, '2024-04-10'),
|
|
('c0eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 'K-10027', 'Bäckerei Hoffmann GmbH', 'Hauptstraße 88', '12345', 'Musterstadt', 50.1080, 8.6650, 1.8, '069 5551234', 'info@baeckerei-hoffmann.de', 'Zufahrt über den Lieferanteneingang Rückseite.', '2020-01-15', 2, '2024-06-01')
|
|
ON CONFLICT (customer_number) DO NOTHING;
|
|
|
|
INSERT INTO installed_parts (customer_id, name, category, serial_number, quantity)
|
|
VALUES
|
|
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Buderus Logamax plus GB172-24', 'Gas-Brennwertgerät', '8374747383', 1),
|
|
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Bosch EasyControl CT200', 'Raumthermostat', '1234567890', 1),
|
|
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Honeywell Hauswasserfilter FF06', 'Wasserfilter', '9876543210', 1);
|
|
|
|
INSERT INTO work_orders (id, customer_id, title, status, scheduled_date)
|
|
VALUES
|
|
('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Wartung Heizungsanlage', 'abgeschlossen', '2024-05-15'),
|
|
('e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a55', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Thermostat Einstellung & Kalibrierung', 'offen', '2026-08-02');
|