-- Database schema for servo remote control system

-- Table to store command queue
CREATE TABLE IF NOT EXISTS servo_commands (
  command_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(20) NOT NULL,
  command_type TINYINT UNSIGNED NOT NULL COMMENT '1=OPEN_DOOR, 2=CLOSE_DOOR, etc',
  target_position SMALLINT DEFAULT NULL COMMENT 'Analog position 0-4095',
  pulse_width SMALLINT UNSIGNED DEFAULT NULL COMMENT 'PWM pulse width in microseconds',
  metadata VARCHAR(255) DEFAULT NULL COMMENT 'Additional parameters as JSON',
  
  status ENUM('pending', 'executing', 'success', 'failed', 'timeout', 'invalid_param') DEFAULT 'pending',
  
  actual_position SMALLINT DEFAULT NULL COMMENT 'Final position reached',
  execution_time_ms INT UNSIGNED DEFAULT NULL COMMENT 'Time taken to execute',
  error_message VARCHAR(255) DEFAULT NULL,
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  started_at TIMESTAMP NULL DEFAULT NULL,
  completed_at TIMESTAMP NULL DEFAULT NULL,
  
  INDEX idx_device_status (device_id, status),
  INDEX idx_created (created_at),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table to log all command executions (audit trail)
CREATE TABLE IF NOT EXISTS servo_command_log (
  log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(20) NOT NULL,
  command_id INT UNSIGNED NOT NULL,
  command_type TINYINT UNSIGNED NOT NULL,
  
  status ENUM('pending', 'executing', 'success', 'failed', 'timeout', 'invalid_param'),
  actual_position SMALLINT DEFAULT NULL,
  execution_time_ms INT UNSIGNED DEFAULT NULL,
  error_message VARCHAR(255) DEFAULT NULL,
  
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_device_time (device_id, timestamp),
  INDEX idx_command (command_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table to store current servo status (updated by diagnostics)
CREATE TABLE IF NOT EXISTS servo_status (
  device_id VARCHAR(20) PRIMARY KEY,
  
  door_position SMALLINT NOT NULL,
  door_health FLOAT NOT NULL,
  door_calibrated BOOLEAN DEFAULT FALSE,
  door_closed_analog SMALLINT UNSIGNED,
  door_open_analog SMALLINT UNSIGNED,
  
  latch_position SMALLINT NOT NULL,
  latch_health FLOAT NOT NULL,
  latch_calibrated BOOLEAN DEFAULT FALSE,
  latch_closed_analog SMALLINT UNSIGNED,
  latch_open_analog SMALLINT UNSIGNED,
  
  remote_control_enabled BOOLEAN DEFAULT FALSE,
  
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_last_update (last_update)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Command type reference (for documentation)
-- 1  = CMD_OPEN_DOOR
-- 2  = CMD_CLOSE_DOOR
-- 3  = CMD_OPEN_LATCH
-- 4  = CMD_CLOSE_LATCH
-- 5  = CMD_MOVE_DOOR_TO_POSITION
-- 6  = CMD_MOVE_LATCH_TO_POSITION
-- 7  = CMD_SET_DOOR_CLOSED_POS
-- 8  = CMD_SET_DOOR_OPEN_POS
-- 9  = CMD_SET_LATCH_CLOSED_POS
-- 10 = CMD_SET_LATCH_OPEN_POS
-- 11 = CMD_CALIBRATE_DOOR
-- 12 = CMD_CALIBRATE_LATCH
-- 13 = CMD_FULL_CALIBRATION
-- 14 = CMD_RESET_TO_DEFAULTS

-- Example: Insert test command
-- INSERT INTO servo_commands (device_id, command_type, target_position)
-- VALUES ('FCE8C04AB440', 5, 1500);