-- BarberShop multi-tenant white-label platform schema
CREATE DATABASE IF NOT EXISTS BarberShop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE BarberShop;

CREATE TABLE IF NOT EXISTS tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    subdomain VARCHAR(100) UNIQUE,
    custom_domain VARCHAR(255) UNIQUE NULL,
    primary_color VARCHAR(7) DEFAULT '#1a1a1a',
    secondary_color VARCHAR(7) DEFAULT '#c9a227',
    font_pairing VARCHAR(50) DEFAULT 'modern_sans',
    license_status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_tag VARCHAR(50) DEFAULT 'general',
    optimized_path VARCHAR(255) NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_media_tenant_tag (tenant_id, file_tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    duration_minutes INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255) NULL,
    display_order INT DEFAULT 0,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_services_tenant_order (tenant_id, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staff_barbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    title VARCHAR(100) NULL,
    bio TEXT,
    specialties VARCHAR(255) NULL,
    profile_image_url VARCHAR(255) NULL,
    working_hours JSON NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staff_services (
    staff_id INT NOT NULL,
    service_id INT NOT NULL,
    PRIMARY KEY (staff_id, service_id),
    FOREIGN KEY (staff_id) REFERENCES staff_barbers(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS style_gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    style_name VARCHAR(150) NOT NULL,
    category_tag VARCHAR(50) NULL,
    image_url VARCHAR(255) NOT NULL,
    linked_service_id INT NULL,
    is_active_stock BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (linked_service_id) REFERENCES services(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_settings (
    tenant_id INT PRIMARY KEY,
    hero_headline VARCHAR(255) NULL,
    hero_subheadline TEXT NULL,
    hero_media_path VARCHAR(255) NULL,
    cta_text VARCHAR(100) DEFAULT 'Book Now',
    cta_link VARCHAR(255) DEFAULT '#book',
    business_name VARCHAR(255) NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    social_links JSON NULL,
    maps_embed TEXT NULL,
    seo_title VARCHAR(255) NULL,
    seo_description TEXT NULL,
    announcement_enabled TINYINT(1) DEFAULT 0,
    announcement_text VARCHAR(500) NULL,
    announcement_bg_color VARCHAR(7) DEFAULT '#1a1a1a',
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_metrics (
    tenant_id INT PRIMARY KEY,
    subscription_type VARCHAR(50) DEFAULT 'starter',
    renewal_date DATE NULL,
    sms_count_month INT DEFAULT 0,
    email_count_month INT DEFAULT 0,
    booking_count_month INT DEFAULT 0,
    notes TEXT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- Seed tenant 1
INSERT INTO tenants (id, name, subdomain, custom_domain, primary_color, secondary_color, font_pairing, license_status)
VALUES (1, 'Company Name Here', 'charlies', NULL, '#1a1a1a', '#c9a227', 'modern_sans', 'active')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO page_settings (
    tenant_id, hero_headline, hero_subheadline, cta_text, cta_link,
    business_name, address, phone, social_links, seo_title, seo_description,
    announcement_enabled, announcement_text, announcement_bg_color
) VALUES (
    1,
    'Title Here',
    'Walk-ins welcome. Appointments preferred. Edit this copy in Admin → Page Content.',
    'Book Now',
    '#book',
    'Company Name Here',
    'Address Here',
    '(555) 000-0000',
    JSON_OBJECT('instagram', '', 'facebook', ''),
    'Company Name Here',
    'Edit this description in Admin → Page Content.',
    0,
    '',
    '#1a1a1a'
) ON DUPLICATE KEY UPDATE business_name = VALUES(business_name);

INSERT INTO license_metrics (tenant_id, subscription_type, renewal_date, sms_count_month, email_count_month, booking_count_month)
VALUES (1, 'pro', DATE_ADD(CURDATE(), INTERVAL 30 DAY), 42, 128, 86)
ON DUPLICATE KEY UPDATE subscription_type = VALUES(subscription_type);

-- Sample services
INSERT INTO services (id, tenant_id, name, description, duration_minutes, price, display_order) VALUES
(1, 1, 'Signature Haircut', 'Consultation, cut, and style finish.', 45, 35.00, 1),
(2, 1, 'Skin Fade', 'Precision fade with clean finish.', 50, 40.00, 2),
(3, 1, 'Beard Trim & Shape', 'Line-up, trim, and hot towel.', 30, 25.00, 3),
(4, 1, 'Kids Cut', 'Ages 12 and under.', 30, 22.00, 4),
(5, 1, 'Cut + Beard Combo', 'Full haircut with beard detailing.', 70, 55.00, 5)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Sample staff
INSERT INTO staff_barbers (id, tenant_id, name, title, bio, specialties, working_hours) VALUES
(1, 1, 'Marcus Cole', 'Master Barber', 'Fifteen years behind the chair specializing in fades and classic cuts.', 'Fades, Tapers', JSON_OBJECT(
  'mon', JSON_OBJECT('open','09:00','close','18:00'),
  'tue', JSON_OBJECT('open','09:00','close','18:00'),
  'wed', JSON_OBJECT('open','09:00','close','18:00'),
  'thu', JSON_OBJECT('open','09:00','close','19:00'),
  'fri', JSON_OBJECT('open','09:00','close','19:00'),
  'sat', JSON_OBJECT('open','08:00','close','16:00'),
  'sun', NULL
)),
(2, 1, 'Jordan Lee', 'Senior Barber', 'Beard sculpting and modern texture work.', 'Beards, Texture', JSON_OBJECT(
  'mon', JSON_OBJECT('open','10:00','close','18:00'),
  'tue', JSON_OBJECT('open','10:00','close','18:00'),
  'wed', JSON_OBJECT('open','10:00','close','18:00'),
  'thu', JSON_OBJECT('open','10:00','close','18:00'),
  'fri', JSON_OBJECT('open','10:00','close','19:00'),
  'sat', JSON_OBJECT('open','09:00','close','15:00'),
  'sun', NULL
))
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO staff_services (staff_id, service_id) VALUES
(1, 1), (1, 2), (1, 5),
(2, 1), (2, 3), (2, 4), (2, 5)
ON DUPLICATE KEY UPDATE staff_id = VALUES(staff_id);

-- Pre-loaded stock styles (18 entries) — Unsplash source images
DELETE FROM style_gallery WHERE tenant_id = 1 AND is_active_stock = 1;

INSERT INTO style_gallery (tenant_id, style_name, category_tag, image_url, linked_service_id, is_active_stock, display_order) VALUES
(1, 'Low Skin Fade', 'Haircut', 'https://images.unsplash.com/photo-1621605815971-fbc98d665033?w=800&q=80', 2, 1, 1),
(1, 'Classic Pompadour', 'Haircut', 'https://images.unsplash.com/photo-1503951914875-452162b0f3f1?w=800&q=80', 1, 1, 2),
(1, 'Tailored Beard Trim', 'Beard', 'https://images.unsplash.com/photo-1622286342621-4bd786c2447c?w=800&q=80', 3, 1, 3),
(1, 'Mid Fade Crop', 'Haircut', 'https://images.unsplash.com/photo-1599351431202-1e0f013fd1c4?w=800&q=80', 2, 1, 4),
(1, 'Textured Quiff', 'Haircut', 'https://images.unsplash.com/photo-1493256338651-d82f77dd9500?w=800&q=80', 1, 1, 5),
(1, 'Full Beard Sculpt', 'Beard', 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=800&q=80', 3, 1, 6),
(1, 'Taper Fade', 'Haircut', 'https://images.unsplash.com/photo-1605497788044-5a32c7078486?w=800&q=80', 2, 1, 7),
(1, 'Side Part Classic', 'Haircut', 'https://images.unsplash.com/photo-1519340241574-2cec6aef0c01?w=800&q=80', 1, 1, 8),
(1, 'Buzz Cut Clean', 'Haircut', 'https://images.unsplash.com/photo-1567894340315-735d7c361db0?w=800&q=80', 1, 1, 9),
(1, 'Kids Soft Fade', 'Kid', 'https://images.unsplash.com/photo-1503454537195-1dcabb73ffb9?w=800&q=80', 4, 1, 10),
(1, 'Mustache Detail', 'Beard', 'https://images.unsplash.com/photo-1480455624313-e29bce8e054b?w=800&q=80', 3, 1, 11),
(1, 'Curly Top Fade', 'Haircut', 'https://images.unsplash.com/photo-1519085360753-af0119f7cbe7?w=800&q=80', 2, 1, 12),
(1, 'French Crop', 'Haircut', 'https://images.unsplash.com/photo-1531384441138-2736e62e0919?w=800&q=80', 1, 1, 13),
(1, 'Line-Up Sharp', 'Haircut', 'https://images.unsplash.com/photo-1463453091185-61582044d556?w=800&q=80', 1, 1, 14),
(1, 'Combo Cut + Beard', 'Haircut', 'https://images.unsplash.com/photo-1521119989659-a352ef753430?w=800&q=80', 5, 1, 15),
(1, 'Undercut Modern', 'Haircut', 'https://images.unsplash.com/photo-1552374196-c4e2ffcff87b?w=800&q=80', 2, 1, 16),
(1, 'Kids Bowl Refresh', 'Kid', 'https://images.unsplash.com/photo-1476703993599-0035a21b17a9?w=800&q=80', 4, 1, 17),
(1, 'Stubble Shape-Up', 'Beard', 'https://images.unsplash.com/photo-1560250097-0b93528c311a?w=800&q=80', 3, 1, 18);
