-- schema.sql
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('SUPERADMIN','KOORDINATOR','PRODUKTADMIN','UTLEVERINGSADMIN','RAPPORT') NOT NULL DEFAULT 'SUPERADMIN',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(120) NOT NULL UNIQUE,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  category ENUM('CLOTHING','BALL','EQUIPMENT','SHOES') NOT NULL,
  description TEXT NULL,
  image_url VARCHAR(500) NULL,
  base_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  logo_policy ENUM('REQUIRED','OPTIONAL','NOT_ALLOWED') NOT NULL DEFAULT 'NOT_ALLOWED',
  name_policy ENUM('REQUIRED','OPTIONAL','NOT_ALLOWED') NOT NULL DEFAULT 'NOT_ALLOWED',
  max_name_chars INT NULL,
  name_position ENUM('CHEST','BACK','SLEEVE','LEG','FRONT','CAP') NULL,
  allow_print TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_attribute_values (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  attribute_code ENUM('MODEL','SIZE','COLOR','OTHER') NOT NULL,
  value_code VARCHAR(60) NOT NULL,
  value_label VARCHAR(120) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_variants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  sku VARCHAR(120) NOT NULL,
  model_code VARCHAR(60) NULL,
  size_code VARCHAR(60) NULL,
  color_code VARCHAR(60) NULL,
  price_override DECIMAL(10,2) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_product_sku (product_id, sku),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS print_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(190) NULL,
  default_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  placement ENUM('CHEST','BACK','SLEEVE','LEG','FRONT','CAP') NOT NULL,
  max_chars INT NULL,
  allow_dynamic_text TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_print_profile (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  print_profile_id INT NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 0,
  price_override DECIMAL(10,2) NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (print_profile_id) REFERENCES print_profiles(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_prod_profile (product_id, print_profile_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
