-- 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;