init.sql

-- =============================================================================
-- Garagena - Complete PostgreSQL Database Initialization Script
-- Compatible with: PostgreSQL 17 + PostGIS 3.5
-- =============================================================================

-- ─── Extensions ──────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- =============================================================================
-- TABLE CREATION (ordered by foreign key dependencies)
-- =============================================================================

-- ─── 1. Users ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    phone         VARCHAR(30),
    role          VARCHAR(20) NOT NULL CHECK (role IN ('client', 'garage', 'admin')) DEFAULT 'client',
    avatar_url    TEXT,
    is_verified   BOOLEAN DEFAULT false,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 2. Garages ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS garages (
    id               SERIAL PRIMARY KEY,
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name             VARCHAR(255) NOT NULL,
    description      TEXT,
    address          TEXT NOT NULL,
    city             VARCHAR(100) NOT NULL,
    location         GEOMETRY(Point, 4326),                          -- PostGIS GPS point
    phone            VARCHAR(30),
    whatsapp         VARCHAR(30),
    email            VARCHAR(255),
    website          VARCHAR(255),
    category         VARCHAR(50) CHECK (category IN ('SERVICE', 'RETAIL', 'BOTH')) DEFAULT 'SERVICE',
    specialties      TEXT[]       DEFAULT '{}',
    working_hours    JSONB,
    rating           DECIMAL(2, 1) DEFAULT 0.0 CHECK (rating >= 0 AND rating <= 5),
    total_reviews    INTEGER DEFAULT 0,
    cover_image_url  TEXT,
    photos           TEXT[]        DEFAULT '{}',
    is_verified      BOOLEAN DEFAULT false,
    is_active        BOOLEAN DEFAULT true,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 3. Vehicles (client cars) ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS vehicles (
    id            SERIAL PRIMARY KEY,
    user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    make          VARCHAR(100) NOT NULL,
    model         VARCHAR(100) NOT NULL,
    year          INTEGER NOT NULL CHECK (year >= 1900 AND year <= 2100),
    license_plate VARCHAR(30) UNIQUE,
    vin           VARCHAR(17),
    color         VARCHAR(50),
    mileage       INTEGER DEFAULT 0,
    fuel_type     VARCHAR(30) CHECK (fuel_type IN ('gasoline', 'diesel', 'electric', 'hybrid', 'lpg')),
    notes         TEXT,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 4. Appointments ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS appointments (
    id           SERIAL PRIMARY KEY,
    user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    garage_id    INTEGER NOT NULL REFERENCES garages(id) ON DELETE CASCADE,
    vehicle_id   INTEGER REFERENCES vehicles(id) ON DELETE SET NULL,
    scheduled_at TIMESTAMP NOT NULL,
    service_type VARCHAR(100) NOT NULL,
    status       VARCHAR(30) NOT NULL CHECK (status IN ('pending', 'confirmed', 'in_progress', 'completed', 'cancelled')) DEFAULT 'pending',
    notes        TEXT,
    price_quote  DECIMAL(10, 2),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 5. Reviews / Posts ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS posts (
    id         SERIAL PRIMARY KEY,
    user_id    UUID REFERENCES users(id) ON DELETE CASCADE,
    garage_id  INTEGER REFERENCES garages(id) ON DELETE CASCADE,
    content    TEXT,
    rating     INTEGER CHECK (rating >= 1 AND rating <= 5),
    photos     TEXT[]  DEFAULT '{}',
    type       VARCHAR(50) DEFAULT 'REVIEW' CHECK (type IN ('REVIEW', 'ANNOUNCE', 'GENERAL')),
    likes      INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 6. Marketplace Items ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS marketplace_items (
    id             SERIAL PRIMARY KEY,
    user_id        UUID REFERENCES users(id) ON DELETE CASCADE,
    garage_id      INTEGER REFERENCES garages(id) ON DELETE SET NULL,
    title          VARCHAR(255) NOT NULL,
    description    TEXT,
    price          DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    category       VARCHAR(100),
    condition      VARCHAR(30) CHECK (condition IN ('new', 'used', 'refurbished')),
    photos         TEXT[]  DEFAULT '{}',
    city           VARCHAR(100),
    stock_quantity INTEGER DEFAULT 1,
    is_available   BOOLEAN DEFAULT true,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 7. Favorites ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS favorites (
    id         SERIAL PRIMARY KEY,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    garage_id  INTEGER NOT NULL REFERENCES garages(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, garage_id)
);

-- ─── 8. Interactions (Analytics) ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS interactions (
    id         SERIAL PRIMARY KEY,
    user_id    UUID REFERENCES users(id) ON DELETE SET NULL,
    garage_id  INTEGER NOT NULL REFERENCES garages(id) ON DELETE CASCADE,
    type       VARCHAR(50) NOT NULL CHECK (type IN ('whatsapp_click', 'profile_view', 'call_click', 'directions_click')),
    metadata   JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 9. Garage Certifications ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS garage_certifications (
    id         SERIAL PRIMARY KEY,
    garage_id  INTEGER NOT NULL REFERENCES garages(id) ON DELETE CASCADE,
    name       VARCHAR(255) NOT NULL,
    issuer     VARCHAR(255),
    year       INTEGER,
    image_url  TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 10. Messages ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
    id          SERIAL PRIMARY KEY,
    sender_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    receiver_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content     TEXT NOT NULL,
    is_read     BOOLEAN DEFAULT false,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 11. Notifications ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
    id         SERIAL PRIMARY KEY,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title      VARCHAR(255) NOT NULL,
    body       TEXT,
    type       VARCHAR(50) DEFAULT 'general',
    is_read    BOOLEAN DEFAULT false,
    data       JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- INDEXES
-- =============================================================================
CREATE INDEX IF NOT EXISTS idx_garages_city          ON garages(city);
CREATE INDEX IF NOT EXISTS idx_garages_category      ON garages(category);
CREATE INDEX IF NOT EXISTS idx_garages_rating        ON garages(rating DESC);
CREATE INDEX IF NOT EXISTS idx_garages_location      ON garages USING GIST(location);
CREATE INDEX IF NOT EXISTS idx_garages_user_id       ON garages(user_id);
CREATE INDEX IF NOT EXISTS idx_vehicles_user_id      ON vehicles(user_id);
CREATE INDEX IF NOT EXISTS idx_appointments_user     ON appointments(user_id);
CREATE INDEX IF NOT EXISTS idx_appointments_garage   ON appointments(garage_id);
CREATE INDEX IF NOT EXISTS idx_posts_garage          ON posts(garage_id);
CREATE INDEX IF NOT EXISTS idx_posts_user            ON posts(user_id);
CREATE INDEX IF NOT EXISTS idx_marketplace_available ON marketplace_items(is_available);
CREATE INDEX IF NOT EXISTS idx_marketplace_city      ON marketplace_items(city);
CREATE INDEX IF NOT EXISTS idx_favorites_user        ON favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_interactions_garage   ON interactions(garage_id);
CREATE INDEX IF NOT EXISTS idx_messages_sender       ON messages(sender_id);
CREATE INDEX IF NOT EXISTS idx_messages_receiver     ON messages(receiver_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user    ON notifications(user_id);

-- =============================================================================
-- AUTO-UPDATE TIMESTAMP TRIGGER
-- =============================================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

DO $$ DECLARE
    t TEXT;
BEGIN
    FOREACH t IN ARRAY ARRAY['users','garages','vehicles','appointments','posts','marketplace_items'] LOOP
        IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_updated_at_' || t) THEN
            EXECUTE format(
                'CREATE TRIGGER trg_updated_at_%I BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()',
                t, t
            );
        END IF;
    END LOOP;
END $$;


-- =============================================================================
-- SEED DATA
-- Password for ALL test users below: `Test1234!`
-- Hash generated with bcrypt (10 rounds)
-- =============================================================================

-- ─── Seed: Users ──────────────────────────────────────────────────────────────
INSERT INTO users (id, email, password_hash, name, phone, role, is_verified) VALUES
    -- Clients
    ('a1b2c3d4-0001-0001-0001-000000000001', 'youssef@gmail.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Youssef El Amrani',   '+212600000001', 'client',  true),
    ('a1b2c3d4-0002-0002-0002-000000000002', 'fatima@gmail.com',   '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Fatima Zahra Benali', '+212600000002', 'client',  true),
    ('a1b2c3d4-0003-0003-0003-000000000003', 'omar@gmail.com',     '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Omar Idrissi',        '+212600000003', 'client',  true),
    -- Garage owners
    ('a1b2c3d4-0004-0004-0004-000000000004', 'garage1@gmail.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Hassan Garage Pro',   '+212661000001', 'garage',  true),
    ('a1b2c3d4-0005-0005-0005-000000000005', 'garage2@gmail.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Karim Auto Expert',   '+212661000002', 'garage',  true),
    ('a1b2c3d4-0006-0006-0006-000000000006', 'garage3@gmail.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Rachid Meca Plus',    '+212661000003', 'garage',  true),
    ('a1b2c3d4-0007-0007-0007-000000000007', 'garage4@gmail.com',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Said Casa Motors',    '+212661000004', 'garage',  true),
    ('a1b2c3d4-0008-0008-0008-000000000008', 'admin@garagena.ma',  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin Garagena',      '+212600000000', 'admin',   true)
ON CONFLICT (email) DO NOTHING;

-- ─── Seed: Garages ────────────────────────────────────────────────────────────
INSERT INTO garages (user_id, name, description, address, city, location, phone, whatsapp, category, specialties, working_hours, rating, total_reviews, is_verified) VALUES

    -- Rabat Garages
    (
        'a1b2c3d4-0004-0004-0004-000000000004',
        'Hassan Garage Pro',
        'Garage professionnel specialise en mecanique generale et diagnostic electronique. Plus de 15 ans d experience.',
        'Rue Ibn Khaldoun 12, Agdal',
        'Rabat',
        ST_SetSRID(ST_MakePoint(-6.8498, 34.0108), 4326),
        '+212661000001', '+212661000001',
        'SERVICE',
        ARRAY['Mecanique generale', 'Diagnostic electronique', 'Vidange', 'Freins'],
        '{"lundi":"08:00-18:00","mardi":"08:00-18:00","mercredi":"08:00-18:00","jeudi":"08:00-18:00","vendredi":"08:00-17:00","samedi":"09:00-13:00","dimanche":"Ferme"}',
        4.5, 23, true
    ),
    (
        'a1b2c3d4-0005-0005-0005-000000000005',
        'Karim Auto Expert',
        'Centre de revision et de reparation auto. Specialiste climatisation et transmission automatique.',
        'Avenue Mohammed V 45, Hassan',
        'Rabat',
        ST_SetSRID(ST_MakePoint(-6.8345, 34.0183), 4326),
        '+212661000002', '+212661000002',
        'SERVICE',
        ARRAY['Climatisation', 'Transmission automatique', 'Pneus', 'Alignement'],
        '{"lundi":"07:30-19:00","mardi":"07:30-19:00","mercredi":"07:30-19:00","jeudi":"07:30-19:00","vendredi":"07:30-18:00","samedi":"08:00-14:00","dimanche":"Ferme"}',
        4.2, 18, true
    ),
    (
        'a1b2c3d4-0006-0006-0006-000000000006',
        'Rachid Meca Plus - Pieces Auto',
        'Vente de pieces detachees neuves et occasions. Livraison disponible dans tout Rabat.',
        'Zone Industrielle, Takaddoum',
        'Rabat',
        ST_SetSRID(ST_MakePoint(-6.8220, 34.0250), 4326),
        '+212661000003', '+212661000003',
        'RETAIL',
        ARRAY['Pieces neuves', 'Pieces occasions', 'Filtres', 'Huiles moteur', 'Batterie'],
        '{"lundi":"08:00-19:00","mardi":"08:00-19:00","mercredi":"08:00-19:00","jeudi":"08:00-19:00","vendredi":"08:00-19:00","samedi":"08:00-19:00","dimanche":"09:00-13:00"}',
        4.7, 31, true
    ),

    -- Casablanca Garages
    (
        'a1b2c3d4-0007-0007-0007-000000000007',
        'Said Casa Motors',
        'Grand garage multi-marques a Casablanca. Mecanique rapide, electricite auto et carrosserie.',
        'Boulevard Zerktouni 101, Maarif',
        'Casablanca',
        ST_SetSRID(ST_MakePoint(-7.6244, 33.5891), 4326),
        '+212661000004', '+212661000004',
        'SERVICE',
        ARRAY['Multi-marques', 'Carrosserie', 'Electricite auto', 'Vidange rapide'],
        '{"lundi":"08:00-20:00","mardi":"08:00-20:00","mercredi":"08:00-20:00","jeudi":"08:00-20:00","vendredi":"08:00-20:00","samedi":"09:00-16:00","dimanche":"Ferme"}',
        4.0, 12, true
    )
ON CONFLICT DO NOTHING;

-- ─── Seed: Garage Certifications ──────────────────────────────────────────────
INSERT INTO garage_certifications (garage_id, name, issuer, year) VALUES
    (1, 'Certification ISO 9001', 'Bureau Veritas Maroc', 2022),
    (1, 'Agree Maroc Assistance', 'Maroc Assistance', 2021),
    (2, 'Centre Agree Renault', 'Renault Maroc', 2023),
    (3, 'Distributeur Officiel Total', 'Total Energies Maroc', 2022)
ON CONFLICT DO NOTHING;

-- ─── Seed: Vehicles ───────────────────────────────────────────────────────────
INSERT INTO vehicles (user_id, make, model, year, license_plate, color, fuel_type, mileage) VALUES
    ('a1b2c3d4-0001-0001-0001-000000000001', 'Dacia',     'Logan',     2019, '12345-A-1', 'Blanc',   'gasoline', 75000),
    ('a1b2c3d4-0001-0001-0001-000000000001', 'Renault',   'Clio',      2021, '67890-A-1', 'Gris',    'diesel',   32000),
    ('a1b2c3d4-0002-0002-0002-000000000002', 'Volkswagen','Polo',      2020, '11111-B-2', 'Bleu',    'gasoline', 48000),
    ('a1b2c3d4-0003-0003-0003-000000000003', 'Toyota',    'Corolla',   2018, '22222-C-3', 'Rouge',   'gasoline', 110000),
    ('a1b2c3d4-0003-0003-0003-000000000003', 'Hyundai',   'Tucson',    2022, '33333-C-3', 'Noir',    'diesel',   15000)
ON CONFLICT (license_plate) DO NOTHING;

-- ─── Seed: Appointments ───────────────────────────────────────────────────────
INSERT INTO appointments (user_id, garage_id, vehicle_id, scheduled_at, service_type, status, notes, price_quote) VALUES
    -- Youssef - Logan - chez Hassan Garage Pro
    ('a1b2c3d4-0001-0001-0001-000000000001', 1, 1, '2026-02-25 09:00:00', 'Vidange + Filtres',        'confirmed',   'Vidange 5W40, filtre huile et air',     350.00),
    -- Youssef - Clio - chez Karim Auto Expert
    ('a1b2c3d4-0001-0001-0001-000000000001', 2, 2, '2026-02-27 10:30:00', 'Revision climatisation',   'pending',     'Recharge gaz + nettoyage evaporateur',  500.00),
    -- Fatima - Polo - chez Hassan Garage Pro
    ('a1b2c3d4-0002-0002-0002-000000000002', 1, 3, '2026-02-22 14:00:00', 'Freins avant',             'completed',   'Remplacement plaquettes avant',         420.00),
    -- Omar - Corolla - chez Said Casa Motors
    ('a1b2c3d4-0003-0003-0003-000000000003', 4, 4, '2026-03-01 08:00:00', 'Diagnostic electronique',  'confirmed',   'Voyant moteur allume',                  200.00),
    -- Omar - Tucson - chez Karim Auto Expert
    ('a1b2c3d4-0003-0003-0003-000000000003', 2, 5, '2026-03-05 11:00:00', 'Revision 15000 km',        'pending',     NULL,                                    NULL)
ON CONFLICT DO NOTHING;

-- ─── Seed: Posts / Reviews ────────────────────────────────────────────────────
INSERT INTO posts (user_id, garage_id, content, rating, type) VALUES
    ('a1b2c3d4-0001-0001-0001-000000000001', 1, 'Excellent service! Hassan est tres professionnel et rapide. Prix raisonnable. Je recommande vivement!', 5, 'REVIEW'),
    ('a1b2c3d4-0002-0002-0002-000000000002', 1, 'Bon garage, personnel accueillant. Delai respecte. Retournerai sans hesiter.', 4, 'REVIEW'),
    ('a1b2c3d4-0003-0003-0003-000000000003', 2, 'Karim connait son metier. Ma voiture tourne parfaitement apres la revision.', 5, 'REVIEW'),
    ('a1b2c3d4-0001-0001-0001-000000000001', 3, 'Grande variete de pieces. Ils ont trouve le filtre que je cherchais depuis 2 semaines!', 5, 'REVIEW'),
    ('a1b2c3d4-0002-0002-0002-000000000002', 4, 'Rapide et competent. Diagnostic en 30 min. Bonne experience globale.', 4, 'REVIEW')
ON CONFLICT DO NOTHING;

-- ─── Seed: Marketplace Items ──────────────────────────────────────────────────
INSERT INTO marketplace_items (user_id, garage_id, title, description, price, category, condition, city, stock_quantity) VALUES
    ('a1b2c3d4-0004-0004-0004-000000000004', 1, 'Huile moteur Total Quartz 5W40 (5L)', 'Huile synthétique 5W40 pour moteurs essence et diesel. Lot de 5 litres.', 180.00, 'Lubrifiants', 'new', 'Rabat', 20),
    ('a1b2c3d4-0004-0004-0004-000000000004', 1, 'Kit plaquettes freins Dacia Logan', 'Plaquettes de frein avant compatibles Dacia Logan 2012-2022. Marque TRW.', 220.00, 'Freinage', 'new', 'Rabat', 8),
    ('a1b2c3d4-0005-0005-0005-000000000005', 2, 'Batterie 60Ah Yuasa', 'Batterie de démarrage 12V 60Ah garantie 2 ans. Convient Renault, Dacia, Peugeot.', 650.00, 'Electrique', 'new', 'Rabat', 5),
    ('a1b2c3d4-0006-0006-0006-000000000006', 3, 'Jeu de 4 pneus Michelin 185/65R15', '4 pneus Michelin Energy Saver A+ neufs. Taille 185/65R15. Prix pour les 4.', 1800.00, 'Pneus', 'new', 'Rabat', 3),
    ('a1b2c3d4-0007-0007-0007-000000000007', 4, 'Alternateur Volkswagen Polo reconditionne', 'Alternateur reconditionne pour VW Polo 2010-2020, garanti 6 mois.', 480.00, 'Moteur', 'refurbished', 'Casablanca', 2),
    ('a1b2c3d4-0003-0003-0003-000000000003', NULL, 'Coffre de voiture Thule 320L', 'Coffre de toit Thule excellent etat, utilise 2 fois, avec barres de toit universelles.', 1200.00, 'Accessoires', 'used', 'Rabat', 1)
ON CONFLICT DO NOTHING;

-- ─── Seed: Favorites ──────────────────────────────────────────────────────────
INSERT INTO favorites (user_id, garage_id) VALUES
    ('a1b2c3d4-0001-0001-0001-000000000001', 1),
    ('a1b2c3d4-0001-0001-0001-000000000001', 3),
    ('a1b2c3d4-0002-0002-0002-000000000002', 1),
    ('a1b2c3d4-0003-0003-0003-000000000003', 2),
    ('a1b2c3d4-0003-0003-0003-000000000003', 4)
ON CONFLICT (user_id, garage_id) DO NOTHING;

-- ─── Seed: Interactions ───────────────────────────────────────────────────────
INSERT INTO interactions (user_id, garage_id, type, metadata) VALUES
    ('a1b2c3d4-0001-0001-0001-000000000001', 1, 'profile_view',    '{"source":"discovery"}'),
    ('a1b2c3d4-0001-0001-0001-000000000001', 1, 'whatsapp_click',  '{"phone":"+212661000001"}'),
    ('a1b2c3d4-0002-0002-0002-000000000002', 1, 'profile_view',    '{"source":"search"}'),
    ('a1b2c3d4-0003-0003-0003-000000000003', 2, 'call_click',      '{"phone":"+212661000002"}'),
    ('a1b2c3d4-0003-0003-0003-000000000003', 4, 'directions_click','{"destination":"Casablanca"}')
ON CONFLICT DO NOTHING;

-- ─── Seed: Notifications ──────────────────────────────────────────────────────
INSERT INTO notifications (user_id, title, body, type) VALUES
    ('a1b2c3d4-0001-0001-0001-000000000001', 'Rendez-vous confirme', 'Votre RDV chez Hassan Garage Pro le 25 Feb a 09h est confirme.', 'appointment'),
    ('a1b2c3d4-0002-0002-0002-000000000002', 'Entretien termine', 'Votre Volkswagen Polo est prete a etre recuperee.', 'appointment'),
    ('a1b2c3d4-0004-0004-0004-000000000004', 'Nouveau avis client', 'Youssef vous a laisse un avis 5 etoiles. Bravo!', 'review')
ON CONFLICT DO NOTHING;

-- =============================================================================
-- Update garage ratings from seeded reviews
-- =============================================================================
UPDATE garages g
SET
    rating = sub.avg_rating,
    total_reviews = sub.cnt
FROM (
    SELECT garage_id, ROUND(AVG(rating)::NUMERIC, 1) as avg_rating, COUNT(*) as cnt
    FROM posts
    WHERE rating IS NOT NULL
    GROUP BY garage_id
) sub
WHERE g.id = sub.garage_id;

-- =============================================================================
-- Done! Summary:
-- 8  users   (3 clients, 4 garage owners, 1 admin)  password: "secret"
-- 4  garages (3 in Rabat, 1 in Casablanca)
-- 5  vehicles
-- 5  appointments
-- 5  reviews/posts
-- 6  marketplace items
-- 5  favorites
-- 5  interactions
-- 3  notifications
-- =============================================================================