agora/contoso_hypermarket/charts/cerebral-api/templates/mssql-setup.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: mssql-setup-script
  namespace: contoso-hypermarket
data:
  setup-sql.sh: |
    #!/bin/bash
  
    # Variables
    DB_HOST="mssql-service"
    DB_USER="SA"
    DB_PASS="$SA_PASSWORD"
    DB_NAME="RetailDB"

    # Wait until the database is ready
    until /opt/mssql-tools/bin/sqlcmd -S $DB_HOST -U $DB_USER -P $DB_PASS -Q "SELECT 1" &> /dev/null
    do
      echo "Waiting for the database to be ready..."
      sleep 30s
    done

    # Create the database
    echo "Database is ready. Creating the database $DB_NAME..."
    /opt/mssql-tools/bin/sqlcmd -S $DB_HOST -U $DB_USER -P $DB_PASS -Q "CREATE DATABASE $DB_NAME"

    # Wait 10 seconds before proceeding
    sleep 10s

    # Create the script.sql file with database structure and data insertion
    cat << EOF > script.sql
    USE $DB_NAME;

    -- Stores table
    CREATE TABLE Stores (
      store_id VARCHAR(10) PRIMARY KEY,
      city VARCHAR(100),
      state VARCHAR(50),
      country VARCHAR(100)
    );

    -- Products table
    CREATE TABLE Products (
      product_id VARCHAR(50) PRIMARY KEY,
      name VARCHAR(200),
      category VARCHAR(100),
      price_min DECIMAL(10,2),
      price_max DECIMAL(10,2),
      stock INT,
      photo_path VARCHAR(500)
    );

    -- Inventory table
    CREATE TABLE Inventory (
      id INT IDENTITY(1,1) PRIMARY KEY,
      date_time DATETIME2,
      store_id VARCHAR(10),
      product_id VARCHAR(50),
      retail_price DECIMAL(10,2),
      in_stock INT,
      reorder_threshold INT,
      last_restocked DATETIME2,
      FOREIGN KEY (store_id) REFERENCES Stores(store_id),
      FOREIGN KEY (product_id) REFERENCES Products(product_id)
    );

    -- Sales table
    CREATE TABLE Sales (
      id INT IDENTITY(1,1) PRIMARY KEY,
      sale_id VARCHAR(50),
      sale_date DATETIME2,
      store_id VARCHAR(10),
      store_city VARCHAR(100),
      product_id VARCHAR(50),
      product_category VARCHAR(100),
      product_name VARCHAR(200),
      price DECIMAL(10,2),
      discount DECIMAL(5,2),
      quantity INT,
      item_total DECIMAL(10,2),
      profit DECIMAL(10,2),
      payment_method VARCHAR(50),
      customer_id VARCHAR(50),
      register_id VARCHAR(20),
      FOREIGN KEY (store_id) REFERENCES Stores(store_id),
      FOREIGN KEY (product_id) REFERENCES Products(product_id)
    );

    -- Device Metrics table
    CREATE TABLE DeviceMetrics (
      id INT IDENTITY(1,1) PRIMARY KEY,
      timestamp DATETIME2,
      device_id VARCHAR(50),
      equipment_type VARCHAR(50),
      metric_name VARCHAR(100),
      metric_value DECIMAL(18,4),
      metric_unit VARCHAR(20)
    );

    -- Insert initial store data
    INSERT INTO Stores (store_id, city, state, country) VALUES
      ('CHI', 'Chicago', 'IL', 'United States'),
      ('SEA', 'Seattle', 'WA', 'United States'),
      ('NYC', 'New York City', 'NY', 'United States'),
      ('DAL', 'Dallas', 'TX', 'United States'),
      ('ATL', 'Atlanta', 'GA', 'United States'),
      ('LAS', 'Las Vegas', 'NV', 'United States'),
      ('MIA', 'Miami', 'FL', 'United States'),
      ('LAX', 'Los Angeles', 'CA', 'United States');

    -- Insert initial product data
    INSERT INTO Products (product_id, name, price_min, price_max, stock, photo_path, category) VALUES
      ('1', 'Red Apple', 0.2, 0.4, 1000, 'static/img/product1.jpg', 'Fruits'),
      ('2', 'Banana', 0.15, 0.3, 2500, 'static/img/product2.jpg', 'Fruits'),
      ('3', 'Avocado', 0.7, 2.0, 500, 'static/img/product3.jpg', 'Vegetables'),
      ('4', 'Bread', 3.0, 5.0, 200, 'static/img/product4jpg', 'Bakery'),
      ('5', 'Milk', 2.5, 4.5, 200, 'static/img/product5.jpg', 'Dairy'),
      ('6', 'Orange Juice', 3.25, 4.75, 250, 'static/img/product6.jpg', 'Beverages'),
      ('7', 'Chips', 0.25, 0.75, 1000, 'static/img/product7.jpg', 'Snacks'),
      ('8', 'Red Pepper', 0.5, 1.5, 300, 'static/img/product8.jpg', 'Vegetables'),
      ('9', 'Lettuce', 0.2, 0.4, 200, 'static/img/product9.jpg', 'Vegetables'),
      ('10', 'Tomato', 0.3, 0.6, 2000, 'static/img/product10.jpg', 'Vegetables'),
      ('11', 'Strawberry', 2.0, 4.0, 1500, 'static/img/product11.jpg', 'Fruits'),
      ('12', 'Eggs', 3.0, 6.0, 1000, 'static/img/product12.jpg', 'Poultry');

    -- Generate initial inventory for each store and product
    INSERT INTO Inventory (date_time, store_id, product_id, retail_price, in_stock, reorder_threshold, last_restocked)
    SELECT 
      GETDATE() as date_time,
      s.store_id,
      p.product_id,
      (p.price_min + ((p.price_max - p.price_min) * 0.5)) as retail_price,
      p.stock as in_stock,
      FLOOR(p.stock * 0.2) as reorder_threshold,
      GETDATE() as last_restocked
    FROM Stores s
    CROSS JOIN Products p;

    -- Create indices for better performance
    CREATE INDEX IX_Inventory_DateTime ON Inventory(date_time);
    CREATE INDEX IX_Sales_DateTime ON Sales(sale_date);
    CREATE INDEX IX_DeviceMetrics_DateTime ON DeviceMetrics(timestamp);
    CREATE INDEX IX_DeviceMetrics_Device ON DeviceMetrics(device_id, equipment_type);
    EOF

    # Run the script file
    /opt/mssql-tools/bin/sqlcmd -S $DB_HOST -U $DB_USER -P $DB_PASS -i script.sql

    echo "Database setup and configuration completed."


---

apiVersion: batch/v1
kind: Job
metadata:
  name: mssql-setup-job
  namespace: contoso-hypermarket
spec:
  template:
    spec:
      containers:
      - name: mssql-job
        image: mcr.microsoft.com/mssql-tools
        command: ["/bin/bash", "-c"]
        args: ["cp /mssql/data/setup-sql.sh /tmp/setup-sql.sh && chmod +x /tmp/setup-sql.sh && /tmp/setup-sql.sh"]
        volumeMounts:
        - name: script-volume
          mountPath: /mssql/data
          readOnly: true
        - name: tmp-volume
          mountPath: /tmp
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: azure-sqlpassword-secret
              key: azure-sqlpassword-secret
      restartPolicy: Never
      volumes:
      - name: script-volume
        configMap:
          name: mssql-setup-script
      - name: tmp-volume
        emptyDir: {}
  backoffLimit: 4