-- Bookings / scheduling calendar
USE BarberShop;

CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    service_id INT NULL,
    staff_id INT NULL,
    customer_name VARCHAR(150) NOT NULL,
    customer_phone VARCHAR(50) NULL,
    customer_email VARCHAR(150) NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    status VARCHAR(30) DEFAULT 'pending',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL,
    FOREIGN KEY (staff_id) REFERENCES staff_barbers(id) ON DELETE SET NULL,
    INDEX idx_bookings_tenant_starts (tenant_id, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample bookings for demo calendar (relative to "today")
INSERT INTO bookings (tenant_id, service_id, staff_id, customer_name, customer_phone, starts_at, ends_at, status, notes)
SELECT 1, 2, 1, 'Alex Rivera', '555-0101',
       TIMESTAMP(CURDATE(), '10:00:00'), TIMESTAMP(CURDATE(), '10:50:00'),
       'confirmed', 'Walk-in converted'
WHERE NOT EXISTS (
  SELECT 1 FROM bookings WHERE tenant_id = 1 AND customer_name = 'Alex Rivera' AND DATE(starts_at) = CURDATE()
);

INSERT INTO bookings (tenant_id, service_id, staff_id, customer_name, customer_phone, starts_at, ends_at, status, notes)
SELECT 1, 1, 2, 'Sam Patel', '555-0102',
       TIMESTAMP(CURDATE(), '13:30:00'), TIMESTAMP(CURDATE(), '14:15:00'),
       'confirmed', NULL
WHERE NOT EXISTS (
  SELECT 1 FROM bookings WHERE tenant_id = 1 AND customer_name = 'Sam Patel' AND DATE(starts_at) = CURDATE()
);

INSERT INTO bookings (tenant_id, service_id, staff_id, customer_name, customer_phone, starts_at, ends_at, status, notes)
SELECT 1, 5, 1, 'Chris Nguyen', '555-0103',
       TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '11:00:00'),
       TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '12:10:00'),
       'pending', 'First visit'
WHERE NOT EXISTS (
  SELECT 1 FROM bookings WHERE tenant_id = 1 AND customer_name = 'Chris Nguyen'
);

INSERT INTO bookings (tenant_id, service_id, staff_id, customer_name, customer_phone, starts_at, ends_at, status, notes)
SELECT 1, 3, 2, 'Jordan Blake', '555-0104',
       TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 2 DAY), '15:00:00'),
       TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 2 DAY), '15:30:00'),
       'confirmed', NULL
WHERE NOT EXISTS (
  SELECT 1 FROM bookings WHERE tenant_id = 1 AND customer_name = 'Jordan Blake'
);
