-- ------------------------------------------------------------
-- Design Core v2 patch
-- Adds design grouping, multi-store warning/admin approval infrastructure.
-- MySQL 5.7 compatible. Does not drop existing data.
-- ------------------------------------------------------------

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='design_group_id')=0,
  'ALTER TABLE design_requests ADD COLUMN design_group_id BIGINT UNSIGNED NULL AFTER created_product_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='sample_no')=0,
  'ALTER TABLE design_requests ADD COLUMN sample_no VARCHAR(40) NULL AFTER design_group_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='sample_status')=0,
  'ALTER TABLE design_requests ADD COLUMN sample_status VARCHAR(40) NULL AFTER sample_no',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS design_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_no VARCHAR(30) NOT NULL UNIQUE,
  group_title VARCHAR(190) NOT NULL,
  normalized_source_url TEXT NULL,
  source_url TEXT NULL,
  reference_design_request_id BIGINT UNSIGNED NULL,
  product_type_code VARCHAR(80) NULL,
  designer_user_id INT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  drawing_cost_actual_usd DECIMAL(10,2) NULL,
  sizing_cost_actual_usd DECIMAL(10,2) NULL,
  drawing_charge_total_usd DECIMAL(10,2) NULL DEFAULT 30.00,
  sizing_charge_total_usd DECIMAL(10,2) NULL DEFAULT 20.00,
  sample_no VARCHAR(40) NULL,
  sample_status VARCHAR(40) NULL,
  sample_note TEXT NULL,
  created_product_id INT UNSIGNED NULL,
  admin_note TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_design_groups_status (status),
  INDEX idx_design_groups_reference (reference_design_request_id),
  INDEX idx_design_groups_designer (designer_user_id),
  CONSTRAINT fk_design_groups_reference FOREIGN KEY (reference_design_request_id) REFERENCES design_requests(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_groups_designer FOREIGN KEY (designer_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_groups_product FOREIGN KEY (created_product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_design_groups_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS design_group_members (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  design_request_id BIGINT UNSIGNED NOT NULL,
  store_id INT UNSIGNED NOT NULL,
  share_drawing_usd DECIMAL(10,2) NULL,
  share_sizing_usd DECIMAL(10,2) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_design_group_request (design_request_id),
  INDEX idx_design_group_members_group (group_id),
  INDEX idx_design_group_members_store (store_id),
  CONSTRAINT fk_design_group_members_group FOREIGN KEY (group_id) REFERENCES design_groups(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_design_group_members_request FOREIGN KEY (design_request_id) REFERENCES design_requests(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_design_group_members_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND INDEX_NAME='idx_design_requests_group')=0,
  'ALTER TABLE design_requests ADD INDEX idx_design_requests_group (design_group_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
