
CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  uid INT NULL,
  action VARCHAR(50) NOT NULL,
  entity VARCHAR(50) NULL,
  entity_id VARCHAR(50) NULL,
  meta JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (action), INDEX (entity)
);

CREATE TABLE IF NOT EXISTS wms_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  barcode VARCHAR(64) UNIQUE NULL,
  category VARCHAR(60) NULL,
  is_expiry_tracked TINYINT(1) DEFAULT 0,
  unit VARCHAR(20) DEFAULT 'EA',
  img_original VARCHAR(255) NULL,
  img_small VARCHAR(255) NULL,
  img_medium VARCHAR(255) NULL,
  img_large VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS wms_inbound (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  location_id INT NULL,
  qty INT NOT NULL,
  expiry_date DATE NULL,
  note VARCHAR(255) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS wms_outbound (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  location_id INT NULL,
  qty INT NOT NULL,
  reason VARCHAR(60) NULL,
  note VARCHAR(255) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS wms_stock (
  product_id INT PRIMARY KEY,
  qty INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS hr_positions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(40) NOT NULL UNIQUE,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS hr_employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_no VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(60) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(120) UNIQUE NULL,
  hire_date DATE NOT NULL,
  employment_type ENUM('정규직','파트','알바') NOT NULL,
  position_id INT NULL,
  base_salary DECIMAL(12,2) DEFAULT 0,
  hourly_wage DECIMAL(10,2) DEFAULT 0,
  bank_name VARCHAR(40) NULL,
  bank_account VARCHAR(64) NULL,
  address VARCHAR(255) NULL,
  is_active ENUM('재직','휴직','퇴직') DEFAULT '재직',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS hr_timesheets (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  work_date DATE NOT NULL,
  start_time TIME NULL,
  end_time TIME NULL,
  night_minutes INT DEFAULT 0,
  overtime_minutes INT DEFAULT 0,
  holiday_minutes INT DEFAULT 0,
  memo VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (employee_id, work_date)
);
CREATE TABLE IF NOT EXISTS hr_payroll (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  period VARCHAR(7) NOT NULL,
  base_pay DECIMAL(12,2) DEFAULT 0,
  overtime_pay DECIMAL(12,2) DEFAULT 0,
  night_pay DECIMAL(12,2) DEFAULT 0,
  holiday_pay DECIMAL(12,2) DEFAULT 0,
  weekly_holiday_allowance DECIMAL(12,2) DEFAULT 0,
  allowances DECIMAL(12,2) DEFAULT 0,
  deductions DECIMAL(12,2) DEFAULT 0,
  four_insurance_employee DECIMAL(12,2) DEFAULT 0,
  four_insurance_employer DECIMAL(12,2) DEFAULT 0,
  income_tax DECIMAL(12,2) DEFAULT 0,
  net_pay DECIMAL(12,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (employee_id, period)
);
INSERT IGNORE INTO hr_positions (name, sort_order) VALUES ('점장',10),('매니저',20),('스태프',30);

CREATE TABLE IF NOT EXISTS studio_modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  slug VARCHAR(60) NOT NULL UNIQUE,
  table_name VARCHAR(60) NOT NULL,
  pk VARCHAR(60) DEFAULT 'id',
  with_timestamps TINYINT(1) DEFAULT 1,
  auth_role VARCHAR(30) DEFAULT '점주',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS studio_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  field_name VARCHAR(60) NOT NULL,
  label VARCHAR(60) NOT NULL,
  input_type VARCHAR(30) NOT NULL,
  is_required TINYINT(1) DEFAULT 0,
  default_value VARCHAR(255) DEFAULT NULL,
  options_json TEXT DEFAULT NULL,
  list_visible TINYINT(1) DEFAULT 1,
  searchable TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0
);
CREATE TABLE IF NOT EXISTS studio_action_catalog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  action_code VARCHAR(60) UNIQUE NOT NULL,
  action_label VARCHAR(100) NOT NULL,
  icon VARCHAR(40) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  event_allowed SET('before_save','after_save','after_create','before_delete','after_delete') NOT NULL,
  param_schema JSON NOT NULL,
  handler_class VARCHAR(120) NOT NULL,
  is_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS studio_module_actions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  event VARCHAR(40) NOT NULL,
  action_code VARCHAR(60) NOT NULL,
  payload JSON NOT NULL,
  sort_order INT DEFAULT 0,
  is_enabled TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings_menu (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_id INT NULL,
  label VARCHAR(60) NOT NULL,
  url VARCHAR(180) NULL,
  icon VARCHAR(40) NULL,
  role_min ENUM('점주','점장','스토어매니저') NOT NULL DEFAULT '스토어매니저',
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
);
INSERT IGNORE INTO settings_menu (id,parent_id,label,url,icon,role_min,sort_order,is_active) VALUES
(1,NULL,'WMS',NULL,'bi-box-seam','스토어매니저',10,1),
(2,1,'상품마스터','/gs25/public/wms/products.php',NULL,'스토어매니저',10,1),
(3,1,'입고관리','/gs25/public/wms/inbound.php',NULL,'스토어매니저',20,1),
(4,1,'출고관리','/gs25/public/wms/outbound.php',NULL,'스토어매니저',30,1),
(5,1,'재고현황','/gs25/public/wms/stock.php',NULL,'점장',40,1),
(10,NULL,'HR',NULL,'bi-people','점장',20,1),
(11,10,'사원관리','/gs25/public/hr/employees.php',NULL,'점장',10,1),
(12,10,'직급 관리','/gs25/public/hr/positions.php',NULL,'점장',20,1),
(13,10,'근태기록','/gs25/public/hr/timesheets.php',NULL,'점장',30,1),
(14,10,'급여마법사','/gs25/public/hr/payroll.php',NULL,'점장',40,1),
(20,NULL,'관리자',NULL,'bi-gear','점주',30,1),
(21,20,'Feature Studio','/gs25/public/admin/studio.php',NULL,'점주',10,1),
(22,20,'설정','/gs25/public/admin/settings.php',NULL,'점주',20,1),
(23,20,'메뉴 관리','/gs25/public/admin/menu.php',NULL,'점주',30,1),
(24,20,'사용자 역할','/gs25/public/admin/users_roles.php',NULL,'점주',40,1);

CREATE TABLE IF NOT EXISTS wms_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  type ENUM('STORE','BACKROOM','WAREHOUSE','ZONE','BIN') DEFAULT 'STORE',
  parent_id INT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT IGNORE INTO wms_locations (code,name,type) VALUES ('STORE','매장','STORE'),('BACK','창고','BACKROOM');

CREATE TABLE IF NOT EXISTS wms_stock_locations (
  product_id INT NOT NULL,
  location_id INT NOT NULL,
  qty INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id, location_id)
);

CREATE TABLE IF NOT EXISTS wms_moves (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  from_location_id INT NULL,
  to_location_id INT NULL,
  qty INT NOT NULL,
  note VARCHAR(255) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SET @sql := IF(
  @exists = 0,
  'ALTER TABLE wms_inbound ADD CONSTRAINT fk_in_prod FOREIGN KEY (product_id) REFERENCES wms_products(id) ON UPDATE CASCADE ON DELETE RESTRICT;',
  'SELECT "fk_in_prod already exists";'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @exists = 0,
  'ALTER TABLE wms_inbound ADD CONSTRAINT fk_in_loc FOREIGN KEY (location_id) REFERENCES wms_locations(id) ON UPDATE CASCADE ON DELETE SET NULL;',
  'SELECT "fk_in_loc already exists";'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @exists = 0,
  'ALTER TABLE wms_outbound ADD CONSTRAINT fk_out_prod FOREIGN KEY (product_id) REFERENCES wms_products(id) ON UPDATE CASCADE ON DELETE RESTRICT;',
  'SELECT "fk_out_prod already exists";'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(
  @exists = 0,
  'ALTER TABLE wms_outbound ADD CONSTRAINT fk_out_loc FOREIGN KEY (location_id) REFERENCES wms_locations(id) ON UPDATE CASCADE ON DELETE SET NULL;',
  'SELECT "fk_out_loc already exists";'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;