70 lines
3.2 KiB
SQL
70 lines
3.2 KiB
SQL
-- Init script per il container PostgreSQL del booking-service
|
|
-- Gira automaticamente al primo avvio
|
|
|
|
CREATE TABLE IF NOT EXISTS services (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
duration_min INT NOT NULL DEFAULT 15,
|
|
description TEXT,
|
|
category TEXT DEFAULT 'generale',
|
|
active BOOLEAN DEFAULT true,
|
|
sort_order INT DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS providers (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
email TEXT,
|
|
phone TEXT,
|
|
google_calendar_id TEXT,
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS provider_services (
|
|
id SERIAL PRIMARY KEY,
|
|
provider_id INT REFERENCES providers(id) ON DELETE CASCADE,
|
|
service_id INT REFERENCES services(id) ON DELETE CASCADE,
|
|
availability_rules JSONB NOT NULL DEFAULT '[]',
|
|
UNIQUE(provider_id, service_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS bookings (
|
|
id SERIAL PRIMARY KEY,
|
|
service_id INT REFERENCES services(id),
|
|
provider_id INT REFERENCES providers(id),
|
|
customer_name TEXT NOT NULL,
|
|
customer_phone TEXT NOT NULL,
|
|
customer_email TEXT,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ NOT NULL,
|
|
status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed','cancelled','completed','no_show')),
|
|
google_event_id TEXT,
|
|
notes TEXT,
|
|
reminder_sent BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_bookings_date ON bookings(start_at);
|
|
CREATE INDEX IF NOT EXISTS idx_bookings_phone ON bookings(customer_phone);
|
|
CREATE INDEX IF NOT EXISTS idx_bookings_status ON bookings(status);
|
|
CREATE INDEX IF NOT EXISTS idx_bookings_provider ON bookings(provider_id, start_at);
|
|
|
|
-- Dati di esempio (da sostituire con i servizi reali dopo la riunione)
|
|
INSERT INTO providers (name, email, phone) VALUES
|
|
('Farmacia Ianni', 'e-shop@farmaciaianni.it', '+39 393 0579002');
|
|
|
|
INSERT INTO services (name, slug, duration_min, description, category, sort_order) VALUES
|
|
('Misurazione pressione', 'pressione', 10, 'Misurazione della pressione arteriosa', 'diagnostica', 1),
|
|
('Test glicemia', 'glicemia', 10, 'Test rapido della glicemia', 'diagnostica', 2),
|
|
('Holter pressorio 24h', 'holter', 30, 'Installazione e ritiro holter pressorio', 'diagnostica', 3),
|
|
('Consulenza dermocosmesi', 'dermocosmesi', 20, 'Consulenza personalizzata prodotti dermocosmesi', 'consulenza', 4),
|
|
('Preparazione galenica', 'galenica', 15, 'Preparazione magistrale su richiesta medica', 'galenica', 5);
|
|
|
|
-- Assegna tutti i servizi al provider di default con orari standard farmacia
|
|
INSERT INTO provider_services (provider_id, service_id, availability_rules)
|
|
SELECT 1, id, '[{"weekday":0,"start":"09:00","end":"13:00"},{"weekday":0,"start":"15:30","end":"19:30"},{"weekday":1,"start":"09:00","end":"13:00"},{"weekday":1,"start":"15:30","end":"19:30"},{"weekday":2,"start":"09:00","end":"13:00"},{"weekday":2,"start":"15:30","end":"19:30"},{"weekday":3,"start":"09:00","end":"13:00"},{"weekday":3,"start":"15:30","end":"19:30"},{"weekday":4,"start":"09:00","end":"13:00"},{"weekday":4,"start":"15:30","end":"19:30"},{"weekday":5,"start":"09:00","end":"13:00"}]'::jsonb
|
|
FROM services;
|